Checking/Optimizing and Repairing Tables
So I have question. Something I was thinking about. I check and repair tables whenever I get a chance, but I am curious what other people do. How do you manage your checks on production servers?
How do you do it for the least impact on the server? Whats up!!
8 Comments so far
Leave a reply
I have a script that checks the wasted space on our MyISAM tables once a month. Of course, those are at a minimum these days. InnoDB tables just don’t need that kind of baby sitting.
Hi,
Good post!
I solve this problem my way: replace darn MySQL with something that doesn’t corrupt tables. Like PostgreSQL. Or if you wanna be really anal have Oracle installed, The Mother Of All Databases.
Cheers!
P.S. I was wondering why the heck are you still using myisam?! This gets me every single time.
Why are you checking your tables on a regular basis? It’s generally only necessary to check your tables if they are corrupted. You can check your mysql server logs, and you would find information about corruption there. What are you checking for exactly?
Michael
If you have a master + n slaves, you can take one of the slaves off line, optimize it, and then make it the master.
or my favorite is to optimize a slave, shutdown the site, and then copy the slave files (scp) directly over the master files :).
so the problem with the scp solution is that many of us can’t take a site down for something like this. Optimizing the slave/promoting to master is a good idea in some cases, but with our particular application slave promotion is fairly difficult to do and not something to be taken lightly.
What a couple of really crappy answers…
“I solve this problem my way: replace darn MySQL with something that doesn’t corrupt tables. Like PostgreSQL. Or if you wanna be really anal have Oracle installed”
Because I have the time to go back and redevelop all of the applications running on MySQL for Postgres and/or Oracle. And speaking of Oracle, I find 80k (per processor) a bit steep for running joomla and the other apps we use at my work place. But hey, maybe you work for the gov’t, who likes spending money frivolously.
What a stupid, useless answer that is intended only to irritate people who are looking for a valid answer to a valid question.
The other bad answer: “Of course, those [MyISAM tables] are at a minimum these days.”
You sound like one of those developer / DBAs who uses InnoDB tables for EVERYTHING simply because at some point you read somewhere that InnoDB is better because of .
What if you need native full text searching? What if you don’t need the overhead InnoDB has for ensuring referential integrity and ACID compliance? What if you need the performance increase that using MyISAM in a read heavy environment provides? I won’t disagree (because it’s true) that MyISAM is slower in a write heavy environment, but InnoDB can’t hold a candle in a heavy read / light write environment.
MyISAM also has the ability to compact the tables into an extremely fast read only form. This means that you can archive things, like logs, for a long period of time using less space, with a performance GAIN!!
I have always been, and will continue to be, an advocate of using the correct tool for the job. If that means I use MySQL and php for one project, then Java and Oracle for the next, then so be it. Each has it’s own strengths and weaknesses, and you should use the one that matches the needs of your project the best.
Now, after ranting about stupidity, my answer: By default, I do table maintenance about once a month for seldom used applications. Seldom used means < 100k operations a month. Depending on the work load, and especially depending on the number of deletes and inserts, I increase the frequency.
As more deletes and inserts happen, the index has a tendency to become fragmented. Deletes remove data from the middle of the table which leaves white space gaps. By themselves, the gaps aren’t all that harmful. But, if the table data is sequential on disk, then the MyISAM engine is able to write new data to the end without locking the table.
CHECK TABLES is never really necessary- mysql reports it soon enough if a table is corrupted (assuming you actually use them).
REPAIR TABLE is necessary if a table becomes corrupted - of course this should ideally never happen. myisamchk may be quicker than REPAIR though in some cases (perhaps because of the way it uses memory).
OPTIMIZE table rebuilds the table, hence is VERY slow for large tables and will prevent writes to the table for the duration of the operation. For this reason, if you have any volume of writes which are expected to complete in a timely fashion, OPTIMIZE cannot be used at all, ever.
—
If you are using mysql in a critical application, all of these should be avoided as they create extra load and make (part of) the database unavailable. This is not acceptable for most applications.
There are some cases in DWH type application where it may be acceptable to optimize / repair a table, particularly if your data are partitioned across many and you’re not locking up the “current” table.
We don’t use DELETEs anyway to delete our old data in such tables, instead copying the rows to keep to another table then renaming them. This is more efficient and doesn’t block access to the table.
Mark
[…] Diamond Notes, Keith Murphy has a lazyweb question or two on checking/optimizing and repairing tables: “How do you manage your checks on production servers? How do you do it for the least impact […]