OMG Optimization.... One of those things that is widely discussed but with no real specifics because everyones setup and situation is different.
So I spent all day trying to find ways to accomplish this as our SQL server was maxed out. Let me do a bit of setup explination....
We have a semi-popular website that uses ajax extensively. These element refreshes are done partially with the aid of a CRON that runs every 60 seconds. The CRON runs some PHP files, a couple of which loop
while() 59 times. Some of those loops query the database for results.
The website uses two servers (local to eachother) one for Apache, and one for MySQL.
Ubuntu Server 10.04.4
Linux 2.6.32-41-server on x86_64
Apache version 2.2.14
AMD Phenom(tm) II X6 1045T Processor, 6 cores
Ubuntu Server 12.04
MySQL version 5.5.24
Linux 3.2.0-26-generic on x86_64
Intel(R) Core(TM) i7-2600K CPU @ 3.40GHz, 8 cores
So today the site was extremely slow (locally), I checked the 2 servers. The apache server was at a little over 1% cpu utlization and the MySQL server was at 99%. This is a fairly new website so we are discovering things as popularity grows. I know enough to install MySQL successfully and deal with tables as they relate to php, but I am no guru when it comes to proper configuration to get the best out of it. So I did what anyone would do.... I scowered the web for optimization tips that best relate to our situation. I made some changes to the my.cfn file.
Key buffer size to 1024M
Query cache size to 2048M
Nothing else I found was relevant... but as I said, I am no guru with mysql.
Here is what mysqltuner.pl had to say about the changes....
OK... so not bad right? Everything looks good...
Here is what TOP had to say about it....
Not good, and no change.
Actually the only thing that helped is when I killed all the DB connections, but of course that killed ajax until the next minnute came up and the CRON kicked in. And with that the issue came back.
I don't know what to do short of setting up another DB server and cluster them for load balancing and even that is not my forte.
Any help would be very appreciated in this matter from someone that knows more than I do about this.
Thank you in advance.