MySQL read replica using more storage and has more rows than master DB

Question

We have a MySQL RDS instance, with a master and read replica setup. The master DB is used by the prod application, while the read replica is just for engineers to debug and analyse something.

One of the tables we have has grown exponentially large over the years and is consuming most of the DB storage space. This table is basically a record of Kafka events we receive from other microservices in our company. Once we receive such an event, we process it, extract some meaningful information from it and store it in another table in a more consumable format. That's the end. At this point, the original Kafka event we got is no longer needed.

Since this table of events is merely needed for a few minutes or hours until we finish the processing, we decided to purge older records. The table had around 2-3 years worth of data, and we decided to first go with a 12-month retention period and see how much storage space we save.

1. We first ran a bulk DELETE operation on records older than 12 months on the master DB.
2. We also wrote a cron that will run every day and do the same DELETE operation, so that every day, anything older than 1 year is deleted.

But then we had to run OPTIMIZE to reclaim the disk space. Since OPTIMIZE is a bulky operation and takes longer the bigger the table is, we decided to run this first on the read replica, and this is where we noticed something strange.

This is the storage consumed by the table on the read replica

mysql> SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Size in GB`, round(((data_free) / 1024 / 1024 / 1024), 2) `Data free Size in GB` FROM information_schema.TABLES WHERE table_name = "events";
+--------+------------+----------------------+
| Table  | Size in GB | Data free Size in GB |
+--------+------------+----------------------+
| events |     295.48 |                 1.13 |
+--------+------------+----------------------+
1 row in set (0.30 sec)

And this is the storage consumed by it on the master DB

mysql> SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Size in GB`, round(((data_free) / 1024 / 1024 / 1024), 2) `Data free Size in GB` FROM information_schema.TABLES WHERE table_name = "events";
+--------+------------+----------------------+
| Table  | Size in GB | Data free Size in GB |
+--------+------------+----------------------+
| events |     273.44 |               190.26 |
+--------+------------+----------------------+
1 row in set (0.32 sec)

When we had done this experiment 1 month back, the data_length + index_length on the read replica was around 226 GB (while data_free was about 5 MB). In just a month, we have seen an increase of 70 GB. Looking at the RDS storage metric graph confirms it. The read replica storage is decreasing by around 1 GB a day (it used to decrease by 2-3 GB earlier, now it's decreased somehow), while on the master DB, it's shrinking by about 300 MB per day.

The high data_free on master DB is expected since we didn't run OPTIMIZE on it, and the 1.13 GB data_free on replica is because in the last 1 month, the cron to DELETE records has run 30-40 times and thus, there are new DELETEd records whose space we haven't reclaimed yet by running OPTIMIZE.

We then tried to find out the incoming - outgoing rate. The incoming rate is around 1.1 GB per day. The outgoing rate (the amount of data that the DELETE cron deletes) is around 1 GB per day. So, the read replica should be increasing by about 100 MB per day. But clearly, that's not happening.

Orthogonally, I did some rough calculations around how much storage each record in the table is taking. I did this by dividing the total space consumed by non-deleted records (data_length + index_length) by the total number of such records. The total number of such records is the same on both master and read replica, but since the total space is different, the per record size came out different.

For the read replica, it was 0.88 KB per record and for master, it was 1.37 KB. Going through this MySQL doc about calculating storage space, it turns out that the storage space according to the table schema SHOULD be 1.37 KB per record. So how is it 0.88 KB on the read replica?

I then went through some AWS docs about storage optimisation and found nothing. The binary log disk usage on the read replica is barely in KBs (according to the RDS graph), and the temporary table space on the replica is also low (see below).

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibtmp%';
+-----------------------------+------------------+------------+--------+--------------+---------------+-------------+
| FILE_NAME                   | TABLESPACE_NAME  | TABLE_NAME | ENGINE | INDEX_LENGTH | TOTAL_EXTENTS | EXTENT_SIZE |
+-----------------------------+------------------+------------+--------+--------------+---------------+-------------+
| /rdsdbdata/db/innodb/ibtmp1 | innodb_temporary |       NULL | InnoDB |         NULL |            12 |     1048576 |
+-----------------------------+------------------+------------+--------+--------------+---------------+-------------+
1 row in set (0.50 sec)

Running show table status where name = 'events' shows that the read replica has about 17 million more rows in the table than on master.

For master

mysql> show table status where name = 'events';
+--------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+--------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows      | Avg_row_length | Data_length  | Max_data_length | Index_length | Data_free    | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+--------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+--------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| events | InnoDB |      10 | Dynamic    | 309928229 |            795 | 246615654400 |               0 |  46988820480 | 204286722048 |      872384343 | 2020-04-14 08:25:49 | 2023-05-15 06:31:57 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+--------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+--------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.42 sec)

For read replica

