Hi, I've tested MySQL 4.0.x and 4.1.x under a double-Opteron server with 4G RAM. The disk is a plain IDE (dmesg and diskinfo are attached, kernel is just SMP). Here my tests, using my-large.cnf: 1. FreeBSD 5.4-STABLE from June 1st mysql-server-4.0.22 from ports with no options root_at_antivirus-1#/usr/local/src/super-smack-1.3(14:38:16)> for i in 1 2 3 4 ; do super-smack -d mysql /usr/share/smacks/select-key.smack 50 1000 ; done | grep select select_index 100000 3 0 17030.08 select_index 100000 3 0 17076.08 select_index 100000 7 0 16908.59 select_index 100000 7 0 16769.76 2. same as 1, but mysql-4.1.14 default port compile ... Performance increase for i in 1 2 3 4 ; do super-smack -d mysql /usr/share/smacks/select-key.smack 50 1000 ; done | grep select select_index 100000 5 0 17893.95 select_index 100000 2 0 18728.95 select_index 100000 9 0 18537.49 select_index 100000 5 0 18496.73 3. same as 2. but with -stable from october 3rd 2005 no change. 4. same as 3. but with 6.0-BETA5 from october 4th 2005 mysql41 recompiled select_index 100000 2 0 18770.87 select_index 100000 3 0 18727.82 select_index 100000 2 0 18781.72 select_index 100000 4 0 18606.72 5. same as 4. but with SCHED_ULE instead of SCHED_4BSD select_index 100000 2 0 22951.84 select_index 100000 4 0 20193.24 select_index 100000 4 0 20103.91 select_index 100000 4 0 20773.52 select_index 100000 5 0 20110.09 Nevertheless I run a production MySQL on a double-Xeon ( Supermicro X5DPR-iG2+ , 4G RAM ) on 5.4-stable, that had continous crashes (Out of memory, every 5 minutes restarts, coredumps, "you hit a bug" sort of message, etc) even with different compiler options , until I recompiled with Linuxthreads. THe error log has been completely silent since then (one week ago). The my.cnf is basically my-large.cnf plus wait_timeout 120 and max_connections 1000, and the tables are all MyISAM. It requires between 400 and 600 threads in memory, and it handles 500 to 700 queries per/second. I couldn't reproduce this config under the double-Opteron because linuxthreads is not available from ports on amd64. I think this has to do with the nature of the queries in a real-world scenario, super-smack hammers MySQL in only one way , but in my case we got at least five completely different applications with very variable requirements (e.g. sort a 10-mail mailbox by subject, then do the same but with 10.000 mails, etc) which eventually kill MySQL with FreeBSD native threads. Same sort of continuous crash under a single-CPU scenario, same apps, 4G RAM and FreeBSD-6.0-RC1. Any ideas ? Regards, -Carlos Robert Watson wrote: > > On Wed, 26 Oct 2005, Marian Hettwer wrote: > >>>> my.cnf is pretty much my-medium.cnf on Debian and FreeBSD. >>>> Although, Debian has MySQL 4.1.11 and FreeBSD Ports has MySQL >>>> 4.1.14. As mentioned before, this benchmark here is a quick shot. >>>> However, the difference shouldn't be _that_ large (3600 vs. 14000). >>> >>> >>> I think you will find people generally agree with this viewpoint. :-) >>> >> Ack. the difference is that huge... well, for some reasons I don't >> know, I think a look into Linux' way of threading may be worth, hm? > > > By the above, I mean that people will generally agree with the > viewpoint that the difference is huge, and shouldn't be. > > The reason I suggested you try libthr is twofold: > > (1) libthr has undergone more performance optimization for MySQL -- in > fact, MySQL has been one of the specific workload targets for libthr. > > (2) libthr uses a threading model more like the Linux model, and since > MySQL has been heavily optimized for Linux, that also helps out. > Note > performance optimization for applications is often one by looking at > what the underlying OS does badly, and what it does well, and > modifying the application to use more features where it does well, > and > fewer where it does badly. This means, for example, that if Linux is > optimized to support a small number of threads, and FreeBSD is > optimized for a large number of threads, that MySQL developers who > are > targetting Linux will focus on having a small number of threads. > This is an interesting property of plugging lots of parts together, > and can't be ignored... It goes both ways, of course. > > Robert N M Watson > _______________________________________________ > freebsd-current_at_freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-current > To unsubscribe, send any mail to > "freebsd-current-unsubscribe_at_freebsd.org" ------------------------------------------------------------- Conectate a Turbo Internet Gratis en Buenos Aires - 5500-5500 Entra a http://free.internet.argentina.com/promointernet/ MySQL supersmack 1.3 root_at_antivirus-1#/usr/src(15:20:23)> diskinfo -tv /dev/ad0 /dev/ad0 512 # sectorsize 200049647616 # mediasize in bytes (186G) 390721968 # mediasize in sectors 387621 # Cylinders according to firmware. 16 # Heads according to firmware. 63 # Sectors according to firmware. Seek times: Full stroke: 250 iter in 9.750260 sec = 39.001 msec Half stroke: 250 iter in 4.380025 sec = 17.520 msec Quarter stroke: 500 iter in 6.612929 sec = 13.226 msec Short forward: 400 iter in 2.848763 sec = 7.122 msec Short backward: 400 iter in 5.099140 sec = 12.748 msec Seq outer: 2048 iter in 0.689416 sec = 0.337 msec Seq inner: 2048 iter in 3.312997 sec = 1.618 msec Transfer rates: outside: 102400 kbytes in 2.481251 sec = 41270 kbytes/sec middle: 102400 kbytes in 5.514508 sec = 18569 kbytes/sec inside: 102400 kbytes in 20.607294 sec = 4969 kbytes/sec dmesg: Copyright (c) 1992-2005 The FreeBSD Project. Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994 The Regents of the University of California. All rights reserved. FreeBSD 5.4-STABLE #0: Wed Jun 1 03:41:22 ART 2005 root_at_antivirus-1.argentina.com:/usr/obj/usr/src/sys/AMDBOLA2 Timecounter "i8254" frequency 1193182 Hz quality 0 CPU: AMD Opteron(tm) Processor 246 (1991.48-MHz K8-class CPU) Origin = "AuthenticAMD" Id = 0xf5a Stepping = 10 Features=0x78bfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,MMX, FXSR,SSE,SSE2> AMD Features=0xe0500800<SYSCALL,NX,MMX+,LM,3DNow+,3DNow> real memory = 4227792896 (4031 MB) avail memory = 4080869376 (3891 MB) ACPI APIC Table: <A M I OEMAPIC > FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs cpu0 (BSP): APIC ID: 0 cpu1 (AP): APIC ID: 1 MADT: Forcing active-low polarity and level trigger for SCI ioapic0 <Version 1.1> irqs 0-23 on motherboard ioapic1 <Version 1.1> irqs 24-27 on motherboard ioapic2 <Version 1.1> irqs 28-31 on motherboard acpi0: <A M I OEMRSDT> on motherboard acpi0: Power Button (fixed) Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000 acpi_timer0: <24-bit timer at 3.579545MHz> port 0x1008-0x100b on acpi0 cpu0: <ACPI CPU> on acpi0 acpi_throttle0: <ACPI CPU Throttling> on cpu0 cpu1: <ACPI CPU> on acpi0 pcib0: <ACPI Host-PCI bridge> port 0xcf8-0xcff on acpi0 pci0: <ACPI PCI bus> on pcib0 pcib1: <ACPI PCI-PCI bridge> at device 6.0 on pci0 pci3: <ACPI PCI bus> on pcib1 ohci0: <OHCI (generic) USB controller> mem 0xfeafc000-0xfeafcfff irq 19 at device 0.0 on pci3 usb0: OHCI version 1.0, legacy support usb0: <OHCI (generic) USB controller> on ohci0 usb0: USB revision 1.0 uhub0: AMD OHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub0: 3 ports with 3 removable, self powered ohci1: <OHCI (generic) USB controller> mem 0xfeafd000-0xfeafdfff irq 19 at device 0.1 on pci3 usb1: OHCI version 1.0, legacy support usb1: <OHCI (generic) USB controller> on ohci1 usb1: USB revision 1.0 uhub1: AMD OHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub1: 3 ports with 3 removable, self powered atapci0: <SiI 3114 SATA150 controller> port 0xa880-0xa88f,0xac00-0xac03,0xb800-0xb807,0xb880-0xb883,0xbc0 0-0xbc07 mem 0xfeafec00-0xfeafefff irq 19 at device 5.0 on pci3 ata2: channel #0 on atapci0 ata3: channel #1 on atapci0 ata4: channel #2 on atapci0 ata5: channel #3 on atapci0 pci3: <display, VGA> at device 6.0 (no driver attached) fxp0: <Intel 82551 Pro/100 Ethernet> port 0xa800-0xa83f mem 0xfeaa0000-0xfeabffff,0xfeafb000-0xfeafbfff i rq 18 at device 8.0 on pci3 miibus0: <MII bus> on fxp0 inphy0: <i82555 10/100 media interface> on miibus0 inphy0: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, auto fxp0: Ethernet address: 00:e0:81:2d:04:97 isab0: <PCI-ISA bridge> at device 7.0 on pci0 isa0: <ISA bus> on isab0 atapci1: <AMD 8111 UDMA133 controller> port 0xffa0-0xffaf,0x376,0x170-0x177,0x3f6,0x1f0-0x1f7 at device 7 .1 on pci0 ata0: channel #0 on atapci1 ata1: channel #1 on atapci1 pci0: <serial bus, SMBus> at device 7.2 (no driver attached) pci0: <bridge> at device 7.3 (no driver attached) pcib2: <ACPI PCI-PCI bridge> at device 10.0 on pci0 pci2: <ACPI PCI bus> on pcib2 bge0: <Broadcom BCM5704C Dual Gigabit Ethernet, ASIC rev. 0x2003> mem 0xfc8b0000-0xfc8bffff,0xfc8c0000-0x fc8cffff irq 24 at device 9.0 on pci2 miibus1: <MII bus> on bge0 brgphy0: <BCM5704 10/100/1000baseTX PHY> on miibus1 brgphy0: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, 1000baseTX, 1000baseTX-FDX, auto bge0: Ethernet address: 00:e0:81:2d:04:c4 bge1: <Broadcom BCM5704C Dual Gigabit Ethernet, ASIC rev. 0x2003> mem 0xfc8e0000-0xfc8effff,0xfc8f0000-0x fc8fffff irq 25 at device 9.1 on pci2 miibus2: <MII bus> on bge1 brgphy1: <BCM5704 10/100/1000baseTX PHY> on miibus2 brgphy1: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, 1000baseTX, 1000baseTX-FDX, auto bge1: Ethernet address: 00:e0:81:2d:04:c5 pci0: <base peripheral, interrupt controller> at device 10.1 (no driver attached) pcib3: <ACPI PCI-PCI bridge> at device 11.0 on pci0 pci1: <ACPI PCI bus> on pcib3 pci0: <base peripheral, interrupt controller> at device 11.1 (no driver attached) acpi_button0: <Power Button> on acpi0 sio0: <16550A-compatible COM port> port 0x3f8-0x3ff irq 4 flags 0x10 on acpi0 sio0: type 16550A sio1: <16550A-compatible COM port> port 0x2f8-0x2ff irq 3 on acpi0 sio1: type 16550A fdc0: <floppy drive controller (FDE)> port 0x3f7,0x3f0-0x3f5 irq 6 drq 2 on acpi0 fd0: <1440-KB 3.5" drive> on fdc0 drive 0 orm0: <ISA Option ROM> at iomem 0xc0000-0xc7fff on isa0 atkbdc0: <Keyboard controller (i8042)> at port 0x64,0x60 on isa0 ppc0: cannot reserve I/O port range sc0: <System console> at flags 0x100 on isa0 sc0: VGA <16 virtual consoles, flags=0x300> vga0: <Generic ISA VGA> at port 0x3c0-0x3df iomem 0xa0000-0xbffff on isa0 Timecounters tick every 1.000 msec ipfw2 initialized, divert disabled, rule-based forwarding disabled, default to accept, logging limited to 100 packets/entry by default ad0: 190782MB <WDC WD2000JB-32EVA0/15.05R15> [387621/16/63] at ata0-master UDMA100 acd0: CDROM <SR244W/T01A> at ata1-master PIO4 SMP: AP CPU #1 Launched! Mounting root from ufs:/dev/ad0s1a 1. FreeBSD 5.4-STABLE from June 1st mysql-server-4.0.22 from ports with no options root_at_antivirus-1#/usr/local/src/super-smack-1.3(14:38:16)> for i in 1 2 3 4 ; do super-smack -d mysql /usr/share/smacks/select-key.smack 50 1000 ; done | grep select select_index 100000 3 0 17030.08 select_index 100000 3 0 17076.08 select_index 100000 7 0 16908.59 select_index 100000 7 0 16769.76 2. same as 1, but mysql-4.1.14 default port compile ... Performance increase for i in 1 2 3 4 ; do super-smack -d mysql /usr/share/smacks/select-key.smack 50 1000 ; done | grep select select_index 100000 5 0 17893.95 select_index 100000 2 0 18728.95 select_index 100000 9 0 18537.49 select_index 100000 5 0 18496.73 3. same as 2. but with -stable from october 3rd 2005 no change. 4. same as 3. but with 6.0-BETA5 from october 4th 2005 mysql41 recompiled select_index 100000 2 0 18770.87 select_index 100000 3 0 18727.82 select_index 100000 2 0 18781.72 select_index 100000 4 0 18606.72 5. same as 4. but with SCHED_ULE instead of SCHED_4BSD select_index 100000 2 0 22951.84 select_index 100000 4 0 20193.24 select_index 100000 4 0 20103.91 select_index 100000 4 0 20773.52 select_index 100000 5 0 20110.09Received on Wed Oct 26 2005 - 14:41:09 UTC
This archive was generated by hypermail 2.4.0 : Wed May 19 2021 - 11:38:46 UTC