How did I reduce CPU overhead problem caused by MySql?

From last day, We were having problem with a project which was shut down in the middle due to heavy traffic. As a technical manager, I was the person who to take charge over the project bring it down to the track. After doing few benchmark test, I came to know that the MySql access from a PHP file was creating the overhead to the CPU of the server.

Problem of CPU Overhead with MySql

Let me explain the problem clearly first,

We’re working on a website which was similar to http://www.swoopo.co.uk/ and as you can see clearly in that website the most recent data should be fetched form MySql database in each second. in the PHP page, which was being called from Ajax every second, had around 5 SQL queries to retrieve data from server.

First of all, I optimized that page reducing the 5 queries into single query by using left outer joins among 3 tables. And then, I did benchmark test by using Apache benchmark tool(ab) 50 request with concurrency of 50 with the following command.

ab -n 50 -c 50 http://xyz.com/ajax_page.php

And then in another SSH shell prompt, I run the top command to view the CPU usages parallelly.

I was still horrified that the CPU usages by mysql after 50 concurrent user was going out of control(100%) despite of optimized query. But many joins have been used in that single query and lots of data were there in those tables so the database overhead was high even though it was a single query.

How did I reduced the CPU overload drastically caused by MySql?

Now, the first challenge was to reduce the database access. It was clear that it was caused by concurrent database overhead in the PHP page which was being called in every second. Here is the simple steps what I did to reduce the database overhead.

1. I created another PHP file in which I’ve transferred the mysql query causing CPU overhead and called it from Cronjob.
2. Created a temporary table for storing the output given by PHP page called from CronJob.
3. Then, I scheduled the CronJob in every second, don’t tell me that CronJob can’t be run less than a minute, take a look at this post before saying this.And from each call, the output data was stored in the temporary table.
4. And, finally from the Ajax the a new PHP page was called which was only accessing the data from temporary table with single row.

I did the same benchmarking again to the newly made page(which is accessing data only from temporary table) and saw that CPU usages after the this process reduced drastically.

I know I can further reduce the CPU overhead by storing the temporary data in a text file. You guyz know that I’m lazy so that part is left for another day.