Re: MySQL Performance 6.0rc1

From: Carlos Horowicz <carlos_at_infodrive.com.ar>
Date: Wed, 26 Oct 2005 13:40:54 -0300
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_at_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_at_freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-current
> To unsubscribe, send any mail to 
> "freebsd-current-unsubscribe_at_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_at_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_at_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_at_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
Received on Wed Oct 26 2005 - 14:41:09 UTC

This archive was generated by hypermail 2.4.0 : Wed May 19 2021 - 11:38:46 UTC