Database size - different index size on replica node

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi

Can anyone tell me why size (index count) of tables are different on this two nodes? devX are slave replica from master node.

dev4

dump: 90M wrz 8 21:15 dictor.sql

ENGINE TABLE_NAME data_length index_length data_free

InnoDB dict_flights 66 0 5

root@hd-dev4:/scripts# cat /tmp/db_list | grep dicto
dictor dict_flights 66

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression

dict_flights 0 PRIMARY 1 account A 1 NULL NULL BTREE YES NULL
dict_flights 0 PRIMARY 2 campaign_id A 188595 NULL NULL BTREE YES NULL
dict_flights 0 PRIMARY 3 taken_date A 189433 NULL NULL BTREE YES NULL
dict_flights 1 idx_campaign_id 1 campaign_id A 206905 NULL NULL BTREE YES NULL

count:
209597

dev5

dump: 90M wrz 8 21:15 dictor.sql

ENGINE TABLE_NAME data_length index_length data_free

InnoDB dict_flights 72 7 2

root@hd-dev5:/scripts# cat /tmp/db_list | grep dicto
dictor dict_flights 78

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression

dict_flights 0 PRIMARY 1 account A 1 NULL NULL BTREE YES NULL
dict_flights 0 PRIMARY 2 campaign_id A 204681 NULL NULL BTREE YES NULL
dict_flights 0 PRIMARY 3 taken_date A 204681 NULL NULL BTREE YES NULL
dict_flights 1 idx_campaign_id 1 campaign_id A 204681 NULL NULL BTREE YES NULL

count:
209597

2 Likes
2

The index counts you are looking at are not actual numbers. They are estimates from statistics calculations. If you want to know the actual numbers, you need to run a real SELECT COUNT style SQL. I advise you run pt-table-checksum — Percona Toolkit Documentation on your master. This will detect any true differences on masters and slaves.

2 Likes
3

Hi

Amount of data on both tables are the same. Actually:

| count(*) |
±---------+
| 220730 |

data size:

srv 4:
83 MB
±-------±-------------±------------±-------------±----------+
| ENGINE | TABLE_NAME | data_length | index_length | data_free |
±-------±-------------±------------±-------------±----------+
| InnoDB | dict_flights | 77 | 7 | 5 |
±-------±-------------±------------±-------------±----------+
1 row in set (0,00 sec)

srv 5
77MB
±-------±-------------±------------±-------------±----------+
| ENGINE | TABLE_NAME | data_length | index_length | data_free |
±-------±-------------±------------±-------------±----------+
| InnoDB | dict_flights | 77 | 0 | 5 |
±-------±-------------±------------±-------------±----------+
1 row in set (0,00 sec)

1 Like