In MySQL what does "Overhead" mean, what is bad about it, and how to fix it?
MysqlOverheadMysql Problem Overview
simple question, but its been nagging me for a while now....
what is "overhead" in MySQL, and should i be worried?
does clicking "optimize table" fix it for real?
Mysql Solutions
Solution 1 - Mysql
It appears that the overhead is temporary diskspace that the database used to run some of the queries, so you should only worry if this gets really high.
You can compare 'Optimizing the table' with the defragmenting of your hard drive.
I quote:
> Every database will, over time, > require some form of maintenance to > keep it at an optimal performance > level. Purging deleted rows, > resequencing, compressing, managing > index paths, defragmenting, etc. is > what is known as OPTIMIZATION in mysql > and other terms in other databases. > For example, IBM DB2/400 calls it > REORGANIZE PHYSICAL FILE MEMBER. > > It's kind of like changing the oil in > your car or getting a tune-up. You may > think you really don't have to, but by > doing so your car runs much better, > you get better gas mileage, etc. A car > that gets lots of mileage requires > tune-ups more often. A database that > gets heavy use requires the same. If > you are doing a lot of UPDATE and/or > DELETE operations, and especially if > your tables have variable length > columns (VARCHAR, TEXT, etc), you need > to keep 'er tuned up.
Solution 2 - Mysql
If you are talking about the thing that phpMyAdmin calls overhead, then it's actual size of a table datafile relative to the ideal size of the same datafile (as if when just restored from backup).
For performance reasons, MySQL does not compact the datafiles after it deletes or updates rows.
This overhead is bad for table scan, i. e. when your query needs to run over all table values, it will need to look at more empty space.
You may get rid of the overhead by running OPTIMIZE TABLE that will compact your table and indexes.
Solution 3 - Mysql
Overhead is Data_free of a table, that is The number of allocated but unused bytes. We can find it by SQL command SHOW TABLE STATUS. It is the free space in allocated size for your table.
Solution 4 - Mysql
Optimize table can be very problematic. For example if the table is used heavily on a site.
http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html >After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.<
I believe I've confirmed this behaviour. And it would certainly be very useful indeed.