How did I reduce CPU overhead problem caused by MySql?

Advertisement

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.

Tags: , ,

Enter your email address and get free tutorials, tips and tricks of PHP, Ajax, JavaScript and CSS directly delivered to you email inbox:

40 Comments on “How did I reduce CPU overhead problem caused by MySql?”

  • utsav wrote on 3 December, 2008, 2:23

    Thanks for the tip.
    Included for my future reference

  • Shafiq wrote on 3 December, 2008, 5:20

    Roshan! That sounds a good idea but keep in mind if your cron-job takes more than one second to execute, wat will happen?

  • Roshan wrote on 3 December, 2008, 5:35

    @shafiq – thanks man you’re right about this but this dirty little technique I had to use to reduce overhead as I was taking remaining time from the temporary table there is margin of error of + or – 1 second furthermore I’m thinking to to use another middle PHP file in which I use sleep for 1 second rather than doing it from bash script in this way I just have to run the cronjob in a minute only…

  • Flüge wrote on 4 December, 2008, 16:10

    Thx for the tip!Its very usefull, my system runs now better…
    But there must be more ways to reduce the cpu overhead problem…

  • Loïc Hoguin wrote on 4 December, 2008, 18:38

    Or you could use a view.

  • Kris Brixon wrote on 5 December, 2008, 3:12

    An option:
    Use the temporary tables, but populate them from triggers instead of a cronjob. Setup a trigger to run the SQL when a relevant field is updated. There is no reason to keep rerunning a SQL statement that returns the same data. If the data on average only changes every 2 seconds then you will reduce the load by half.
    http://www.databasedesign-resource.com/mysql-triggers.html
    Note, I come from an Oracle world and not MySQL, but from the link above it looks like you have this ability.

    I did not stay on the site long, but it looks like you are just updating prices, if so, make sure the SQL that runs the most frequently is just returning the prices and not everything about the items.

    Another link that looked interesting:
    http://swerl.tudelft.nl/twiki/pub/Main/TechnicalReports/TUD-SERG-2007-016.pdf

  • Roshan wrote on 5 December, 2008, 7:31

    @Krix – thanks for idea of using trigger I may have to try that one as well. but the issuse is with most frequent data …they are updating the highest bid every time user bid so you never know when it’s going to update and I’ve up get data from server in each second, furthermore in last few second of auction.. it’s most necessary……

  • Anonymous wrote on 5 December, 2008, 7:42

    @Loïc Hoguin: No, you can’t. A view is not a table. The performance of a view isn’t different to the underlying query. (see http://tinyurl.com/ysgm3w)

    @Roshan: Anogher suggestions to further reduce load. Don’t poll the DB. It’s much more efficient to push changes as they occur – without any database roundtrips. Have a look at http://cometdproject.dojotoolkit.org/ for ideas.

  • john wrote on 5 December, 2008, 11:25

    http://www.sphinxsearch.com/ ? ;)

  • Loïc Hoguin wrote on 5 December, 2008, 13:59

    @Anon: I guess you gave me yet another reason to love MySQL. (Not that I use it, unless I’m required to.) Thanks for the tip.

  • mobilya wrote on 14 December, 2008, 11:12

    Roshan very very good thx…

  • Clint Rowden wrote on 5 February, 2009, 3:42

    What about using prepared statements? I’ve read this reduces overhead, though I’m not experienced in using them.

  • bboing wrote on 30 April, 2009, 20:47

    why not use memcached? if data changes so fast why bother saving it every second to the db? use memcached to generate the page and save the data in the db for a “backup”

  • Lee wrote on 31 May, 2009, 4:12

    Oh wow, that was really helpful. I’m working on project with a similar concept. Basically, we have a few “broadcasted” events for the page that also needs a more then temporary storage system. The problem came after having a few people come on to test the feature. Unbelievably, our cpu usage went off the chart. I’ve been searching for a solution to this for days now. This post has been a real help.

  • IMF wrote on 9 June, 2009, 16:23

    I agree with Bboing memcached is recommended to solve this overload problems.

  • angkasuwan wrote on 9 June, 2009, 19:12

    thanks for the post roshan

  • Chris wrote on 9 June, 2009, 23:22

    Thanks for sharing, this is real useful and somthing you would not expect.

  • sohbet wrote on 2 July, 2009, 13:19

    hi good thank you master

  • Webagentur wrote on 14 July, 2009, 20:03

    Thank you for these great tips.

  • SD wrote on 4 August, 2009, 5:06

    I havent implemented the way you have said but i am sure that cronjob will too overhead your CPU uses. This is not the exact answer I was expecting for, which has driven me inside to read more on this title, reducing the CPU overhead.
    Sorry ,but i thought that you would be describing about the feature of database or base of mysql
    like query execution time ,normilazition and so on……
    last question:
    you havent mentioned the sql query execution time. why?

  • Dinesh wrote on 4 August, 2009, 5:13

    The techniques you have said are not optimum way to reduce database overhead problem,i think. runnning crone job and accessing file via ajax process seems to be same way of retrieving information.so, server cpu overhead problem can not be reduce until it(database) has managed in well manner . server clustering, normalizing(4f,5f) tables and complex join queries
    on the other hand important parts beetween server and client which bridge the gap beetween them in a optimum way

  • wish wrote on 4 August, 2009, 5:33

    May be i am wrong(cause u r genius) BUT::
    This is the very much disappointing answer as for Kris Brixon question.
    You definitely might knew the difference between the trigger and the cronjob.
    I am 100% agree with the Kris Brixon answer because Trigger will execute on some event whenever there is some change in the table. so running the cronjob without any changes in the table data (say: with no new bids in your case) is vanity.

  • Roshan wrote on 5 August, 2009, 8:34

    @SD – I agree that that later on this technique has definitely reduced the cpu overhead as before. But was found much better solution for this….

    make a temporary table for accepting the bids….
    make the indexes properly …
    use memcached for caching data (can cache for the second which is enough)
    flush the data from temporary table and move to real table in a particular interval time ….
    and use innodb type if you’ve frequent insert queries and have to use transactions…
    and off course use mysql clusters if you really need it…

  • Mitul wrote on 7 September, 2009, 9:13

    Instead of using CronJob you can create a view for it and data will be populated based on request. There are other several things which you do to optimize result.
    – Create indexes(Single or combine) based on requirement.
    – Access only those elements which are required.

    Also there is another option is you can create memory table which deliver results more faster then any other.

    You can drop me an email if you need more help.

    Thanks,
    Mitul Patel

  • Sam IT wrote on 22 September, 2009, 2:02

    What if the database server gets busier? You’ll end up in a much worse situation…

    There are *so many* better ways to do this!

    First, let’s start with the fact that in most scenarios you would need the content to be updated up to the second, what about 2, 5, 10, 60 seconds? Just a thought, it is possible to you need it up to the second.

    What about MySQL cache, is it turned on?

    What about persistent connections? Is it turned on?

    You can output the html cache into a file, could be partial part of the file or the whole thing. It will definitely give you a huge benefit over database queries as well as PHP processing.

    In terms of architecture, you can probably display cached output (again, you choose the update period), for example, do guests really need up-to-the-second information?

    From output cache to db data cache, you can store the data in a file or possibly SQLite.

    You should also consider Memcache if you have a really high amount of requests. If would let your RAM do the work instead of your harddrive (no reason to explain why RAM is faster, right?).

    In my experience, cached output, Memcache and MySQL’s internal cache always worked but in your case, I would use the info above with some Javascript code to calculate the time they’ve left.

    Just my thoughts…

  • Somebody wrote on 22 September, 2009, 9:26

    Are you the creator behind bolibol.in???

  • Sarfraz Ahmed wrote on 24 September, 2009, 12:28

    thanks for sharing :)

  • Artur Ejsmont wrote on 14 December, 2009, 0:51

    That seems a bit complicated to me. I mean in half year you will not expect this app to depend on some cron job.

    Would caching not make sense here? maybe APC or memcached ?

    Art

  • clonevideos.com wrote on 7 January, 2010, 18:05

    wow great tips but i can’t do as you are saying. i got the same problem CPU over limit due to uploading and playing videos by many users at a time and our server halted and some of them (previous hosts) had refuge to host our site. is there any solution that i can take from you? please reply.

  • Roshan wrote on 9 January, 2010, 3:27

    I think after uploading the video take some time to make it active or use cron job to convert it to flv from cron. Use one server for web and one for database. Hardware scaling thats the only thing you can do at the momoment..

  • Umzug München wrote on 23 February, 2010, 21:36

    Hello, Very good article. I will continue to pursue this article, as it is written is very interesting. Since we are interpreted very much on good information. Best regards :-)

  • Studenten wrote on 2 March, 2010, 19:55

    The article seems to be very interesting for students. We would be delighted if we could pursue this Arikel for our students.

  • metta wrote on 16 March, 2010, 21:05

    Thanks for sharing. Very useful.

  • matbaa wrote on 28 March, 2010, 19:34

    Really nice and useful comments. Informative article. Thanks for sharing.

  • Bild Geschenk wrote on 16 May, 2010, 9:30

    Its old thread but always new!
    thanx

  • Newbodys München Ultraschall Waver Schlank Fett weg wrote on 16 May, 2010, 18:10

    Hi, I find the website very informative and helpful. I will recommend this site to people on. The site is written very interesting.
    Greetings

  • tomsbikecorner.de wrote on 13 June, 2010, 7:22

    Thanks for these tipps. I currently have an similiar problem with my website. So i check your input and see if it works out for my issue.

  • ashish singh wrote on 11 August, 2010, 9:07

    Hi, i would like to know what is the best architecture in php to manage the auction site which having a large amount of data.

  • Carole wrote on 25 August, 2010, 12:05

    Hi roshan, very gud job, keep it up!

  • Bodenbeläge München wrote on 8 September, 2010, 19:25

    The article seems to be very interesting. We would be pleased if students can pursue this Arikel. Thanks

Write a Comment

 


Copyright © 2014 Roshan Bhattarai's Blog. All rights reserved.
Powered by WordPress.org, Custom Theme and ComFi.com Calling Card Company.