MariaDB InnoDB Disk Space & Fragmentation

As a DBA, disk space has always worried me. In fact, the biggest problem for a MySQL/MariaDB DBA is the large, fragmented InnoDB Tablespaces. It was even more problematic when a single tablespace handled everything.

But even with a tablespace per table (innodb_file_per_table=ON) fragmentation on large tables can still be an issue and waste disk space.

I already covered this topic for MySQL [1][2], but those queries don’t work with the latest MariaDB Server.

Disk Space

Let’s start by getting the information about the disk space used by our tables and see how accurate it is.

By querying INFORMATION_SCHEMA, we can already get an overview using this revisited query:

WITH top5 AS (
    SELECT
        it.NAME,
        t.TABLE_ROWS,
        t.DATA_LENGTH,
        t.INDEX_LENGTH,
        t.DATA_LENGTH + t.INDEX_LENGTH AS TOTAL_SIZE_BYTES,
        t.DATA_FREE,
        it.FILE_SIZE AS FILE_SIZE_BYTES,
        it.ALLOCATED_SIZE,
        CAST(it.FILE_SIZE AS SIGNED)
          - CAST(t.DATA_LENGTH AS SIGNED)
          - CAST(t.INDEX_LENGTH AS SIGNED) AS WASTED_SIZE_BYTES
    FROM information_schema.TABLES AS t
    JOIN information_schema.INNODB_SYS_TABLESPACES AS it
      ON it.NAME = CONCAT(t.TABLE_SCHEMA, '/', t.TABLE_NAME)
    WHERE t.ENGINE = 'InnoDB'
    ORDER BY
        TOTAL_SIZE_BYTES DESC,
        FILE_SIZE_BYTES DESC,
        it.NAME
    LIMIT 5
)
SELECT
    NAME,
    TABLE_ROWS,
    FORMAT_BYTES(DATA_LENGTH)       AS DATA_SIZE,
    FORMAT_BYTES(INDEX_LENGTH)      AS INDEX_SIZE,
    FORMAT_BYTES(TOTAL_SIZE_BYTES)  AS TOTAL_SIZE,
    FORMAT_BYTES(DATA_FREE)         AS DATA_FREE,
    FORMAT_BYTES(FILE_SIZE_BYTES)   AS FILE_SIZE,
    FORMAT_BYTES(ALLOCATED_SIZE)    AS ALLOCATED_SIZE,
    FORMAT_BYTES(WASTED_SIZE_BYTES) AS WASTED_SIZE
FROM top5
ORDER BY
    TOTAL_SIZE_BYTES DESC,
    FILE_SIZE_BYTES DESC,
    NAME;

This will return the top 5 largest tables (I only display the first one, the one we will focus on):

*************************** 1. row ***************************
          NAME: sbtest/sbtest1
    TABLE_ROWS: 9863064
     DATA_SIZE: 2.09 GiB
    INDEX_SIZE: 134.66 MiB
    TOTAL_SIZE: 2.22 GiB
     DATA_FREE:    0 bytes
     FILE_SIZE: 2.23 GiB
ALLOCATED_SIZE: 2.23 GiB
   WASTED_SIZE: 10.34 MiB
...

Let’s now have a look on disk:

