I am facing an issue for the past couple of weeks. I am working on a windows server which is a dedicated server for MySQL having 32GB of RAM and using mysql 5.1.42 version.
I am having two databases 'abc' and 'abc_bak', my main aim is to maintain two days data in a table for 'abc' database and 30 days of data in 'abc_bak' database and from there the data has to be moved to another database and also make sure that all this process should be completed in less than 1hr but as of now its taking more than 5hrs. I am following the same procedure for other tables too and they are also having the same number of records and those are finishing their backup process in less than 15 mins.
I am regularly running a script that moves the records to a table from 'abc' database to 'abc_bak' database, most of the time I am seeing this backup getting delayed. I am using INSERT...SELECT for copying the records from abc to abc_bak and then delete the records in abc database. On using this my GUI is affected with its performance.
I am also pretty much sure that these tables are not optimized for a longtime. I have tried various methods in moving the records for some days it works and again I am facing the delay in backup. I am receiving more than 3million records a day.
The table I am referring to is pretty much indexed and using MyISAM Engine. I am left with a dilemma with the below 3 solutions, not sure how far will they help me 1)Mysql partitioning 2)Changing the Engine type to Innodb 3)Setting up Master-Slave replication
Currently I have changed this table to Innodb Engine type. Please help me in finding out a better solution for this problem and also please share your suggestions.