NYCPHP Meetup

[Rolan Yang]

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