[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Optimize MySQL Table

Posted on 2008-06-18
4
Medium Priority
?
1,043 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1000 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 1000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

649 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