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?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
RPK
  • 1,425
  • 6
  • 20
  • 39

2 Answers2

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

Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
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.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536