5

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?

Improve this question
1
  • 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, 2011 at 17:32

2 Answers 2

Reset to default
3

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.

Improve this answer
3

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

Improve this answer
2
  • 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, 2011 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, 2011 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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