tag:blogger.com,1999:blog-23496029.post1708230410469619438..comments2024-01-11T10:27:57.989+01:00Comments on MySQL Musings: Crash-safe ReplicationMats Kindahlhttp://www.blogger.com/profile/07528917029894926261noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-23496029.post-76604195710835932642015-11-18T15:34:17.453+01:002015-11-18T15:34:17.453+01:00Is it necessary to set sync_binlog=1 at a master t...Is it necessary to set sync_binlog=1 at a master to make it to be a crash safe master?Anonymoushttps://www.blogger.com/profile/13742542427736466799noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-1859057638725281612013-04-18T00:07:02.880+02:002013-04-18T00:07:02.880+02:00Hi Mats,
In 5.6 GA version, both the tables
doesn...Hi Mats,<br /><br />In 5.6 GA version, both the tables<br />doesn't have Master_id. Rithhttps://www.blogger.com/profile/07966285669099440768noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-82258820443030358752012-04-14T03:59:04.583+02:002012-04-14T03:59:04.583+02:00Hooray. Thanks for making a lot of progress on rep...Hooray. Thanks for making a lot of progress on replication.<br /><br />Block commit as described by Robert Hodges might not be that different from running the slave with innodb_flush_log_at_trx_commit=2. In both cases you avoid fsync on every commit.Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-44324796902293016122011-04-22T17:43:49.014+02:002011-04-22T17:43:49.014+02:00Hello Kristian, many thanks for your feedback! Ple...Hello Kristian, many thanks for your feedback! Please, allow me to correct your premises:<br /><br /> 1. In the current feature preview available on labs, the slave does not do INTRA-transaction but rather INTER-transaction parallelization on apply.<br /><br /> 2. Again, in the same feature preview available on labs, the slave apply procedure does not have a global lock, because different worker threads update their own row on a different table: mysql.slave_workers_info. As such binlog group commit is not at risk here...<br /><br />I invite you to read a worklog and a blog entry on this matter:<br /><br /> - http://forge.mysql.com/worklog/task.php?id=5569<br /> - http://d2-systems.blogspot.com/2011/04/mysql-56x-feature-preview-multi.html<br /><br />If you find bugs (which is likely, as the current implementation is yet a feature preview), or have more feedback, let us know about it. Again, thanks for taking time to look into this!<br /><br />Hello Robert, you have posted some good feedback as well, thanks! On applying multiple transactions at once to avoid the additional position row update, I don't think that the update is the expensive part of it. Indeed you're removing those from the execution path, but more importantly, you're removing several expensive COMMIT operations. The latter, IMHO, is what makes the difference (and not so much the former).Luís Soareshttps://www.blogger.com/profile/15801959795245235917noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-24912681138804325842011-04-20T10:17:24.479+02:002011-04-20T10:17:24.479+02:00Kristian, I prefer to actually go over the design ...Kristian, I prefer to actually go over the design myself, including examining the alternative options, before I publish it. This give me a chance to have good answers to the questions that undoubtedly arise. However, I do understand that YMMV.<br /><br />You can find the current status on the binary log group commit as <a href="http://forge.mysql.com/worklog/task.php?id=5223" rel="nofollow">WL#5223</a>, which includes the current tentative design Note that it is still work in progress.Mats Kindahlhttps://www.blogger.com/profile/07528917029894926261noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-24242239808870689102011-04-19T08:51:51.304+02:002011-04-19T08:51:51.304+02:00Mats, I am sorry you cannot comment on this. MySQL...Mats, I am sorry you cannot comment on this. MySQL is one of the most important programs in the Free Software community, and it would really help if things like this were designed and discussed publicly, not in private mailing lists, IRC channels, etc, etc.<br /><br />I just spent a year removing the InnoDB prepare_commit_mutex, which lost us group commit for >5 years. So I worry if you introduce a new user-visible feature that by design re-introduces the prepare_commit_mutex (this time as a table lock). So whatever solution you end up with, public discussions or no, please at least make sure you understand the problem.Kristian Nielsenhttp://kristiannielsen.livejournal.com/noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-66647639519088345372011-04-19T07:48:12.157+02:002011-04-19T07:48:12.157+02:00Robert, I would love to come to Sardinia, but I...Robert, I would love to come to Sardinia, but I'm not sure I will be able to. :)Mats Kindahlhttps://www.blogger.com/profile/07528917029894926261noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-4329478736153345802011-04-19T06:29:51.268+02:002011-04-19T06:29:51.268+02:00@Mats, you and Kristian should come to Giuseppe...@Mats, you and Kristian should come to Giuseppe's Open Database Camp in Sardinia next month and we can reflect on these matters together. It should be fun. :)Robert Hodgeshttps://www.blogger.com/profile/05379726998057344092noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-48568156990664024642011-04-19T00:50:37.646+02:002011-04-19T00:50:37.646+02:00Hi Kristian and Robert! It is quite interesting to...Hi Kristian and Robert! It is quite interesting to read your comments. I cannot fully comment on what you've written, it's just too much, but I can add one comment regarding one issue that Kristian mention.<br /><br />Kristian, there is no difference between writing to a file and to a table w.r.t. the locked row and group commits. In both cases it is necessary to handle the group commit/batch commit, so this implementation does not change anything. There are several ways of handling that (I will elaborate on them at some other time).<br /><br />Unfortunately, recovering the information from the binary log does not help since this is the *slave* information that is not in sync. The information could be written to the binary log on the slave (is that what you mean?), but that would mean that the binary log would have to be enabled for all slaves, and that is not always the case.Mats Kindahlhttps://www.blogger.com/profile/07528917029894926261noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-33737743428722037822011-04-18T21:05:08.425+02:002011-04-18T21:05:08.425+02:00@Kristian On the write overhead--there's defin...@Kristian On the write overhead--there's definitely some cost to tracking your position. Imagine you have short auto-commit writes with no block commit. Updating a table to track position adds an extra write for each user update. This is a worst case that can double the data going into the log, hence implies additional disk I/O. <br /><br />Disclaimer: I don't have benchmark numbers for this specific effect, just for block commit in general, which is a big win at least in our case. The actual impact could be quite variable depending on workload, fsync intervals, and hardware configuration. <br /><br />Incidentally I read with some interest your designs for parallelizing based on group commit. It will be interesting to see how the different approaches end up playing out practice. Performance results in this area are sometimes a bit non-intuitive.Robert Hodgeshttps://www.blogger.com/profile/05379726998057344092noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-81428569574425890792011-04-18T13:06:31.176+02:002011-04-18T13:06:31.176+02:00``I know the current project for parallel replicat...``I know the current project for parallel replication at MySQL is based on<br />running one transaction at a time, but split in multiple threads.''<br /><br />Kristian, actually it's not like that.<br />Consider another article<br />http://d2-systems.blogspot.com/2011/04/mysql-56x-feature-preview-multi.html<br />describing MTS implementation.<br />MTS runs multiple master transaction at a time (provided they fit to partitioning requirement), and that is supposed to make use of Innodb group commit.<br /><br />cheers,<br /><br />Andrei ElkinAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-23496029.post-67585279880044178302011-04-18T08:26:14.135+02:002011-04-18T08:26:14.135+02:00Robert,
The main problem with your suggestion is ...Robert,<br /><br />The main problem with your suggestion is that it assumes a static, fixed assignment of event to apply thread. This is true for Tungsten replication, but no necessarily in the general case of parallel replication (see for example http://askmonty.org/worklog/Server-RawIdeaBin/?tid=184 and http://askmonty.org/worklog/Server-RawIdeaBin/?tid=186 ).<br /><br />On the other hand, the idea of replicating distinct databases individually (which I believe Tungsten uses? And I think I read MySQL is doing something similar) is an important one, so in my suggestion it would make sense to include the event group database as the second component for the primary key; this way it is possible to obtain current position for given database/apply thread, as well as globally, depending on the replication method.<br /><br />I still think we should avoid in a general user-visible facility to include by-design global locks.<br /><br />BTW, I am surprised you think the writes to the extra catalog table should incour significant overhead? It is just a single tiny table that should certainly fit in RAM, and one insert per commit. Remember, when we are talking crash-safe replication, we need to run with innodb_flush_log_at_trx_commit=1 and sync_binlog=1, and incur the overhead of fsync() on commits. It seems to me an in-memory insert will hardly be noticable compared to this ...Kristian Nielsenhttp://kristiannielsen.livejournal.comnoreply@blogger.comtag:blogger.com,1999:blog-23496029.post-1365864408460366382011-04-17T20:37:35.863+02:002011-04-17T20:37:35.863+02:00Hi Mats and Kristian. There is another solution t...Hi Mats and Kristian. There is another solution to the global lock problem, namely to have a catalog table with a separate row for each apply thread and to access the rows only by their primary key. This avoids lock problems and allows parallel apply to proceed. Tungsten uses this approach to make parallel replication crash-safe. <br /><br />The extra catalog table does have another problem Kristian did not mention, namely that you end up with a lot of extra writes on the table that tracks recovery position. Tungsten mitigates this problem with block commit--each apply thread applies multiple transactions on the slave at once whenever possible, which allows us to amortize the commit table write across several transactions. <br /><br />These are two of the considerations that drove us to adopt to the serialized shard design described in my blog. (http://scale-out-blog.blogspot.com/2011/03/parallel-replication-using-shards-is.html)Robert Hodgeshttps://www.blogger.com/profile/05379726998057344092noreply@blogger.comtag:blogger.com,1999:blog-23496029.post-30195548372034599662011-04-14T11:14:19.218+02:002011-04-14T11:14:19.218+02:00It is very good to see work from MySQL@Oracle on m...It is very good to see work from MySQL@Oracle on making replication<br />crash-safe!<br /><br />Unfortunately, I see a big problem with this approach as relates to possible<br />future implementations of parallel replication, and I was wondering what your<br />thoughts and plans are for this (hopefully you already thought of this and I<br />am just missing the possible solution).<br /><br />The problem is the UPDATE statement at the end of each transaction. This means<br />every transaction needs to lock the same row! This makes it impossible to run<br />two transactions in parallel on the slave.<br /><br />I know the current project for parallel replication at MySQL is based on<br />running one transaction at a time, but split in multiple threads. However I do<br />not believe this is sufficient for all future needs for scaling parallel<br />replication.<br /><br />More importantly, to make replication really crash-safe when using<br />--log-slave-updates, it is necessary to run with --sync_binlog=1 and<br />--innodb-flush-log-at-trx-commit=1. To do this with acceptable performance we<br />must have group commit, as implemented in the Facebook patch and MariaDB. But<br />group commit is again impossible with this feature; as all transactions need<br />to lock the same row before the prepare step, only one transaction can commit<br />at a time -> no group commit.<br /><br />Have you checked MWL#188<br />(http://askmonty.org/worklog/Server-RawIdeaBin/?tid=188)? It describes another<br />way to handle the crash safety, without introducing this lock contention<br />problem. Basically, it recovers the information from the binlog on the slave,<br />which is trasnactional due to the 2-phase commit.<br /><br />Well, making the information available from SQL from system tables is a good<br />idea; but it should be done without imposing locking of a global row for every<br />commit. I think it could be done by updating the position with an INSERT<br />rather than an update. Each transaction at the end inserts a new row, rather<br />than update the last one. Add a sequence number that is increased at every<br />commit. Then to read the current position from SQL, just SELECT ... ORDER BY<br />sequence_number DESC LIMIT 1.<br /><br />(A background thread can batch delete old entries from time to time).<br /><br />This way, you avoid transactions contending one another on the single row,<br />which I think is really important for future development.Kristian Nielsenhttp://kristiannielsen.livejournal.com/noreply@blogger.com