 So I think we can get started This is we're going to be talking about moving your open set cloud from Postgres to MariaDB. I'm Steve Qualic and this is Hello, my name is Rove Haferkamp. We we both work for SUSE on the on their open set cloud product so Why first of all, what's our motivation? So? Postgres is still supported upstream works fine usually There's been a few issues upstream here and there that are Postgres specific But my school gets a little bit more attention upstream. There's far more testing upstream with my school and There's a lot more experience upstream with my school The user survey that was conducted in 2017 says that 10% of deployments are using Postgres The TC talked about it in 2017 which ended with a resolution to say we are expressing a bias towards my school so we decided to Decide to spend some time to research possible ways to migrate Actually migrate as opposed to drawing a line in the sand and going this version of the product is going to be Postgres And this version will be by a school. We wanted some way to actually move And whatever we mentioned my SQL we're not actually referring to just my SQL, but to all the different flavors like the Kona MariaDB so Let's just migrate how hard gonna be So it's all just SQL right we can dump all the Postgres data using PgDump all and import it Turns out we can't do that Um The SQL is different between Postgres and MySQL You've got different data types You've got text which is different in MySQL versus Postgres boolean versus tiny int accuracy issues and Charsets Has anyone tried that command It works great Postgres and With MySQL you get current product to the database because Because utf8 in MySQL is not real utf8. It's three byte utf8 Which means that it's only the basic multi-lingual plane It covers most commonly used characters skips on emojis Mathematical symbols and some of the less often used CJK characters Postgres by default supports all of utf8 So in MySQL if you want to use utf8 you have to use utf8 mb4 so for four byte multi byte and There's really no good way to solve this Trying to convert is just going to result in data loss So any tool we use would have to be able to take this issue and either deal with it or fix it if possible and Converting every project to use utf8 mb4 is Also, not that easy to undertake It's been talked about in the past But hasn't really had any traction and it's also sort of unclear as to how we would deal with this for existing deployments Would we try and convert the char sets would we force people to drop the tables and recreate them? but Luckily most of the characters aren't going to appear in this sort of open stack database, so We need to be aware of it. We know we don't need to deal with it And there's back ended back in specific differences. Oh Solometer for example decided to emulate high precision timestamps using decimal Postgres has a high precision timestamp type That's just available and you can just use it So someone must have done this before I think this is Yeah, right so We thought well somebody must have done this before like converting from Databases from postgres to MariaDB or maybe vice versa or whatever so we didn't spend some time looking for Existing tools and we laid down a couple of Requirements we had that we needed for for the existing tools So the first obvious requirement is of course we need a tool for migrating data from postgres to MariaDB But then like for example Who will be creating the database schema like in our case when migrating data for postgres? We want to make sure that the schema on the MariaDB side exactly matches what? OpenStack expects so we didn't want the migration tool to create the schema. We wanted to use the OpenStack tools like Keystone manage DB sync or Nova manage DB sync or Whatever the tool is for the project to have the schema created on the target side and just use the migration tool to migrate the data itself So, yeah What about downtime? We very quickly figured out there will be some downtime required for for doing such a migration but yeah, what how downtime makes it is acceptable and for us we Decided that yeah, some downtime is needed, but we needed to We wanted to try to minimize that amount of downtime and there certain issue certain approaches for for doing that we come to that later And There is like how do we cope with the incompatibilities that that Steve mentioned just a few slides ago like what to do about char sets or other kind of differences between the different two implementations and So sorry, then there is a few other things we thought would be nice to have like There are when when we rely on post on OpenStack tools to create the database schema these tools also create some metadata for for the for example for SQL I can be or for The Alembic migration tools which we wanted to keep in the target database We didn't want to have the migration tool touch that data So it would be nice if the tool could like skip certain tables or rows of tables or something like that Then lots of OpenStack projects Use I think what they call soft deletes instead of really deleting the data from the database They just mark it as deleted with a boolean flag and it doesn't show up in queries anymore and we wanted to Or it would have been nice to find a tool that could have like also not migrate this deleted data to just for example speed up the migration process I Think there is for example similar in over that use this feature a lot and then one feature that would also be interesting for for migrating is Doing an incremental sync so that you could reduce the downtime of the migration itself by first doing a full database thing while the API's are still running and then shut down and Do a next run just synchronization synchronizing the data that has been changed or updated since the last thing that could Reduce downtime quite a bit More on that later actually so there Like we tested like to be all we found like two tools which kind of Claim to provide what we needed at least to some extent and we did some tests with them The first one of these is was called PG to MySQL Which is written in go? It provides a pretty simple one time so non incremental synchronization It has a bit of schema validation available actually so it tests for certain schema compatibilities in the different types of the target and the source database like it's checking if the target database can hold the The size of the data and so on but it doesn't really do sophisticated checks there And when testing it we quickly found several issues with the tool like it Especially on the postgres site. It didn't handle Postgres reserved words very well like when you have a table called user Which is a postgres is a reserved word you need to quote that if it doesn't read refer to a real user and the tool itself couldn't really handle that and There were lots of these kind of smaller issues What we all would have to address to really get something useful out of it off out of that and probably the biggest problem with the tool was that it wasn't really able to handle Database the the integration types in the different databases correctly So it tried to figure out what lengths this types have and it Postgres really doesn't support that and and so it really for more advanced types It's really not suitable unfortunately, so it's pretty barebones tools which a tool which is kind of Interesting because it does parallelization and so on but it didn't really fit for us. So the next tool we Looked at was it's called kitchen sink. You can also have the links there on the slide Which goes slightly further than PG to my SQL it claims to do incremental sync, but it has certain requirements for that on the schema of the database It's written in C++ and has very little external dependencies. You basically only need The client libraries of MariaDB or my SQL and Postgres available and everything else is implemented in the tool Which is kind of nice But on the other hand it has very strict expectations Regarding the schemas of both databases, so it wants to have control of the schema of the target database So you can't really create the schema yourself Or you have to at least carefully Maybe make changes to the to the kitchen sink tool itself similar to The first two we tested it also didn't handle the the enumeration types and unfortunately some OpenStack projects use them a lot and it turned out it is very hard to debug because it doesn't have much logging in there and it's Pretty complex to a code on the other hand it provides some neat features probably for other use cases, so it Might be really a nice tool, but unfortunately Didn't fit our needs as well and there's a couple of more tools for example Well, there's a couple of commercial offerings including Consulting and things like that and and there's many like one-time tools that are quick hacks that that people used for for migrating specific projects or specific applications between databases, but it's not really Something that we could use so really it turned out that Yeah, we know of the existing tools really fitted our needs, so well That's right something for our own cause how hard can it be? So before really starting to write this We spent some time about like Thinking like how do we want to operate the migration and as I already already said There is the way to do an incremental thing to to minimize the downtime But this is pretty hard to get right with with SQL you have to have There's no standard way to figure out What entries or what rows in a database table changed since the last synchronization or since a specific time stamp or something there might be possible ways to implement that but They're going to be pretty complex so The next possible way to do it is to do it in a one-time fashion So it means you shut down all your services or the service that you want to migrate you Start your migration tool. So soon all the data once then you reconfigure your services And start them again And after that they are using the new database and everything is there again They said of course a slightly longer downtime requirement Then the incremental sync approach, but it's Presumably a lot easier to implement and And also a lot less error prone than the incremental sync approach And like there's the third approach which is just like a variation of the one-time sync where you Would dump the data to some kind of intermediate file format to the disk and reload it from from that file but that has even longer downtime requirements and So we decided to go for like in the first iteration for a one-time synchronization approach and implement that There were a few like other Soft goals we had for the tool that we wanted to create the first one was we We needed to work with OpenStack Newton because that's One of the versions of our product that's still running on postgres and we wanted users of that being able to migrate to MariaDB had to be open source and it It's supposed to be very simple to be Yeah, easily to debug to be easy to enhance and to be not like a complicated monster that nobody can maintain. So Basically, that's it so Let me introduce Our tool we created a tiff need named it ps psql to mysql. It's available on github Um, we choose python to write it in this of course. Well, it was supposed to be for OpenStack so We choose python and by that we could also Leverage sql alchemy which provides a very nice abstraction layer for databases in and pyson um Basically what you see there is the the simplest command line For the case that you want to migrate a database. You basically just specify the URLs to the source and the target with Authentication credentials use the migrate command and it will Yeah, try to migrate data from there It also provides sub commands for pre checks with uh in this pre checks we implement for example the check for the utf 8 characters that that steve mentioned like if the source database contains any non three byte utf 8 character In some text table, we will warn the user about that and Give them hints how or where to fix that We will have a demo later on that how that looks um And there is a tables sub command That can be used to like once you did a migration And want to retry it again before like doing the switch over or something You can delete all entries from the target database again to have a clean start over And as a need add-on feature, we also also added the batch mode where you can like input Create a yaml file which Contains a list of databases and users and credentials which it's then migrating All at once so instead of like you're having to call the tool it's more to tool multiple times. It will just like import I don't know migrate these databases in batch The tool itself can be basically run on any node that has connectivity to both of the database nodes So you could run it on your workstation if both servers are or if it can reach both servers But ideally you would run it on One of the machines locally to to avoid too much network traffic or bandwidth issues Um So As I told you you we uh, we are using SQL alchemy and The reason for that I will talk about on this slide Um, because SQL alchemy actually provides some very neat features for for our use case It's very easy with with SQL alchemy to just like introspect your databases it's you can Read all the Tables columns and column types from both databases and and check if they are comfortable or do More things with that. It's possible or or SQL alchemy provides abstractions internally for all basic SQL types. So if you read Some specific intern integer type from prosgres that will be internally converted into the type abstraction of SQL alchemy And when you write that back to MariaDB it will automatically do the right thing there Which is actually very needful for our use case Same is true for inner morations. So They are abstracted as well and that means for us the problems we had with with the Tools we tested with the existing tools are simply a non-issue with with SQL alchemy. It just works there Um another nice thing about the type abstractions is that they are extensible. So There is a concept called type decorators in SQL alchemy where you can adjust the behavior of a specific mapping of specific type to Do like a kind of different mapping and uh, that's also something I'll talk about in more detail in a couple of minutes um Then SQL alchemy also provides the possibility to um to execute raw SQL code When you still need it usually you would when you use SQL alchemy you would never really write raw SQL code But use all their abstractions, but in some cases you need to be Create the back end specific commands and that's still possible with with SQL alchemy Well, and finally SQL alchemy is extensively used by uh open stack, so Maybe we could borrow some code from there as well So while working on the migration tool, we ran into a couple of issues, uh, which we quickly talk about Um, and of course how we solved these issues the first one actually The first issue we ran into Was about tables or databases which used foreign keys in their tables as our tool like really reads all the tables from the database and then per table migrates row by row without like looking after foreign keys or or Dependencies between them you quickly like seen error message like this where it's violating some foreign key um Luckily, and again, it's a nice thing of SQL alchemy. It provides a method to return you the tables of a database sorted by Their dependency order. So if a certain table has a foreign key Uh dependency on another table that will return them in the right order so that you could first implement Uh import the row without any any of these constraints Um on the other side there might be circular dependencies in these cases and these exist in in some open stack databases. So this does not fully solve the problem And there we come to raw SQL statements. Um With my scale it's possible to uh disable all the const range checks and Like our migration tool will do that for the time of the migration We'll just like on the session level turn all const range check off temporarily and um As it does that on a session level they will be enabled again after the migration and that actually works pretty well um the next interesting issue we ran into what's related to galera because uh Yeah, galera has certain limits on the transaction sizes um by default still or the version of Maria dv and galera we were using by default still had a limit of Of maximum rows that could be changed in a single transaction Um nowadays that limit doesn't exist anymore or is set to unlimited by default, but still Um, there are other limits that that limits the limit the size of that a single transaction can have so um In that case, um, well You can't really if your transactions get that big you can't really do much about About that you need to split your transactions up in multiple smaller smaller ones and um That's basically what we did. It also has a benefit for for other in other areas because like when your database gets too big the tool itself and Of course the database server itself if it needs to process large large transactions Uses a lot of memory and and that's why we decided to to split The data set in several transactions um, we did some profiling for that actually and um There are Some graphs for that So what you see here is basically different profiling runs of the tool It's was using I think we used the nova database for for the profiling run which had about The largest table in that database had about three hundred thousand rows, I think and um the Let me see if the mouse pointer is here. Oh, yeah Uh, the the top line this one here basically Is from the run Where everything or every table was migrated in a single transaction and you can see that the tool itself used like almost 1200 uh megabytes of memory and and the database itself was also pretty pretty busy there And yeah, basically every every of these edges here is like one database table That's being migrated So it was done with the database after like around 180 seconds or something And then we experimented with different sizes of chunks in which we Commit to the database this one the middle one you can see it Like if this one was we're in a thousand entries This one was like three commits. So 100,000 lines per commit 100,000 rows per commit Which is even slightly faster than doing it all in once and the green one was uh, I think 10,000 lines 10,000 rows per commit and actually we ended up by default going for 10,000 rows per commit which provided Quite a nice compromise between memory usage and required runtime from for the tool This is just another graphic showing The the maximum memory size depending on the on the chunk size of the transactions And there you also see that like 10,000 seems like Like a nice compromise here But the tool itself provides a command line option for For actually setting that manually if you don't well if you have other requirements This one is comparing the runtimes on the different Transaction sizes and you see like if you do a single commit per row it really is Amazingly slow it takes like half an hour for that big database or slightly big database Um, and for the others it's almost the same runtime There is like like the unlimited one is slightly slower than than doing chunk sizes of 10,000 or 100,000 But it's all in the same ballpark I'd say Finally there was one other interesting Incompatibility we ran into and that was caused by a silometer When we migrated to the silometer database We found that For a certain table It didn't want to like write the timestamps and then refused on that and When taking a look at this the database schema, we saw that like in postgres for that Table column it used the timestamp type But when using mysql or MariaDB as a database began That it was using a decimal type and this was caused by a Workaround that was added to silometer to support some Some older releases of mysql because I'm not entirely sure which Version number it actually was but some older versions of mysql Actually don't support high precision timestamps like was microseconds so silometer folks decided to To to well kind of work around that by by using when using the mysql back end by using the decimal type and Implementing a type abstraction inside a scale alchemy or inside the database model For silometer that will when the the timestamp type is used will convert that to a decimal and Write that to the database in the end. We just needed to Use the same database type decorator for for all migration tool and Was actually pretty easy to to import that from from silometer and then it will just do the same conversion when When doing the migration So with that I think we're ready for some demos So we've got a number of demos the first one is that The pre-check failure So here you can see that we've got a database that's got the emoji in the display name and It errs out so it points out where the error occurred what row And so what do we do there we we fix it we use the abstract volume command to set the name to something else and rerun The pre-check And there are no errors now for a more full on demo So we fog into mysql We create Neutron database And the Neutron user We grant privileges So we copy the Neutron configuration file so that we can change the connection string and then we Run db manage It's probably worse to mention that this all happens when the service itself is still running right So we db manage create the schema At this point we run pre-checks using the source and target database So now we can stop the service We can Actually do the migration because you don't want data being written to the database while you're migrating it We can edit the config file To change the connection string And we can start the service again And as a test we can list The networks which is using mysql So we've also got batching So here we have a batch file So what's interesting note here is that every time you run the migration tool Your database credentials will be visible in the process listing Whereas if you use the batch file and set mission correctly, they're not So here we can We've got keystone and glance set up with source and target URIs And we can run pre-checks or express them That looks good. So we can migrate them Which is done So we We wanted to convert it into a module. So it was written as a proof of concept script That's the destiny is this module because google images for module is not really that exciting So proof of concept script is is really great for for getting an idea of how to solve the problem and and running into issues, but it doesn't actually Give you much help So namespace is a great which allows you to logically separate parts of the code We did that for the solobot of high precision stuff. We're able to Subplant it from solometer and put it into its own own module And it means you've got easier testing because you don't need to use something like import lib to To import the script as a module And we said we wanted to be open source Which means that we wanted to put it up on pipi So you don't just push it up to github and say look it's open source. We're done. We need releases Uh, so we should make regular releases and we should push them up And as a bonus this allows us to install it via pip makes it much easier to distribute And gives us metrics from pipi allowing us to monitor installations and downloads on a month by month basis And pipi has some opinions Pretty strong ones in fact the license of classifier is only checked on upload And if it's incorrect the upload's knockback Which may have bit me directly And you get some fun engaged with pbr So pbr is python build reasonable. It's uh, it's got a bunch of useful patterns around installation and Building it's really heavily used by open stack, which is why I wanted to use it Um, it's even more opinion So if you choose to use requirements files And also use setup tools you've got to Keep both of the uh requirements up to date in both places, which means that if you go to update one stop breaks There's a gotcha there pbr setup py install won't use dependencies at all You need to install them yourself But pip Will deal with that all for you If you accidentally include a version number then pbr start behaving very oddly And if you forget to include a long description pbr evaluates it to false and then uses that as a long description anyway, so that was our first release So, uh, there's some possible improvements we could make we could parallelize it using a thread pool or something We we didn't really look at that Uh, we could have a better ui because neither alpha or i are really ui designers And uh, we could have more testing because tests we don't really finished So from both of us, thank you Are there any questions looks like no questions So we confused everybody yep Ah, there's a question please To add it to ansible open stack so Like how So you could else already use it an ansible open stack by I'm not well. I'm not very familiar with ansible open stack, but uh I guess you can just pip install it and use it from there. Yeah, uh, so And yeah, in theory it would be possible since we converted it into a module You don't need to use the command line client You should be able to use just the module directly or write a ansible module that imports our module for for this yeah That would make the orchestration kind of simpler for example, um We've done something similar for for uh for our product. We're not using ansible or And um, we're mostly using chef still and and and there we have Put some orchestration around that so that we don't need to worry about stopping services and so on that We'll do the right thing in that case Which open stack services is important? Um all of them We we we haven't actually run into any issues with uh any of the services that our product supports Um, it's migrated their databases cleanly and correctly. I guess we're done. So no more questions and uh, thanks a lot and Have a nice rest of the uh summit. I think it's one more slot after us. That's right