Randal Rust wrote:
We have been having some performance issues on one of our larger
sites, and I'm trying to figure out the best way to go about fixing
them.
.....
On a related note, our main issue is that the site keeps slowing down
and we are getting a 'Too Many Connections' error from MySQL. I had
the number of connections increased from 100 to 150 on both Apache and
MySQL two weeks ago, but we are still having the problem, although
less frequently. One thing I'd like to be able to do is check how many
connections have physically been made to the database. I did this a
couple of weeks ago, but now I can't find the MySQL query returns that
information. If anyone could throw that out to me, I'd greatly
appreciate it.
Most common problem I see is a database that is not optimized or a sql
server that is just underpowered (not enough cpu, ram, or disk i/o
bandwidth .. usually it's the latter 2). If the database can be
optimized, you will likely not need to upgrade the hardware.
First, examine your queries to see what is slow:
When your website begins to die, check the mysql process list
mysqladmin -p processlist
You'll probably see a list of queries that are taking a long time. These
will eventually build up until you reach "Too Many Connections".
You can also enable "--log-slow-queries" in mysql to log the slow
queries to a file.
Track down the queries, prune your tables, add indexes, or rewrite the
sql properly to speed things up.
You can tweak the my.conf mysql config file settings to get a little
better performance but the underlying problem is usually something more
serious. If it all looks good, next blame the hardware.
Good tools to monitor ram and disk i/o usage using vmstat and iostat.
Leave "vmstat 5" running to see if your machine has run out of memory
and is swapping pages to disk. This is often the tipping point leading
to meltdown. The "cpu wa" column will show amount of time in wait state.
if this is high, you've likely run out of disk bandwidth
Time to buy more or faster hard drives or break the app apart into a
cluster of machines.
~Rolan
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
