MySql Overload

[ravemittal]

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!

[kicken]

Sounds like you probably are going to want to setup replication to another server.  Then you could probably split the read traffic off to the replicated copy and just have the writes go to the master.  I've not used replication myself so I don't know much about the process.