Türker Yıldırım

The database that was 80% dead rows

Deep dive10 min read

The database volume was at eighty-four per cent and the plan was to grow it. Before doing that somebody asked what was actually in it, and the answer was a hundred and eighty gigabytes of files containing forty-one gigabytes of rows.

The symptom

SELECT table_name,
       ROUND(data_length/1073741824, 1)  AS data_gb,
       ROUND(index_length/1073741824, 1) AS idx_gb,
       ROUND(data_free/1073741824, 1)    AS free_gb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_free DESC LIMIT 5;

| received_webhooks | 2.1 | 0.4 | 88.2 |
| jobs              | 0.1 | 0.2 | 41.8 |
| sessions          | 0.3 | 0.1 | 12.4 |
| audit_log         | 18.2| 6.1 |  0.2 |

-- 142 GB of data_free across three tables.

Three tables account for all of it and they share a shape: high insert rate, high delete rate, and a retention policy that deletes rows without ever reclaiming the pages they occupied.

Why it happens

InnoDB marks deleted rows as free within the tablespace and reuses that space for new rows. It does not return it to the filesystem, and it does not compact pages — a page holding one row after nine were deleted stays a page.

For a table with a stable size that is correct and invisible. For a table that grew to eighty gigabytes during an incident in 2021 and has been small ever since, the file is permanently eighty gigabytes.

The fix

Distinguishing a disk problem from a performance one

data_free is a disk figure and is mostly harmless: the
space IS reused, so a table oscillating between 1 GB and
80 GB costs 80 GB of disk and no performance.

page fragmentation is not harmless: a page holding 1 row
after 9 were deleted still occupies a buffer pool page,
so a scan reads 10 pages to find 10 rows.

so the question is rows per page:
  SELECT table_rows / (data_length / 16384) AS rows_per_page
  FROM information_schema.tables WHERE table_name = ?;
| table             | pages   | rows      | rows/page |
| received_webhooks |  137216 |    41,208 |      0.30 |
| audit_log         | 1191936 | 18,204,118|     15.27 |

-- 0.3 rows per page: every scan reads three pages to find
-- one row. audit_log at 15 has no fragmentation.

Rows per page is the number that distinguishes a table wasting disk from a table wasting the buffer pool, and it is not a figure anybody looks at. The webhooks table at 0.3 rows per page was the one costing query time; the jobs table at forty gigabytes of data_free was costing only disk.

Reclaiming, and what it actually is

mysql> OPTIMIZE TABLE received_webhooks;
| Msg_text: Table does not support optimize, doing
|           recreate + analyze instead |

-- for InnoDB it maps to ALTER TABLE ... FORCE: a full
-- table copy, with a metadata lock at each end.

-- the same thing, said honestly:
ALTER TABLE received_webhooks ENGINE=InnoDB;

The friendly name is the problem: OPTIMIZE TABLE sounds like a maintenance command and is a table rebuild with the same risks as any schema change. On a large table it belongs behind the same online-schema-change tooling as an ALTER, and knowing that ENGINE=InnoDB is the no-op alter that forces a rebuild is what makes that possible.

$ gh-ost --host=db-replica-01 --database=shop 
    --table=received_webhooks 
    --alter="ENGINE=InnoDB" 
    --max-load='Threads_running=40' 
    --postpone-cut-over-flag-file=/tmp/gh-ost.postpone 
    --execute

Copy: 41208/41208 100.0%; Applied: 1204; Backlog: 0/1000

# 88 GB → 2.4 GB, in 4 minutes, with no lock.

The pattern that caused it

// the retention job, as written in 2019
ReceivedWebhook::where('created_at', '<', now()->subDays(14))
    ->delete();

// which deletes 40,000 rows a day from a table that
// receives 40,000 a day, and leaves the pages behind.

// what it should have been, from the start:
//   a partitioned table, dropping a partition per day
//   — a metadata operation, and the space is returned
ALTER TABLE received_webhooks
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p20221001 VALUES LESS THAN (TO_DAYS('2022-10-02')),
  ...
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- retention becomes one instant metadata operation, and
-- the file shrinks:
ALTER TABLE received_webhooks DROP PARTITION p20220917;

Dropping a partition is a metadata operation that returns the space immediately, which is the correct shape for any table with a time-based retention policy. The cost is real: the partitioning key must be in every unique index, so a table with a primary key that is not the timestamp needs a composite one — which changes the physical layout of every query.

For the webhooks table that was acceptable because nothing looks a row up by id alone. For the audit log it was not, and the audit log keeps its ordinary delete plus an annual rebuild.

The archive table, for the case that cannot be dropped

-- audit_log is retained for 7 years and queried for 90 days
INSERT INTO audit_log_archive
SELECT * FROM audit_log
WHERE occurred_at < NOW() - INTERVAL 90 DAY LIMIT 10000;

DELETE FROM audit_log
WHERE occurred_at < NOW() - INTERVAL 90 DAY LIMIT 10000;

-- chunked, with a pause, and the archive on slower storage.
-- the hot table stays at ~2 GB instead of 18.

Splitting a hot table from an archive is what keeps the working set proportional to the working data rather than to the history, and it is the change that most improves the buffer pool hit rate. The cost is that a query spanning the boundary now has to union two tables, which is a real constraint on anything reporting across the whole period.

Monitoring it, so this does not recur

-- a daily snapshot, so the trend is visible
INSERT INTO table_size_history
SELECT NOW(), table_name, data_length, index_length, data_free,
       table_rows,
       table_rows / NULLIF(data_length / 16384, 0) AS rows_per_page
FROM information_schema.tables
WHERE table_schema = 'shop';

-- and the alert:
--   rows_per_page < 5 on a table over 1 GB
--   data_free > 20 GB on any table

Alerting on rows per page rather than on data_free is what makes this a performance signal rather than a disk one, and the threshold has to be per table because a table of wide rows legitimately holds few per page. Five is a reasonable floor for narrow rows and would have fired on the webhooks table eighteen months earlier.

Verifying it worked

| table             | data_gb | free_gb | rows/page |
| received_webhooks |     2.1 |     0.1 |     18.40 |
| jobs              |     0.1 |     0.1 |     22.10 |
| sessions          |     0.3 |     0.0 |     31.20 |
| audit_log         |     2.2 |     0.1 |     15.80 |

-- 180 GB → 38 GB.

-- and the change that was not the goal:
--   buffer pool hit rate  994/1000 → 999/1000
--   p95 on the webhook lookup: 41ms → 6ms

The buffer pool improvement is the outcome that mattered and was not the reason for the work — a hundred and forty gigabytes of mostly-empty pages had been competing for memory with the data anybody actually reads. The disk saving is what justified the afternoon and the query latency is what it bought.

What this costs

A maintenance operation with no visible benefit, which is the category of work that does not get scheduled. The daily size snapshot and the rows-per-page alert are the mechanism that turns it from an archaeology exercise into a ticket, and they are two queries and a cron entry.

Partitioning is also a commitment that constrains the schema: the partition key in every unique index means a primary key that is a composite of an id and a timestamp, which changes how every query is planned. It is the right answer for a time-series table and it is not something to apply generally, and reversing it is another full rebuild.