All right, so talk about MySQL upgrades and a.4rebooking.com, good morning, thanks for coming.
Try again. As usual, comments and things here are basically my opinions and obviously work
of done and things is of work I've been doing at a booking. The comments and things
are basically our usage of booking.com, things about running MySQL, upgrading to MySQL
.4, why how things we've seen in progress and the collusions about us? I guess, first of
all myself, I have, I'm Simon Mood, I've been working at booking for 17 years, running
MySQL there, other things outside with databases with a side base, which no one probably
knows these days, and prior to that doing things in financial working in financial markets.
I've been in East, and I MySQL Rockstar, but these things all expire unfortunately, but
the Rockstar doesn't expire. Okay, the basis expire, but you can do it again, and most
of you are nice to have those both being recognized for that. The MySQL team at booking
is about 20 engineers, we're located various places in the world, in Amsterdam, primarily,
which is where the head offices, in Cambridge, where one of the original offices for booking
was located. I'm in Madrid, I've been sitting there outside the world, and there's also some
colleagues in Manchester at our rental car offices. I guess you know about booking, this is
a picture of the head office in Amsterdam, which is quite pretty and things. Go there if
you want to go to Amsterdam, it's nice to see going the summer when the weather's good, and
what we do, a typical thing, accommodation, rental cars, flights, and attractions, and things,
I guess most of you know us. MySQL 30, so congratulations on that, sent to Oracle for
looking after MySQL since acquiring sun in 2009. And I think the main thing from the booking
dot com perspective is we probably wouldn't be where we are, if MySQL didn't exist, at least
there's current form. The usage of mySQL of booking, we started off being a postgres shop,
which was interesting, and that seemed to be a good idea at the time, but eventually we saw that
in basically in 2003, 2004, we had some scalability issues, and we started to use mySQL and on big
servers running 32 bit Linux, four gigs of RAM, this is high a big heavy stuff, you know,
myocentables and mySQL 4, and since then we've basically been running mySQL and scaled up,
and using slightly more modern versions of mySQL since then. The original database called BP,
which was booking's portal, still exists in the company, and it's had, we run replication,
I think, most of you realize, and it's the clusters changing size, it's been spit out with data
splitting out and things, and it's been as big as a thousand servers in one cluster, and today
it's a bit smaller, but it's still running, because we can't shut down mySQL, the company is up
24, 7, 365, and bookings we're using mySQL basically for 22 years, I'm sorry, so our mySQL usage,
currently we're managing mySQL with a single framework, which we call badminton, it runs on prem,
with on prem bare metal servers, we run with open accounts open stack, and we also we can run as
again CC2, in principle we could extend that to other cloud providers, we've not run
any on virtual machines, we don't do that at the moment, we've still got thousands of production
instances running on hundreds of clusters, and even our development environment is a similar scale,
it's smaller, smaller VMs, we don't need as many replicas and things, but it's also quite
a large input set up, and basically the centralized management offloads a lot of work from the developers,
they don't have to care about managing data bases, they just make their, they make their applications,
talk to the database and get, get the store the data, they can retrieve it, and try and get on with
making the applications and the business work better. We do look at some cloud vendor offerings
for mySQL, and I're using some of those, and there's different discussions about where that's
going, but primarily at the moment we're on mySQL on those environments, and we'll probably
move more to the cloud as those days move forward. Okay, so upgrading, the first question is, you know,
why? I guess the obvious reasons are 80 is end well, 80 is end of life next year, a lot of people forget,
because it's been five, seven months, end of life last year, and they said, yeah, we've got some time
with 80, blah, blah, blah, blah, blah, blah, but it's end of life next year, so if you're on 80,
you need, like, you need to get off, because basically don't leave it to June, or don't leave
it to the last minute, because you're suddenly finding your problems. MySQL is eight four is the latest
stable version of, that's provided by Oracle, nine seven will be coming out soon, so basically the
idea is get on to the latest stable version, which currently is eight point four, and in theory,
take advantage of new features, take advantage of improvements in performance, which have been shown that
I think eight four is better than eight zero, and in theory, stay more secure, so there's some of the
reasons. The new features, I don't see so many new features yet, there's a lot about heat wave,
which we're not using very much or a little, basically, the new features in the optimizer world,
which is not really exposed, except if you're using heat wave, but I think the new optimizer,
which I've heard a few things about, does some good things if you're doing analytics and things,
even on a normal mySQL server. It's not exposed, and I think it'd be really nice to make it visible.
If not in eight four, in nine x when it goes GA, so that we can play with it, we can see if it works well,
if it doesn't work well. There's an open telemetry plugin, that's enterprise only, that would be nice to
have to use on the community versions, which we're running. The eight point four upgrades are a bit
of a question mark, but you have to do it, basically, because you have to get off eight zero.
The other perspective, which I think is important is looking forward, is that Oracle's current
innovation release, which is the new things they're adding in their system, or in nine, nine dot two
is out in January, so a few weeks ago, and that's got a few more features compared to what's in eight dot four,
and basically, the plan for Oracle is that nine seven, which will be April next year,
when eight zero goes end of life will become LTS. So probably also, you don't need to jump to
that version of LTS when it goes LTS, but maybe also, if there are new features and good things to
make as go there, there's to be worthwhile moving forward. So I'm hoping that between now and next
year, that will see more features, and that will provide things that will lead us to go to nine,
so that we get better things. Sorry, wrong direction, sorry, yeah. So this is basically what I'm saying
that. I'll sort of go wrong direction, excuse me. Okay, and then the other question is how,
how do we upgrade? So our environments, fleet, several thousand servers, we've got hundreds of clusters,
we're running my SQL running spanning three regions, they're all in Europe, but there's still three regions,
we run three tier replication, we also run with GR, or with asynchronous replicas behind the GR,
and of course we don't want to shut down the site while we do upgrades, because that wouldn't be a
good idea. So things when you're upgrading, what do you have to think about the consumers,
so the SQL users, they're the typical people. Some of the changes in 8.4, my SQL native
password is going away, so in theory you've got to get off it. That also implies that you need to use
tier, so make sure that's set up, which from a security perspective, you should be anyway,
but if you're not, that has to be done. A lot of configuration changes in the settings,
lot of settings that the word deprecated will remove in 80, so on 840 you have to remove them all
to make sure the system starts up. And then from a perspective, we use replication a lot,
all the settings that mentioned master and slave go away and they replace by source and replica.
So you need to change that, and if you manage replication yourself and we've got a big set up that
does that, then you've got to change all of that, because otherwise 840 doesn't understand what you're
talking about. The other thing is the users need to check that the SQL behaves the same,
sometimes optimizers changes mean that the queries may behave differently, so you also need to
sort of check that. And then the other thing which we saw less frequently, but we're seeing
more now, we now have a lot of binlog consumers. So the data goes then gets pushed into analytics
and things like that, which is not running on my SQL. So your binlog consumers tend to be
simpler and they sometimes don't like any new formats and may not handle that. So this is to allow
them when you're upgrading to make sure that they can support and ingest the binlogs right for.
Technically from in terms of other things too, that we have to involve. We have my SQL plugins
for specific things, and then obviously all your work flows you need to check, make sure that
everything works. We have multiple applications with different groups of what we call
pools of dedicated service for different groups of applications. We have topology rearrangements
of the replication environment and things. Credential management, you need to get off of the
native password, you need to get onto the CASHING SA2 and so on. So there's multiple things that
you need to, when you're operating, that you need to make sure it all works and doesn't so
cause any problems. So the code base from our perspective, as I say, the replication changes
are quite important because the replication related changes functionality doesn't change, but what
does all the commands used. And the main thing is that really one of the things that I've seen
from this is Oracle deprecates lots of things quite frequently, but actually it never goes away.
So what do you do ignore it? The noise is there. Oh yes, I know about that. And until eventually
they do remove it and then of course you didn't pay any attention for the last
X years, which you could have done. So because of that, that's also a problem. It's easy to
ignore things and then you pay the price for having ignored it. And it's also hard within I think
we're at the technical level, we've got management above us. You said we could do some
what we need to clean something up. It's hard to push that to the upstream management.
Do you need to do it now? No, okay, we'll leave it. And that's getting that balance right's hard.
I think also with the deprecations, sorry, with the deprecations, it will be good if you know
when you're going to, so this is for the Oracle people. When you know they're going to
remove it, make it clearer when you're when you've basically decided. So we know with more
times, so we have more time to plan for it. This is going to go away. We have to handle it.
In terms of upgrades, I guess most people here are the sumo you don't have single service and
they have probably running replication. So if you can use replication, it's easier. Don't upgrade
in place and don't and also read the manual because if you upgrade a single server in place and
don't pay any attention to reading the manual, probably it won't start because the settings might be
wrong. If you fix the settings and it starts up, you might nobody get back in the server because
you're probably still using my single native passwords. One of the things I'd love that Oracle, if
it noticed that the full privilege users don't have, won't be able to access the server because
they use a different authentication password, a plug-in which doesn't exist anymore. They shouldn't
not upgrade the server. But they let you to do that and then you can't get back in. Luckily there's
a way to avoid that and you can basically turn on native passwords temporarily to get back in again.
But you do have to restart the server to do that. And then if you're running with replication,
you can startup8.4 and you can test things on the replicas. You don't have to touch
your existing infrastructure and your existing service, so that makes life much better.
Yes.
Technically, you're going to keep with the native password at the end of the video.
Well, I mean, you could do it forever until 9, I guess. But I assume that they wanted to turn
it off completely. And someone said, if you do that, you're going to call us a huge amount of pain.
Which, which I'm sure, luckily, because I did that on my home PC. I was just not ready to force you
what happens, doesn't matter. I can't get back in again. And okay, I could and I had to poke someone
to say, yes, really easy to do this. But if you don't know that, you go around and it sees the fix
it, yeah. And they probably have exactly that, yeah, so that if on the 6th, if you're developing
locally, you've got to have some of the test data blah, blah, blah, blah, blah, blah. You're up
rate to 8.4 and you can't get back in. Also, you're upgrade, so you can't go back to 8.0.
That's the thing is you can't go back because you've upgrade to 8.4. So the server will let you
upgrade and then you can't get in. Yeah, yeah, okay. But the point is that some of these things
they could be safer and they're less safer than I would like, but maybe, you know, maybe I should
read the manual. Anyway, so this is a typical, I mean, we're shared after apology in previous
presentations myself and other colleagues and things. So we tend to have like a three-tier architecture
with the master or the source here on the left, intermediate masters so that we can spread out
to a large number of replicas on the right-hand side. The coloriest gaming here is different colors
for different data centers and theory we are all regions. In theory we have three regions, but
actually we have more for various reasons in some cases. The nice, the basically applications read
from the right-hand side and if they need to write, they write to the left-hand side. And the good
thing about is that you can scale out the right-hand side as much as you like within reason.
Yeah, and sometimes we will split a cluster like this up into two because the data sets
get too large and then of course the topology gets even bigger and more complicated and it might
be four or five things steep while we're doing that. Yeah, so reading as I say from the leaf,
we read from the leaf replicas, we write to the masters. GR basically is the same as
asynchronous replication. The difference is that we have, although orchestrator, the way it's built
so, or the way we patched it so it's built, it shows the primary on the left because we use single
primary. The rest of the thing is the same. The other GR secondaries are there and then basically
I think nodes read off that so you can do that. Yeah, so again, in place upgrades. So once
you're running, if you want to upgrade, the easiest thing is to just, you could do in place
upgrades on the nodes on the right-hand side. If it's not, if it's not in use normal, we'll notice
and if it works, it works fine, you can check that replication works and then you could check
your applications individually, you could do some basic testing, but basically anything on the right-hand
side, you could just plug that in so you're existing infrastructure and doesn't touch anything so
it's really easy to do that. And in terms of a full upgrade process, you basically do some like
initial testing on a few servers, then basically you upgrade the right-hand side completely,
you then do the intermediate masters, which are intermediate partners, servers, secondary,
and then eventually you change the primary or the master and then you're running your new version.
So the process is quite simple. This process though, in production, when we do it, we've done it
from 5'7' to 8 and earlier versions, it may take a few weeks. You can do it faster, but sometimes
you want to leave it running a little bit longer to make sure that users notice if there are
any changes or any problems or any issues. Basically, the most of the risks by following this process
are reduced. Once you've upgraded the master, you have to fix it yourself and figure it out.
Basically, that's the bottom line. So any testing you could do previously, you could also
test in your development environment, you might have a replicate set of developments in which
you can test there. If you break development, just build it again, you don't care. So I mean,
there's lots of ways around that, but it's good to do that because sometimes things break and
is better for it not to break in production and you've hopefully caught most things.
Issues we've seen, basically, internal tooling doesn't quite do the right thing, it gets confused
between 0x0 and 0x4 or the new version. Sometimes we see behavioral changes in queries, which
you know, the optimizer changes, it does something slightly different. So again, that might be a
report to Oracle or something and so it doesn't work. Maybe my SQL crashes are just something
weird on the query, but if that's the same thing, you can report that if necessary.
But in most cases, if something breaks and you don't really want to proceed, you can just
reclone any of the replicas from 8x0 from existing service and you can just, and once the
problems fix, you move forward. My SQL clients, Oracle recommends running the latest 9.2
or the 9.ex version that's being used. Most clients tend to be on slightly older versions, so
it's good to poke your developers and tell them, please upgrade your clients. Use the latest
9x even on against 8x4 or against 8x0. If you want to know what clients are actually connecting
to your server, performance schema, session, contract, connects attributes tells you and you can
actually see what spread of versions are used. I do this yesterday, just quickly you have a quick
check or once one specific server and as you can see it's quite a blivermix there, most of
my SQL, as you see they're not all running 8x421 or 8x421, which is 8x480. You see there's a couple
of live MariaDB. That's strange. Why is MariaDB used to talk to my SQL? Well, proxy SQL uses
live MariaDB, so I think these are proxy SQL connections into the, anyway, it's interesting to see
that because I saw that and I thought, what are we doing? It turns out to be something that's
expected, but it might surprise. Grant management things will obviously the users have to change
grants, so you get that need to get off-native password. There's no way to rotate from one,
from one authentication and mechanism to another one, it might have been nice to have a plug-in
when you rotate grants that you do that automatically, so that we have to do that. And we have to go
through all our budget, no 25, 34, tens of thousands of users and upgrade them and modify them.
The new percent wild-car for database grants we are using in a few places, not in many, so that needs
to be fixed or chain when you change our tooling to handle that. And the secondary password
is also an option which is useful, but that still uses the same authentication method. So you
can't change, you have to change your authentication method while upgrading. Orchestrator, we use
Orchestrator a lot and we used it right from the beginning when Swami comes to work with us for a while
and he said, you know, what are you doing these silly manual failovers and things? You guys
got no idea what you're doing. We didn't say that, but he helped us do it automatically and made life
much, much, much, much better, so we thank you very much for letting me. But Orchestrator and
Swami's no longer maintaining orchestrator works up to 8-0, doesn't it? It doesn't support for 8-4.
So we had to get some help and the code and the help to us make Orchestrator 8.4 a
way and we're now testing that and using it and it looks quite good. So it looks like it wasn't
a big change, but someone has to make the change, but there is now an 8.4 orchestrator where
a version which can be used if you're upgrading. Orchestrator doesn't handle GR failure, we patched it
and that patch is upstream so that it was GR aware. But also one of the other things that's
happening now is because Oracle are providing features to make automatic failover part of the
server process. I think it's not as smooth as I'd like and we're looking at it and maybe we'll
move over so we don't use Orchestrator for failover maybe moving forward. However, the visualization
of a topology is brilliant and there's nothing better. Plugins, we run my SQL plugins. We run
our Pam plugins so because we're running on the community version we patch in the the
Corona Pam plugin onto my SQL server. We have something to check discusse so the developers don't
fill up the disk which they like to buy inserting lots of data. It gives them warnings and that
basically makes them the what's happening and that makes them consider if they're inserting data
all the disk sizes too large. We have a couple of other specialised all the plug-ins that we use
and again the audit plugin is available on the enterprise version so these were self-built
to and also very lightweight to alternative to that. Plugins have been used for a long time. We
have modified this now so with the 8.4 upgrade we've changed this so we build against 808,
4 and 9x and now the automation process is in place so it's really easy to rebuild this all the time
while we're going through the upgrade process. Comment with the plugins plug-ins are going away
being replaced by components. When I was writing this it still wasn't very visible. I don't
think Oracle say enough about plug-ins please move on to components. It looks like now you can do
everything that could be done with plug-ins with components in which case it's good to look to
move off of that because I think also you get better a safer way to use the server and things like that.
And obviously we shared documentation how to use components and things.
Group replication so when you upgrade we're running group replication with replicas
if it breaks you don't care if you break a group you do care because the whole group goes down
you can't take rights. So upgrading a GR cluster is more problematic you can go through
right at the end upgrading the GR members we're discussing that's one thing. We're also discussing
internally whether to spin up an 84 GR cluster which replicates from a zero so you can do testing
of that completely independently and then eventually whenever the else is upgraded you just change
your cluster from one to the other and that's something that we're still not quite sure if that'll
be the way we're doing so or not. Yeah that's what I'm saying here. When I did the call for papers
and I said I'll do an 8.4 upgrade thing I was expecting to tell you that it's all complete it was
really easy to took a few weeks and was done. Unfortunately we got a bit delayed so we're actually
still working progress so we've started on it the still work to do. I hope maybe in a future
presentation to be able to give the complete things on how painful or not it was but I'm hoping
that we're hoping probably in a few weeks to be have the 8.4 upgrade complete and then we'll be
going to talk about it in more detail later. Issues seen a thing about the MySQL native
password don't get locked out because that's a pain. Semisink plug is bitus as well. Something
very simple we have 80 boxes can feel with semisink plug-ins using Master Slave. We then
we change the configuration 9 that it should be saw some replica but when you clone from one
box to another they all got mixed up and it basically didn't work. I've followed a bug report
to that but also found out how to it can fix it but I had to modify tooling to handle that
a bit better. The HyperGraphort optimizer I'd love to try it and I'd love to be built on
the community versions that are available so even if it's still experimental it's not complete
it'll be nice to use it. I think you know it may not be better for everything but some things
if it is better I'd love to take advantage of that. And some missing 2 features are still
really really love to see the first. Well the second is based on the first. I still want to
build a native clone from 804 or from 9 from 84 to 9. Oracle the clone plug-in now's hope
knows how to copy the data over and what does it do immediately after doing that it restarts.
So if you do that so you don't care about the 9 you know the 8.4 server you can copy 80
what the data are over you can do that and say okay I now restart oh I've got 80 data I can
upgrade. If you could do that you'll just make life easier because then you can clone from the previous
version to the next major version and then the other thing which I'd love to see is that I can
basically clone and spin up a new server by providing a please clone from that box over there
again also doing it from the previous version because otherwise you have to set it all up you have
to set the config file and all these other things but if you use Cassandra use a lot of other tools
you could just say point at that thing make a copy and it will just work. So I'd love to see
some sort of feature like this which would be really good for upgrading for lots of testing lots
of things that you can spin and maybe that all the parameters that are now in 80c might not see
enough maybe there is the the custom parameters in the data there and the in the persistent settings
well that's possible I don't know be a lovely feature to have because then you can copy boxes
around really really easily and it just makes life easier for management. So yep so the main thing
is time to upgrade don't delay 80 is going away nine dot nine dot x hopefully will have something really
good. GR upgrades are more complicated and I'm looking forward to seeing what comes in nine dot seven
LTS and some references a couple of reports there. Any questions?
Gordon. So when people were grading from five seven to eight in a lot of us in the agent
which you will please step right away five seven would not work up the date from eight on this
or five you change your main goal. We've not got that file yet. I mean I think they've been
long format hasn't changed that much so assume it will probably replicate unless you do anything strange.
There is a for GT IDs there's a new like tagging thing if you don't use it then it shouldn't
bite you. So my guess is that right now syntax changes between five seven and eights.
So I think there are eight four I think a minimal except for master but that doesn't affect
replication because that goes from there that's that's our grant changes and things like that so
I suspect that probably again like it was from five seven to eight probably probably it wasn't matter.
With respect to a change of our master's layer for source replica you have probably a variety of tool in
Yeah yeah and that's painful this is so this is painful that the moment we're going to let you out the moment.
So the thing is I'm not a hundred percent so so right now I'm with fixing in place and supporting both.
The reason for that is we know in theory with eight zero and eight with with eight zero and eight for it's the same.
I'm not a hundred percent sure of going to nine where it will stay the same or not because if we if you go to nine in one year
then you've got a handle multiple versions so maybe having being version of where is better.
I think probably being version of where is better is more flexible even if the actual configs are same.
But what we'll have to say still still something that I have I need to see.
Okay so I mean the main thing is that this has really been kicked off very very recently for a simple reason
that other priority business priorities came first and we weren't able to to an issue so I didn't know
that when obviously I did that I had the paper so a lot of the things here is what you need to do
so in practice we've done very little basically most most of this is the plug-in handling because we have
custom plug-ins they have to be built and one of the things is what I've forgotten is when you've got
automation everything works so when you break it just by changing the version which you don't think
it how can it possibly break anything everything breaks because all our monitoring checks slave
status and blah blah blah and it checks this and it does this and it tries to set things and nothing works
so you forget when you have automation in place and everything is smooth and perfect you change
something that is not compatible and if you actually suddenly start to read as somebody you know it's
in multiple different libraries we've got different languages doing different things we've got
grant manager we've got loads of things all over the place and you suddenly realise how painful that
changes and it wasn't had I realised or had we realised and I didn't I don't think in any of one
of the team really thought about it much I always go and do that and I had a plan but we should have
done this earlier but that's what you that's what you find in practice that you didn't realise
the consequences and it would be better but I still I still think there are differences from nine
to eight eight to four and nine so possibly actually it wouldn't help it might save most of the
problems but you only see this when you do it and obviously this is the thing sometimes it's
it's good to delay things and not do it and do it later and obviously if you're busy with
and you know there's business things to do it's good to not do these things until you need to do
it but sometimes it's actually good to do it early and I think it's good to follow follow what
Oracle's doing if you're using or it won't mind my sequel don't don't don't be bleeding edge
but be close to bleeding edge ish enough so that if you have to stop you stop but don't leave
it too long because then the jump can be large and then it's going to be painful
do you have the rollback plan in case it doesn't work on to you? No but I mean the thing the
thing is eight fours are same as eight zero if we're on it I mean I'm this is a personal perspective
eight zero and eight four are basically the same so I mean maybe if nine seven turns out
having loads of new syntax and new things and the optimizer has been hacked away to do something
really megaly different than you might have really big concerns but the code base I think is
it's a lot of clean up in eight zero but yeah you said you're not really at that I'm really curious
about the findings that you will like the things you'll find that works in eight zero
and not in eight four because I know about at least one which is not different given in eight
oh and we're break eight four so I mean I don't know better I mean as I say I was hoping to
coming here and say I can tell you everything in blah blah blah blah no one knows anything and
sometimes you don't you'd like to be ashamed or I can't share more at that this time of
right yeah and that and the replication is something that we use heavily as you and a lot of
people do and if you break replication that's that's and that's not good so I mean eight the
new versions past eight eight zero need to clear up to absorb the the lower version
things and if they can't do that I think it's a bug it's not it's a verified bug and I'm
pressured there's others because I think too many things were removed in the eight four yeah
oh I think I'm going to be yeah yeah thank you