Date: Tue, 6 Jul 2004 19:56:01 -0400 (EDT) From: Charles Sprickman <spork@inch.com> To: stable@freebsd.org Subject: mysql slowdown under load Message-ID: <20040706193151.K6269@shell.inch.com>
next in thread | raw e-mail | index | archive | help
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@inch.com
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20040706193151.K6269>