LINUX vs FreeBSD mysql performance using a large RT database
Mike Tancsa
mike at sentex.net
Fri Oct 20 18:59:54 UTC 2006
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 at 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<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,MMX,FXSR,SSE,SSE2,HTT>
Features2=0x2001<SSE3,CX16>
AMD Features=0xea500800<SYSCALL,NX,MMX+,FFXSR,RDTSCP,LM,3DNow+,3DNow>
AMD Features2=0x1f<LAHF,CMP,<b2>,<b3>,CR8>
Cores per package: 2
real memory = 5368709120 (5120 MB)
avail memory = 4118446080 (3927 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-15 on motherboard
ioapic1 <Version 1.1> irqs 16-31 on motherboard
ioapic2 <Version 1.1> irqs 32-47 on motherboard
kbd1 at kbdmux0
acpi0: <A M I OEMXSDT> 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: <ACPI CPU> on acpi0
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 1.0 on pci0
pci1: <ACPI PCI bus> on pcib1
pcib2: <ACPI PCI-PCI bridge> at device 13.0 on pci1
pci2: <ACPI PCI bus> 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: <ServerWorks HT1000 SATA150 controller> port
0xc080-0xc087,0xc000-0xc003,0xbc00-0xbc07,0xb880-0xb883,0xb800-0xb80f
mem 0xff3fe000-0xff3fffff irq 11 at device 14.0 on pci1
ata2: <ATA channel 0> on atapci0
ata3: <ATA channel 1> on atapci0
atapci1: <ServerWorks HT1000 SATA150 controller> port
0xcc00-0xcc07,0xc880-0xc883,0xc800-0xc807,0xc480-0xc483,0xc400-0xc40f
irq 11 at device 14.1 on pci1
ata4: <ATA channel 0> on atapci1
ata5: <ATA channel 1> on atapci1
atapci2: <ServerWorks HT1000 UDMA100 controller> port
0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0xffa0-0xffaf at device 2.1 on pci0
ata0: <ATA channel 0> on atapci2
ata1: <ATA channel 1> on atapci2
isab0: <PCI-ISA bridge> at device 2.2 on pci0
isa0: <ISA bus> on isab0
ohci0: <OHCI (generic) USB controller> 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: <OHCI (generic) USB controller> 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: <OHCI (generic) USB controller> 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: <OHCI (generic) USB controller> 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: <EHCI (generic) USB 2.0 controller> 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: <EHCI (generic) USB 2.0 controller> 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: <Intel(R) PRO/1000 Network Connection Version - 6.1.4> 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: <Intel(R) PRO/1000 Network Connection Version - 6.1.4> 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: <display, VGA> at device 6.0 (no driver attached)
acpi_button0: <Power Button> on acpi0
atkbdc0: <Keyboard controller (i8042)> port 0x60,0x64 irq 1 on acpi0
atkbd0: <AT Keyboard> flags 0x1 irq 1 on atkbdc0
kbd0 at atkbd0
atkbd0: [GIANT-LOCKED]
psm0: <PS/2 Mouse> 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: <floppy drive controller (FDE)> port 0x3f0-0x3f5,0x3f7 irq 6
drq 2 on acpi0
fdc0: [FAST]
fd0: <1440-KB 3.5" drive> on fdc0 drive 0
orm0: <ISA Option ROMs> at iomem 0xc0000-0xc7fff,0xc8000-0xc97ff on isa0
sc0: <System console> at flags 0x100 on isa0
sc0: VGA <16 virtual consoles, flags=0x100>
vga0: <Generic ISA VGA> 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: <AMCC 9550SX-4LP DISK 3.01> 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 at sentex.net
Providing Internet since 1994 www.sentex.net
Cambridge, Ontario Canada www.sentex.net/mike
More information about the freebsd-performance
mailing list