List:General Discussion« Previous MessageNext Message »
From:walt Date:March 14 2003 7:22pm
Subject:Re: FreeBSD + MySQL bottleneck
View as plain text  
Matthias Trevarthan wrote:
> 
> Howdy list,
> 
> I run MySQL 3.23.54 with FreeBSD 4.6-RELEASE.
> 
> We recently had a BBS get hammered by a lot of
> concentrated traffic.
> 
> I currently run a 'mysql-optimize.sh' script from
> cron on Wednesday and Sunday that executes:
> 
> ${bindir}/myisamchk -i -r --check --sort-index --analyze ${datadir}/*/*.MYI
> ${bindir}/isamchk -i -r --analyze --sort-index ${datadir}/*/*.ISM
> 
> This works great to keep my databases lean and mean
> for normal server load and traffic, but this last
> hit was just too much. The server was bottlenecked
> somewhere.
> 
> Problem was, I couldn't figure out where!
> 
> I'm running SCSI 160 disks in a Raid config, with a
> dual 1GHZ PIII and 1G of SDRAM. I'd think that setup
> would be able to handle some pretty killer loads...
> 
> Anyway, I ran 'top', and MySQL was turning about 97%
> processor utilitzation on one processor.
> 
> It said I still had 128M of free ram left (and my
> MySQL tables are all under 10M). And I was only using
> 3% swap, which is normal because I run phpa_accelerator.
> 
> Also, 'mysql> show status;' showed that I only had
> about 25 threads open at a time. And it also said I
> had 82 tables open.
> 
> My T1s were NOT maxed out. They weren't even half full,
> and besides: I accessed the BBS from our 100Mb switch,
> and it was still dog slow (20-30 seconds for a page load),
> which means it was purely a bottleneck in my machine.
> 
> So, my questions are these:
> ---------------------------
> 
> Can any experienced MySQL-FreeBSD admins out there
> give me some pointers for identifying bottlenecks?
> 
> Specifically, I don't know how to determine if my
> disks were being maxed out. Could someone give me
> some pointers?
> 
> And also, from 'show status', is the number of 'threads'
> directly related to the specific number of MySQL socket
> connections? (I have MySQL setup to allow up to 200 and I
> wasn't even getting over 30 from 'netstat | grep -i mysql')
> 
> Any help would be appreciated! Thanks!
> 
> Matthias
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread134717@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-kernel=nea-fast.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Matthias,
I'm not sure on BSD (never used it) but with Linux if your processor
load is high and load
avg is low, you are not suffering from an I/O bottleneck. If your load
avg
was high and cpu was low, then you have lots of I/O. It sounds like you
have
a query or two that needs to be optimized. I'd run "show full
processlist" 
next time it bogs down and test all running queries on another system
and see if
you can tweak them or move the logic out of the database into the actual
code
that's making the query.

Hope this helps!

walt
Thread
FreeBSD + MySQL bottleneckMatthias Trevarthan14 Mar
  • Re: FreeBSD + MySQL bottleneckJohn Wards14 Mar
  • RE: FreeBSD + MySQL bottleneckJoe Stump14 Mar
    • Re: FreeBSD + MySQL bottleneckJeremy Zawodny14 Mar
      • Re: FreeBSD + MySQL bottleneckMatthias Trevarthan14 Mar
  • Re: FreeBSD + MySQL bottleneckwalt14 Mar
  • Re: FreeBSD + MySQL bottleneckWarren W. Gay VE3WWG14 Mar
  • Re: FreeBSD + MySQL bottleneckJesse Guardiani15 Mar
    • Re: FreeBSD + MySQL bottleneckDan Nelson15 Mar