maria-discuss team mailing list archive

[Hartmut Holzgraefe <hartmut@xxxxxxxxxxx>]

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/