First of all, what I have:
Linode 512
32-bit Ubuntu 10.04 LTS
Apache 2.2.14
PHP 5.3.2 with APC
MySQL 5.1.41

I'm running http://americankpopfans.com/ for a couple friends, and they had a huge spike in users a couple days ago, and ever since then, it's been having performance issues. The original issues were due to hitting the swap hard. I realized MaxClients in Apache was way too high (it was at the default of 150), so my settings now look like this:

<IfModule mpm_prefork_module>
    StartServers              1
    MinSpareServers           3
    MaxSpareServers           6
    ServerLimit              15
    MaxClients               15
    MaxRequestsPerChild    3000
</IfModule>

That fixed the swap issue, but now the CPU is going berserk. It looks like this:



It's pretty much all due to MySQL, too. I've been trying to tweak it, but it doesn't seem to be helping. Here are my settings:

key_buffer          = 8M
max_allowed_packet  = 1M
thread_stack        = 128K
thread_cache_size   = 8
max_connections     = 100
table_cache         = 128
query_cache_limit   = 1M
query_cache_size    = 32M

They have a decent amount of traffic, but it's not outrageous by any means (it's on the order of 2000-6000 hits a day). I don't really know what to do right now. I've also installed Quick Cache which is working (view the source on the homepage and scroll to the bottom). For some reason, though, it's still hitting MySQL hard and is loading super slowly. Does anyone have any ideas about what might be causing this?

I just uninstalled APC because my Apache error log was filled up with tons of these: "[apc-warning] Unable to allocate memory for pool." However, that doesn't seem to have fixed the issue.

Removing your PHP cache is going to lead to more load on your database, not less.

You should increase apc.shmsize to the point where the cache isn't constantly getting full.


Most of those are pretty low, especially key_buffer, assuming your tables are all MyISAM. You probably want to lower max_connections.

You might want to try a tool like mysqltuner that will give you suggestions on what to adjust.

I reinstalled APC and doubled apc.shm_size to 64mb, and it hasn't complained yet. I also changed some other things around per mysqltuner's recommendation.

skip-innodb
tmp_table_size = 64M
max_heap_table_size = 64M
key_buffer      = 8M
max_allowed_packet   = 1M
thread_stack      = 128K
thread_cache_size       = 8
max_connections        = 75
table_cache            = 128
query_cache_limit   = 1M
query_cache_size        = 64M

Even after changing this stuff, though, there's still not really a difference. It's still responding pretty slowly as you can see.

After I restart MySQL or Apache (either one), the site responds really fast for about 30 seconds, and then it resumes responding super slowly. Why would that happen?

I think I just fixed it! W3 Super Cache with page caching, db caching, and object caching all done with APC seems to have solved the problem! It's using virtually no CPU, and I have 150mb of memory free.

Thanks for the help!

Yes, I cache the hell out of my site using APC - just make sure to delete the appropriate cache entries when a database update impacts the cached content.

Also, often MySQL is hit hard from poor DB design with an un-necessarily large number of queries performed. It pays to tune your code and reduce the number of queries that need to be made.