[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1053
  • Last Modified:

Optimize MySQL Table

I have inherited the management of a MySQL DB whihc i don't have much experience in.

MySQL 4 is running on OpenSuSE 10.0 and is running out of space. I have a table in my DB which is 9GB. It has never been optimized. This table is constantly written to and deleted from. I think there is no way there can be 9 GB (proabably more like 1GB).

I was wondering if i can simply run an optimize on the table. I was going to do it through the MySQL Administrator GUI. I was wondering if how this will affect the server. Will it continue to run or should i stop the service whihc writes to the DB.  If so how long does this take?

Thanks
0
inf2300
Asked:
inf2300
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
MySQL 4 is a lot different animal that newer versions.  It has a lot of limitations and quirks that have been addressed and for the most part, corrected.

I'd take a backup, upgrade to MySQL 5.x, and restore the database to the new DBMS.  You'll indirectly accomplish the reorganization, and be running on a much better product that is mostly compatible with what you have.


Good Luck,
Kent
0
 
AdrianSRUCommented:
It is a good idea to upgrade to the latest production release of MySQL as Kdo has mentioned, but that it not necessary in order to optimize your table.  You can run the query:

OPTIMIZE TABLE table_name;

>Will it continue to run or should i stop the service whihc writes to the DB.
When a table is in the process of being optimized with OPTIMIZE TABLE no other connections will be able to read or write to it.  If another query does try to access the table it will wait until the OPTIMIZE TABLE process is done.  It is only going to be necessary to stop the service if there will be problems (such as timeout issues) if it gets held up for a little while.  However, queries accessing other tables will continue to execute normally.

>If so how long does this take?
On a 9GB table this is likely to take a while.  I wouldn't be surprised if it takes 10-20 minutes.  It is impossible to predict exactly how long it will take since that will depend on many things such as the column types and indices.

It is a good idea to have a maintenance plan in place so that your tables don't get like this.  At least once a week I run a process during low traffic periods that optimizes all of my tables.  If it is done regularly the optimize process does not take very long at all.

Hope this helps!


--Adrian
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now