Delete job caused tables to grow larger in slaves
Posted by: Benny Pei
Date: July 17, 2017 09:03AM

Hello all,

I have a question. I have a replicated environment. One master and two readonly slaves. I have a few large partitioned table about 60GB. Today I found the table in slave is much larger than the master. I ran a query to check the data size,index size and fragmentation. The data size is much larger in slaves. The second slave which we built recently didn't experience the same we have in the first slave. That indicates there must be some fragmentation. But I don't understand why. Could someone explain what might have occurred? I need to confirm with our developer whether we have a delete job for this table but he is OOO today. Could the delete batch job caused the slave table to grow in size? The replication is doing row based replication and we are running on version 5.5. All tables are in InnoDB format.

I need some suggestion. Any input is truly appreciated.

benny



Master
====

+----------------+------------+-------------+--------------+-----------+------------+
| PARTITION_NAME | TABLE_ROWS | data_length | index_length | data_free | frag_ratio |
+----------------+------------+-------------+--------------+-----------+------------+
| m01 | 0 | 0 | 0 | 0 | 0.0000 |
| m02 | 0 | 0 | 0 | 0 | 0.0000 |
| m03 | 0 | 0 | 0 | 0 | 0.0000 |
| m04 | 6385619 | 1483 | 738 | 0 | 0.0000 |
| m05 | 163694900 | 39429 | 19463 | 0 | 0.0000 |
| m06 | 0 | 0 | 0 | 0 | 0.0000 |
| m07 | 0 | 0 | 0 | 0 | 0.0000 |
| m08 | 0 | 0 | 0 | 0 | 0.0000 |
| m09 | 0 | 0 | 0 | 0 | 0.0000 |
| m10 | 0 | 0 | 0 | 0 | 0.0000 |
| m11 | 0 | 0 | 0 | 0 | 0.0000 |
| m12 | 0 | 0 | 0 | 0 | 0.0000 |
+----------------+------------+-------------+--------------+-----------+------------+


Slave
====

+----------------+------------+-------------+--------------+-----------+------------+
| PARTITION_NAME | TABLE_ROWS | data_length | index_length | data_free | frag_ratio |
+----------------+------------+-------------+--------------+-----------+------------+
| m01 | 0 | 0 | 0 | 0 | 0.0000 |
| m02 | 0 | 0 | 0 | 0 | 0.0000 |
| m03 | 0 | 0 | 0 | 0 | 0.0000 |
| m04 | 5695472 | 1484 | 738 | 5 | 0.0023 |
| m05 | 156336301 | 43878 | 19601 | 5 | 0.0001 |
| m06 | 0 | 0 | 0 | 0 | 0.0000 |
| m07 | 0 | 0 | 0 | 0 | 0.0000 |
| m08 | 0 | 0 | 0 | 0 | 0.0000 |
| m09 | 0 | 0 | 0 | 0 | 0.0000 |
| m10 | 0 | 0 | 0 | 0 | 0.0000 |
| m11 | 0 | 0 | 0 | 0 | 0.0000 |
| m12 | 0 | 0 | 0 | 0 | 0.0000 |
+----------------+------------+-------------+--------------+-----------+------------+


benny

Options: ReplyQuote


Subject
Written By
Posted
Delete job caused tables to grow larger in slaves
July 17, 2017 09:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.