Re: MySQL Performance 6.0rc1

From: Carlos Horowicz (carlos_at_infodrive.com.ar)
Date: 10/26/05

Next message: John Baldwin: "Re: The case of the missing USB controllers"

Previous message: Rainer Duffner: "Re: FreeBSD on IBM blade servers"
In reply to: Robert Watson: "Re: MySQL Performance 6.0rc1"
Next in thread: Dag-Erling Smørgrav: "Re: MySQL Performance 6.0rc1"
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Wed, 26 Oct 2005 13:40:54 -0300
To: current@freebsd.org

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/

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<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@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