Menu
I am more familiar with PostgreSQL than MySQL. Have encountered wraparound Id failure once with the PostgreSQL db and then understood the importance of vacuuming in the db. Actually, that was such a massive overhead work to deal with(and it was with an old version 7.4.3 which is updated a few months back to have the autovacuum). When comparing MySQL with PostgreSQL, assume that MySQL does not have to deal with such overheads like vacuum in PostgreSQL. Is this assumption right?
Also why is it a vacuum not needed with MySQL Dbs compared to PostgreSQL? Is there any other optimization alternatives similar to vacuum exist for MySQL dbs?
780
The MySQL approximation of PostgreSQL's vacuum is OPTIMIZE TABLE tablename (MySQL docs). It performs a similar function in MySQL as PostgreSQL in that, depending on the storage engine used, it reclaims unused space, reorganizes indexes and tables, and defragments data files.
In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.
The biggest difference between Vacuum Full and Vacuum is that Vacuum Full physically deletes dead tuples and re-releases the released space to the operating system, so after vacuum full, the size of the table will be reduced to the actual space size.
Also since FULL VACUUM takes exclusive lock on the table, it blocks all operations on the corresponding table. So doing FULL VACUUM sometimes can slow down the overall database. In summary Full VACUUM should be avoided unless it is known that the majority of storage space is because of dead tuples.
Robert Haas wrote on this topic.
The short version is that InnoDB uses rollback logs, more like Oracle's design. Only the most recent version of a row is kept on the main table. It must manage log purging, an asynchronous/delayed operation with a related function to PostgreSQL's VACUUM.
This means more writes to do on updates and makes access to old row versions quite a lot slower, but gets rid of the need for asynchronous vacuum and means you don't have table bloat issues. Instead you can have huge rollback segments or run out of space for rollback.
So it's a trade-off, a design with a different set of advantages and problems.
If you're talking about MyISAM tables, then it's totally different. PostgreSQL's tables won't eat your data. MyISAM will. PostgreSQL's tables are transactional. MyISAM isn't. A flat file doesn't require VACUUM either, that doesn't make it a good idea.
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
