Solved

Optimize MySQL Table

Posted on 2008-06-18
4
890 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

22 Experts available now in Live!

Get 1:1 Help Now