0

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.

0

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?
  • hello @tero-kilkanen all points you suggested, is checked and ok as per my knowledge. but still I want to replicate my database and store all the data on SLAVE and only 2 months data (some of tables) on MASTER (eg PRODUCTION Database). Is is possible? If yes, How? and another question about real-time or latency. imagine my MASTER server is located in USA and my SLAVE at Singapore, when records get inserted in MASTER, at how many time it will take insert it on SLAVE (forgot about network latency) – Manish Sapkal Jul 28 '14 at 10:39
  • You cannot use replication this way. Replication always contains the complete dataset on both nodes. You have to implement this with some other way, for example with mysqldump, and then you need to keep inventory which data is on the backup node and which one is in production node. – Tero Kilkanen Jul 28 '14 at 12:31
-1

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?

  • I sorry for typo. It is not billion, it's a million. :) and not just for backup. I want solution for real-time synchronization, so I have another option in case of main server crashed. – Manish Sapkal Jul 28 '14 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.