On Tue, 6 Jul 2004, Jeremy Zawodny wrote:
> On Tue, Jul 06, 2004 at 02:29:16PM -0400, Charles Sprickman wrote:
> > On Tue, 6 Jul 2004, Jeremy Zawodny wrote:
> >
> > > Do you have a summary of the poor performance somewhere? Or at least
> > > a sense of where you think the bottleneck is?
> >
> > The best I can tell you is that mysql + moderate qmail load on the
> > same box causes problems. I don't know if this is a scheduler issue
> > with FreeBSD, or just qmail telling me that I should be using
> > Postfix.
>
> Interesting. Do you find the mysqld process using alot of CPU?
No. There's no one process chugging CPU juice, but many many small
processes (qmail-remote, qmail-local, maildrop, etc.). Looking at vmstat,
it seems like just keeping track of all the processes and scheduling them
is problematic (sorry about the wrapping):
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
0 13 0 782108 61388 748 0 0 0 863 0 13 0 399 3756 276 2
3 95
2 13 0 788184 59172 2350 0 0 0 1394 0 73 0 424 7918 1142 2
9 90 0 13 0 780712 63220 1926 0 0 0 2652 0 50 0 577 7597 911
7 9 83
3 15 0 781320 62228 1320 0 0 0 952 0 90 0 515 5978 471 10
5 85
5 13 0 782204 62120 1834 0 0 0 1410 0 40 0 635 10150 812 17
16 68 0 14 0 783496 61168 2102 0 0 0 1550 0 109 0 643 17888
1217 28 15 57
>From the vmstat manpage, it appears that the "faults" line, specifically
"cs" represents context switches, which the best I can understand is that
that indicates how much the cpus are "thrashing" from process to process
in the run queue. At some point this number gets high enough that
processes block even though there's no memory shortage, swapping, or disk
i/o problems:
faults Trap/interrupt rate averages per second over last 5 seconds.
in device interrupts per interval (including clock
interrupts)
sy system calls per interval
cs cpu context switch rate (switches/interval)
This might be a red herring, as I have no similarly loaded boxes to
compare these numbers to.
> > Out of the blue mysql will start logging stuff like this in the slow query
> > log:
> >
> > # administrator command: Ping;
> > # [EMAIL PROTECTED]: squirrelmail[squirrelmail] @ localhost []
> > # Query_time: 47 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
> > # administrator command: Ping;
> > # [EMAIL PROTECTED]: vpopmail[vpopmail] @ localhost []
> > # Query_time: 48 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
> >
> > Load is moderate, but not so bad that any other services on here are
> > affected in any perceptible way.
>
> Without looking at the box, I can hazard a few guesses.
>
> I suspect you're seeing one of two things (or both). I suspect that
> qmail, like some mail servers, makes heavy use of syncrous disk
> writes. And it's probably competing with MySQL for precious disk I/O
> resources. (Are they sharing a disk?)
Everything's on a 4 disk RAID 1+0 array.
> What's iostat look like?
Not very heavy, it doesn't seem disk bound:
tty da0 md0 cpu
tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
-0 -85 0.00 0 0.00 0.00 0 0.00 -90 -3-107 -5306
0 30 8.77 26 0.22 0.00 0 0.00 6 0 10 1 83
0 30 10.57 66 0.68 0.00 0 0.00 3 0 11 1 84
0 30 8.79 61 0.53 0.00 0 0.00 17 0 9 1 73
0 30 9.80 35 0.33 0.00 0 0.00 4 0 7 1 88
0 30 1.00 1 0.00 0.00 0 0.00 1 0 5 0 94
> If you're not using LinuxThreads, you'll find that MySQL on FreeBSD
> behaves very poorly in high I/O situations. FreeBSD's userspace,
> self-scheduling threads just suck for database applications. There's
> no way around that.
I was using LinuxThreads, but found that it made the situation worse; I
think scheduling a few hundred procs was harder than dealing with the
threads; just speculation on my part... I also couldn't find good docs
explaining LT on BSD very well. I was worried that each "thread" (in
reality, a process) in the LT model had it's own memory footprint. Of
course I knew about LT from reading your site...
I'll also reiterate a few datapoints about mysql for any latecomers:
-Most queries are simple selects to grab user info (check password, check
"homedir").
-The few updates or inserts are for a relay table for smtp use; it simply
tracks where each pop/imap user connects from and smtp can refer to that
table to see if someone should be able to relay mail.
-There is also an insert/update on a "last auth from..." table.
The hardware is a dual Athlon MP-1600 smp box with 1GB of RAM.
Queries/second is about 15 tops, and there is a hard limit on the mail
side; the box will only accept a finite amount of inbound/outbound smtp
connections.
Also, out of curiousity, the db servers that you've mentioned Yahoo is
running are all likely dedicated mysql boxes, right? No dual-purpose
stuff, correct? I can offload all this to another box, but I thought the
rather light db load (compared to what other people have done with mysql)
would allow me to put qmail and mysql on one box.
Thanks so much for your help, I appreciate it.
Charles
> Jeremy
> --
> Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/
>
> [book] High Performance MySQL -- http://highperformancemysql.com/
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
