February 11th, 2013, 09:56 AM
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?
February 11th, 2013, 10:31 AM
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.
February 11th, 2013, 11:03 AM
We are using an Amazon EC2 Large instance. Adding more memory would not be possible.
Originally Posted by ByGoneYrs
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.
February 11th, 2013, 07:35 PM
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.