Contolling overhead in mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Contolling overhead in mysql

Post by mikebr »

I have a development database on my server and during testing i find that there is a lot of overhead in one of the tables after a short amount of use.....

Data 884 Bytes
Index: 7,168 Bytes
Overhead: 994 Bytes

Yet all the other tables have no overhead at all, I can't seem to find any clear information on what caused overhead or if it is important.

Maybe some experianced mysql user might enlighten us as to what creates overhead and how to avoid it as much as possible.

Thanks
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

i currently cannot remember the name of the function (probably the drink to be honest 8O ) but there is a function in PHPmyADMIN that basically, "cleans" up the table and optimises it basically, try that and see if theres any change.

It would also be a good idea to write a function that can optimise your tables automatically, so basically yopu could click a link in your CMS area of your site and it would optimise all your tables for you automatically, i have a tendency to employ this.....

..hope that helps
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

malcolmboston wrote:i currently cannot remember the name of the function (probably the drink to be honest 8O ) but there is a function in PHPmyADMIN that basically, "cleans" up the table and optimises...
How about the OPTIMIZE function :P
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

Yes I understand that the above 'malcolmboston'is possible but I am really trying to find out the why's behind what causes a tables overhead and why one table needs it and another not. Is it because my php scripts are badly written, is it the way the inserts, updates are being made? what affects this aspect of a table in mysql?

Thanks
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

MySQL uses files to store the data and as they are accessed, an 'overhead' is built up of data which is cached, in use, stored temporarily etc. Over time, this overhead builds up and I would assume you would eventually get a performance decrease. The OPTIMIZE command from within MySQL and phpMyAdmin gets rid of the overhead.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

OK, I see. So why would one table get an overhead and another 'even much larger table' not, if there is as much overhead as this is the schema badly designed maybe? Seems strange as to one table gets a lot of overhead with little use while others don't get any.

Thanks
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

There really isnt any need as far as I can see. The overhead is really small, and will probably go up and down by itself since you have people adding and deleting from the table all the time.

If we were talking many MB I could understand you, but a few hundred KB is really silly spending so much time avoiding since it will appear in 5 minutes again when people delete something from your table.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

I haven't really worried about the overhead before as it has only ever been a very small amount 'if any' on a few tables from other databases I have worked on, much smaller than what is showing here and so my attention was never really drawn to it, or at least it was but not enough to interest me. I felt it strange in this case because the table was so small and only has 1 'text' column apart from the main id column and the id column reference from another table, although the 'text' column entries are updated a lot.

Thanks
Post Reply