Solved

Optimize MySQL Table

Posted on 2008-06-18
4
1,035 Views
Last Modified: 2013-11-05
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
Comment
Question by:inf2300
4 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 250 total points
ID: 21815899
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
 
LVL 12

Assisted Solution

by:AdrianSRU
AdrianSRU earned 250 total points
ID: 21816187
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Bubble user-defined Sql RAISERROR(...) to c# exception 14 166
Easy to use inventory management software 7 92
change database name 2 28
MySQL Query Using Up Memory 6 21
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now