From owner-freebsd-performance@FreeBSD.ORG Wed Jun 15 02:11:51 2005 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 592CD16A41C for ; Wed, 15 Jun 2005 02:11:51 +0000 (GMT) (envelope-from tspencer@hungry.com) Received: from terror.hungry.com (terror.hungry.com [199.181.107.40]) by mx1.FreeBSD.org (Postfix) with ESMTP id D750F43D48 for ; Wed, 15 Jun 2005 02:11:50 +0000 (GMT) (envelope-from tspencer@hungry.com) Received: from [10.250.7.154] (nat2.ironport.com [63.251.108.10]) (AUTH: LOGIN tspencer, TLS: TLSv1/SSLv3,128bits,RC4-SHA) by terror.hungry.com with esmtp; Tue, 14 Jun 2005 19:11:49 -0700 Mime-Version: 1.0 (Apple Message framework v730) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <43DB0180-8478-47E9-91BC-3AE5AA10D913@hungry.com> Content-Transfer-Encoding: 7bit From: Tim Spencer Date: Tue, 14 Jun 2005 19:11:16 -0700 To: freebsd-performance@freebsd.org X-Mailer: Apple Mail (2.730) Subject: mysql performance? 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: Wed, 15 Jun 2005 02:11:51 -0000 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 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: on motherboard npx0: INT 16 interface pcib0: on motherboard IOAPIC #1 intpin 3 -> irq 2 IOAPIC #1 intpin 7 -> irq 7 IOAPIC #1 intpin 11 -> irq 10 pci0: on pcib0 pci0: (vendor=0x1028, dev=0x000c) at 4.0 irq 2 pci0: (vendor=0x1028, dev=0x0008) at 4.1 irq 7 pci0: (vendor=0x1028, dev=0x000d) at 4.2 irq 10 pci0: at 14.0 atapci0: 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: at 15.2 irq 5 isab0: at device 15.3 on pci0 isa0: on isab0 pcib1: on motherboard IOAPIC #1 intpin 4 -> irq 11 pci1: on pcib1 fxp0: 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: on miibus0 inphy0: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, auto pcib2: on motherboard IOAPIC #1 intpin 8 -> irq 13 pci2: on pcib2 isp0: port 0xcc00-0xccff mem 0xfcd00000-0xfcd00fff irq 13 at device 6.0 on pci2 isp0: bad execution throttle of 0- using 16 pcib3: on motherboard IOAPIC #1 intpin 12 -> irq 16 IOAPIC #1 intpin 13 -> irq 17 pci3: on pcib3 bge0: mem 0xfcb10000-0xfcb1ffff irq 16 at device 6.0 on pci3 bge0: Ethernet address: 00:11:43:34:7b:3f miibus1: on bge0 brgphy0: on miibus1 brgphy0: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, 1000baseTX, 1000baseTX-FDX, auto bge1: mem 0xfcb00000-0xfcb0ffff irq 17 at device 8.0 on pci3 bge1: Ethernet address: 00:11:43:34:7b:40 miibus2: on bge1 brgphy1: on miibus2 brgphy1: 10baseT, 10baseT-FDX, 100baseTX, 100baseTX-FDX, 1000baseTX, 1000baseTX-FDX, auto pcib4: on motherboard IOAPIC #1 intpin 14 -> irq 18 pci4: on pcib4 pcib8: at device 8.0 on pci4 pci5: on pcib8 aac0: 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 pcib5: on motherboard pci6: on pcib5 pcib6: on motherboard pci7: on pcib6 pcib7: on motherboard pci8: on pcib7 orm0: