Thread: MySql overload

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    MySql overload


    Hi All,

    We are looking for a specific solution related to our MySql database which is effecting our server performance.

    We have a social networking website which is database intensive (the max. load is on the database server i.e MySql).

    It so happens that our MySql load reaches 70-85% on an average. More of this happens because we complie some reports on a daily basis where we have to create about 60,000 records everyday (10,000 records x 6 tables). Each cron here takes about 30-40 minutes to generate these 10,000 records and we have to run 6 crons daily. While we have checked slow queries, but these queries are optimized. However, due to the nature of these customized reports, it takes quite a load on the MySql server.

    What could be a possible solution for us to reduce the load:
    1. Do we host a database on an alternate server, then generate records to this database?
    2. Do we run a mirror database on another server and run our queries there?

    Any other possible solution where we could pull more data from MySql whenever required without effecting the normal performance of the database while end users are using the site?

    Kindly help!
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    One thing you could start off with is look at the memory useage on the box and see if it would help to increase that. It is always a quick and easy fix to add more memory to the box. Honestly just max out the box, it will increase the effectiveness and processing power on that box anyway.

    Next you could review what you actually have running on the box and see if you have anything running at the same time that might clobber the performance and compete for system resources.

    Also remember too, check your code to ensure that it can't be tweeked in any way to inprove performance.

    Many things to do, BUT adding the extra memory is always a increase in permormance regardless.
    Last edited by ByGoneYrs; February 11th, 2013 at 10:33 AM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Originally Posted by ByGoneYrs
    One thing you could start off with is look at the memory useage on the box and see if it would help to increase that. It is always a quick and easy fix to add more memory to the box. Honestly just max out the box, it will increase the effectiveness and processing power on that box anyway.

    Next you could review what you actually have running on the box and see if you have anything running at the same time that might clobber the performance and compete for system resources.

    Also remember too, check your code to ensure that it can't be tweeked in any way to inprove performance.

    Many things to do, BUT adding the extra memory is always a increase in permormance regardless.
    We are using an Amazon EC2 Large instance. Adding more memory would not be possible.

    We usually run these reports early in the morning when there is no traffic on the site. However, it does slow down the DB.

    While tweaking the code is not an option, since we need this data in particular formats, it makes me wonder how sites like Facebook Ads / Google Adwords are able to complie data for the customer on the fly so quickly, no matter, how intricate the required report.
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    it makes me wonder how sites like Facebook Ads / Google Adwords are able to complie data for the customer on the fly so quickly
    In the case of the former, with tens of thousands of servers and a massive amount of caching.

    In the case of the latter, using hundreds of thousands of server, a custom database engine built in-house, a multi-billion dollar engineering staff, and a massive amount of caching.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo