MySQL Overhead and Repair

I have a MySQL database with a few tables that build up a significant amount of overhead. I have used phpMyAdmin a few times to REPAIR these tables, removing the overhead. What I'd like to do is automate this and run a PHP script by cron every night repairing the tables that needed - is there any reason why I shouldn't do this? The reason I ask is that if repairing tables with overhead is so quick and easy, then why doesn't MySQL automatically do this? Is there a raised chance of something going badly wrong when repairing a table?

 

Overheads appear when you delete rows from the table. When a new row is added, it will be written into the overhead space instead of appending to the end thus reducing the need to increase the filesize everytime you delete and add a new row. However, excessive overhead can cause performance impact and therefore it is wise to do optimization from time to time. You can do cron job every night when the load isn't that high, it won't have any damage.

 

Indeed, check out your statistics for the best time to run your database optimalisation script. Its alway good to preform such action(s)

 

Here's a snippet of code that you can run as a cronjob. It will only optimize those tables that have overhead:

electrictoolbox.com/optimize-tables-mysql-php/

 

Change the the table type to InnoDB which does not use the sequential access method thereby saving on the overhead of exclusive table locks on data modification and complete index rebuild before releasing the lock. This alone will not only add overhead but also bottlekneck a modestly concurrent application.

The InnoDB engine uses row level locking and shared read locks so has a much higher concurrency than MyISAM. In addition, all transactions whether implied automatically or manual are handled within the engine instead of the operating system such as with MyISAM.

Bobby

 

However, myisam has much faster reads and is also better at paging results. There is not one better engine. Test them out and see which performs best.

 

With very large databases and much frequent access, I've had headaches on MyISAM which needed fixing every now and then. InnoDB seemed to fix this. Howevery yeah, it it slower.

 

I will concede that it is the engine with the fastest seek times but that is a trade off for the whole table locks on data modification queries. This fact will absolutely kill even a modestly concurrent load.

RE: better at paging results? What are you smoking?

InnoDB is NOT slower. It merely indicates to me that your installation was not properly tuned for performance. Everybody spends a lot of time tuning their MyISAM engine and just uses the defaults for InnoDB. Wrong. Take the time to tune the InnoDB engine and it will be nearly as fast in seek time as MyISAM.

Bobby

 

We did optimize it, and not just the database server, the OS as well, since you basically have to tweak linux as a server to get the most out of it. We were still getting faster results from queries on a myisam engine compared to innodb, but it's not that much, hence i said the latter is slower.