From owner-freebsd-performance@FreeBSD.ORG Fri Oct 20 18:59:54 2006 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 0684616A4E6 for ; Fri, 20 Oct 2006 18:59:54 +0000 (UTC) (envelope-from mike@sentex.net) Received: from smarthost1.sentex.ca (smarthost1.sentex.ca [64.7.153.18]) by mx1.FreeBSD.org (Postfix) with ESMTP id 963E943E3E for ; Fri, 20 Oct 2006 18:57:52 +0000 (GMT) (envelope-from mike@sentex.net) Received: from lava.sentex.ca (pyroxene.sentex.ca [199.212.134.18]) by smarthost1.sentex.ca (8.13.6/8.13.6) with ESMTP id k9KIvf9J040498 for ; Fri, 20 Oct 2006 14:57:42 -0400 (EDT) (envelope-from mike@sentex.net) Received: from mdt-xp.sentex.net (simeon.sentex.ca [192.168.43.27]) by lava.sentex.ca (8.13.6/8.13.3) with ESMTP id k9KIvej8091353 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Fri, 20 Oct 2006 14:57:41 -0400 (EDT) (envelope-from mike@sentex.net) Message-Id: <7.0.1.0.0.20061020104944.144d9068@sentex.net> X-Mailer: QUALCOMM Windows Eudora Version 7.0.1.0 Date: Fri, 20 Oct 2006 14:57:46 -0400 To: freebsd-performance@freebsd.org From: Mike Tancsa Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: ClamAV version 0.88.3, clamav-milter version 0.88.3 on clamscanner2 X-Virus-Status: Clean Subject: LINUX vs FreeBSD mysql performance using a large RT database 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: Fri, 20 Oct 2006 18:59:54 -0000 One of our larger db apps is our RT system (http://bestpractical.com/). Our old RELENG_4 box was starting to get long in the tooth, so it was time to put in faster disks (3ware 7000 in RAID1 vs 9500SX in RAID10) and more memory to help with searches. Its not that CPU intensive, but it does get the odd burst of CPU work as mail comes in and is processed by a number of filters. Access to the information is generally a couple of people at a time, but there is on occasion 4 or 5 people doing something at the very same time, sometimes doing large content searches. With all the threads about poor FreeBSD performance, I wanted to test it out myself to see how 64bit LINUX would compare using the same hardware. The database from mysqldump is about 6G. For testing purposes, I used a box with 4G of RAM (full dmesg below). I decided to test against a default Fedora Core5 install, but with the latest kernel 2.6.18.1. Apart from removing some modules from the build, it was a stock kernel. For the mysql.cnf, I used the same config on both machines. To try and compensate for disk layout, I installed the OS each time on a separate segate IDE drive and left the entire 3ware 9500SX disk in RAID10 (writeback cache, 64k stripes) to act as the database drive. Some simple numbers via bonnie with default FS. For LINUX, it was EXT3 (journal) -------Sequential Output-------- ---Sequential Input-- --Random-- -Per Char- --Block--- -Rewrite-- -Per Char- --Block--- --Seeks--- Machine MB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU /sec %CPU amd64SMP 5000 110199 72.8 110363 26.0 34922 9.7 77879 69.9 123349 17.3 729.0 2.5 amd64UP 5000 111557 68.6 114372 20.9 25162 4.8 98951 88.1 124080 14.7 720.7 1.9 LINUXSMP 5000 91132 91.4 103707 43.9 61248 17.4 50721 92.5 139426 11.4 926.0 0.7 BSD LINUX time mysql rt3 < all.out (full import) 106m 123m first full content search 35.92 45.29 Second content search after first full content search 24.66 26.14 Subject search 0.48 0.35 4 different select scripts run at the same time on different tables 62 59 create index Subject_IDX on Tickets (Subject); 8.29 7.12 To try and minimize cache effects, I would reboot the box before tests. The first test was total time to integrate. They are both disk intensive with almost no CPU, but FreeBSD came out ahead a bit. The first full content search was done after rebooting the box. It would search through the entire database counting the instances of a string sending the results (100 rows) to the screen. Right after that, I would do a second content search on a different string. The 4 different selects is a set of queries that would typically run by our customer service reps. i.e. search all tickets for a name, bring up the tickets for a single user, tickets on a circuit etc. These would all be started at the same time and I measured the wall time it took. In this case, statistically identical times I would say as 2 second difference on 4 tests is meaningless... At least for my real world app its uninteresting. The index creation was a bit faster, but again differences not really that interesting Some basic info about the array's speed. [am64]# diskinfo -t /dev/da0s1 /dev/da0s1 512 # sectorsize 159973438464 # mediasize in bytes (149G) 312448122 # mediasize in sectors 19448 # Cylinders according to firmware. 255 # Heads according to firmware. 63 # Sectors according to firmware. Seek times: Full stroke: 250 iter in 1.439902 sec = 5.760 msec Half stroke: 250 iter in 1.455372 sec = 5.821 msec Quarter stroke: 500 iter in 6.351412 sec = 12.703 msec Short forward: 400 iter in 1.341134 sec = 3.353 msec Short backward: 400 iter in 2.936428 sec = 7.341 msec Seq outer: 2048 iter in 0.359675 sec = 0.176 msec Seq inner: 2048 iter in 0.377226 sec = 0.184 msec Transfer rates: outside: 102400 kbytes in 0.741994 sec = 138007 kbytes/sec middle: 102400 kbytes in 0.866048 sec = 118238 kbytes/sec inside: 102400 kbytes in 1.567069 sec = 65345 kbytes/sec [am64]# I used libmap.conf to use libthr.so.2. For building mysql50 out of the ports, I also used BUILD_OPTIMIZED=yes. [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 2M table_cache = 512 sort_buffer_size = 8M read_buffer_size = 128M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 128M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 log-bin=mysql-bin server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout One strange thing is that FreeBSD thinks the box really has 5G of RAM, which is does not. Its just 4G.... However I am pretty sure thats just a cosmetic bug. Copyright (c) 1992-2006 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 is a registered trademark of The FreeBSD Foundation. FreeBSD 6.2-PRERELEASE #1: Thu Oct 19 02:57:52 EDT 2006 mdtancsa@obsidian.sentex.ca:/usr/obj/usr/src/sys/obsidian Timecounter "i8254" frequency 1193182 Hz quality 0 CPU: AMD Athlon(tm) 64 X2 Dual Core Processor 4200+ (2194.52-MHz K8-class CPU) Origin = "AuthenticAMD" Id = 0x40fb2 Stepping = 2 Features=0x178bfbff Features2=0x2001 AMD Features=0xea500800 AMD Features2=0x1f,,CR8> Cores per package: 2 real memory = 5368709120 (5120 MB) avail memory = 4118446080 (3927 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-15 on motherboard ioapic1 irqs 16-31 on motherboard ioapic2 irqs 32-47 on motherboard kbd1 at kbdmux0 acpi0: on motherboard acpi0: Power Button (fixed) acpi0: Sleep Button (fixed) Timecounter "ACPI-safe" frequency 3579545 Hz quality 1000 acpi_timer0: <32-bit timer at 3.579545MHz> port 0x508-0x50b on acpi0 cpu0: on acpi0 cpu1: on acpi0 pcib0: port 0xcf8-0xcff on acpi0 pci0: on pcib0 pcib1: at device 1.0 on pci0 pci1: on pcib1 pcib2: at device 13.0 on pci1 pci2: on pcib2 3ware device driver for 9000 series storage controllers, version: 3.60.02.012 twa0: <3ware 9000 series Storage Controller> port 0xac00-0xac3f mem 0xf4000000-0xf5ffffff,0xff2ff000-0xff2fffff irq 16 at device 3.0 on pci2 twa0: [FAST] twa0: INFO: (0x15: 0x1300): Controller details:: Model 9550SX-4LP, 4 ports, Firmware FE9X 3.01.01.028, BIOS BE9X 3.01.00.024 atapci0: port 0xc080-0xc087,0xc000-0xc003,0xbc00-0xbc07,0xb880-0xb883,0xb800-0xb80f mem 0xff3fe000-0xff3fffff irq 11 at device 14.0 on pci1 ata2: on atapci0 ata3: on atapci0 atapci1: port 0xcc00-0xcc07,0xc880-0xc883,0xc800-0xc807,0xc480-0xc483,0xc400-0xc40f irq 11 at device 14.1 on pci1 ata4: on atapci1 ata5: on atapci1 atapci2: port 0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0xffa0-0xffaf at device 2.1 on pci0 ata0: on atapci2 ata1: on atapci2 isab0: at device 2.2 on pci0 isa0: on isab0 ohci0: port 0xe000-0xe0ff mem 0xff6b4000-0xff6b4fff irq 10 at device 3.0 on pci0 ohci0: [GIANT-LOCKED] usb0: OHCI version 1.0, legacy support usb0: SMM does not respond, resetting usb0: on ohci0 usb0: USB revision 1.0 uhub0: (0x1166) OHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub0: 2 ports with 2 removable, self powered ohci1: port 0xe400-0xe4ff mem 0xff6b5000-0xff6b5fff irq 10 at device 3.1 on pci0 ohci1: [GIANT-LOCKED] usb1: OHCI version 1.0, legacy support usb1: SMM does not respond, resetting usb1: on ohci1 usb1: USB revision 1.0 uhub1: (0x1166) OHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub1: 2 ports with 2 removable, self powered ehci0: port 0xe800-0xe8ff mem 0xff6b6000-0xff6b6fff irq 10 at device 3.2 on pci0 ehci0: [GIANT-LOCKED] usb2: EHCI version 1.0 usb2: companion controllers, 2 ports each: usb0 usb1 usb2: on ehci0 usb2: USB revision 2.0 uhub2: (0x1166) EHCI root hub, class 9/0, rev 2.00/1.00, addr 1 uhub2: 4 ports with 4 removable, self powered em0: port 0xdc00-0xdc3f mem 0xff680000-0xff69ffff,0xff660000-0xff67ffff irq 24 at device 4.0 on pci0 em0: Ethernet address: 00:e0:81:5e:bd:9a em0: [FAST] em1: port 0xd880-0xd8bf mem 0xff620000-0xff63ffff,0xff600000-0xff61ffff irq 25 at device 5.0 on pci0 em1: Ethernet address: 00:e0:81:5e:bd:9b em1: [FAST] pci0: at device 6.0 (no driver attached) acpi_button0: on acpi0 atkbdc0: port 0x60,0x64 irq 1 on acpi0 atkbd0: flags 0x1 irq 1 on atkbdc0 kbd0 at atkbd0 atkbd0: [GIANT-LOCKED] psm0: irq 12 on atkbdc0 psm0: [GIANT-LOCKED] psm0: model Generic PS/2 mouse, device ID 0 sio0: configured irq 4 not in bitmap of probed irqs 0 sio0: port may not be enabled sio0: <16550A-compatible COM port> port 0x3f8-0x3ff irq 4 flags 0x10 on acpi0 sio0: type 16550A, console sio1: configured irq 3 not in bitmap of probed irqs 0 sio1: port may not be enabled sio1: <16550A-compatible COM port> port 0x2f8-0x2ff irq 3 drq 0 on acpi0 sio1: type 16550A fdc0: port 0x3f0-0x3f5,0x3f7 irq 6 drq 2 on acpi0 fdc0: [FAST] fd0: <1440-KB 3.5" drive> on fdc0 drive 0 orm0: at iomem 0xc0000-0xc7fff,0xc8000-0xc97ff on isa0 sc0: at flags 0x100 on isa0 sc0: VGA <16 virtual consoles, flags=0x100> vga0: at port 0x3c0-0x3df iomem 0xa0000-0xbffff on isa0 Timecounters tick every 1.000 msec da0 at twa0 bus 0 target 0 lun 0 da0: Fixed Direct Access SCSI-3 device da0: 100.000MB/s transfers da0: 152566MB (312455168 512 byte sectors: 255H 63S/T 19449C) SMP: AP CPU #1 Launched! Trying to mount root from ufs:/dev/da0s1a ---Mike -------------------------------------------------------------------- Mike Tancsa, tel +1 519 651 3400 Sentex Communications, mike@sentex.net Providing Internet since 1994 www.sentex.net Cambridge, Ontario Canada www.sentex.net/mike