Re: [Maria-discuss] MariaDB master-slave chained replication and parallelism
Hi Kristian,
Thank you for your answer.
On Thu, 5 Aug 2021 at 09:44, Kristian Nielsen knielsen@knielsen-hq.org wrote:
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.
I see these counts almost at the end of 'mysql -e "show slave status\G"'
You can see these yourself in a mysqlbinlog output from a binlog on the
master respectively the slaves.
I will check that when I get the setup running again. Thank you for the tip.
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.
I am sure that at least the big tables use MyISAM on master and all the slaves. Could be that one or two small tables use InnoDB. But when the replication is running, just one counter is always increasing, the other is staying zero (or in case of switching the master while being run) staying fixed.
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 could be the reason, even though I am sure that all slaves use MyISAM for almost all tables. I suppose that (could be) the default engine might be switched to InnoDB.
The obvious answer is to change the tables to be InnoDB on the master. Which may or may not be possible in your setup.
Oh, this is not so easy. The size of a few MyISAM tables is reaching almost TBs.
And thank you for the idea with the BlackHole engine :)
Jan
I think you are going to have to bite the bullet on conversion to InnoDB sooner rather than later. The performance improvement you are going to see will likely be orders of magnitude. The longer you leave it, the more painful it is going to become. You could do it on a slave and then promote it, or you could do it with pt-online-schema-change. While MyISAM still has some very narrow niche uses, it really is way past time to retire it in any regular use.
On Thu, Aug 5, 2021 at 11:04 AM Jan Křístek jkresa@gmail.com wrote:
Hi Kristian,
Thank you for your answer.
On Thu, 5 Aug 2021 at 09:44, Kristian Nielsen knielsen@knielsen-hq.org wrote:
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.
I see these counts almost at the end of 'mysql -e "show slave status\G"'
You can see these yourself in a mysqlbinlog output from a binlog on the
master respectively the slaves.
I will check that when I get the setup running again. Thank you for the tip.
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.
I am sure that at least the big tables use MyISAM on master and all the slaves. Could be that one or two small tables use InnoDB. But when the replication is running, just one counter is always increasing, the other is staying zero (or in case of switching the master while being run) staying fixed.
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 could be the reason, even though I am sure that all slaves use MyISAM for almost all tables. I suppose that (could be) the default engine might be switched to InnoDB.
The obvious answer is to change the tables to be InnoDB on the master. Which may or may not be possible in your setup.
Oh, this is not so easy. The size of a few MyISAM tables is reaching almost TBs.
And thank you for the idea with the BlackHole engine :)
Jan _______________________________________________ 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
It's used as an archive of events and data. The data are written just once and then read multiple times.
I think that the response times are limited by the disk speed and that indexing for lookups works in a similar way on both MyISAM and InnoDB. We were considering upgrading it to Aria storage engine, as it offers better consistency (after a crash - I have read that somewhere), but it's not much time to do it now.
Could be the upgrade might be easier when we will have a DB-proxy in place.
On Thu, 5 Aug 2021 at 10:14, Gordan Bobic gordan.bobic@gmail.com wrote:
I think you are going to have to bite the bullet on conversion to InnoDB sooner rather than later. The performance improvement you are going to see will likely be orders of magnitude. The longer you leave it, the more painful it is going to become. You could do it on a slave and then promote it, or you could do it with pt-online-schema-change. While MyISAM still has some very narrow niche uses, it really is way past time to retire it in any regular use.
InnoDB is faster even on a read-only workload now and has been for over a decade. One of very, very few cases for use of MyISAM is if you use compressed read-only MyISAM tables.
On Thu, Aug 5, 2021 at 11:39 AM Jan Křístek jkresa@gmail.com wrote:
It's used as an archive of events and data. The data are written just once and then read multiple times.
I think that the response times are limited by the disk speed and that indexing for lookups works in a similar way on both MyISAM and InnoDB. We were considering upgrading it to Aria storage engine, as it offers better consistency (after a crash - I have read that somewhere), but it's not much time to do it now.
Could be the upgrade might be easier when we will have a DB-proxy in place.
On Thu, 5 Aug 2021 at 10:14, Gordan Bobic gordan.bobic@gmail.com wrote:
I think you are going to have to bite the bullet on conversion to InnoDB sooner rather than later. The performance improvement you are going to see will likely be orders of magnitude. The longer you leave it, the more painful it is going to become. You could do it on a slave and then promote it, or you could do it with pt-online-schema-change. While MyISAM still has some very narrow niche uses, it really is way past time to retire it in any regular use.
We don't use read-only compressed MyISAM tables, but I found out, that by using PACK_KEYS I can save a significant amount of disk space. With some small data, the index size can be the same as the data file size, with PACK_KEYS we saved about 4/5 of the index volume (if I am right).
On Thu, 5 Aug 2021 at 11:05, Gordan Bobic gordan.bobic@gmail.com wrote:
InnoDB is faster even on a read-only workload now and has been for over a decade. One of very, very few cases for use of MyISAM is if you use compressed read-only MyISAM tables.
participants (2)
-
Gordan Bobic -
Jan Křístek