Date: Tue, 14 Jun 2005 19:11:16 -0700 From: Tim Spencer <tspencer@hungry.com> To: freebsd-performance@freebsd.org Subject: mysql performance? Message-ID: <43DB0180-8478-47E9-91BC-3AE5AA10D913@hungry.com>
next in thread | raw e-mail | index | archive | help
Hey there!
We've got a couple of fairly beefy mysql servers that just
aren't operating as fast as they should be. For instance, we have a
slave that is falling behind just with replication going on, even
though it doesn't seem to be constrained by any system parameter that
I've looked at.
The systems in question are a pair of dual proc 2.8ghz Xeons
with 3GB of memory. They are running 4.11-stable from maybe a month
or so ago, with machdep.hyperthreading_allowed=0. They are running
mysql-server-4.1.11_1 which was built with "WITH_LINUXTHREADS=1
BUILD_OPTIMIZED=1 BUILD_STATIC=1" on a 4.10 box. They are mounting a
NetApp 940c volume over an isp0 Fibre-Channel card (as da0, as you'll
see in the output of stuff below). The NetApp is doing nothing right
now but handling the I/O of these hosts, and it's pretty much
unloaded. For instance, here's the result of iostat -x for a minute
on the NetApp:
CPU NFS CIFS HTTP Total Net kB/s Disk kB/s Tape kB/s
Cache Cache CP CP Disk DAFS FCP iSCSI FCP kB/s
in out read write read
write age hit time ty util in out
27% 0 0 0 703 0 0 11931 12091 0
0 3 98% 65% Ff 47% 0 703 0 11123 3154
25% 0 0 0 670 0 0 10779 9102 0
0 3 98% 49% Fn 45% 0 670 0 10547 3212
27% 0 0 0 680 0 0 12219 11833 0
0 3 98% 62% Ff 49% 0 680 0 10610 2959
31% 0 0 0 765 0 0 13478 12889 0
0 3 98% 62% Ff 51% 0 765 0 12343 3356
There's stuff going on, but it's by no means loaded. I can
easily push it to 100MB/s and 3000+ I/O ops/sec with iozone and
untarring /usr/ports, etc. So I don't believe this is the disk
subsystem. Neither does "iostat 2" on the slave show the system as
loaded:
tty aacd0 da0
acd0 cpu
tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy
in id
0 5 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 0 3
4 0 93
0 19 0.00 0 0.00 19.51 177 3.38 0.00 0 0.00 0 9
4 0 87
0 19 0.00 0 0.00 18.72 158 2.89 0.00 0 0.00 0 5
2 0 93
0 19 0.00 0 0.00 17.93 161 2.81 0.00 0 0.00 0 1
2 0 97
0 19 0.00 0 0.00 18.08 161 2.84 0.00 0 0.00 0 1
2 0 97
0 19 0.00 0 0.00 19.04 236 4.38 0.00 0 0.00 0 7
7 0 86
0 19 16.00 1 0.02 19.69 177 3.41 0.00 0 0.00 0 5
2 0 92
We can certainly do better than that. As you can also see, the
CPU isn't loaded either. So it's not CPU bound. Here's the result
of a representative "systat -vm 30":
--------------------------------------------
3 users Load 0.11 0.10 0.04 Jun 14 18:38
Mem:KB REAL VIRTUAL VN PAGER SWAP
PAGER
Tot Share Tot Share Free in out
in out
Act 1199336 848 1235180 1064 127076 count
All 3083500 4904 763152 8500 pages
zfod
Interrupts
Proc:r p d s w Csw Trp Sys Int Sof Flt cow 1022
total
1 12 4614 122837482 1022 2257 331528
wire ata0 irq14
1216680 act
265 isp0 irq13
4.2%Sys 0.3%Intr 0.0%User 2.8%Nice 92.7%Idl 1412564 inact 301
bge0 irq16
| | | | | | | | | | 122728 cache
aac0 irq18
==-- 4348 free
fdc0 irq6
daefr sio0 irq4
Namei Name-cache Dir-cache prcfr 200
clk irq0
Calls hits % hits % react
256 rtc irq8
6 6 100 pdwake
1074 pdpgs
Disks aacd0 da0 acd0 fd0 md0 intrn
KB/t 16.00 19.21 0.00 0.00 0.00 204096 buf
tps 0 184 0 0 0 11 dirtybuf
MB/s 0.00 3.45 0.00 0.00 0.00 197106 desiredvnodes
% busy 0 92 0 0 0 92387 numvnodes
72260 freevnodes
--------------------------------------------
And yes, I see that %busy for da0 is 92, but again, I can easily
start up an iozone benchmark and cause it to scream for a while, so I
suspect that this measurement is not really measuring the how busy
the disk is. :-)
This only seems to leave mysql as the thing that is slow. Has
anybody else seen this sort of thing, and can anybody suggest either
a solution, or more stuff to look at or test? I have pasted my dmesg
and my.cnf for the slave (the master is pretty much the same) below
in case that helps. Thanks, and have fun!
-tspencer
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 4.11-STABLE #0: Wed May 25 05:39:38 GMT 2005
root@:/usr/src/sys/compile/BSD4.11.GODSPEED-SMP
Timecounter "i8254" frequency 1193182 Hz
CPU: Intel(R) Xeon(TM) CPU 2.80GHz (2786.13-MHz 686-class CPU)
Origin = "GenuineIntel" Id = 0xf29 Stepping = 9
Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE
,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE>
Hyperthreading: 2 logical CPUs
real memory = 3221094400 (3145600K bytes)
avail memory = 3134447616 (3060984K bytes)
Changing APIC ID for IO APIC #0 from 0 to 8 on chip
Changing APIC ID for IO APIC #1 from 0 to 9 on chip
Changing APIC ID for IO APIC #2 from 0 to 10 on chip
Programming 16 pins in IOAPIC #0
IOAPIC #0 intpin 2 -> irq 0
Programming 16 pins in IOAPIC #1
Programming 16 pins in IOAPIC #2
FreeBSD/SMP: Multiprocessor motherboard: 4 CPUs
cpu0 (BSP): apic id: 0, version: 0x00050014, at 0xfee00000
cpu1 (AP): apic id: 1, version: 0x00050014, at 0xfee00000
cpu2 (AP): apic id: 6, version: 0x00050014, at 0xfee00000
cpu3 (AP): apic id: 7, version: 0x00050014, at 0xfee00000
io0 (APIC): apic id: 8, version: 0x000f0011, at 0xfec00000
io1 (APIC): apic id: 9, version: 0x000f0011, at 0xfec01000
io2 (APIC): apic id: 10, version: 0x000f0011, at 0xfec02000
Preloaded elf kernel "kernel" at 0x9f3d2000.
Warning: Pentium 4 CPU: PSE disabled
Pentium Pro MTRR support enabled
md0: Malloc disk
Using $PIR table, 9 entries at 0x9f0fc410
npx0: <math processor> on motherboard
npx0: INT 16 interface
pcib0: <Host to PCI bridge> on motherboard
IOAPIC #1 intpin 3 -> irq 2
IOAPIC #1 intpin 7 -> irq 7
IOAPIC #1 intpin 11 -> irq 10
pci0: <PCI bus> on pcib0
pci0: <unknown card> (vendor=0x1028, dev=0x000c) at 4.0 irq 2
pci0: <unknown card> (vendor=0x1028, dev=0x0008) at 4.1 irq 7
pci0: <unknown card> (vendor=0x1028, dev=0x000d) at 4.2 irq 10
pci0: <ATI Mach64-GR graphics accelerator> at 14.0
atapci0: <ServerWorks CSB5 ATA100 controller> port 0x8b0-0x8bf,
0x8d8-0x8db,0x8d0-0x8d7,0x8c8-0x8cb,0x8c0-0x8c7 at device 15.1 on pci0
ata0: at 0x1f0 irq 14 on atapci0
ata1: at 0x170 irq 15 on atapci0
pci0: <OHCI USB controller> at 15.2 irq 5
isab0: <PCI to ISA bridge (vendor=1166 device=0225)> at device 15.3
on pci0
isa0: <ISA bus> on isab0
pcib1: <Host to PCI bridge> on motherboard
IOAPIC #1 intpin 4 -> irq 11
pci1: <PCI bus> on pcib1
fxp0: <Intel 82550 Pro/100 Ethernet> port 0xdcc0-0xdcff mem
0xfcf00000-0xfcf1ffff,0xfcf20000-0xfcf20fff irq 11 at device 8.0 on pci1
fxp0: Ethernet address 00:0e:0c:62:9e:17
inphy0: <i82555 10/100 media interface> on miibus0
inphy0: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, auto
pcib2: <Host to PCI bridge> on motherboard
IOAPIC #1 intpin 8 -> irq 13
pci2: <PCI bus> on pcib2
isp0: <Qlogic ISP 2312 PCI FC-AL Adapter> port 0xcc00-0xccff mem
0xfcd00000-0xfcd00fff irq 13 at device 6.0 on pci2
isp0: bad execution throttle of 0- using 16
pcib3: <Host to PCI bridge> on motherboard
IOAPIC #1 intpin 12 -> irq 16
IOAPIC #1 intpin 13 -> irq 17
pci3: <PCI bus> on pcib3
bge0: <Broadcom BCM5703 Gigabit Ethernet, ASIC rev. 0x1002> mem
0xfcb10000-0xfcb1ffff irq 16 at device 6.0 on pci3
bge0: Ethernet address: 00:11:43:34:7b:3f
miibus1: <MII bus> on bge0
brgphy0: <BCM5703 10/100/1000baseTX PHY> on miibus1
brgphy0: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, 1000baseTX,
1000baseTX-FDX, auto
bge1: <Broadcom BCM5703 Gigabit Ethernet, ASIC rev. 0x1002> mem
0xfcb00000-0xfcb0ffff irq 17 at device 8.0 on pci3
bge1: Ethernet address: 00:11:43:34:7b:40
miibus2: <MII bus> on bge1
brgphy1: <BCM5703 10/100/1000baseTX PHY> on miibus2
brgphy1: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, 1000baseTX,
1000baseTX-FDX, auto
pcib4: <ServerWorks host to PCI bridge(unknown chipset)> on motherboard
IOAPIC #1 intpin 14 -> irq 18
pci4: <PCI bus> on pcib4
pcib8: <PCI to PCI bridge (vendor=8086 device=0309)> at device 8.0 on
pci4
pci5: <PCI bus> on pcib8
aac0: <Dell PERC 3/Di> mem 0xf0000000-0xf7ffffff irq 18 at device 8.1
on pci4
aac0: i960RX 100MHz, 118MB cache memory, optional battery present
aac0: Kernel 2.8-0, Build 6089, S/N 74a1d3
aac0: Supported
Options=275c<WCACHE,DATA64,HOSTTIME,WINDOW4GB,SOFTERR,NORECOND,SGMAP64>
pcib5: <ServerWorks host to PCI bridge(unknown chipset)> on motherboard
pci6: <PCI bus> on pcib5
pcib6: <ServerWorks host to PCI bridge(unknown chipset)> on motherboard
pci7: <PCI bus> on pcib6
pcib7: <ServerWorks host to PCI bridge(unknown chipset)> on motherboard
pci8: <PCI bus> on pcib7
orm0: <Option ROMs> at iomem 0xc0000-0xc7fff,0xc8000-0xc8fff,
0xc9800-0xcd7ff,0xcd800-0xcefff,0xec000-0xeffff on isa0
pmtimer0 on isa0
fdc0: <NEC 72065B or clone> at port 0x3f0-0x3f5,0x3f7 irq 6 drq 2 on
isa0
fdc0: FIFO enabled, 8 bytes threshold
fd0: <1440-KB 3.5" drive> on fdc0 drive 0
atkbdc0: <Keyboard controller (i8042)> at port 0x60,0x64 on isa0
atkbd0: <AT Keyboard> irq 1 on atkbdc0
kbd0 at atkbd0
vga0: <Generic ISA VGA> at port 0x3c0-0x3df iomem 0xa0000-0xbffff on
isa0
sc0: <System console> at flags 0x100 on isa0
sc0: VGA <16 virtual consoles, flags=0x300>
sio0 at port 0x3f8-0x3ff irq 4 flags 0x10 on isa0
sio0: type 16550A
sio1 at port 0x2f8-0x2ff irq 3 on isa0
sio1: type 16550A
APIC_IO: Testing 8254 interrupt delivery
APIC_IO: Broken MP table detected: 8254 is not connected to IOAPIC #0
intpin 2
APIC_IO: routing 8254 via 8259 and IOAPIC #0 intpin 0
IP packet filtering initialized, divert disabled, rule-based
forwarding enabled, default to accept, logging limited to 100 packets/
entry by default
ata0-slave: ATAPI identify retries exceeded
SMP: AP CPU #2 Launched!
SMP: AP CPU #3 Launched!
SMP: AP CPU #1 Launched!
acd0: CDROM <TEAC CD-ROM CD-224E> at ata0-master PIO4
aacd0: <RAID 0/1> on aac0
aacd0: 139997MB (286714368 sectors)
Mounting root from ufs:/dev/aacd0s1a
da0 at isp0 bus 0 target 0 lun 0
da0: <NETAPP LUN 0.2> Fixed Direct Access SCSI-4 device
da0: 200.000MB/s transfers, Tagged Queueing Enabled
da0: 817152MB (1673527296 512 byte sectors: 255H 63S/T 38636C)
-------------------------------------------------
[mysqld]
skip-bdb
#log-bin
server-id = 2
datadir = /var/db/mysql
innodb_data_home_dir = /var/db/mysql
innodb_log_group_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:10M:autoextend
set-variable = innodb_log_files_in_group=2
set-variable = innodb_buffer_pool_size=1000M
set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = key_buffer=32M
set-variable = table_cache=256
set-variable = innodb_flush_log_at_trx_commit=0
set-variable = innodb_file_io_threads=20
set-variable = innodb_flush_method=nosync
set-variable = max_connections=1000
set-variable = max_connect_errors=999999999
max_allowed_packet = 1M
table_cache = 512
old-passwords
innodb_file_per_table
log_slow_queries
set-variable = innodb_autoextend_increment=64
set-variable = long_query_time=30
set-variable = default-table-type=innodb
set-variable = tmpdir=/var/db/mysql/tmp
replicate-wild-ignore-table=phpmyadmin.%
replicate-wild-ignore-table=mysql.%
-------------------------------------------------
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: toc2-db1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: toc2-db1-bin.000063
Read_Master_Log_Pos: 67550165
Relay_Log_File: toc2-db2-relay-bin.000053
Relay_Log_Pos: 791391995
Relay_Master_Log_File: toc2-db1-bin.000059
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: phpmyadmin.%,mysql.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 818958608
Relay_Log_Space: 3653846680
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 60772
1 row in set (0.00 sec)
mysql>
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?43DB0180-8478-47E9-91BC-3AE5AA10D913>