Why is my MySQL table building an "Overhead"/what is that/how can I prevent it?

[f.ruecker]

Hey Folks,
I hope you'll be nice to me and let this post go through even know its
mysql and not directly php.
But of course my script is php.

The problem is, that my Table (1,8 MIO entries/100 MB/MyISAM) is not
working anymore. Everytime my script wants to write in something the
number of entries decreases, and at the "Overhead" column the number is
growing.

How can I make my table work without that overhead problem, without any
further crashes and without loosing my data?

If I use the repair or optimize function (phpmyadmin) the overhead is
deleted, but the data is still lost, and the next time my script trys
to put in something more entries will be deleted.

I hope you can help me with this.
Have a good Sunday.

Flo.

[Frank]

f.*******@gmail.com wrote:

Hey Folks,
I hope you'll be nice to me and let this post go through even know its
mysql and not directly php.
But of course my script is php.

The problem is, that my Table (1,8 MIO entries/100 MB/MyISAM) is not
working anymore. Everytime my script wants to write in something the
number of entries decreases, and at the "Overhead" column the number is
growing.

How can I make my table work without that overhead problem, without any
further crashes and without loosing my data?

I'm no DBA or mysql-wiz, but I'm sure overhead is probably just that,
the db is running with a little slack. I'm guessing disk-space which is
reserved but not currently in use.

You _really_ don't want to move data around whenever a 'hole' occurs,
say as a single row is deleted. Instead you keep that space open, and
sooner or later there will probably come an insert which fits in that
space. Perhaps a few bytes shorter, but what difference does a few
bytes, or even a million combined, make in a 100MB+ db.

Overhead increasing at the same time as data goes bye-bye makes perfect
sense, but I'm sure even mysql wouldn't delete already stored rows
unless you tell it to.

Just forget about the whole overhead issue, focus on fixing your code
instead. Run an optimize whenever you're wasting more disk space than
you can afford. Best of luck.

[Colin McKinnon]

Frank wrote:

f.*******@gmail.com wrote:

Everytime my script wants to write in something the
number of entries decreases, and at the "Overhead" column the number is
growing.

How can I make my table work without that overhead problem, without any
further crashes and without loosing my data?

I'm no DBA or mysql-wiz, but I'm sure overhead is probably just that,
the db is running with a little slack. I'm guessing disk-space which is
reserved but not currently in use.

Indeed.

If it's still happenning after you have done a comprehensive repair on your
database files then something is seriously wrong. MySQL is (IME) quite
reliable. Try switching disks and/or upgrading/downgrading to a recent
stable version.

C.

[Wayne]

On 17 Apr 2005 13:42:35 -0700, "f.*******@gmail.com"
<f.*******@gmail.com> wrote:

The problem is, that my Table (1,8 MIO entries/100 MB/MyISAM) is not
working anymore.
Repair your tables and ensure that your server is operating correctly
(no disk corruption, bad memory, etc).
How can I make my table work without that overhead problem, without any
further crashes and without loosing my data?

"Overhead" is a normal part of the operation of the database. When
you delete something, the space isn't immediately reclaimed. When new
records are added, it fills the overhead space. This is a performance
optimization and there is no harm in it.