From owner-freebsd-current@FreeBSD.ORG Wed Oct 26 16:41:09 2005 Return-Path: X-Original-To: current@freebsd.org Delivered-To: freebsd-current@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id DDA6D16A41F for ; Wed, 26 Oct 2005 16:41:09 +0000 (GMT) (envelope-from carlos@infodrive.com.ar) Received: from ns-1.net (ns-1.net [200.68.192.242]) by mx1.FreeBSD.org (Postfix) with ESMTP id 1186543D5F for ; Wed, 26 Oct 2005 16:40:59 +0000 (GMT) (envelope-from carlos@infodrive.com.ar) Received: by ns-1.net (Postfix, from userid 65534) id 9D23E62D52B; Wed, 26 Oct 2005 13:40:55 -0300 (ART) Received: from [127.0.0.1] (77-138-89-200.fibertel.com.ar [200.89.138.77]) by ns-1.net (Postfix) with ESMTP id B1BDC62D529 for ; Wed, 26 Oct 2005 13:40:54 -0300 (ART) X-AntiVirus: Checked by Dr.Web [version: 4.32b, engine: 4.32b, virus records: 91867, updated: 26.10.2005] Message-ID: <435FB196.30406@infodrive.com.ar> Date: Wed, 26 Oct 2005 13:40:54 -0300 From: Carlos Horowicz User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: current@freebsd.org References: <435F48DA.6060009@kernel32.de> <20051026105411.L32255@fledge.watson.org> <435F6B01.5020003@kernel32.de> <20051026133606.O32255@fledge.watson.org> <435F7B67.5080709@kernel32.de> <20051026135927.Y32255@fledge.watson.org> In-Reply-To: <20051026135927.Y32255@fledge.watson.org> Content-Type: multipart/mixed; boundary="------------000504060802050809050005" X-SMTP: http://internet.Argentina.com Cc: Subject: Re: MySQL Performance 6.0rc1 X-BeenThere: freebsd-current@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Discussions about the use of FreeBSD-current List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 26 Oct 2005 16:41:10 -0000 This is a multi-part message in MIME format. --------------000504060802050809050005 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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@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@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-current > To unsubscribe, send any mail to > "freebsd-current-unsubscribe@freebsd.org" ------------------------------------------------------------- Conectate a Turbo Internet Gratis en Buenos Aires - 5500-5500 Entra a http://free.internet.argentina.com/promointernet/ --------------000504060802050809050005 Content-Type: text/plain; name="supersmack.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="supersmack.txt" MySQL supersmack 1.3 root@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@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 AMD Features=0xe0500800 real memory = 4227792896 (4031 MB) avail memory = 4080869376 (3891 MB) ACPI APIC Table: 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 irqs 0-23 on motherboard ioapic1 irqs 24-27 on motherboard ioapic2 irqs 28-31 on motherboard acpi0: 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: on acpi0 acpi_throttle0: on cpu0 cpu1: on acpi0 pcib0: port 0xcf8-0xcff on acpi0 pci0: on pcib0 pcib1: at device 6.0 on pci0 pci3: on pcib1 ohci0: mem 0xfeafc000-0xfeafcfff irq 19 at device 0.0 on pci3 usb0: OHCI version 1.0, legacy support usb0: 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: mem 0xfeafd000-0xfeafdfff irq 19 at device 0.1 on pci3 usb1: OHCI version 1.0, legacy support usb1: 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: 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: at device 6.0 (no driver attached) fxp0: port 0xa800-0xa83f mem 0xfeaa0000-0xfeabffff,0xfeafb000-0xfeafbfff i rq 18 at device 8.0 on pci3 miibus0: on fxp0 inphy0: on miibus0 inphy0: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, auto fxp0: Ethernet address: 00:e0:81:2d:04:97 isab0: at device 7.0 on pci0 isa0: on isab0 atapci1: 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: at device 7.2 (no driver attached) pci0: at device 7.3 (no driver attached) pcib2: at device 10.0 on pci0 pci2: on pcib2 bge0: mem 0xfc8b0000-0xfc8bffff,0xfc8c0000-0x fc8cffff irq 24 at device 9.0 on pci2 miibus1: on bge0 brgphy0: on miibus1 brgphy0: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, 1000baseTX, 1000baseTX-FDX, auto bge0: Ethernet address: 00:e0:81:2d:04:c4 bge1: mem 0xfc8e0000-0xfc8effff,0xfc8f0000-0x fc8fffff irq 25 at device 9.1 on pci2 miibus2: on bge1 brgphy1: on miibus2 brgphy1: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, 1000baseTX, 1000baseTX-FDX, auto bge1: Ethernet address: 00:e0:81:2d:04:c5 pci0: at device 10.1 (no driver attached) pcib3: at device 11.0 on pci0 pci1: on pcib3 pci0: at device 11.1 (no driver attached) acpi_button0: 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: port 0x3f7,0x3f0-0x3f5 irq 6 drq 2 on acpi0 fd0: <1440-KB 3.5" drive> on fdc0 drive 0 orm0: at iomem 0xc0000-0xc7fff on isa0 atkbdc0: at port 0x64,0x60 on isa0 ppc0: cannot reserve I/O port range sc0: at flags 0x100 on isa0 sc0: VGA <16 virtual consoles, flags=0x300> vga0: 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 [387621/16/63] at ata0-master UDMA100 acd0: CDROM 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@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 --------------000504060802050809050005--