1

I have a Percona MySQL Master-Master setup (5.6) with second master as passive master(read-only).

Slave:

mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| common_schema      |      1.89 |
| information_schema |      0.01 |
| mysql              |      1.70 |
| dataplay           |   1684.56 |
| dataplay1          |      0.14 |
| percona            |      0.20 |
| performance_schema |      0.00 |
+--------------------+-----------+

Master:

mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| common_schema      |      1.89 |
| information_schema |      0.01 |
| mysql              |      1.69 |
| dataplay           |   1614.80 |
| dataplay1          |      0.14 |
| percona            |      0.20 |
| performance_schema |      0.00 |
+--------------------+-----------+
7 rows in set (0.07 sec)

The size difference is massive. Is it something wrong with the setup which is running? What is wrong?

3

From the details, it looks like the 'massive' difference is ~70MB in the dataplay schema. You can adjust your query to find out exactly which tables are different.

Two potential reasons for this difference is:

  • Schema difference on the master vs slave. Does the slave have utf8 instead of latin1 on a column or three? Does the slave have additional indexes? You can compare the schema by running a mysqldump of just the schema (--no-data option) and then diff'ing the results.
  • The slave has actual different data. Do you have session-only data that the application stores and won't matter? If no, you can use pt-table-checksum to find rows that are actually different. Then you need to dig into why they are different and fix that issue before reseeding the passive master or using pt-table-sync to resolve the differences.
  • Plainly, the replication setup is not proper - correct? – tesla747 Jan 20 '16 at 19:16
  • More likely, it is the way the applications utilize the environments is the cause of the discrepancy. Some causes can be "fixed" by updating the replication configuration, others require application changes. – Derek Downey Jan 20 '16 at 19:44
  • @tesla747 the difference in size is not a direct proof of any problem, the database sizes will never be the same even if the actual data are. – jkavalik Jan 21 '16 at 8:17
  • @jkavalik correct but the size difference is way too much.. even if the master size is high and slave is less, logically it is acceptable. But this is other way around.. anyways looks like I will have to reconfigure the setup altogether – tesla747 Jan 21 '16 at 11:01
  • @tesla747 well, it's less than 5%, I would not call it much. Sounds well within the limit of a b-tree overhead imho (check answer from Rolando too). It is sure to better check if everything is ok, but other than that replication is only supposed to keep the same data, not the same storage files. – jkavalik Jan 21 '16 at 11:17
2

When you setup a Slave by loading a mysqldump from a Master, there is a strong likelihood that many of the the new Slave's tables will be bigger than the Master because of the order BTREE indexes are being loaded as the corresponding tables are being loaded. Usually that order is dictated by PRIMARY KEYs. If you talking about InnoDB, data and PRIMARY KEY nodes coexists in the same pages and segments (This is known as the gen_clust_index or Clustered Index).

This bloating of indexes due to ordered keys being loaded is also evident with MyISAM tables.

See my earlier posts

  • Sol Rolando..you mean to say that this is acceptable? – tesla747 Jan 21 '16 at 18:37
2

Which one was built first? How did you copy the data to the other one? Have you ever done OPTIMIZE TABLE or ALTER TABLE on one machine but not the other? ROW or STATEMENT based replication? Same indexes?

I ask about the order of the dual-master because I would sort of expect the Master to be smaller. RBR vs SBR impacts how the inserts/updates are performed on the Slave. Each secondary index is a BTree, separate from the BTree that contains both the Data and PRIMARY KEY.

Anyway, if you are using InnoDB, I would not worry until the difference is more than 40%, not a mere 4%. A BTree that has been inserted into a lot will have its blocks only about 69% full -- this is due to block splits, etc.

  • Master was built first. ROW based replication. No optimize runs on Master. Copied the data using Percona Xtra Backup. – tesla747 Nov 14 '16 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.