MariaDB Galera estimated database size is different from each node

Posted on

Question :

newbie question, why does my database size differs from other nodes in the cluster when I run:

SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema;

For the first and second nodes the database size is 6.1GB while on the third node it is only 1.6GB

But on storage they are have the same directory size of 20GB.

Is this normal? Or are some records not synced or maybe it’s just because I set the third node on backup on my HAProxy config?

Answer :

Please do both of these on each node.

SELECT COUNT(*) FROM tbl;
SHOW TABLE STATUS LIKE 'tbl';

If the COUNT(*) is identical (or very close) on the nodes, then I would not worry.

Nearly a factor of 4 makes it difficult to explain. Here are some thoughts:

  • The third node was ‘rebuilt’ recently, and a fresh copy of that table was built.
  • Certain ways of loading a table lead to more compact representation.
  • There were a lot of DELETEs from the table recently. This, together with the “fresh rebuild” would explain the more compact 1.6 while the others are quite fragmented.

I expect the table STATUS to show much less “Data_free” on node 3; if necessary, I can suggest why the difference is not enough to fully explain things.

Leave a Reply

Your email address will not be published. Required fields are marked *