Jan, Kristian, howdy.
Jan Křístek <jkresa@gmail.com> writes:
We have a MariaDB 10.3 replication setup with one master and a few chained slaves (each has log_slave_updates switched on). Master uses mainly MyISAM tables, slaves have about 10 or 40 threads for parallel replication.
Interesting is, that the first slave in the chain counts replicated statements into Non-Transactional Groups
So the first slave does not change the group type which remain non-transactional, I assume the input (master binlog) group is such.
and the following ones count them into Transactional Groups.
And the above transition can be explained by MDEV-24654 GTID event falsely marked transactional, its patch is under review.
Interesting. Where do you see these counts? My guess is that these are counting the "transactional" status flag on each GTID event in the binlog. You can see these yourself in a mysqlbinlog output from a binlog on the master respectively the slaves.
#190606 19:42:35 server id 1 end_log_pos 514 GTID 0-1-2 trans
If these show non-transactional on the master but transactional on the first slave, it sounds like you are replicating from MyISAM tables on the master to InnoDB tables on the slave. Try SHOW CREATE TABLE t on a relevant table on the master and the slave and see which storage engine they are using.
This remains to be a possibility too.
Also, when checking process lists it seems that just one statement is being processed at the time (of the many threads) on the first slave, while there are multiple slave replication statements being executed on the 2nd and following slaves.
This observation matches the theory that the tables are MyISAM on the master but InnoDB on the slaves. MariaDB parallel replication has limited capabilities in parallelising MyISAM changes. The main algorithms are based on optimistic apply, where transactions are run in parallel by default, and any conflicts are handled by rollback and retry. This is possible in InnoDB but not MyISAM. And the transactional status is checked on the table engine used on the master, not the slave.
Thus, the first slave sees MyISAM changes, and does not do parallel operation, but writes InnoDB transactions. These InnoDB transactions are then seen by following slaves which enables the parallel replication algorithms.
This fits to MDEV-24654 scenario.
Please, does anyone know the reason why the replicated statements are counted into different groups? Or, more importantly, how to increase the parallelism on the first slave in the chain?
The obvious answer is to change the tables to be InnoDB on the master. Which may or may not be possible in your setup.
I'd also recommend that.
A possibly crazy/theoretical idea would be to setup the first slave with the blackhole engine for all tables. This requires statement-based replication and doesn't store _any_ data on the slave, just passes statements through to the next slave in line.
Actually ROW format is fine so the 1st slave would re-log replicated group in ROW format to its binlog.
There's an old idea to use the blackhole engine in this way as a "replication relay", and IIRC the blackhole engine is transactional.
Right.
Not sure if this would actually work though, would require careful testing and is definitely not a supported configuration, I would say (but fun to think about).
I am also not sure how practical it could be in this specifica case, but it's fast as well :-))).
- Kristian.
Cheers, Andrei