ben_1uk Posted October 9, 2013 Share Posted October 9, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/ Share on other sites More sharing options...
Barand Posted October 9, 2013 Share Posted October 9, 2013 http://stackoverflow.com/questions/565997/in-mysql-what-does-overhead-mean-what-is-bad-about-it-and-how-to-fix-it Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453295 Share on other sites More sharing options...
vinny42 Posted October 9, 2013 Share Posted October 9, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453302 Share on other sites More sharing options...
ben_1uk Posted October 10, 2013 Author Share Posted October 10, 2013 Thank you both very much for your replies. What would be considered an excessive 'overhead' in terms of size? Ben_1uk Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453380 Share on other sites More sharing options...
ben_1uk Posted October 10, 2013 Author Share Posted October 10, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453381 Share on other sites More sharing options...
vinny42 Posted October 10, 2013 Share Posted October 10, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453383 Share on other sites More sharing options...
Barand Posted October 10, 2013 Share Posted October 10, 2013 I'd say the time to optimize is when it begins to have a significant negative impact on performance. Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453413 Share on other sites More sharing options...
vinny42 Posted October 10, 2013 Share Posted October 10, 2013 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? :-) Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453418 Share on other sites More sharing options...
ben_1uk Posted October 10, 2013 Author Share Posted October 10, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453421 Share on other sites More sharing options...
vinny42 Posted October 10, 2013 Share Posted October 10, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453422 Share on other sites More sharing options...
Barand Posted October 10, 2013 Share Posted October 10, 2013 How do you suggest to measure the performance effect of table bloating? :-) When performance degradation becomes noticeable and unacceptable. Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453429 Share on other sites More sharing options...
vinny42 Posted October 10, 2013 Share Posted October 10, 2013 When performance degradation becomes noticeable and unacceptable. Yeah, so how can you tell that the overhead is causing a performance issue? Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453437 Share on other sites More sharing options...
kicken Posted October 10, 2013 Share Posted October 10, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453442 Share on other sites More sharing options...
vinny42 Posted October 10, 2013 Share Posted October 10, 2013 So it seems it does. Thanks for digging that up, that should put all worries about performance issues through overhead to rest. Quote Link to comment https://forums.phpfreaks.com/topic/282841-can-somebody-tell-me-what-overhead-is-in-my-sql-database/#findComment-1453448 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.