[MDEV-10530] Replication unable to catch up master, seconds behind master increase Created: 2016-08-10 Updated: 2017-10-17 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | Replication |
| Affects Version/s: | 10.0.26 |
| Fix Version/s: | 10.1 |
| Type: | Bug | Priority: | Major |
| Reporter: | Tomas Matejicek | Assignee: | Andrei Elkin |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | None | ||
| Environment: |
Debian 8.5 |
||
| Description |
|
I am using master-slave replication. Master and slave are the same mariadb versions. Replicatin is set up properly, using mixed format, and slave server replicates properly. But it is incredibly slow. Seconds behind master never reaches zero, and gradually increments. Replication is completely useless this way. There is no particular sql query which would block, the replication goes on, only very slow. SHOW PROCESSLIST does never show any sql query which would block the replication, all sql queries are very fast by themselves. Even if I set wild_ignore to %.% to ignore all tables in all databases, it still has a problem that it is delayed and the delay increases (by about 1 second each 2 seconds). But, when I mount the /var/lib/mysql directory to tmpfs, replication works fast. So it looks like it has something to do with syncing data to disk, or so. But, I do not see any high I/O on the disk at all. I fixed the problem by installing mysqld 5.5.50, this works without any issue at all. |
| Comments |
| Comment by Kristian Nielsen [ 2016-08-10 ] |
|
One possibility is that the mysql.gtid_slave_pos table is innodb (it is created using the default storage engine, but maybe that default was InnoDB at the time of creation). If so, you can ALTER TABLE mysql.gtid_slave_pos ENGINE=MyISAM (since you are not otherwise using InnoDB). That table is updated for every replicated event group, and that can be slow for InnoDB with default settings. |
| Comment by Tomas Matejicek [ 2016-08-10 ] |
|
The problem is kind of strange, and your suggestion will not be causing this because of the following: Before I upgraded to mariadb 10.0.26, I was using some older version of mariadb on both master and slave servers (the operating system was Slackware on both machines). Unfortunately I do not remember the mariadb version. Then, I moved master to other machine, where was Debian installed. Once I moved the master to Debian with mariadb 10.0.26 (using the very same config file), the problem started to occur - slave was unable to catch up, replication started to be delayed. So, moving master caused the slave to make problems. The slave server was intact at that moment, still using the older mariadb version. So storage engine for mysql.* tables was the very same, not changed. like with the older mariadb version, which worked just fine. It worked before with older mariadb version, so it must be a bug in newer mariadb release, on the master side. |
| Comment by Tomas Matejicek [ 2016-08-10 ] |
|
I have to add to my previous comment, that after I moved to new master, I "changed master" on the slave mariadb properly, to make sure it gets replication from the new master, of course. Just mentioning it to ensure you that the slave was indeed connected to new master after the change. |