From owner-freebsd-performance@FreeBSD.ORG Thu Dec 1 04:33:56 2005 Return-Path: X-Original-To: freebsd-performance@freebsd.org Delivered-To: freebsd-performance@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 1FF4616A41F for ; Thu, 1 Dec 2005 04:33:56 +0000 (GMT) (envelope-from gbaratto@superb.net) Received: from master4.yvr1.superb.net (master4.yvr1.superb.net [64.251.80.20]) by mx1.FreeBSD.org (Postfix) with ESMTP id AE2F943D53 for ; Thu, 1 Dec 2005 04:33:55 +0000 (GMT) (envelope-from gbaratto@superb.net) Received: from guinness (100.80.251.64.in-addr.arpa [64.251.80.100] (may be forged)) by master4.yvr1.superb.net (8.12.9/8.12.9) with SMTP id jB14XsaE027254 for ; Wed, 30 Nov 2005 20:33:54 -0800 (PST) Message-ID: <04f401c5f630$b9af6570$6450fb40@guinness> From: "Gustavo A. Baratto" To: Date: Wed, 30 Nov 2005 20:35:52 -0800 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailman-Approved-At: Thu, 01 Dec 2005 04:43:42 +0000 Subject: new benchmarks. WAS: FreeBSD MySQL still WAY slower than Linux X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 01 Dec 2005 04:33:56 -0000 following Steve Roome's benchmarks from a few months ago, here it goes a few more, on freebsd 6.0 RELEASE, using libpthreads (system, and process scopes), libthr, and linuxthreads. Unless otherwise specified, the my.cnf values are the ones posted below. Dmesg, and diff of the kernel compared with GENERIC are posted in the end as well. File systems are mounted as default (no fancy mount opts): ---- # more /etc/fstab # Device Mountpoint FStype Options Dump Pass# /dev/da0s1b none swap sw 0 0 /dev/da0s1a / ufs rw 1 1 /dev/da0s1d /tmp ufs rw 2 2 /dev/da0s1e /var ufs rw 2 2 /dev/da0s1f /home ufs rw 2 2 /dev/acd0 /cdrom cd9660 ro,noauto 0 0 ------- Cheers all ++++ lipthread: standard # date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 16:20:52 EST 2005 select_index 20000 0 0 14570.99 select_index 20000 2 0 14787.32 select_index 20000 3 0 14621.65 select_index 20000 1 0 13554.19 select_index 20000 0 0 13514.42 Wed Nov 30 16:20:59 EST 2005 date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 22:45:37 EST 2005 select_index 20000 1 0 14567.60 select_index 20000 0 0 13745.24 select_index 20000 1 0 13625.50 select_index 20000 0 0 13558.84 select_index 20000 1 0 13460.09 Wed Nov 30 22:45:45 EST 2005 ++++ lipthread: LIBPTHREAD_PROCESS_SCOPE=yes # date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 16:37:16 EST 2005 select_index 20000 2 0 12014.51 select_index 20000 2 0 11991.61 select_index 20000 0 0 10890.61 select_index 20000 1 0 11992.41 select_index 20000 3 0 11775.37 Wed Nov 30 16:37:24 EST 2005 date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 22:49:40 EST 2005 select_index 20000 1 0 11035.18 select_index 20000 4 0 11877.35 select_index 20000 0 0 10097.29 select_index 20000 0 0 10765.29 select_index 20000 1 0 11401.68 Wed Nov 30 22:49:50 EST 2005 ++++ libthr: # more /etc/libmap.conf [mysqld] libpthread.so.2 libthr.so.2 libpthread.so libthr.so # ldd /usr/local/libexec/mysqld /usr/local/libexec/mysqld: libz.so.3 => /lib/libz.so.3 (0x881f8000) libcrypt.so.3 => /lib/libcrypt.so.3 (0x88208000) libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x88220000) libm.so.4 => /lib/libm.so.4 (0x882ea000) libpthread.so.2 => /usr/lib/libthr.so.2 (0x88300000) libc.so.6 => /lib/libc.so.6 (0x88312000) # date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 16:38:45 EST 2005 select_index 20000 0 0 15789.56 select_index 20000 1 0 14804.51 select_index 20000 0 0 14810.69 select_index 20000 0 0 14600.25 select_index 20000 0 0 14478.79 Wed Nov 30 16:38:52 EST 2005 # date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 22:47:26 EST 2005 select_index 20000 1 0 14375.95 select_index 20000 0 0 14392.69 select_index 20000 0 0 14478.93 select_index 20000 0 0 14371.66 select_index 20000 0 0 14416.07 Wed Nov 30 22:47:33 EST 2005 ++++ linuxthreads # ldd /usr/local/libexec/mysqld /usr/local/libexec/mysqld: liblthread.so.3 => /usr/local/lib/liblthread.so.3 (0x88201000) liblstdc++.so.5 => /usr/local/lib/liblstdc++.so.5 (0x88222000) libz.so.3 => /lib/libz.so.3 (0x882ed000) libcrypt.so.3 => /lib/libcrypt.so.3 (0x882fd000) libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x88315000) libm.so.4 => /lib/libm.so.4 (0x883df000) libc.so.6 => /lib/libc.so.6 (0x883f5000) # date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 23:30:24 EST 2005 select_index 20000 0 0 14357.28 select_index 20000 0 0 14055.69 select_index 20000 0 0 13694.37 select_index 20000 1 0 13394.22 select_index 20000 0 0 13723.05 Wed Nov 30 23:30:32 EST 2005 # date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 23:30:38 EST 2005 select_index 20000 2 0 13555.20 select_index 20000 2 0 13500.62 select_index 20000 0 0 13546.00 select_index 20000 0 0 13161.49 select_index 20000 0 0 13510.82 Wed Nov 30 23:30:46 EST 2005 ++++ linuxthreads with query cache disabled (query_cache_size commented out in my.cnf):: # date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 21:59:51 EST 2005 select_index 20000 1 0 8615.98 select_index 20000 3 0 8700.63 select_index 20000 1 0 8590.18 select_index 20000 1 0 8733.90 select_index 20000 1 0 8582.01 Wed Nov 30 22:00:02 EST 2005 ++++ libthr with query cache disabled (query_cache_size commented out in my.cnf): date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 22:52:42 EST 2005 select_index 20000 1 0 9511.13 select_index 20000 1 0 9464.96 select_index 20000 1 0 9229.52 select_index 20000 1 0 9195.97 select_index 20000 1 0 9346.92 Wed Nov 30 22:52:53 EST 2005 ++++ libthr, connecting to a local mysql using TCP, instead of socket # date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 22:57:03 EST 2005 select_index 20000 1 0 9286.62 select_index 20000 2 0 9245.66 select_index 20000 1 0 9126.25 select_index 20000 0 0 9303.66 select_index 20000 1 0 9286.14 Wed Nov 30 22:57:15 EST 2005 ++++ linuxthreads, connecting to a local mysql using TCP, instead of socket # date; for i in 1 2 3 4 5; do super-smack select-key-mysql.smack 10 1000 | grep select_index; done; date Wed Nov 30 23:07:16 EST 2005 select_index 20000 2 0 8137.02 select_index 20000 2 0 8220.83 select_index 20000 3 0 8264.19 select_index 20000 1 0 8268.86 select_index 20000 3 0 8234.24 Wed Nov 30 23:07:28 EST 2005 ++++ my.cnf ---------------- [mysqld] server-id=2 log-bin=/var/log/mysql/binlog log-slow-queries=/var/log/mysql/slow-queries.log relay-log=/var/log/mysql/relay-log relay-log-index=/var/log/mysql/relay-log-index skip-innodb skip-bdb safe-user-create skip-name-resolve skip-log-warnings safe-show-database set-variable = flush_time=86400 set-variable = max_connections=300 set-variable = max_connect_errors=100 set-variable = interactive_timeout=600 set-variable = wait_timeout=60 set-variable = myisam-recover=FORCE set-variable = key_buffer_size=400M set-variable = sort_buffer_size=1M set-variable = read_buffer_size=512k set-variable = table_cache=4096 set-variable = max_delayed_threads=0 set-variable = max_user_connections=100 set-variable = query_cache_size=300M set-variable = thread_cache_size=50 [mysql.server] user=mysql [safe_mysqld] err-log=/var/log/mysql/error.log ------------------------ ++++ DMESG: ---------- # 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 6.0-RELEASE #1: Tue Nov 29 23:36:08 EST 2005 gbaratto@sh-mysql4.dca1.superb.net:/usr/obj/usr/src/sys/GBARATTO Timecounter "i8254" frequency 1193182 Hz quality 0 CPU: Intel(R) Xeon(TM) CPU 3.06GHz (3066.79-MHz 686-class CPU) Origin = "GenuineIntel" Id = 0xf29 Stepping = 9 Features=0xbfebfbff Features2=0x4400> Hyperthreading: 2 logical CPUs real memory = 1073741824 (1024 MB) avail memory = 1041698816 (993 MB) MPTable: FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs cpu0 (BSP): APIC ID: 0 cpu1 (AP): APIC ID: 6 ioapic0: Assuming intbase of 0 ioapic1: Assuming intbase of 24 ioapic2: Assuming intbase of 48 ioapic0 irqs 0-23 on motherboard ioapic1 irqs 24-47 on motherboard ioapic2 irqs 48-71 on motherboard npx0: [FAST] npx0: on motherboard npx0: INT 16 interface cpu0 on motherboard cpu1 on motherboard pcib0: pcibus 0 on motherboard pci0: on pcib0 pcib1: at device 2.0 on pci0 pci2: on pcib1 pci2: at device 28.0 (no driver attached) pcib2: at device 29.0 on pci2 pci5: on pcib2 em0: port 0xa400-0xa43f mem 0xfe9c0000-0xfe9dffff irq 48 at device 1.0 on pci5 em0: Ethernet address: 00:e0:81:2a:48:64 em0: Speed:N/A Duplex:N/A em1: port 0xa800-0xa83f mem 0xfe9e0000-0xfe9fffff irq 49 at device 1.1 on pci5 em1: Ethernet address: 00:e0:81:2a:48:65 em1: Speed:N/A Duplex:N/A pci2: at device 30.0 (no driver attached) pcib3: at device 31.0 on pci2 pci3: on pcib3 asr0: mem 0xfa000000-0xfbffffff irq 24 at device 3.0 on pci3 asr0: [GIANT-LOCKED] asr0: ADAPTEC 2110S FW Rev. 380E, 1 channel, 256 CCBs, Protocol I2O pcib4: at device 3.1 on pci3 pci4: on pcib4 uhci0: port 0xc400-0xc41f irq 16 at device 29.0 on pci0 uhci0: [GIANT-LOCKED] usb0: on uhci0 usb0: USB revision 1.0 uhub0: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub0: 2 ports with 2 removable, self powered uhci1: port 0xc800-0xc81f irq 19 at device 29.1 on pci0 uhci1: [GIANT-LOCKED] usb1: on uhci1 usb1: USB revision 1.0 uhub1: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub1: 2 ports with 2 removable, self powered ehci0: mem 0xfebffc00-0xfebfffff irq 23 at device 29.7 on pci0 ehci0: [GIANT-LOCKED] usb2: waiting for BIOS to give up control usb2: timed out waiting for BIOS usb2: EHCI version 1.0 usb2: companion controllers, 2 ports each: usb0 usb1 usb2: on ehci0 usb2: USB revision 2.0 uhub2: Intel EHCI root hub, class 9/0, rev 2.00/1.00, addr 1 uhub2: 4 ports with 4 removable, self powered pcib5: at device 30.0 on pci0 pci1: on pcib5 fxp0: port 0x9800-0x983f mem 0xfe6fe000-0xfe6fefff,0xfe6a0000-0xfe6bffff irq 17 at device 1.0 on pci1 miibus0: on fxp0 inphy0: on miibus0 inphy0: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, auto fxp0: Ethernet address: 00:e0:81:2a:48:35 pci1: at device 2.0 (no driver attached) isab0: at device 31.0 on pci0 isa0: on isab0 atapci0: port 0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0xfc00-0xfc0f at device 31.1 on pci0 ata0: on atapci0 ata1: on atapci0 pci0: at device 31.3 (no driver attached) pmtimer0 on isa0 orm0: at iomem 0xc0000-0xc7fff,0xc8000-0xcdfff on isa0 atkbdc0: at port 0x60,0x64 on isa0 atkbd0: irq 1 on atkbdc0 kbd0 at atkbd0 atkbd0: [GIANT-LOCKED] fdc0: at port 0x3f0-0x3f5,0x3f7 irq 6 drq 2 on isa0 fdc0: [FAST] fd0: <1440-KB 3.5" drive> on fdc0 drive 0 ppc0: parallel port not found. sc0: at flags 0x100 on isa0 sc0: VGA <16 virtual consoles, flags=0x300> sio0: configured irq 4 not in bitmap of probed irqs 0 sio0: port may not be enabled sio0 at port 0x3f8-0x3ff irq 4 flags 0x10 on isa0 sio0: type 16550A sio1: configured irq 3 not in bitmap of probed irqs 0 sio1: port may not be enabled sio1 at port 0x2f8-0x2ff irq 3 on isa0 sio1: type 16550A vga0: at port 0x3c0-0x3df iomem 0xa0000-0xbffff on isa0 unknown: can't assign resources (memory) unknown: can't assign resources (port) unknown: can't assign resources (port) unknown: can't assign resources (port) unknown: can't assign resources (port) Timecounters tick every 1.000 msec acd0: CDROM at ata1-master PIO4 da0 at asr0 bus 0 target 0 lun 0 da0: Fixed Direct Access SCSI-2 device da0: Tagged Queueing Enabled da0: 70006MB (143372288 512 byte sectors: 255H 63S/T 8924C) SMP: AP CPU #1 Launched! Trying to mount root from ufs:/dev/da0s1a em0: link state changed to UP ---------------- ++++ DIFF KERNEL # diff -bu /usr/src/sys/i386/conf/GENERIC /usr/src/sys/i386/conf/GBARATTO --- /usr/src/sys/i386/conf/GENERIC Fri Oct 28 15:22:41 2005 +++ /usr/src/sys/i386/conf/GBARATTO Tue Nov 29 23:29:19 2005 @@ -19,10 +19,8 @@ # $FreeBSD: src/sys/i386/conf/GENERIC,v 1.429.2.3.2.1 2005/10/28 19:22:41 jhb Exp $ machine i386 -cpu I486_CPU -cpu I586_CPU cpu I686_CPU -ident GENERIC +ident GBARATTO # To statically compile in device wiring instead of /boot/device.hints #hints "GENERIC.hints" # Default places to look for devices. @@ -64,6 +62,26 @@ options ADAPTIVE_GIANT # Giant mutex is adaptive. device apic # I/O APIC + +# ADDED BY GUSTAVO +options SMP + +device carp + +options ALTQ +options ALTQ_CBQ +options ALTQ_RED +options ALTQ_RIO +options ALTQ_HFSC +options ALTQ_CDNR +options ALTQ_PRIQ +options ALTQ_NOPCC # just for SMP builds + +options MAXDSIZ=(2048UL*1024*1024) #added by gustavo +options MAXSSIZ=(512UL*1024*1024) #added by gustavo +options DFLDSIZ=(2048UL*1024*1024) #added by gustavo +# + # Bus support. device eisa