Mysql replication with c

Question

I have huge database on MySql and It's become more and more huge. So, Now I am facing performance issues with my database. Some of tables has millions of records in it. like my login detail table contain about 2 million records, most of that unused for end-user, it is just useful to my administrative staff for analysis purpose.

One possible solution to handle this situation is, create another table with same schema of login detail table with different name, say login archive. copy 1 month before data at every midnight with cron job, and remove from original table. So, when I have to present report, I have to consider both tables to get data. I am ready to do this, but problem is I have so many tables like this (e.g. 30-35 tables at least), and I have to re-write my code, which time taking job for me. that's why I am looking for another solution which is gives me performance boost as well as less changes effort.

I have read about REPLICATION, but I am clear understaning about it. Can I do SLAVE which contain all the data (eg. all records in login detail table) and MASTER which have only 1 month's data? In this situation, I can give report from SLAVE, because It has all records. In above situation, what happen If my cron job remove records from MASTER. should it reflect on SLAVE? when my end-user made changes in records like INSERT/UPDATE/DELETE must be reflect on SLAVE. Does any latency issue create in REPLICATION?

And one more thing, Can I do same (above mentioned) with AMAZON RDS read replica?

Any blogs/example/suggestions are welcome.

Thanks.

Answer 1

Replication maintains simply a real-time copy of the entire database on another server, so it is not suited to your problem.

I would suggest you to take a look at your database server configuration.

• Are your datatypes optimal in the tables?
• Do you have optimal indexing?
• Are you using optimal table engines on your tables?
• Do you have enough memory assigned to buffers?

Answer 2

Is it your back up performance that you're having problems with?

If so, take a look at this - incremental backup using binary logging (and of course, the docco here). As to quite why you have 2 billion login detail records is beyond me - are you running Facebook :-). The advantage of incremental logging is that you're only backing up data that has changed - I'm assuming your 2 billion records don't change regularly. You can also check out backing up using replication here.

Just a thought about the title of your question - it appears to have very little to do with the C programming language. I thought that was what your question was about and I only looked at your post out of curiosity - maybe you could edit the title and make it applicable to DBAs who are not C programmers?