Jan, Kristian, howdy. > Jan Křístek <jkr...@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 _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
