On 28.07.2018 01:01, Arlindo Neto wrote:
Nice. Thanks Pavel.
A few more questions though: Can InnoDB reuse this space by adding new rows with AUTO_INCREMENT primary key when our table use clustered indexes? As new rows are added, wouldn't the table structure organize the data based on the primary key, preventing the disk space containing the old row from being written again?
yes, when adding new rows in primary key order only a row marked as free can't be reused for that, only a full page marked as free can. With only a single row deleted, a page will just continue to exist with that gap in it, unless doing an OPTIMIZE TABLE or a copying ALTER operation on it. If "enough" rows on a page get deleted, making its "fill factor" drop below a specific low water mark, InnoDB will attempt to re-balance the clustered primary index by merging it with other pages.
Also, when talking about large tables, could this "table bloat" impact performance in, for instance, sequential scans?
Depends on whether the table fits into the buffer pool, or is too large for that so that operations on the full table will become IO bound. As long as pages are cached in-memory in the buffer pool, there will be some impact due to cache misses, but that would usually not be that bad. With pages from disk the effect will be more obvious, as rows marked as freed still need to be read from disk and will consume precious IO bandwidth. And if the clustered primary index gets rebalanced a lot its pages will not necessarily be in linear order anymore, so that even a full table scan can cause quite a bit of random IO. That can be an issue on rotating disks for sure, but luckily not so much on SSDs anymore. -- Hartmut Holzgraefe - Principal Support Engineer (EMEA) MariaDB Corporation - http://www.mariadb.com/