On Tue, Jul 06, 2004 at 04:26:04PM -0400, Charles Sprickman wrote: > 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 > > 0 13 0 782108 61388 748 0 0 0 863 0 13 0 399 3756 276 23 95 > 2 13 0 788184 59172 2350 0 0 0 1394 0 73 0 424 7918 1142 29 90
[snip] Wow. If I've reassembled your vmstat output correctly, you're burning A LOT of system time. :-( > 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. Well, I've seen machines witth cs numbers at lest 20 times that high and they were still getting some work done. (It was part of a MyQSL benchmark I ran, in fact.) > > 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 Yeah, you're not doing much I/O at all. Hmm. > > 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... Well, they're really apples and oranges. But I think you problem is *not* MySQL. It sounds as though you still have trouble with LinuxThreads, so I'd look at qmail. I'd try tracing (via truss) some of qmail's procs to see what they heck they're doing. Maybe they're needlessly making A LOT of syscalls? > 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. No, the memory is almost all shared, so memory overhead isn't an issue. > 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"). Using the query cache at all? > -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? That's accurate for the majority of servers, yes. But not because apache and MySQL don't co-habitate well. It's because the raito of "apache machines" to "mysql machines" needed is rarely 1:1. > 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. You'd think, yeah. I don't know squat about qmail, having moved from Sendmail to Exim a few years back. Maybe it really hammers systems? 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]
