mysql slowdown under load
On Track Technicial Support
techsupp at on-track.ca
Tue Jul 6 17:51:08 PDT 2004
A few suggestions
- Enable query caching in MySQL
- Enable thread caching in MySQL
- You can tweek the memory use (increase stuff like key/index cache, table
cache, and some other memory sizes)
- You could try compiling mysql as static
----- Original Message -----
From: "Charles Sprickman" <spork at inch.com>
To: <stable at freebsd.org>
Sent: Tuesday, July 06, 2004 4:56 PM
Subject: mysql slowdown under load
> Hi,
>
> I've been trying to track down this problem for some time now. Since I
> have a temporary fix in place, I can breathe a bit and hope someone here
> has some answers (questions are good too!).
>
> I have a dual Athlon 1600MP box running 4.8-p3. It's a mail server
> running qmail with vpopmail on top of that, backed by mysql for storing
> user info (user/pass/homedir, etc.). Webmail is currently served off of
> the box as well (waiting for a vpopmail upgrade to peel this service off
> the box).
>
> In the past week we've had complaints from users about having to re-enter
> their POP passwords, some bounces of legit mail (chkuser patch on
> qmail-smtpd gives a 550 if it can't do a lookup, blech), and password
> failures on webmail. After some digging, I was able to figure out that
> this all came down to mysql not returning an answer fast enough.
>
> The box generally runs a load of about .70, peaks at 3-4 during heavy spam
> runs. Each mail delivery can theoretically generate about 6 sql queries
> (don't ask - I wouldn't likely do vpopmail again if I had a choice).
> There are about 3,000 active mail users on here. Even at high loads, the
> box is not swapping at all, and interactive response such as webmail and
> normal IMAP sessions is still excellent. But the higher the load goes,
> the more problems mysql seems to have. The slow query log grows, even
> simple db "pings" can take up to 45 seconds to complete.
>
> Mysql is built from ports, so far I've had these versions running:
>
> 4.0.18 w/LinuxThreads
> 4.0.20 w/LinuxThreads
> 4.0.20 w/native threads
>
> There was no difference going from .18 to .20, but going from LinuxThreads
> to system threads did bring a bit of relief.
>
> My vague hypothesis here (based on past work on larger scale qmail boxes)
> is that at some point the kernel just gets a bit "lost" as to what
> processes get prioritized; qmail with it's "hand the mail from process to
> process" architecture causes lots of processes to be spawned then
> destroyed. It seems to me that the system gets so busy with spawning all
> of this that mysql languishes.
>
> Looking at iostat, vmstat and systat, it seems like the number of syscalls
> is very high, as is the "context switches" number. However memory is fine
> (no paging in/out to disk), cpu is fine, disk i/o is fairly low
> (underlying storage is 4 disks on an adaptec raid controller, raid 1+0),
> but mysql continues to "stall". Mysqladmin and some mrtg graphing shows
> that I'm doing about 15 queries/second peak (which seems pretty low
> compared to past work I've done with mysql).
>
> I saw a previous thread about this problem that never really came to any
> good conclusions; the tester cron'd a script that just connected to mysql,
> ran a simple query and recorded whether the connection failed or not and
> how long it took. Even under very low load connects would timeout or take
> a long time to complete.
>
> My band-aid is to replicate the db to another box and use that for
> vpopmail for all non-update/insert queries. So far, so good, but my
> curiousity has the better of me on this; it seems like I should be able to
> run a local mysql server with such a low Q/S rate without issue.
>
> This could also be fixed in vpopmail; the design there is not very robust
> in the face of db failures.
>
> Here's a few stats, perhaps something jumps out at someone. And if
> anyone can explain exactly what the high level of syscalls indicates (and
> how much is too much) and what "context switches" are and how they figure
> in, that would be great.
>
> iostat:
>
> tty da0 md0 cpu
> tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
> -0 -86 0.00 0 0.00 0.00 0 0.00 -91 -3-108 -5308
> 0 30 12.00 50 0.58 0.00 0 0.00 2 0 6 0 92
> 0 30 10.64 90 0.94 0.00 0 0.00 5 0 6 0 89
> 0 30 8.44 45 0.37 0.00 0 0.00 3 0 7 0 90
> 0 30 9.67 60 0.57 0.00 0 0.00 3 0 7 0 90
> 0 30 11.68 37 0.42 0.00 0 0.00 1 0 3 0 95
> 0 30 0.00 0 0.00 0.00 0 0.00 3 0 2 0 95
> 0 30 10.41 37 0.37 0.00 0 0.00 4 0 8 0 87
> 0 30 11.78 9 0.10 0.00 0 0.00 0 0 3 0 97
> 0 30 5.00 1 0.00 0.00 0 0.00 1 0 1 0 98
> 0 30 8.23 461 3.71 0.00 0 0.00 1 0 5 0 94
>
> vmstat (sorry about the wrap - "sy" and "cs" concern me here):
>
> procs memory page disks faults cpu
> r b w avm fre flt re pi po fr sr da0 md0 in sy cs us sy
> id
> 1 12 0 800380 43788 135 0 0 0 206 21 0 0 159 72 187 -94
> -114 308
> 1 12 0 795304 45700 1656 0 0 0 1919 0 75 0 509 8289 533 3
> 8 89
> 0 11 0 793812 46644 610 0 0 0 776 0 18 0 411 2718 211 1
> 3 96
> 0 11 0 794232 46264 1668 0 0 0 1335 0 174 0 463 5620 647 3
> 10 87
> 0 11 0 793772 46548 793 0 0 0 700 0 15 0 397 3265 301 2
> 4 95
> 2 12 0 796476 46344 970 0 0 0 869 0 20 0 393 3871 353 1
> 6 93
> 0 12 0 796900 45560 1390 0 0 0 935 0 16 0 381 9099 577 22
> 7 71
> 2 15 0 799664 43432 2392 0 0 0 1619 0 32 0 469 9379 704 2
> 10 87
> 2 14 0 803256 42372 3131 0 0 0 2794 0 73 0 579 13544 1495
> 4 19 77
> 1 14 0 803124 42348 2299 0 0 0 2043 0 65 0 541 10809 1173
> 4 13 83
> 1 15 0 803348 41624 2614 0 0 0 2385 0 24 0 448 9002 549 4
> 9 87
> 0 15 0 801100 42108 1099 0 0 0 973 0 27 0 470 6665 554 5
> 8 87
> 0 17 0 801320 42100 1219 0 0 0 983 0 37 0 442 5557 533 1
> 6 92
> 4 16 0 809912 38136 3095 0 0 0 2126 0 89 0 580 13160 1319
> 5 16 79
> 2 16 0 810028 59412 3107 0 0 0 3472 5560 109 0 521 11596 799
> 3 16 81
>
> systat -v:
>
> Mem:KB REAL VIRTUAL VN PAGER SWAP PAGER
> Tot Share Tot Share Free in out in out
> Act 424440 7376 793348 16896 83652 count
> All 998604 11808 2012876 28536 pages
> 715 zfod Interrupts
> Proc:r p d s w Csw Trp Sys Int Sof Flt 478 cow 486
> tota 12 64 757 1792 7409 486 319 1702 185992 wire ata0
irq14 509556 act 48
> mux
irq2
> 7.5%Sys 0.5%Intr 7.9%User 0.0%Nice 84.2%Idl 248948 inact ahc1
> irq5 | | | | | | | | | | 54108 cache 210
> xl0 irq9
> ====>>>> 29544 free fdc0
> irq6 daefr
> atkbd0 irqNamei Name-cache Dir-cache 1049 prcfr
> sio0 irq4
> Calls hits % hits % react 100 clk
> irq0
> 12376 11927 96 5 0 pdwak 128 rtc
> irq8
> pdpgs Disks
> da0 acd0 fd0 pass0 md0 intrn
> KB/t 10.33 0.00 0.00 0.00 0.00 114480 buf tps 48
> 0 0 0 0 666 dirtybuf
> MB/s 0.48 0.00 0.00 0.00 0.00 70423 desiredvnodes
> % busy 17 0 0 0 0 81095 numvnodes
> 63089 freevnodes
>
> Thanks,
>
> Charles
>
> --
> Charles Sprickman
> spork at inch.com
>
> _______________________________________________
> freebsd-stable at freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-stable
> To unsubscribe, send any mail to "freebsd-stable-unsubscribe at freebsd.org"
>
More information about the freebsd-stable
mailing list