Can Somebody Tell Me What 'Overhead' Is In My SQL Database?

[ben_1uk]

Hi everyone,

 

I have recently had to manually delete a number of rows in a SQL database table (120 in all) and noticed on the table summary screen I now have an overhead in excess of 9,000B?

 

Could someone please explain to me what this means and whether or not I need to be concerned about it?

 

Also, is there a better standard practice when data needs to be deleted from a SQL database to prevent the overhead becoming too large?

 

What is 'Optimize table' and how often should it be used?

 

Sorry for all the questions.

 

Many thanks in advance,

 

Ben_1uk

[Barand]

http://stackoverflow.com/questions/565997/in-mysql-what-does-overhead-mean-what-is-bad-about-it-and-how-to-fix-it

[vinny42]

In addition to what is said in that link:

 

Overhead has very little impact on performance, do not optimize pre-emptively.

Why: running optimize causes MySQL to completely rebuild the table. it does this

by marking the old table as "no longer used", and copying all data to the new table.

During this procedure you effectively have no working table.

If that operation is interrupted in any way other than what MySQL expects, you loose both copies

of the table. What kind of interruptions can you get? Well running out of diskspace is a very popular one.

MySQL doesn't (or didn't, I haven't tried this for a while) check for the required amount of diskspace.

So if you want to get more diskspace by running OPTIMIZE, make sure you have enough space

to rebuild the table (which you probably don't, otherwise you wouldn't want to clear diskspace   )

 

I don't know about MySQL but other databases have freespace mappers that keep track of this overhead

and re-use it when the amount of space is enough to hold a new record.

[ben_1uk]

Thank you both very much for your replies.

 

What would be considered an excessive 'overhead' in terms of size?

 

Ben_1uk

[ben_1uk]

 

I don't know about MySQL but other databases have freespace mappers that keep track of this overhead

and re-use it when the amount of space is enough to hold a new record.

 

Vinny,

Are you saying that because of there being an overhead, new data cannot be inserted into the table? Sorry, I'm new to SQL databases and still learning as I go along.

[vinny42]

Excessive overhead, yeah it's a bit of a grey area. Small tables aren't bothered by overhead, and large tables can be too large to optimize anyway.

I'd define "too much overhead" as hunderds of megabytes of wasted space in a table. That's purely a gut feeling, not based on any measurements.

As long as you don't optimize to get a few dozen megabytes back, especially if the table is 2GB.

 

 

Are you saying that because of there being an overhead, new data cannot be inserted into the table? Sorry, I'm new to SQL databases and still learning as I go along.

 

Of course overhead doesn't prevent new records from being inserted :-) That would be weird.

 

I'll try to explain:

Databases store information, or "records" in a file on disk. Files are sequencial things, each record is written directly after the previous record, like words in a text.

When a record is removed, the space where the record used to be is marked as 'empty'. When a new record is created the database needs to find an empty space in the file, large wnough to hold the new record. Most databases don't search for empty space inside the file, they simply append the new record to the end of the file, where there is always enough space.

PostgreSQL and the like, maintain a freespacemap, that effectively remembers where all the empty spaces in the file are. When a new record has to be stored, the freespacemap is used to find a gap large enough to hold the new record. If there is such a gap, it's re-used by the new record, thus reducing the amount of wasted space.

 

So again, overhead has no effect at all except for wasting diskspace. If you can live with the waste, you can live with the overhead.

[Barand]

I'd say the time to optimize is when it begins to have a significant negative impact on performance.

[vinny42]

 

I'd say the time to optimize is when it begins to have a significant negative impact on performance.

 

How do you suggest to measure the performance effect of table bloating? :-)

[ben_1uk]

Thank you again.

 

I don't know of any other way of deleting the data without optimising or rebuilding the table, which from you have said above, would be a little heavy handed and unnecessary.

 

I have needed to delete more data today and the overhead now stands at around 13,000B.

 

One other question...upon a new record being inserted into the table, the overhead should reduce right..?

 

Ben_1uk

[vinny42]

 

I have needed to delete more data today and the overhead now stands at around 13,000B.

 

So you have hardly any overhead at all :-)

 

One other question...upon a new record being inserted into the table, the overhead should reduce right..?

 

Only if MySQL can re-use the empty space left behind from earlier DELETE operations which I'm not sure it can.

 

But I really wouldn't worry about this overhead thing, if you're not running  out of diskspace or experiencing an unexplained slowdown then the overhead is simply not an issue.

[Barand]

How do you suggest to measure the performance effect of table bloating? :-)

When performance degradation becomes noticeable and unacceptable.

[vinny42]

 

When performance degradation becomes noticeable and unacceptable.

 

Yeah, so how can you tell that the overhead is causing a performance issue?

[kicken]

Only if MySQL can re-use the empty space left behind from earlier DELETE operations which I'm not sure it can.

I had thought I read somewhere that MySQL did reuse space on inserts. A little digging around in the manual revealed:

Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

So it seems it does.

[vinny42]

 

So it seems it does. 

 

Thanks for digging that up, that should put all worries about performance issues through overhead to rest.