Hi,
We are using mysql 5.1 DB for storing the huge search data. We use queue servers on the fly which writes the online searches into mysql db. Data set is very huge 1.25 TB in month.
Since the db size is very huge, we switch the DB every 15 days and there could be 2-3 databases in a month. Over the months we have added more columns to the tables.
We have php and ruby applications connect to this db. Earlier we used to have tthe same DB used for reading and writing and the DB used to corrupt every often. Then I have implemented replication (mysql1srv master and mysql2srv slave).
Now the replication gets delayed every day for 2-3 hours and then goes to 5-6 hours delay. Moreover the processlist shows “freeing items” and “connect”.
Please suggest
1…how can I improve the DB performance
2…How can I improve replication performance.
3…Can I integrate all these dbs and make 1 single partition db for an year?
4…Rather than switching the DB every week, what is the best alternative. As of now for any queries I need to fire the sqls against multiple databases or use “union” in SQL.
Expert advice would be appreciated.
Thanks
Jeevan