Okay, hello. Sorry for the technical trouble. My machine didn't work for this screen, so
I got the help from a colleague. So, I'm Marco Mäkele. I've been working on an ENO-DB
code for 20 years almost, and today I will talk about one of my pet hates in ENO-DB,
this change buffer, which I long time suspected that it's causing bugs, but I couldn't prove
all of them. So, I took a car analogy because some software people like car analogies this
unsafe at any speed. It's from the 1960s. I'm a bicycle person myself. So, what was
the change buffer good for? It was something developed in the times of the spinning rust,
the hard disks. The idea was that when you are doing sequential I.O., like page reads
or page writes sequentially, then the read write hit will move less on the hard disk.
And if you're doing random cx, it could take a long time to position the head to the correct
track and then wait for the sector to come under the track when it's rotating. So, the
idea of the change buffer was that instead of reading something from a page and then
applying a change to that page, you would write a buffer change to somewhere else. So,
if a B3 secondary index leaf page is not in the memory, in that case, instead of reading
the page to perform an insert, originally it was only insert buffering, we would write
that insert operation into a separate insert buffer tree. And then later when some other
operation needs to read the page, it would merge the changes from that buffer to the
page. And these structures are persistent. So, even the insert buffering could happen
years ago and then at some point years later happens somebody wants to access that page
and then you'll get the trouble. This was extended in MySQL 5.5 to cover delete operations
and purge operations. Deleting in InnoDB only marks the record for deletion, same for update
of a key in a secondary index, it will do delete marking and insert. So, the purge is
what is actually removing the record. Those operations could be buffered, but not rollback
of an insert, that was never buffered. And this leads to lots of problems, like the
change buffer is located in the InnoDB system table space. And up to this time there is
no mechanism to shrink the system table space. If you at some point use the change buffer
a lot, the system table space will grow by some hundreds of gigabytes, there's no way
to reclaim that space. Okay, in MariaDB we have something, we are working on something
to help with that, but it's not done yet. And then this obvious right amplification,
if you are doing an insert, okay, that's rather fine. Instead of doing just one insert, you
are doing two inserts, you are doubling the right, plus you have to write some metadata,
some index information so that the contents of the page can be interpreted correctly because
the change buffer doesn't have access to the data dictionary. But for delete or delete
marking, if you apply the change directly to the page, you would write one byte or couple
of bytes, now you have to copy the entire record which you are going to delete or delete
marked to the change buffer and the metadata. And then at some point it will be merged.
And then there is some overhead, even if you are disabling the change buffer, you still
have some overhead, you have to maintain some metadata saying how full your pages are. If
somebody is going to enable the change buffering or insert buffering later, this data has to
be accurate, otherwise you would get a page overflow. The insert buffering must know that
the page will not get too full when you are buffering the insert and merging later. And
then we got lots of nice corruptions where the secondary index gets out of sync with
the primary key index. And these are very hard to reproduce. So why is it hard to reproduce?
Well, the first part is the same as on the previous slide. It is exactly this that you
cannot easily control when the change buffer merge happens. It's like the Spanish inquisition
in the Montice Python sketch. Nobody expects a change buffer merge. And to reproduce something,
as a user, you are unlucky and as a tester, you are lucky if you can reproduce this. And
you need lots of luck to get that. Because especially this perching of the history, which
is deleting records from the index, it can be blocked by reviews. Like if you have long
running transactions which are holding a review open, that will prevent perch from running.
And then at some point that review will be closed and perch can start running. And then
there is also this buffer pool. If a page is locked by something, it can't be written
out and it can't be evicted from the buffer pool. So the change buffer can't be used.
And we have a debug setting that forces that, okay, user is asking for operation that could
use the change buffer and we see the page is in the buffer pool. We are going the evil
and we evict the page. But we cannot do that because somebody could be holding a latch
on that page or the current thread is holding a latch and the page was modified. And we
cannot wait for page writes to happen because this latch is blocking the page write. So
this is really difficult to test. And there was a recent fix to some hanks which were
introduced in MySQL 5.7. We have that fix in the release that is coming out next week.
That one will make it even more tricky this debug option. So in order for tests for this
to be effective, they have to do some smart tricks like abandon some tables for a while
and let them cool down. Use some other tables meanwhile and then come back.
Well, we got some nice magic tools as well. We have this random query generator. It's
also used at MySQL and a grammar simplifier. We could start with the huge grammar of all
of the SQL covering all the features and let it run. If the crash was frequent enough,
then we could use this grammar simplifier. But in this case, this is very hard to reproduce
back. We cannot use the simplifier. We cannot get any simpler grammar. We just have to run
it all and hope for the best. But then we got this debugger RR, record and replay.
That one is really a huge productivity boost. We started using it maybe two or three years
ago. So when you are able to reproduce a problem while running it under RR, what you would
do that you will save RR record will save a trace, a deterministic trace of an execution
that is interleaving processors or threads that are being monitored by it. It saves
the system calls and the results and so on. And this trace can be debugged as many times
as you want. You just need the same binaries, same libraries and compatible processor. Then
you can run it. And you can set break points, you can set data watch points and you can
execute in forward and backward direction. You can see what happened before the bad thing
was observed. And this can also be used for optimized code. You are probably familiar
with cases where you are debugging an optimized executable, then the debugger complains that
some variable has been optimized out. Well, then you can just single step some instructions,
you get it from the registers, because you can go backwards in time. So now I am coming
to describe one bug that we found last year. And actually there was a support customer
last week who hit this bug or a consequence of this bug. So we had a bug that would be
a slow shutdown which is doing this change buffer merge. It would hang because the change
buffer got corrupted. And we were testing some fixes in a branch for that. And then
we got this assertion failure. This assertion failure essentially means that when it tried
to insert a record that was insert buffered, it ran out of space in the page. And what
was the reason? Well, there were some extra records in the page. And it turned out that
this is partly by design. Hei Kittori, the creator of InnoDB, he was a friend of lazy
deletion or lazy operations. So drop index wouldn't clear anything from the change buffer.
It would leave the garbage behind. And later on, if the same page is reallocated for something
else, then we would pay the price and free the space from the change buffer, delete the
records. And in MySQL 5.7, there was a new feature, bulk insert creation or building
indexes faster. And that codebase didn't do this adjustment correctly. It only cleared
some bit, but it didn't remove the records. And there was a mandatory Oracle security
train I took several years ago before switching to MariaDB. It said something like complexity
is the friend of security bugs. I found it somehow fitting here.
So the immediate root cause of this failure was that this new code cleared a bit that
says that there are buffer changes for this page. So when somebody is going to use that
page, he will see that, OK, there's nothing to do. I don't have to care about the change
buffer. And then later on, something adds records to the page, and then these old records
from the change buffer will come to the page as part of a merge.
And how can we prove this using this RR tool? By the way, you can download the slides from
the first page, and you can also download an attachment that has a script replay recording
of the RR session. So I'm only showing some high-level view here, but you can download
a debugger session that shows the exact commands and the output, which I'm going to present
in the next slides. So the short version, how we did this, how we can prove these claims
in the debugger, we let it continue from the start to the crash or the assertion failure.
Then we set the break point on a function that was the last one to access the change
buffer bitmap bits. And from that function, we get the address of the bitmap bits for
this page, and we can set the data watch point on that. And I found this hardware watch
point is a very powerful tool. It's really much easier for some things when you don't
have an idea which code is going to modify or read something.
And then based on this watch point, we get some call stacks where these change buffer
bits were last changed. And then we set the break points on functions that insert records
into the change buffer and delete records from there. And then we observe that, okay,
there was nothing to delete records for this page and basically proving this claim. So
we are printing the index ID and index name to get some more detail to this proof.
Oh, sorry. Okay, so we were unable to reproduce this with a small grammar. We just took something
and we got lucky and got the trace and debugged it.
Possible consequences of this bug are the wrong results. That's of course very difficult
to prove. You don't have any testing tools to prove that really or not many tools. Or
you could get the crash on change buffer merge like here we got. And that change buffer merge
could happen any time, even if you are running check table to check if your table is okay,
then before my score, MariaDB 10.6, your server would crash because of this change buffer
corruption. So in our case, it was a page overflow when applying an insert. And change
buffer doesn't allow any page splitting. It must fit in the page. In the support case
I mentioned one week ago, the case was that the page split failed. It ran out of space.
You are taking one page, you are trying to copy part of the records to a new page and
it ran out of space. How can that be? It turned out that the page contained records for some
other index which apparently had been dropped earlier and that index apparently had not
null columns. So the length of a variable length field was interpreted, was stored there for
that index where this correct index would have the null bit map. And then we would read
the length of the record from previous byte and that's how we would get these two long
records being copied. Oh, five minutes left. I have to hurry up. So this debugging, how
it goes in detail, we continue to the end of the execution from the start. And then
we reverse continue to go back from the abort signal. Then we set the temporary breakpoint
to this bitmap page access. Then we get to that breakpoint and we get in a register we
happen to have the byte address of the bitmap byte which we are interested in. And then
we reverse continue to the changes of that byte. So the last occurrence of that was for
an insert that was buffered after the at index. And we continue from that, we see that this,
the previous occurrence is the at index that is clearing the flag. And from that we can
get the page number which is affected and we can get the index ID and index name and
the SQL statement which is alter table. And then we set some more breakpoints on this
insert buffer delete and insert operations. Set the condition that we wanted only for
this page and then we reverse continue, we get the insert that was buffered before this
at index. Apparently there was a drop index in between but I didn't add statements to
get a breakpoint there this time. So the index name is different as after the at index and
index ID is different. And there was no call to the change buffer deletion. When we continue
from this point to the end of the execution we just reached the assertion failure again
without any change buffer record deletion in between.
So I'm quoting this Finnish ski jumper who apparently was confusing to French phrases.
He was wishing him a good trip when he is starting to do the ski jumping. And I'm wishing
a good trip for anybody who is using the change buffer. So and the deja vu, yes, we have seen
this shut down hang actually earlier. There was a 10.1, MariaDB 10.1 support customer case.
They got this hang and we had to do something to fix that. But in MariaDB 10.5 we made
another change hopefully to reduce the chances of getting like random change buffer much
because basically after this change buffer much only happens when SQL statement needs
that change buffer much to happen. No background operation. So we had to adjust that previous
fix for the 10.5 code base but that was not adjusted correctly. And we were not able to
reproduce this corruption or this hang earlier. So only quite lately we were able to reproduce
it and then we were able to debug it properly. There are some other corruption caused by
the change buffer. And one thing I want to notice that in MariaDB 10.6 recently there
was a fix that we should not crash on any page corruption. If there are any cases where
we are still crashing I would be interested in details. And this includes like check table
when there is a crash. When there is a failure during change buffer much it will not cause
a crash. There was a mystery bug filed like 12 years ago. MySQL bug. The customer got
a crash during change buffer much because the page got empty as a result of applying
a perch operation. I can think of several bugs that have been fixed in MariaDB that
could be the explanation. The last one, this one from the previous example that cannot
apply because that only was introduced in MySQL 5.7 which they didn't use. And of this
list the 30422 it's a clone of MySQL fix which is applicable to MariaDB 10.3 and 10.4. Others
I don't think have been fixed in MySQL yet. So this teaches us that it really pays off
to analyze any obscure failure you get from running with RR because there are games in
there. And I think that assertions are like lottery tickets. If you don't write assertions
in your code then you can't win these kind of bug findings. And sometimes you can lose,
you can write a bogus assertion okay you make mistake, you correct it and improve the assertion
and then hopefully you will get something better later. We have some mitigation for this in
MariaDB. We don't access the data file when executing drop table. So if in that case we
are avoiding maybe more crashes on drop table. And there was a bug in you know DB slow shutdown
that the change buffer merge wouldn't check for log file overflow. So if the user got
impatient and killed the server because it's taken too long time then they could end up
with unrecoverable database. And some more mitigation that we disabled this change buffer
by default. We deprecated the parameter and we removed the change buffer in the 11.0 release.
The upgrade code for handling it, it was tested and I hope that if there is some corruption
notice during the upgrade it should still be a possible to go back to the earlier version
and then do something to correct. Yes, if there are any records in the change buffer
we ignore, we don't trust these bitmap bits. We go through the change buffer records and
if there are any we will apply them. Yeah, so that was basically what I wanted to say
and maybe this last slide that it's a good thing to have a nice layer design if you optimize
things by breaking this layer boundaries then you are asking for trouble. That's basically
what we can learn from this. Question, you said that you fixed a few things in Merle
DB and they're not yet fixed in upstream. Are there bugs open upstream for fixing these
things? I haven't filed any, I lost my MySQL account when I resigned Oracle.
Okay, well, I'll look into it so we can get this fixed in upstream. Yeah, you are welcome
to file bugs and maybe they will be fixed. Well, especially if it's a crashing bug.
Yeah, but you can't repeat them, so you have a hard time proving that. Well, there are
tools obviously now, weren't obvious before. I'm very persistent. It's like once we know
it exists in Merle DB as it takes and somebody that is persistent enough should be able to
do that. Yes. It is very bad. I mean, if you have multiple threats or processes, it's running
them on a single CPU core at the time. So that's why we are running hundreds of servers
in parallel on a single server for several hours to get these traces. Actually, also
for normal debugging, there have been cases like if you have lots of conditional branches
in your code like this debug library or performance schema. Those branches are never taken, but
because RR is interested in conditional branches. I have seen a case where if I compile without
these things, I get a crash or problem in let's say like three seconds. And then I was curious
how long does it take if I use these stupid compilation options with these extra conditions.
For that particular thing, I interrupted it after two hours. So it was 7,000 seconds
versus three seconds. So don't use conditional branches or unnecessary debugging. Turn off
all the code that you don't need. Conditional branches are evil for RR. Well, maybe you
are not using it like multi-threaded with context switches and so on. For single-threaded,
there is basically no overhead. Thank you for saving me. It was a team effort.
Thank you.
One, two, three. Is it working? Okay, there is no audio in the room, right? So you want
me to speak loud. Well, anyway, we are going to talk today about MySQL 8 and MariaDB 10.10.
Original Toxa is 10.11, but I wanted to make sure we're sticking to the latest GA or stable
version so it had to go down a bit. Well, and let me start by congratulating MariaDB team
with MariaDB Corporation going public. In particular, Monty, congrats for driving two
very impactful open-source database companies to exit. That's quite an achievement, I think
you people in the universe have that. Yeah. Well, so what are we going to talk about first?
I think which we need to recognize where MariaDB and MySQL started from the same roots, right?
We have diverged substantially, right? So I think it was interesting when on the previous
talk, Jean-François was talking about the upstream, right? I was thinking, hey, you know,
what does MariaDB really consider MySQL upstream at this point? Or not quite, right? In this
case, I think there is enough diversity right what this is our kind of, you know, ancestors,
maybe, you know, like monkeys for humans, you know, something of this regard. Now, in
this case, like I am trying to be fair the best way I can, right, which for me always
means offends everybody equally, right? So, you know, if Monty is not screaming at me
saying you are fucking moron, Peter, that is not how it is, then probably I am not doing
my job properly. No, no, no, but you... Oh, you see? Yes, yes, yes. Of course, of course.
You always do everything with loving your heart, right? And you don't use bad words
as I do. That is wonderful. So, let's talk about development model first. Obviously,
MySQL is developed by the Oracle corporations. We can see what the contributions are accepted,
but I wouldn't say they are encouraged in the same way as MariaDB does. And we also have
open source, as I would say, like a drop ship open source, right? We have those release
coming, but we do not really have a tree there over developers changes, right, happen. You
know, as we can see. That, I think, can be particularly problematic, for example, for
security bugs where it can be hard to track, like, what exactly change fixes that particular
issue, right, which is different from MariaDB, which is... has a server released by MariaDB
Foundation, though there is a lot of work, right, for actual new features done by MariaDB
corporations, though foundations ensure what the contributions are encouraged and developers
really done in the public, right, as I would say, through open source project. One thing
I wanted to point out, which I think is interesting, is also changes from the Oracle side, right?
For years, I've been actually defender of Oracle in regards to, hey, you know, besides
all this kind of stuff that Oracle is looking to kill MySQL, they have actually been doing
a pretty good job in releasing majority features of the open source and the proprietary enterprise
features have been kind of well-isolated, abstracted through API, and it was relatively
easy for companies as well, especially, like, per corner, to implement the equivalent.
Now things have been changing in the last couple of years, right? We can see what, everybody
knows this guy? Yeah, yeah, yeah. Well, like, we can see what Larry actually discovered,
what the MySQL exists in the last couple of years, right? And he only seems to care about
the MySQL as a heatwave, because we all know heatwave supports the melt zone of lake, right?
And we can see a lot of focus getting on this snowflake development, which is sort of a
cloud-only, and of course, you know, proprietary version of MySQL. So far, it is only analytic
extension, right? But I think it's all questions to us, hey, could there be some other critical
features which will be only property, right? Maybe Oracle somewhere in a bellies developing
something like transparent sharding for MySQL, maybe that is going to be proprietary first,
right? So that is, I think, the questions what a lot of people in MySQL community are
asking. Now, with MySQL, with MariaDB, I think what is interesting compared to, like, a MySQL
is that there are actually two companies, MariaDB, well, two entities, probably better
than MariaDB Foundation and MariaDB Corporation, right? That is the latest mission, which I
just grabbed a couple of days ago from MariaDB Foundation side, right? And I think that is
a very good to understand relationship with those companies to understand this, right?
Now, if you think in this case is what MariaDB Foundation is really at large focusing on
serving MariaDB community, MariaDB ecosystem, right? It develops open-source software around
MySQL. They are MariaDB Corporation. That is now public company, right? Which is providing
a property solutions and commercializing MariaDB software, right? That is, I think, the interest
way, right? Now, relationship sometimes can be a little bit complicated, though I would
say there have been some more complicated entitlements in which I mentioned in my previous
talks, right? And some of them have been made more clear, which I think is a great progress.
So if you think about this, what is interesting is MariaDB Foundation has responsibility kind
of relatively narrow to the MariaDB server, right? And we can see what number of other
components which are very valuable in MySQL ecosystem are owned by MariaDB Corporation,
right? Not by the Foundation and also a lot of development. Roadmap is driven by the Corporation.
I also find it interesting what we see MySQL knowledge base, which is kind of built by
a community but is hosted by MariaDB Corporation. I find that not in a very good sense for like
an open-source software, there is also entanglement on the website level, right? So if I am downloading
MariaDB software from.org, right, then I am kind of redirected next to the MariaDB
Corporation knowledge base, right? And encouraged to fill out the lead which will go to their
MariaDB Corporation, which is not totally transparent, right? I think that's kind of, I may be still
looking like, oh, I am engaging with a non-profit while actually I am giving my connections
to somewhere else. Now, I wouldn't say though that that is completely unfair in this case
because MariaDB does carry the largest way to development and promote in MariaDB, right?
And they do also get the largest rewards compared to the other sponsors of MariaDB Foundation.
Now let's look quickly at what is really open-source between those versions, right? Now, in MySQL,
what we can see is a very clear open-core platform and we have Maria, MySQL community, right?
And, you know, router, cluster, whatever, all that comes which comes in open-source edition
and there is also enterprise version. Plus, as I mentioned, additionally, we have increasing
focus on the cloud-only solution as a heat wave. In terms of MariaDB, there are, you
know, a lot more nuance in this case, right? Because there are certain things coming from
MariaDB Foundation which are completely open-source right now. The things in MariaDB Corporation
Spaceway can be with a variety of licenses.