Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I developed an application with MySQL 5.1 back-end four years ago. After that, I only gave remote support a couple of times, but that too for application crashes, and not for database. The database is still running well. The customer takes the backup on decided schedules. I want to optimize the database as I think in these years many records have been deleted and many added.

How do I tune up the database and bring it into proper shape? What things need attention?

share|improve this question
    
I am sure there are other people with old databases that have wondered about an issue like this. A +1 for bringing this question out into the open. –  RolandoMySQLDBA Aug 11 '11 at 17:32
add comment

2 Answers

up vote 3 down vote accepted

There are two things you could so to eliminate table fragmentation and allocate proper caching resources:

Option 1 : Run OPTIMIZE TABLE on every table. This will shrink each table individually and compute index statistics for all tables.

Option 2 : mysqldump everything and reload backup. Naturally, each table gets dropped and recreated without table fragmentation. Indexes are also rebuilt.

You may also want to compute the proper size for InnoDB Buffer Pool and MyISAM Key Cache. Whatever number comes out as the recommended sizes, make sure the combined size of innodb_buffer_pool_size and key_buffer_size does not exceed 75% of the RAM installed on the machine.

share|improve this answer
add comment

I want to optimize the database as I think in these years many records have been deleted and many added.

You have some other reason for wanting to fiddle with a database that is "running well"?

Good reasons might include:

  • The backups are getting unmanageably large
  • The customer has mentioned a specific area of performance they want improved

If you don't have a good reason, then my advice would be to do nothing

share|improve this answer
    
I voted you up, because you have a valid point. But I don't agree with you on that: rebuilding indexes, statistics, revising backup plans, verifying backups, verifying permissions for old users...there's so much to check on a server that's not been touched for a few years. –  Marian Jul 25 '11 at 20:56
    
I'll upvote this one because your point is valid for low-trafficked sites, that do not log visits, and have a fair amount of the working set data cached all the time. In that instance, doing nothing is the thing to do. But a little routine maintenance never hurts. –  RolandoMySQLDBA Aug 11 '11 at 17:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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