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?
First thing I would look at is are there any particular processes which are causing the heavy load? Could this be a heavy load because of missing indexes, lack of memory assigned to the engine buffer cache, memory on the machine, slow disk access, lack of CPU etc. If you find everything possible has been done to reduce the load on the system, I would then have a look at using replication to replicate the data to another database on another server. This copy could be used exclusively for reporting. This then splits the work between two different servers. If this still is not enough then consider using clustering for your online component and replicated copy for reporting.