Re: FreeBSD + MySQL bottleneck
- Original Message - From: Warren W. Gay VE3WWG [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 14, 2003 4:45 PM Subject: Re: FreeBSD + MySQL bottleneck snip The OP didn't indicate where the files are being kept, and under FreeBSD this can be significant. I know for example, that using UFS file systems, are very slow compared to newer file systems. Yes. I am using UFS + softupdates. I wasn't aware that I could run ext3 on FreeBSD. However, I suppose, they have the trade off that they're perhaps safer in the event of a system crash. But the disk I/O is much much heavier on a UFS file system, than on some of the newer systems, like ext3 file systems. UFS seems to insist on doing writes as soon as possible, to prevent accidental loss of information due to a crash (just my guess, based upon the experience). I tried to speed up a very slow PostgreSQL database on UFS once.. I turned off fsync(2) for the database, and that helped some, but the disk I/O was still enormously extensive for what was going on. The same database under Linux flies (ext3) (with significantly less disk I/O activity). I was glad to see the MySQL supports the InnoDB in a partition (raw disk space). This can eliminate the file system from the equation, assuming that the necessary caching occurs in either the block device or in the database engine (using a raw char device). -- Warren W. Gay VE3WWG http://home.cogeco.ca/~ve3wwg - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FreeBSD + MySQL bottleneck
In the last episode (Mar 15), Jesse Guardiani said: From: Warren W. Gay VE3WWG [EMAIL PROTECTED] The OP didn't indicate where the files are being kept, and under FreeBSD this can be significant. I know for example, that using UFS file systems, are very slow compared to newer file systems. Yes. I am using UFS + softupdates. I wasn't aware that I could run ext3 on FreeBSD. You can't. UFS should be just as fast as ext3 as far as mysql is concerned. Softupdates doesn't help you here because you're not creating or deleting many files. -- Dan Nelson [EMAIL PROTECTED] sql, query - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FreeBSD + MySQL bottleneck
So, my questions are these: --- Can any experienced MySQL-FreeBSD admins out there give me some pointers for identifying bottlenecks? I have been running MySQl-FreeBSD for over a year now and we are now doing about 140-150,000 page views a day all dynamic from the database. Running top displayed MySQL running about 40% but we kept getting bottle necks and mysql would hang periodicaly. What we have done is recompiled mysql with linuxthreads which made a difference, but what made the biggest difference was upgrading to MySQL 4 and turning on query caching, that what I would recomend. Cheers John Wards SportNetwork.net - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: FreeBSD + MySQL bottleneck
This may help - I just got this from a friend.
http://jeremy.zawodny.com/blog/archives/000203.html
--Joe
--
Joe Stump [EMAIL PROTECTED]
http://www.joestump.net
Label makers are proof God wants Sys Admins to be happy.
-Original Message-
From: Matthias Trevarthan [mailto:[EMAIL PROTECTED]
Sent: Friday, March 14, 2003 7:16 AM
To: [EMAIL PROTECTED]
Subject: FreeBSD + MySQL bottleneck
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 [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FreeBSD + MySQL bottleneck
On Fri, Mar 14, 2003 at 12:03:14PM -0800, Joe Stump wrote: This may help - I just got this from a friend. http://jeremy.zawodny.com/blog/archives/000203.html Ugh. I *really* need to update that. It has become a popular reference. But I have some new information to add. The short version. If you use LinuxThreads and MySQL 4.0.x (where x is 4 or so), MySQL/FreeBSD is readlly damned good. But using LinuxThreads is a key piece there. FreeBSD's threading still sucks and can hamper MySQL. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 39 days, processed 1,287,285,706 queries (379/sec. avg) - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FreeBSD + MySQL bottleneck
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
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
-
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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FreeBSD + MySQL bottleneck
On Friday 14 March 2003 12:34, Jeremy Zawodny wrote: On Fri, Mar 14, 2003 at 12:03:14PM -0800, Joe Stump wrote: This may help - I just got this from a friend. http://jeremy.zawodny.com/blog/archives/000203.html Ugh. I *really* need to update that. It has become a popular reference. But I have some new information to add. What about the DNS issue. Is that still a problem on FreeBSD 4? Thanks! The short version. If you use LinuxThreads and MySQL 4.0.x (where x is 4 or so), MySQL/FreeBSD is readlly damned good. But using LinuxThreads is a key piece there. FreeBSD's threading still sucks and can hamper MySQL. Jeremy - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FreeBSD + MySQL bottleneck
Jeremy Zawodny wrote: On Fri, Mar 14, 2003 at 12:03:14PM -0800, Joe Stump wrote: This may help - I just got this from a friend. http://jeremy.zawodny.com/blog/archives/000203.html Ugh. I *really* need to update that. It has become a popular reference. But I have some new information to add. The short version. If you use LinuxThreads and MySQL 4.0.x (where x is 4 or so), MySQL/FreeBSD is readlly damned good. But using LinuxThreads is a key piece there. FreeBSD's threading still sucks and can hamper MySQL. Jeremy The OP didn't indicate where the files are being kept, and under FreeBSD this can be significant. I know for example, that using UFS file systems, are very slow compared to newer file systems. However, I suppose, they have the trade off that they're perhaps safer in the event of a system crash. But the disk I/O is much much heavier on a UFS file system, than on some of the newer systems, like ext3 file systems. UFS seems to insist on doing writes as soon as possible, to prevent accidental loss of information due to a crash (just my guess, based upon the experience). I tried to speed up a very slow PostgreSQL database on UFS once.. I turned off fsync(2) for the database, and that helped some, but the disk I/O was still enormously extensive for what was going on. The same database under Linux flies (ext3) (with significantly less disk I/O activity). I was glad to see the MySQL supports the InnoDB in a partition (raw disk space). This can eliminate the file system from the equation, assuming that the necessary caching occurs in either the block device or in the database engine (using a raw char device). -- Warren W. Gay VE3WWG http://home.cogeco.ca/~ve3wwg - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
