These type of problem occurs when your Site is accessed by more number of user with in second and that will bring your server down. 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.
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.
No comments:
Post a Comment