 Hello everybody from Foss Asia. My name is Colin Charles. I'm here to give you a quick recording of my scale 8 versus MariaDB server 10.4 first recording actually failed. So this is my second attempt at doing it So here to talk to you about my scale 8 versus MariaDB server 10.4. I've been in the My scale world for more than 15 years I've active relationships with codership because a gallery cluster as well as proxy SQL funding team of MariaDB server Probably of my SQL AB worked at Prokona. So I've basically been around the block quite a bit in terms of licensing This slides are obviously fully CC licensed. So yeah, go for it So what is compatibility basically to state in which two things are able to exist or occur together without problems or conflict? Why it matters is because MariaDB server is the default in many lengths distributions and This is slowly of course changing because I've used sent read at enterprise index 8 or You'll find that it ships both MariaDB as well as My SQL that's because you know things are obviously different But if you use sent OS 7 or an ability 7 it only comes with my scale 5 5 MariaDB 5 5 as a default. Sorry, not my skill. There's no my skill at all Of course, it's abundantly clear that MariaDB server is not my SQL Which is again, why you starting to see Amazon as you're an Alibaba offer different services expect always in combat abilities between the two Now when it comes to licensing MariaDB service GPL v2 only max scale is business source licensed It is not an OSI approved license proxy SQL is GPL v3 Column store again GPL v2 because of history But many of the tools that make column store usable and useful actually business source licensed So you want to maybe think twice about that my skill Of course is community enterprise reasons just like MariaDB per Kona follows GPL v2 when it comes to support What is the ecosystem and landscape look like I don't know if you if you go to a third party company That's not MariaDB per Kona or my SQL Would you find that they are willing to support the solution that you are providing? That's what that's very important. So third party companies include like Pithian or you know local local companies and so forth Who does training? Who does certification both my skill and MariaDB do certification per Kona doesn't do certification Being an open source conference Community contributions extremely important in terms of who actually Takes the most contributions I'd say it's MariaDB because you can put it on the MariaDB contribute agreement or the PSD new followed by Oracle My SQL which has the OCA and then followed by per Kona Per Kona being very much more interested in only solving the customer problems like I was in governance You know MariaDB cooperation is a full-profit cooperation. It's been funded a lot. It's on its third CEO Not a founder CEO. It's raised over 98 million dollars It obviously has a foundation, but it's also heavily dependent on venture money So, you know, I always ask you know, how many times things like website change management change and be careful What would end up in an open source? comes the releases my SQL of course, you know in the middle is the is the sole sole sole release Releaser, but MariaDB does release once every year my skill once every two years plus Per Kona, you'll notice is actually lagging behind in terms of release time compared to Compared to my SQL it can sometimes lag up to eight months behind a release And that's because it's getting extremely hard to merge my SQL because my SQL Point releases also get new features So this is something that's very important to remember with my SQL is that it introduces new features in point releases like 8 0 17 8 0 16 and so forth When it comes to installation It is clear that Per Kona server follows the easiest installation model because you have a young file You just install a RPM and then you're good to go MariaDB makes you copy and paste edit a file Then install because it's got a repo builder, but we might go to grab packages and yeah This can be kind of vaguely annoying obviously Now when it comes to replication You know is that it's easier to migrate to MariaDB server, but it's much harder for you to migrate away from MariaDB server So you can do kind of like a hotel California program to receive check out any time you like, but you can never leave And you always have the GT ID variances and MariaDB when it's attached to slave to my SQL will just discard the GT IDs and Actually create create its own Now when it comes to replication the default binary log format inside of MariaDB is mixed Where is the default is row inside of my SQL? So you should probably note that you may want to change it because mix means you get statement most of the most of the time or you know, you'll get Row only when statement is deemed problematic. Also, you may want to think about turning on the Replicate annotate row events to off because it's on by default, which means that if you get a binary log Or application a row based replication. It will also append the statement on the top Which will actually waste binary log space. However, you could also turn on binary log compression Which is unique to MariaDB. Maybe then save some space Time delay replication is also extremely interesting because This is something that's been around for a while, which allows you to have a delayed replica Maybe one hour delay. So if you're doing live code pushes, this might be useful for you Especially if something breaks and you don't want to restore It was present in MySQL 5.6, but it only arrived in MariaDB 10.2, which is a significant delay in time dml only flashback is great allows you to roll back instances database is stable So an older snapshot via came by Ali Baba is the MySQL bin log tool extremely extremely useful And I highly recommend you check it check that out Now when it comes to Security MySQL is of course not using something called a caching SHA-2 password And it actually is not compatible with many connectors Unless it's provided by MySQL. So this is something very important to know when you're moving to MySQL 8 Is that connector support is extremely extremely important Now MySQL for the longest time it uses something called MySQL native password Which actually previously uses SHA-1 base authentication and double SHA-1 But then computers begin faster SHA-1 isn't great Because you get collisions double SHA-1 is also not really considered as secure any longer Though we haven't really seen any breakings So MySQL naturally has SHA-2 physics MariaDB has N25519 Which is based on elliptical curve digital signature algorithm Same as OpenSSH uses elliptical curve by Daniel J Bernstein Now they're not compatible with each other so you can migrate users passwords between the two Very important to remember that validate password is on by default in MySQL 5.7 and greater But not in MariaDB so maybe you want to turn that on because it's actually kind of kind of handy My basically you have the extended slow query log inside of MariaDB very very useful You have things like a thread pool you have That you don't have to buy MySQL enterprise for thread pool of course also comes inside of the Kornos server You have things like cable elimination which allows you to do anchor modeling You don't get that in any other open source database Generally speaking error numbers are 1900 and above Perkona has taken like 30 the 3000s and above Things like show processes with progress reporting so you can actually see if you do an altar table Should you do an altar table and And go for a cup of coffee or go away for the weekend Dynamic columns allow you to actually have nice new Many new columns for each and every row inside of your database tables Very useful up to one gigabyte in size. So it's like a no SQL kind of thing For you encryption is of course different. MariaDB ships a very old performance schema. So don't use it Because MariaDB prefers you to use information schema whereas my skills and Perkona 8 are excellent performance schema Now this is an example of where I can do the Time in microseconds. It's the stages max stage and so forth stuff. That's not present inside of My SQL 8 When it comes to JSON, there is a binary data type inside of my SQL whereas in MariaDB you really have blobs with handling functions, so to speak When it comes to using The x protocol which allows you to use a new port 33060 MariaDB is missing the functionality for this and unfortunately, this also means it doesn't work with things like propagation and you can't actually Do Queries with javascript and so forth. So this is one of those key key differences I think that that are quite significant that will drive things to be a little different going forward Storage engines. I'll talk to you only about the cool storage engines like my rocks My rocks is you know based on rocks DB, which is a fork a level DB It's right optimized focus on endurance of flash devices because it has 10 times right less right amplification compared to inner DB It's also a better compression than inner DB And it's got the ability to load data fast avoiding compaction overheads. In fact, my rocks powers More than 95 percent of facebook today so If you if you are wondering If it is ever used in production My rocks is the facebook storage engine of choice, which is great news for you because you're actually getting huge amounts of benefits from from them This is a spider, which is great for us transparent sharding and re sharding via sql So you can partition via range key hash list And this is a all via sql And of course there's things like connect storage engine Which allows you to import MongoDB files and Text files and so forth. It's also fairly cool The ability to do things like column compression, which is present inside of prokona server with this dictionary support also From row row compression to page compression You can now create columns that are also compressed and you can actually see if columns are how they're compressing or decompressing Invisible columns allow you to remove a dependency to applications So basically a column can be added to a table without hiding them from the application, which may fail to run Keep historical columns are needed by applications anymore Prepare, uh, you know database level upgrade So when you do a select star from as you could see select star from user It doesn't show anything unless you explicitly specify the secret column in this example system version tables I really really enjoyed this because it's a sql 2011 standard Stalls history of all changes so you can alter a table to enable disable remove system version data You can make as of queries, which will be on the next slide. So you can select data as a point in time You can also select between and end to select data between two points It's great for forensic analysis or legal requirements data analytics point-in-time recovery To find time step versions of data And in this example, uh, I show you that, uh, you know a one user Uh called call in $1,000 in marketing department then gets a $10,000 salary bump and then move to the engineering department and The as of example of that is to see what What he was earning at that point in time back in time so There are of course, um things like uh differences obviously like no resource groups No, you know, maybe native partitioning no timetable storage engine No no set persist No, the regular expression libraries are different my sql eight uses I see you Whereas marie uses pcre, but everyone's gotten rid of harry spencer's library But you know, I think there are many many features in marie be that outweigh the differences Or whatever is lacking so to speak But at the same time we have to remember that differences also mean that things like Not having a gtid available in the ok column for example is a is a real big issue So, um, you know, you can find the tools like proxy sql can do do interesting things Because it has a ok packet gtid in the ok packet, but it is missing inside of Things like marie db So you can do consistent reads and an adaptive query routing based on global transaction id tracking Which you can't do inside of say if you use marie db So you have to know the pros and the cons of which server you end up choosing Of course, it's also very important to note that marie db is a While it is still considered a drop-in replacement can use you can read your milet cnf Um can basically use valid mysql It's it's it's an easy upgrade, but migrating back may not necessarily always work And when it comes to clustering mysql eight uses a group replication with indb cluster Mysql shell and mysql router. It's all one Package you can get and mysql shell can configure your your inner db cluster and the router We've served and for ship's galera cluster rolled into it It's a configurable again via milet cnf, but you don't have the tools like shell and there's no proxy Buildings you still have to download like max scale a proxy sql Perkana server uses galera cluster. It does ship in actually be cluster a proxy sql admin tool as well as proxy sql So you're looking for out-of-the-box clustering solution. It just works PXC actually is is pretty good But uh, the development definitely lags behind what you see inside of marie db server as well as codership directly So galera is now at version four Which is available inside of marie db server 10 4 and codership and obviously as a release But perkana server is still Not not not is still using galera 3 in production use cases And of course when it comes to proxies mysql recommends router marie db server recommends max scale Perkana recommends proxy sql proxy sql works with all the above and it's like the swiss army knife for dba's it works generally With with everything everywhere So with that I actually compressed this 25 minute talk into 15 minutes because this is now the second recording i'm making And I will always be available via email or twitter. So feel free to drop me a line I can have a very comprehensive resource. So please watch watch twitter and the blog for Further information. So, uh, thank you. Thank you very much for listening. And if you have any questions, feel free to ask them. Thank you