mysql> show table status where name = 'events';
+--------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows      | Avg_row_length | Data_length  | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+--------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| events | InnoDB |      10 | Dynamic    | 327405857 |            876 | 287025692672 |               0 |  30246715392 | 1210056704 |      872370868 | 2023-04-06 07:44:41 | 2023-05-15 06:11:57 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+--------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.31 sec)

Looking at the history list length (through SHOW ENGINE INNODB STATUS) doesn't show any anomalies. It's 64 on the read replica and 97 on the master.

We're stuck figuring out what exactly is causing the rate of growth on the read replica to be so high. When the deletion rate (via the cron) is 1 GB and the new incoming data is 1.1 GB, shouldn't the overall growth rate be 100 MB?

Would really appreciate any help we can get on this. Thank you!

I'm not sure if I should paste the full output of SHOW ENGINE INNODB STATUS since it's too long, but please let me know if I should. Thank you again!

Edit: As requested, adding the cron code that DELETEs in chunks

The ORM we use is Peewee. This cron is a Lambda function that gets triggered once a day.

oldest_safe_date = datetime.datetime.now() - relativedelta(days=365)
batch_size = 10000

def full_delete() -> int:
    return Event.delete().order_by(Event.id).limit(batch_size).execute()

def partial_delete(sorted_events: List[Event]) -> int:
    event_ids_to_delete = []
    for event in sorted_events:
        if event.created_at < oldest_safe_date:
            event_ids_to_delete.append(event.id)
        else:
            break
    return Event.delete().where(Event.id << event_ids_to_delete).execute()


while True:
    curr_batch_rows = (
        Event.select(Event.id, Event.created_at)
        .order_by(Event.id)
        .limit(batch_size)
    )

    if not bool(curr_batch_rows):
        return

    if curr_batch_rows[-1].created_at <= oldest_safe_date:
        # delete the entire batch as all the events are older than the safe date
        full_delete()
    else:
        # delete partial batch as some rows are younger than the safe date
        partial_delete(curr_batch_rows)

    if curr_batch_rows_deleted < batch_size:
        # we've reached the last batch
        break

Answer 1

• SHOW TABLE STATUS only estimates the number of rows. It is not exact
• Because of the way BTrees are built and maintained, they can be bigger or smaller than expected. A large table (or index) can change by a factor or 2 (larger or smaller) after normal actions.
• Data_free is only one of several metrics for what is "free"; it does not include most BTree 'wasted' space.
• Avg_row_length is computed from total space / Rows. But, since "Rows" is an estimate, the Avg is not stable.
• OPTIMIZE TABLE will, for a large, non-Partitioned, table, shrink Data_free to between 4MB and 7MB.
• OPTIMIZE TABLE will return some of the freed space to the OS only if the table was created with innodb_file_per_table = ON.
• DELETEing one row us unlikely to show any meaningful difference. Often, it means that a BTree block how has one less row in it.
• OPTIMIZE TABLE is rarely worth the downtime that ir requires.
• A BTree block is 16KB; the basic unit of allocating and freeing. There is a much larger "extent" that comes into play for large tables.
• If you are doing big DELETEs there are several techniques for avoiding the need for OPTIMIZE Start a new Question to discuss the particular use case.
• For a "12-month retention period", I recommend PARTITION BY RANGE(...) with monthly [Partitions]. (http://mysql.rjweb.org/doc.php/partitionmaint). Its layout avoids the need for OPTIMIZE and uses DROP PARTITION, which is immensely faster than DELETE.
• Each secondary index is a B+Tree`, very similar to the Data's B+Tree.
• 1 GB per day? Typically the table is 400GB?
• Perceive this Question as mostly about poor metrics (space, row count, etc). If you would like to focus on some aspect (ingestion speed, retrieval speed, or disk space, etc), start a new Question with a different focus.

(I may have missed some of your points, but some of my points need to be stitched together to answer some of them. Let me know in a Comment if I left out one of your questions. Or start a new, narrower, Question.)

Re: cron

• Use 1000 instead of 10000 as the chunk size. This will decrease the impact on other queries.

• Combine the queries (using SQL syntax):

    DELETE FROM Event
        WHERE id < :cutoff
        ORDER BY id
        LIMIT 1000
  

• Use LIMIT 1000, 1 for probing to find the "cutoff". This is much better than shoveling around the [up to] 1000 ids.

• Consider adding something like "sleep 1" between iterations -- to further lessen the impact on other queries.

Impact

• A DELETE or UPDATE will lock some number of rows.
• Locks block other tasks by delaying them (cf lock_wait_timeout) or causing a deadlock (causing one or the other to abort and ROLLBACK).
• The larger the number of rows, the longer the lock time and the more rows that threaten to cause blockage and deadlock.
• If you are not doing anything else with that table in some other connection, then my points are moot. However, my suggestions are relatively harmless.
• Lowering the number of rows per batch will increase the total elapsed time -- but only slightly. In various situations, I recommend between 100 and 1000 as the "sweet spot" for speed versus interference.