$ ls -lh  data/sbtest/*
-rw-rw---- 1 fred fred   70 Aug 14 13:02 data/sbtest/db.opt
-rw-rw---- 1 fred fred 2.2K Aug 14 13:04 data/sbtest/sbtest1.frm
-rw-rw---- 1 fred fred 2.3G Aug 14 13:05 data/sbtest/sbtest1.ibd

This is accurate.

Let’s have a graphical representation of the table and how filled it is:

We can see that the table (just created and populated) is well filled.

mariadb-innodb-space-map

Let me take a moment to present this new tool: mariadb-innodb-space-map.

I’ve always liked generating graphical representations of my InnoDB tablespaces using innodb_ruby; unfortunately, it isn’t compatible with MariaDB Server. I also wanted to create a tool that better fits my needs and removes innodb_ruby capabilities I don’t use.

mariabd-innodb-space-map also supports InnoDB-based binary logs; it can show how full they are.

Feel free to use this Python tool, report bugs, and, eventually, submit pull requests!

Fragmentation

Now let’s see the effects of fragmentation in our tablespace. Fragmentation occurs when you delete data or modify large blocks.

Deleting first records

Let’s start by deleting the first 500k records (5%) in our table:

MariaDB > delete from sbtest1 order by id limit 500000;
Query OK, 500000 rows affected (46.901 sec)

If we check again using our first query, we can see that the file size didn’t change at all:

*************************** 1. row ***************************
          NAME: sbtest/sbtest1
    TABLE_ROWS: 9363064
     DATA_SIZE: 2.09 GiB
    INDEX_SIZE: 134.66 MiB
    TOTAL_SIZE: 2.22 GiB
     DATA_FREE: 84.00 MiB
     FILE_SIZE: 2.23 GiB
ALLOCATED_SIZE: 2.23 GiB
   WASTED_SIZE: 10.34 MiB
...

We can also verify on the filesystem:

$ ls -lh  data/sbtest/*
-rw-rw---- 1 fred fred   70 Aug 14 13:02 data/sbtest/db.opt
-rw-rw---- 1 fred fred 2.2K Aug 14 13:04 data/sbtest/sbtest1.frm
-rw-rw---- 1 fred fred 2.3G Aug 14 13:15 data/sbtest/sbtest1.ibd

And indeed, we see that the size on disk didn’t shrink (as expected), nothing changed.

We can also see that 84 MB is free and 10 MB is wasted.

But in MariaDB Server, we cannot blindly trust the DATA_FREE column in INFORMATION_SCHEMA (we will see more on this soon).

Let’s check the graphic representation of the tablespace:

This doesn’t really look like 5% of the data… in fact, you also need to be sure all changes have been transferred from the Buffer Pool to the tablespace. This is the checkpointing. Don’t forget that, by default, MariaDB Server tries to avoid writing to disk and, when possible, works in RAM, grouping changes there before writing them to disk. See innodb_max_dirty_pages_pct=90;

So if we try to represent the tablespace before the flushing is done, the result won’t be correct.

Before generating a map of a tablespace, we need the purge to be done and then check that all the dirty pages for that specific table are flushed to disk:

We can check the purge status using the following query:

MariaDB > SELECT
       COUNT AS history_list_length,
       CASE
           WHEN COUNT = 0 THEN 'purge caught up'
           ELSE 'purge still pending'
       END AS purge_status
   FROM information_schema.INNODB_METRICS
   WHERE NAME = 'trx_rseg_history_len';
+---------------------+-----------------+
| history_list_length | purge_status    |
+---------------------+-----------------+
|                   0 | purge caught up |
+---------------------+-----------------+
1 row in set (0.004 sec)

MariaDB > SELECT
          COUNT(*) AS cached_pages,
          SUM(OLDEST_MODIFICATION > 0) AS dirty_pages,
          ROUND(
              SUM(OLDEST_MODIFICATION > 0)
              * @@innodb_page_size / 1024 / 1024,
              2
          ) AS dirty_mb,
          MIN(NULLIF(OLDEST_MODIFICATION, 0)) AS oldest_dirty_lsn,
          MAX(NULLIF(NEWEST_MODIFICATION, 0)) AS newest_dirty_lsn
       FROM information_schema.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME = '`sbtest`.`sbtest1`';
+--------------+-------------+----------+------------------+------------------+
| cached_pages | dirty_pages | dirty_mb | oldest_dirty_lsn | newest_dirty_lsn |
+--------------+-------------+----------+------------------+------------------+
|         6445 |           0 |     0.00 |             NULL |      16671592658 |
+--------------+-------------+----------+------------------+------------------+
1 row in set (0.030 sec)

Now, it’s the right time to generate the tablespace image again:

In case you want to force the flushing, you can set innodb_max_dirty_pages_pct to 0.

Deleting random records

Now, we will remove 5% of records again, but randomly:

MariaDB > delete from sbtest1 order by rand() limit 500000;
Query OK, 500000 rows affected (4 min 48.879 sec)

We wait for the purge to complete and the checkpointing to finish.

When history_list_length is 0, and the dirty_pages for that table is also 0, we can generate the image again:

Here we don’t see differences in the png. Why?

Before deletion, pages were roughly 93–94% occupied. Randomly deleting 5% of the records distributes the removed records across nearly every leaf page:

Before: approximately 93.6% occupied
After: approximately 88.4% occupied

Almost no page becomes empty or even falls below the default 50% merge threshold:

index_pages_below_50_percent: 6 (tool setting)

But the tool also provides information when generating the image:

$ ./innodb_space.py data/sbtest/sbtest1.ibd fragmentation \
   -o sbtest04.png --layout=compact
Scanning tablespace: 146,432/146,432 pages (100%)
Generating PNG...
{
  "file": "data/sbtest/sbtest1.ibd",
  "file_size": 2399141888,
  "page_size": 16384,
  "page_count": 146432,
  "space_id": 72,
  "flags": 21,
  "fsp_size_pages": 146432,
  "free_limit": 146304,
  "extent_pages": 64,
  "page_types": {
    "fsp-header": 1,
    "ibuf-bitmap": 9,
    "inode": 1,
    "index": 138572,
    "free/uninitialized": 7841,
    "extent-descriptor": 8
  },
  "index_pages": 138572,
  "average_index_fill_percent": 88.38,
  "index_live_bytes": 1990834698,
  "whole_file_live_index_percent": 82.98,
  "index_pages_below_50_percent": 6,
  "index_garbage_bytes": 115582194,
  "free_page_runs": 19,
  "allocation_transitions": 37,
  "min_lsn": 11706888748,
  "max_lsn": 16671592658
}
wrote sbtest04.png

On disk, the size is still the same:

$ ls -lh  data/sbtest/*
-rw-rw---- 1 fred fred   70 Aug 14 13:02 data/sbtest/db.opt
-rw-rw---- 1 fred fred 2.2K Aug 14 13:04 data/sbtest/sbtest1.frm
-rw-rw---- 1 fred fred 2.3G Aug 14 13:42 data/sbtest/sbtest1.ibd

Deleting in the middle

Let’s delete even more data in the middle of our table:

MariaDB > delete from sbtest1 where id > 4749999 order by id limit 500000;
Query OK, 500000 rows affected (43.990 sec)

We can see that this is the expected result.

But the size didn’t change.

Deleting at the end

And now let’s try to delete at the end of the table and see what happens:

MariaDB > delete from sbtest1 where id > 9500000 order by id limit 500000;
Query OK, 473445 rows affected (47.595 sec)

Question: We deleted at the end but there are still filled pages at the end of the table, why??

This is normal; the blue data that remains near the end belongs to the secondary index, not the deleted end of the clustered primary-key range.

If we generate the SVG output of the tablespace and open it in a browser, we can see that the first blue page at the end belongs to the secondary index, k_1 (148):

Identifying tables to optimize

As a DBA, the most important thing is to identify tables that are good candidates for optimization because they are fragmented and have a lot of free space.

What we would do is to use DATA_FREE like this to identify such tables, where we list only the tables with more than 10% free space:

SELECT
      CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS table_name,
      ROUND(
          (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024,
          1
      ) AS tablespace_mb,
      ROUND(DATA_FREE / 1024 / 1024, 1) AS free_mb,
      ROUND(
          100 * DATA_FREE /
          NULLIF(DATA_LENGTH + INDEX_LENGTH + DATA_FREE, 0),
          1
      ) AS free_pct
  FROM information_schema.TABLES
  WHERE ENGINE = 'InnoDB'
    AND TABLE_SCHEMA NOT IN (
        'mysql',
        'information_schema',
        'performance_schema',
        'sys'
    )
    AND DATA_FREE > 0
  HAVING free_pct > 10
  ORDER BY free_pct DESC, free_mb DESC;

This is the result with our example:

+----------------+---------------+---------+----------+
| table_name     | tablespace_mb | free_mb | free_pct |
+----------------+---------------+---------+----------+
| sbtest.sbtest1 |        2362.1 |   303.0 |     12.8 |
+----------------+---------------+---------+----------+
1 row in set (0.001 sec)

So we could save 300MB by optimizing the table according to this query.

But remember I said earlier that we shouldn’t trust DATA_FREE.

INFORMATION_SCHEMA.TABLES.DATA_FREE mainly describes unallocated/free
tablespace space. But after random deletes, leaf pages usually remain allocated to the
B-tree but contain fewer records. Both allocated-page values may therefore
remain nearly unchanged while a rebuild could pack records into far fewer
pages. So the query could also return an empty set if the deletes are scattered everywhere in the table.

Another option is to check the data from the Buffer Pool:

WITH buffer_sample AS (
      SELECT
          TABLE_NAME,
          COUNT(*) AS sampled_pages,
          SUM(DATA_SIZE) AS sampled_data_bytes
      FROM information_schema.INNODB_BUFFER_PAGE
      WHERE TABLE_NAME LIKE '%t1%'
        AND PAGE_TYPE = 'INDEX'
      GROUP BY TABLE_NAME
  )
  SELECT
      CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS table_name,

      ROUND(
          (t.DATA_LENGTH + t.INDEX_LENGTH) / 1024 / 1024,
          1
      ) AS tablespace_mb,

      ROUND(
          (t.DATA_LENGTH + t.INDEX_LENGTH)
          * (
              1 - s.sampled_data_bytes
                  / NULLIF(s.sampled_pages * @@innodb_page_size, 0)
          )
          / 1024 / 1024,
          1
      ) AS free_mb,

      ROUND(
          100 * (
              1 - s.sampled_data_bytes
                  / NULLIF(s.sampled_pages * @@innodb_page_size, 0)
          ),
          1
      ) AS free_pct

  FROM information_schema.TABLES AS t
  JOIN buffer_sample AS s
    ON s.TABLE_NAME = CONCAT(
        '`', t.TABLE_SCHEMA, '`.`', t.TABLE_NAME, '`'
    )
  WHERE t.ENGINE = 'InnoDB'
  HAVING free_pct > 10
  ORDER BY free_pct DESC, free_mb DESC;

This will provide the following output:

+----------------+---------------+---------+----------+
| table_name     | tablespace_mb | free_mb | free_pct |
+----------------+---------------+---------+----------+
| sbtest.sbtest1 |        2059.1 |   482.6 |     23.4 |
+----------------+---------------+---------+----------+
1 row in set (0.029 sec)

So % free almost doubled, and free space increased by 60%.

Of course, using the Buffer Pool also has caveats; the table should be in the Buffer Pool for accurate values.

For example, after a restart (and no InnoDB Buffer Pool Load at startup), this is what we could see for the exact same query on the same server:

+----------------+---------------+---------+----------+
| table_name     | tablespace_mb | free_mb | free_pct |
+----------------+---------------+---------+----------+
| sbtest.sbtest1 |        2059.1 |  1899.0 |     92.2 |
+----------------+---------------+---------+----------+

So if we count all the records (it can be expensive), and then check again, the result is more accurate:

MariaDB > select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  8026555 |
+----------+
1 row in set (1.364 sec)

MariaDB > WITH buffer_sample AS (
      .... ORDER BY free_pct DESC, free_mb DESC;
+----------------+---------------+---------+----------+
| table_name     | tablespace_mb | free_mb | free_pct |
+----------------+---------------+---------+----------+
| sbtest.sbtest1 |        2059.1 |   482.0 |     23.4 |
+----------------+---------------+---------+----------+
1 row in set (0.050 sec)

Optimize

It’s time to optimize and see the real numbers:

MariaDB > optimize table sbtest1;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| sbtest.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sbtest.sbtest1 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (47.156 sec)

And now let’s verify on disk:

$ ls -lh  data/sbtest/*
-rw-rw---- 1 fred fred   70 Aug 14 13:02 data/sbtest/db.opt
-rw-rw---- 1 fred fred 2.2K Aug 14 17:29 data/sbtest/sbtest1.frm
-rw-rw---- 1 fred fred 1.8G Aug 14 17:30 data/sbtest/sbtest1.ibd

So we went from 2.3G to 1.8G. The query from the buffer pool is more accurate but requires loading the data into it.

The tablespace looks like this now:

Nicely packed!

Better option

So right now, we can’t fully trust either query. The one using DATA_FREE, and the one that requires loading the table in the Buffer Pool.

Again, I can propose a better option using mariadb-innodb-space-map.

The tool has an option (estimate-defrag) that will estimate the gain of using OPTIMZE on a table.

You can run it by performing a full scan of the file or by using all XEDS descriptor pages and some allocated-page samples.

This is an example output:

Reading allocation metadata: 118,016/118,016 pages (100%)
Sampling allocated pages: 2,048/2,048 pages (100%)
{
  "mode": "fast_sampled",
  "file": "data/sbtest/sbtest1.ibd",
  "space_id": 73,
  "page_size_bytes": 16384,
  "page_count": 118016,
  "sample": {
    "requested_pages": 2048,
    "pages_read": 2048,
    "physical_sample_regions": 64,
    "sampled_index_pages": 2045,
    "estimated_average_index_fill_percent": 76.01
  },
  "current_size": {
    "bytes": 1933574144,
    "MiB": 1844.0,
    "GiB": 1.801
  },
  "fully_free_tablespace": {
    "accuracy": "exact_from_XDES",
    "pages": 1266,
    "bytes": 20742144,
    "MiB": 19.78,
    "GiB": 0.019,
    "percent_of_current_file": 1.07
  },
  "reusable_inside_allocated_index_pages": {
    "accuracy": "sampled_estimate",
    "bytes": 454663573,
    "MiB": 433.6,
    "GiB": 0.423,
    "percent_of_current_file": 23.51
  },
  "estimated_after_optimize": {
    "accuracy": "sampled_estimate",
    "target_index_fill_percent": 93.0,
    "index_pages_current_estimated": 116579,
    "index_pages_after_estimated": 95280,
    "pages_after_extent_rounding": 95488,
    "size": {
      "bytes": 1564475392,
      "MiB": 1492.0,
      "GiB": 1.457
    }
  },
  "estimated_savings": {
    "accuracy": "sampled_estimate",
    "bytes": 369098752,
    "MiB": 352.0,
    "GiB": 0.344,
    "percent_of_current_file": 19.09
  },
  "notes": [
    "All XDES descriptor pages were read, so the completely 
     free page count is exact for this snapshot.",
    "Index occupancy and OPTIMIZE savings are extrapolated from evenly
     distributed allocated-page samples.",
    "Increase --samples for heterogeneous tablespaces or use the default
     full scan for the best estimate."
  ]
}

You can use jq to only display what you need:
$ ./innodb_space.py data/sbtest/sbtest1.ibd estimate-defrag --fast | jq '.estimated_savings' Reading allocation metadata: 118,016/118,016 pages (100%) Sampling allocated pages: 2,048/2,048 pages (100%) { "accuracy": "sampled_estimate", "bytes": 369098752, "MiB": 352.0, "GiB": 0.344, "percent_of_current_file": 19.09 }

Let’s verify both options in time and filesystem-cache usage:

Fast

$ time ./innodb_space.py data/sbtest/sbtest1.ibd estimate-defrag \ 
   --fast | jq '.estimated_savings'
Reading allocation metadata: 118,016/118,016 pages (100%)
Sampling allocated pages: 2,048/2,048 pages (100%)
{
  "accuracy": "sampled_estimate",
  "bytes": 369098752,
  "MiB": 352.0,
  "GiB": 0.344,
  "percent_of_current_file": 19.09
}

real	0m0.527s
user	0m0.498s
sys	0m0.032s

$ dbsake fincore data/sbtest/sbtest1.ibd
data/sbtest/sbtest1.ibd: total_pages=472064 cached=26912 percent=5.70

Half a second to estimate a 1.9G tablespace, loading 5.70% of it in memory (FS cache).

Full Page Estimate

Now let’s estimate again without the --fast option:

$ time ./innodb_space.py data/sbtest/sbtest1.ibd estimate-defrag  | jq \
'.estimated_savings'
Scanning tablespace: 118,016/118,016 pages (100%)
{
  "bytes": 368050176,
  "MiB": 351.0,
  "GiB": 0.343,
  "percent_of_current_file": 19.03
}

real	0m2.447s
user	0m1.196s
sys	0m0.728s

$ dbsake fincore data/sbtest/sbtest1.ibd
data/sbtest/sbtest1.ibd: total_pages=472064 cached=112225 percent=23.77

We can see that it’s longer (5 times) and uses more FS cache for a very similar estimate.

Let’s optimize and verify. First, let’s check the size on disk:

$ ls -l data/sbtest/sbtest1.ibd
-rw-rw---- 1 fred fred 1933574144 Aug 14 17:59 data/sbtest/sbtest1.ibd

Now we run optimize:

MariaDB > optimize table sbtest1;

And we verify the disk size after:

$ ls -l data/sbtest/sbtest1.ibd
-rw-rw---- 1 fred fred 1572864000 Aug 14 23:40 data/sbtest/sbtest1.ibd

Let’s do the math:

1933574144 − 1572864000 = 360710144   ---> 344MB

The tool’s estimations are validated, and the --fast option is recommended.

Conclusion

You can save space by defragmenting your InnoDB tablespaces in MariaDB Server. However, there is no easy and accurate way to know from SQL which tables would really benefit from an OPTIMIZE TABLE.

So I wrote a Python tool to help you find those tables and estimate the gain.

Let me know if you find it useful and use it.

Enjoy optimizing your InnoDB tablespace with MariaDB Server!

Cookie	Duration	Description
cookielawinfo-checbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.