[MDEV-32121] Table size in DB Replication is not same Created: 2023-09-07  Updated: 2024-01-08  Resolved: 2024-01-08

Status: Closed
Project: MariaDB Server
Component/s: Configuration, Replication, Storage Engine - InnoDB
Affects Version/s: 10.11
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: ASHWANI SINGH Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None

Attachments: PNG File 2023-09-18_16h07_02.png     Text File sessions.txt    

 Description   

Hi Team,

We have a MariaDB Replication Setup and on the same we had created a database.
Inside the database we have created a table in which we are performing CRUD Operations.

And we had observed that the table size on Maria DB1 is around 1-2 GB while size on Maria DB2 is around 250-300GB and it keeps on increasing.

We have also found that data-free variable from output of show table status command keeps on increasing.

When we take backup of that table & restore it again then the size gets decreased & then after some time it gets increased.

Please suggest the way forward.



 Comments   
Comment by Kristian Nielsen [ 2023-09-07 ]

This sounds more like a support request than a bug report.
Is the data in the table on the slave identical with the data on the master? That is, are there extra rows in the table on the slave taking up the extra space, or is the data identical, but the space usage on the slave somehow much larger?
What is the SHOW CREATE TABLE of the table in question?
What is the output of the commands run on the master and slave that show the difference in size used?
Are there any long-running queries on the slave that are holding old read snapshots open and preventing purge to run on the slave? If purge cannot free old deleted rows, then this can lead to excessive use of storage.

Comment by ASHWANI SINGH [ 2023-09-07 ]

Hi Kristian,

Data on slave & master is identical.
There is no extra rows on the slave.
There are no queries running on slave all queries are pointed to the master only.

Query used to check size:

SELECT table_schema as `Database`, table_name AS `Table`, PARTITION_NAME, from_unixtime(SUBSTRING_INDEX(SUBSTRING_INDEX(PARTITION_NAME,"",2),"",1)*3600, '%Y%m-%d %H:%i:%s') as DateTime,round(((data_length + index_length)/1024/1024/1024 ), 2) `SizeGB` FROM information_schema.PARTITIONS where table_schema='alepopcrf' ORDER BY SIZEGB DESC limit 20;

Show create table output is mentioned in the file attached. sessions.txt

I have also tried to reproduce this scenario for another table & I was able to reproduce it so this is not a schema related issue.

Comment by Kristian Nielsen [ 2023-09-07 ]

Sorry, I'm not familiar with exactly how data_length and index_length in INFORMATION_SCHEMA are computed. It's hard to know what would cause these to differ between the servers without more info and a way to reproduce.

If the data is correct, then it might not be related to replication.

Maybe someone else can have an idea.

Comment by ASHWANI SINGH [ 2023-09-07 ]

Can you please suggest to me the best way to calculate the table size?
Also, if you can check for this issue with your team?

Comment by Sergei Golubchik [ 2023-09-08 ]

what mariadb versions you use? on both nodes

Comment by ASHWANI SINGH [ 2023-09-09 ]

Hi Team,

We are using MariaDB Version 10.7.4

Comment by Sergei Golubchik [ 2023-09-10 ]

10.7 has reached EOL about half a year ago, the will be no more bug fix releases of 10.7. Please, check whether you still have this issue on one of the still maintained MariaDB versions. See https://mariadb.org/about/#mariadb-general-release-maintenance-periods

Comment by ASHWANI SINGH [ 2023-09-18 ]

Hi Team,
We have observed the same issue on MariaDB 10.11.5.
Please find the attached screenshot for your reference.
Kindly look into it & help with the resolution as soon as possible.

Comment by Sergei Golubchik [ 2023-10-23 ]

Thanks. So the .ibd file is 348M. What is the .ibd file size on the other node?

Comment by ASHWANI SINGH [ 2023-10-24 ]

Hi Sergei,

ibd size on another node is 39MB.
Please check & feedback asap.

Comment by Sergei Golubchik [ 2023-11-03 ]

is the table definition identical?
I mean, show create table on both nodes.
Is the data identical? Like, CHECKSUM TABLE, the same?
How did you bootstrap the slave, where did it get the initial table from? Was it an empty slave and everything, including CREATE TABLE was replicated or did you restore it from a backup?

Comment by ASHWANI SINGH [ 2023-11-04 ]

Hi Sergei,
The table & checksum are identical.
Data is also identical.

Replication we enabled using the MASTER LOG File & MASTER LOG POS.
The table was also created via replication only.
There are some applications that write data on the master DB.

Comment by Sergei Golubchik [ 2023-11-04 ]

still, please, show the output of show create table on the master.

Comment by ASHWANI SINGH [ 2023-11-16 ]

Hi Sergei,
Please find the attached txt file.
sessions.txt

Comment by Sergei Golubchik [ 2023-11-24 ]

Do you have InnoDB page compression enabled? What's the filesystem on the master and on the slave, the same?

Generated at Thu Feb 08 10:28:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.