[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1067
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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