 Thank you all for coming It is the last talk of the day and I'm in between you and your beer I'm here to talk to you a little bit about Drupal at high performance Especially when you're using the MySQL database and here's an alternative called MariaDB. Oh, okay Is this better? This mic doesn't seem to like Become longer, so it seems I have to bend. Oh, is this better? Okay Okay So first things first how many of you here use my SQL if you I was gonna say if you don't use it you should put up your head Okay, how many of you have heard of MariaDB before That's good, how many of you here use MariaDB? Okay, so a bunch of you already use MariaDB So there is there is likelihood that you may not learn many new things because you're already Part of the MariaDB community However, I'm here to also help you maybe make more use of MariaDB And if you are using MariaDB on a site and would like a case study, please send me an email So a little bit about me. I am Colin Charles. You can email me at Colin at MariaDB.org I'm also active on the Twitter if you Are on Twitter consider sending me a tweet say hi and the MariaDB guy at Monty program I should probably tell you that SkySQL and Monty program have agreed to merge about a month ago So I guess very soon. I'll be the MariaDB guy at SkySQL however, if you have been familiar with the MySQL world and realized that my SQL was then purchased by Sun and Sun that was then purchased by Oracle You might be thinking is this MariaDB thing safe for me to continue using and yes It is because MariaDB is actually governed by a foundation very much like the Drupal foundation So there can never be One and one controlling entity buying over MariaDB So consider this MySQL done right. I Used to work at MySQL AB for a very long time making MySQL and then I joined Sun when they acquired us But I left as soon as Oracle Proposed to acquire Sun. I have worked on the Fedora project as well. I was on the very first Fesco Board anybody use Fedora? Wow, it's cool and I used to also work on openoffice.org Anybody use that? Wow Okay, so I was responsible for some of the presentation module in open office and even I use keynote today My history with Drupal. I have to admit I've spent more time on Media wiki and WordPress Schemas and helping those projects Then I've helped Drupal, which is sad because I've actually known Drupal since 2005 where I was introduced to Civic CRM at Aspiration Tech Advocacy Day in San Francisco. I Really wish to contribute more to Drupal, but I'm not a front-end guy. So I guess soon I'll be contributing stuff on the back end This is the rough agenda of what we're going to discuss today and I have approximately 15 minutes to do that with some questions I'm not sure if we should do questions and answers before or after but let's try to take it after Also for most of this talk, I'm going to reference MariaDB 10 which is currently software that is not GA release, so I'm going to mention some stuff that is only available in a alpha release But it will be GA in like at the end of July, so It's almost as good as being GA and we run it in production and we know customers that run in production as well so I'm guessing your you as a Drupal user would have great experience with MariaDB because on The fourth screen of set up a database. It says set up my SQL MariaDB or equivalent So you've probably already seen this word MariaDB But you've just maybe not taken action a handful of you have maybe about 10% of you have taken action Which is great Hopefully at the end of this talk the rest of you will take action and convert from my SQL to MariaDB as well It has also been recommended in the Drupal documentation as part of the requirements Alongside my SQL it is used in testbot, which means Drupal core runs well against MariaDB Azure and barracuda Also has MariaDB available for it and we received our first bug from a Drupal user During the 5.3 series days, so we received our first bug from a Drupal user. I would say about a Year and a half ago So we've definitely had users saying hey We have you know bugs that the query didn't materialize in a similar way as it did in my SQL And we spent time since then making MariaDB's query optimizer even better and We would naturally have some bugs when we were kicking the tires on improving the query optimizer So what is MariaDB? MariaDB is a community-developed branch of my SQL very much like Drupal is a community Oriented project it is not backed by any one company. It is it is you know governed by a foundation It is also a feature-enhanced my SQL But I guess we I want to make clear that it's not a patch set against my SQL any longer If for some absurd reason my SQL isn't developed in the future. We're happy to continue developing MariaDB We have lots of core developers Who know what we're doing so it doesn't make a difference if My SQL is developed or not But I also don't want to say that we're a fork because we merge with my SQL on a monthly basis So we're still a branch with a huge amount of different code and different features inside We are also fully 100% compatible with my SQL We are a drop-in replacement and we are feature complete to the T So if my SQL has something we will have the same thing and Again, I cannot reiterate more how important it is to be governed by the MariaDB foundation as opposed to be governed by a corporation And the aims of MariaDB are really really Important we are 100% compatible drop-in replacement This means today if you uninstall my SQL install MariaDB. It'll just work All the on-disk data is the same. This is true even for in a DB The on-the-wire protocol is exactly the same If you are migrating from my SQL 5 1 to 5 5 like MariaDB 5 5 you should run my SQL Upgrade, but if you are migrating from my SQL 5 5 25 to MariaDB 5 5 30 Where it's a it's a similar major version. You probably don't even need to run my SQL upgrade Our focus is really to make stable releases We we don't believe in releasing software We like to have a schedule. We like to say we like to create one new release every six to nine months But if if it's not stable or if there are bugs or if their regressions, we do not want to release it We will hold back a release to make sure the software is perfect because our aim is to be used in data centers large data centers and We really increase the tests and the QA cases available for MariaDB our code coverage is actually pretty good now We have way more test cases than any other my SQL branch or my SQL itself Also, we have way more features. So you have more test cases naturally and we obviously aim to have no regressions That means if you are we all every time we make a push We test upgrades from my SQL to MariaDB previous version of MariaDB to the current version of MariaDB. We have intensive test suites The other thing is we are 100% GPL V2 We have no enterprise product. No commercial extensions. Nothing of that sort. We are GPL to the core However, if you want to embed Things again say live my SQL client and you want to have commercial software We also provide LG PLC and Java client libraries So that you don't have to get an OEM license or pay anyone for that. I Did not include that in the slides because it's not directly important to the Drupal community But we found at least one person outside at the expo hall saying that this was something that they could they could use So I'm mentioning you know We also have Maria captains from many different companies companies like swing search LinkedIn Sky SQL ha ha We were different companies before Taobao out of China Facebook Perkona codership and a whole bunch more companies and if you follow the Maria Discuss mailing lists and the Maria developers mailing lists you end our bugstree. You'll realize that Google has also taken a great interest in MariaDB Why they are taking an interest is speculation up to you Now MariaDB is filled with a whole bunch of features and the last time I talked about all the features without even going to practical examples It took a good eight-hour course. I I have one hour and I have to respect your time So I'm going to only focus on the MariaDB features that benefit Drupal users Not all the MariaDB features In fact, I took a list of all our features and I pasted it into Wordl and it came up with this tag a cloud how web 2.0 of me and It's it's a lot of features. I mean we spent a lot of years engineering MariaDB now But I think the one most important thing is that our default storage engine is extra DB We can switch to inner DB, but we always want to generally have extra DB around extra DB is generally it has a lot less check pointing so smoother check pointing on the graph on the left and It has a lot less slashing to disk with a lot more stable performance. So Here you can see the slides with extra DB in green is MariaDB with extra DB showing you generally stable performance that's increasing not, you know jigsaws The other thing that you should probably notice that Oracle has been in developing in a DB a lot So kudos to Oracle. We have taken Inno DB from 5.6 because there's more more features inside compared to the 5.5 release however We are just waiting for extra DB to include the bitmap checking so that we can have better immediate backup snapshots and Why is an ODB important to you? Why is extra DB important to you? Because extra DB as features that inner DB itself doesn't have like fast inner DB restarts How many of you run Drupal in the cloud? Okay So the cloud is inherently Today I have a slave. I don't have enough. I have too much capacity I turned the slave down I add another one tomorrow and I get slash dotted and I take it down again, etc We have fast inner DB restarts, which basically pre-populates the inner DB buffer pool and This is great for your cloud environments because it'll pre-populate the buffer pool to a state right before shutdown with sequential IO So you get a fully warmed up server with a warm buffer pool in minutes rather than hours or days This is hugely important for cloud. We also improve single Core performance, which is something you get in the cloud regularly because you just fire up an instance so We improved single core performance as well as multi-threaded performance. We make use of RAM better than we Would in stock in ODB So for all intents and purposes you can consider extra DB to be a better in ODB and Extra DB is jointly really developed by us at Maria DB as well as the folk at Percona who developed Percona extra DB And they developed wonderful backup software as well So these are some of the interesting features that we've been shipping inside of Maria DB That will benefit a Drupal user Maria DB 5 1 we released in February of 2010 keep in mind that Oracle announced the purchase of MySQL Sun in April of 2009. So we didn't have a lot of time We had to you know leave the company start start the new build system, etc Create new test cases and so on so the most Important features that we focused on was one was table elimination table elimination is basically anchor modeling So if you have a highly normalized tables You can now instead of querying When you when you write a query instead of touching each and every table Sometimes you can derive the answer without touching the tables and anchor modeling is not something you get commonly inside of open source databases We also included the most important new storage engine Extra DB we included a whole bunch of other storage engines Which I will which I don't consider important enough to mention here today And we've had this wonderful thing called pool of threads or a thread pool So in February of 2010 we've had a thread pool I have a whole slide dedicated thread pool. So let's move on from that Maria DB 5 2 came out in November of 2010 as well. This is based on Maria DB 5 1 Here we gave you more instrumentation now you can do things like show client index statistics Show client user statistics so you can start monitoring what users are doing how your indexes are performing and so on Today you have something called a performance schema that you can use as well Instead of mysql 5 5 and greater. However, the performance schema has overhead close to even 10% Running overhead Whereas the extended user statistics have no overhead. So this is something you can consider if you're running Drupal at scale as well I Have dedicated slides for both the segmented my eyes and key cache as well as the Pam authentication plug-in So we'll move on from that Maria DB 5 3 was based on 5 2 and It may sound like we were very lazy in the whole of 2011 But this is probably the biggest change to the optimizer we made it took like 12 man years of work so we released it in February of 2012 and It was still based in the 5 1 code base 5 1 base and Here we had huge changes to the optimizer join changes like you know block nested loop joins hash joins classic hash join batch key access We had materialized views come it's to come in 5 3 we have Disk access changes like multi-range read all these changes Benefit your queries without you having to do anything for them You may have to turn them on via the add add optimizer switch, but you don't have to do anything else So if you're writing your regular queries Chances are if you test against Maria DB, it should run better query execution time should be faster and We also included something called microsecond precision because nowadays when you run queries You get them returned sometimes in 0.00 seconds or 0.01 seconds That's not enough When you're running with today's hardware because today's hardware is so much faster So we have microsecond precision to tell you how many tenths of a second You your query is coming back in so you can actually improve query performance that way as well We improve replication tremendously How many of you here run a master slave environment Awesome, so Master slave environments We have enabled things like group comment as well. I have a whole slide dedicated to that as well Progress reporting When you do an alter table or a load data in file, sometimes the alter table can take you several seconds Sometimes it can take you five minutes. You can go get a cup of coffee. Sometimes it can take you months All the tables to take you months are no fun So we included a progress report So now it'll actually tell you how long it's gonna take you and if you're doing low data in file for your like Your ETL operations you can also see Progress reporting as to how long it's gonna take so you can figure out if I get a cup of coffee or go away for the weekend But one of the other cool features that we're starting to implement and you'll start seeing is that we're building building blocks Is that if you are running in shared hosting environments or in cloud environments, and you're on like an ops person? With my SQL you could always do kill hard thread ID or kill soft thread ID So soft would you know not not kill like running process that would damage the database We now allow you to do things like kill hard user and specify username so if you running in a multi-tenant environment and you have one user having Drupal instance with module that is clearly using too many queries That's affecting all the other users. You can just go kill hard user username You can of course schedule this put it inside the event scheduler, which we have inside of MariaDB Which is also inside of my SQL or Trigger it against cron So I think the kill user is actually pretty useful for you MariaDB 5 5 is when people actually really started getting interested in MariaDB. Does anybody here use anything older than MariaDB 5 5? No, so all of you started with 5 5 For the ones that did start MariaDB 5 5 came out in April of 2012, so it's out for a little over a year now We were we obviously took a long time to release it because we had to merge 5 3 big changes in the optimizer And then we had to add some new features as well We included a more efficient thread pool and I want to Talk clearly a little bit more about that. I have a slide still on the thread pool Which I will show you nice pretty graph, but our thread pool is open source If you use my SQL and you want to use the thread pool you have to buy my SQL enterprise We said that's not right. We must make it open source for you. So that's what we did We made an open source more efficient thread pool compared to what we had in MariaDB 5 1 We also included a non-blocking client library so you can start operations in threads and Let it let the result travel back to you when it actually is ready to come back to you This again has no great performance in terms of PHP, but if you're running my SQL on the command line You can actually do things like you know show status on many machines for example The node.js people because node.js is very asynchronous They decided to make something called Maria SQL. So there's a there's a very fast node.js driver out there But again not relevant to this crowd We also included limit rows examined This is kind of useful Because sometimes you have silly users that say we want to do a select star from T1 and just let it run Now you can actually say you want to limit it to how many rows it's examined So we we use condition pushdown to actually not just read the first Like you know this example is 10,000 rows not just read the first 10,000 rows What's read to use the optimizer to see what the optimizer was going to see and then let the optimizer limit 10,000 rows So you're not just getting a head of 10,000 or a tail of 10,000 But you're actually getting what you should be getting. I have a slide dedicated to Sphinx to see and we also included extended keys for InnoDB which we are now extending so that you can also use in TokiDB and InnoDB has secondary keys and they contain user defined columns as well as primary key columns The optimizer now uses Uses it finally so the less so less rows are examined So when you do an explain on a query You will now see that the reference strategy will change from constant to now and you'll see that joint performance can go up anywhere between Zero to 2.5 times improvement in performance So extended keys is something that you will never have to touch but will improve your performance anyway MarieDB 10 This is generally what most of my examples are based on Where we're horribly embarrassed to tell you that it took us about a year to merge MarieDB 5.5 But we can with 5.5 compare against my scale 5.5 and At last comparison we have over one and a half million lines of extra code and the diff stands at 61 megabytes in size That means we have a huge huge chunk of changes and that actually comes back into features 5.6 the code has been heavily refactored. We should make us porting to 5.6 Very very hard. It would probably take us another year to do the merge So instead of doing that we decided we'll continue on with MarieDB 5.5 We will backport all the features from mysql 5.6 and we'll continue adding new features that benefit users So for all intents and purposes you will MarieDB 10 is mysql 5.6 MarieDB 11 will be mysql 5.7 So in 10.0 we took a bunch of new features. We took inodb from Mysql pretty stock. We took the performance schema from mysql also pretty stock Online operations is actually pretty cool because finally you can do things like online alter table Which is something that you couldn't do before which would take your database Potentially offline and could be very long operation, which is probably what made no sql databases generally quite popular We also re-implemented something so now for example Now will be the default in your daytime columns We're also including things like global transaction ID But not not the actual implementation that you get inside of mysql 5.6 because we think that is a buggy Implementation, so we are improving it and we're also giving true parallel replication in the slave threads But what what other things can benefit a Drupal user? Multisource replication is definitely one of them if you are already running in a master slave environment You are likely also thinking about partitioning your data or what is now called sharding Now if you have many now if you have many different masters And you want to actually synchronize the data back again Multisource replication allow you to replicate many masters back into a single slave Now you can run your ETL and a little quick analytical queries or BI tools against that one slave This has been a long standing feature request for mysql Which we now have inside of MariaDB. This is not available inside of mysql 5.6 It's unlikely to be available in 5.7 either Remember I told you about the building blocks as well. Now you can explain on Running threads you can do show explain for thread ID So show explain for maybe two or three and you'll actually give you the explain of a running query Previously you had to actually do explain before you ran the query now you can do it on running queries as well Probably something again very useful when you have long running queries We also have per thread memory usage Again useful if you are in a Multitenant environment where you can now do you can either query the information schema and then kill things based on users Exceeding certain amounts of memory for example or and even show status will show you how much memory is being used So more useful features for people in these kind of environments Anybody here use Cassandra for big data Okay, I see at least one person put up their hand We're kind of deciding to do Think that MariaDB is more of a data platform now as opposed to just accessing SQL So very soon. Well, actually this is already something you can do today You can query instead of using SQL you can query a Cassandra cluster from MariaDB So there are no Applications out there, but if you are considering writing a new module That maybe you want to also have big data querying or you want to keep track of logged in users in real time You might want to start that kind of details inside a Cassandra and then query it from MariaDB The benefit of this is you don't have to ever write SQL. You can do it with regular SQL So you can extend Drupal relatively easily to do this for you in your new modules We're also working on a key value store called level DB Many of you probably never heard of it or used it but it actually comes inside of your standard Chrome browsers as part of index DB so if you are using Chrome you already have a certain embedded copy of level DB and We figured that at some stage people will want to access key value stores as well Dynamic columns is the building block for how we end up doing the Cassandra storage engine Here you can store different sets of columns for every row in the table. This is very very much. No SQL like this is great for an e-commerce store for example and There are definitely commerce related applications that currently ours That are you know satisfied by Drupal However, now you can have different attributes to each item We Did use this as a base for building the Cassandra storage engine and we wanted to join The rest of the world so that now you can actually request a row and get it returned back to you in JSON format JSON is becoming a popular Interchange format and we expect that this should be relatively useful in the future and With a Drupal 8 coming out where you will also be able to query things against MongoDB. This might be a very good bridge again Oh, and you can name dynamic columns. This was a limitation We introduced dynamic columns in 5-3, but we've only allowed you to finally name them in 10-0 Anybody still using my ISM Everybody switched to you know DB. No, there are a couple of hands going up My ISM has major major bottlenecks. It's it's great for bulk inserts But it has major read bottlenecks and that's largely because it's affected by the key buffer This is something that Drupal uses who are currently on my ISM know and feel the pain of You can use segmented my ISM key caches You can add this as an option in your my.cnf and have up to 64 segments to improve performance of up to 250% Based on more threads that are started So this slide shows Queries per second as well as the threads that are going so at the moment you start hitting eight threads you start seeing great performance already My advice though is to obviously migrate to you know DB which we ship as extra DB because you do not want to be running my ISM But if you are using my ISM, this is invaluable We also have batch key access speed ups and this is an example from Certain commerce product that you can extend from Drupal Showing you increased performance With regular joins and batch key access joins so anywhere between three to ten times the performance for This particular query Another really cool thing is that some queries finally materialize Many users of my SQL know that some queries just do not work You rewrite them as joins because sub queries never materialize Of course, we've seen many modules and extensions also have the use of sub queries which then Trashes the server because the server attempts to rewrite them or maybe never materializes them which makes things really slow for you Now we materialize sub queries This is a run on a data set of about 30 gigabytes using dbt3 You can see that from slow to faster or impossible to much faster Usually inside of several Drupal models that we've tested you actually get slow returns because it it does eventually materialize But it's really slow However, with MariaDB the sub queries do materialize and they're much faster and we have a sub query cache as well So we do cache the results Now mysql56 also has a sub query Optimizer that can generally optimize most sub queries, but ours is a little better Plus it has the cache If you are using replication You do realize that if you want to have the D in acid the durability You need to you need to enable sync bin log equals one as well as in a db flush log a transaction commit equals one If you do that with mysql you get the green line Flat performance you don't get any through any increased throughput However, if you do it with MariaDB your transactions that can continue continually increase Even when you have many more current clients with relatively good Standing which is the blue one up there Now you might be wondering what the other orange and yellow ones are and that is Facebook This benchmark was not performed by any of us. It was performed by Mark Callahan who works at Facebook He they arguably probably one the largest users of group commit out that they created a Facebook version 1 and version 2 of the patch as well But with MariaDB we did a much better job and it's in production code now So it's it's been in MariaDB since 5.3 and if you happen to use the prokona server variant It's also inside of 5.5. They've posted our group commit So if you are if you are using replication and you if you are running Drupal at scale You will definitely have a slave. You probably want to turn this on The third pool I think is probably the most useful for Drupal users You have many many short running queries every time you load a page that doesn't hit the cache or whenever you make a change If you have many many concurrent clients or users You will want to definitely turn the third pool on Why is this important when you have short running queries instead of opening up? One thread per connection. You now can make use of a pool of threads that keep on getting reused One of the things that is bad for performance is that if you don't have enough cache threads You will actually launch too many new threads and it will cause your OS to have contention issues You just turn this on by having an option in your mine at CNF thread underscore handling equals pool of threads restart the server and that green line there is my SQL with MariaDB with concurrent clients with pool of threads enabled and the orange version is One without pool of threads enabled. My suggestion is turn this on if you are running a Drupal website at scale You will find that this is very useful because as you have more concurrent clients. You want more OLTP threshold This is one feature that you can get inside of my SQL enterprise ie something you pay for or inside of MariaDB 55 or MariaDB 10 if you have ever wanted to authenticate against the PAM server And we are now working actively to authenticate against LDAP as well as active directory We now allow you to do this as well with the PAM authentication plug-in which we introduced inside of MariaDB 5.2 This is something again. You don't have to pay us for it's completely free and I think one of the cool features that I Played with is integrating it with Google authenticator So Google authenticator is something you can download on your Android phone and possibly even your iPhone Which will generate sequence numbers for you. You can turn you can configure it to work with PAM And it can also be a it can be two-factor authentication for you So you can now instead of just logging in regularly to Drupal you can provide one extra form of login which is Code that is generated that's valid for like one minute every time it's generated So many people get hacked over time because of weak passwords or using passwords over Wi-Fi and so on Two-factor authentication will ensure that you don't get hacked unless your phone gets stolen as well Anybody here use full-text search All right a bunch of you anybody here use things before Also a bunch of you Now you can do full-text search with Sphinx and you can also have your regular database There however, we have a storage engine for Sphinx that will connect directly to the Sphinx search D So you can specify queries using regular SQL again And you can let Sphinx do what it's good good at doing indexing searching sorting filtering etc So let Sphinx do what it's good at because Sphinx is optimized for these tasks There is no performance overhead except if it's traveling over a network that will be network latency But if it's running locally, you should have no performance overhead And you can also join the search tables later with other my SQL table So if you have if you make a query and the return result set is something you want to join with you know DB or my ISM Something you can totally do. This is one of the beauties of the storage engine interface is that you can join tables from other Other databases or in the Postgres world. This is now called the foreign data wrappers and We've called it the storage engine interface for a long time now Anybody here use Drupal with maps Anybody building location-aware apps all right a bunch of you My SQL sucks at GIS My SQL can allow you to find out the minimum boundary regions and tell you how to get in a rectangle Rectangular side for tell you this is one line and this is one line Last I checked you needed to check maps inside and when we started Maria DB We said we will never touch two spaces one space is embedded Embedded the embedded war is worn by SQLite and we will never touch GIS because it's we think it's worn by post GIS How will we found a customer that wanted to pay for this feature and we ended up developing it? So now we have full open GIS support inside of Maria DB since 5 3 and It has full SQL geometry types so you can tell you how to get from Martin Luther King to the Lloyd Center not via just lines But an actual route you can now store that inside of Maria DB using the SD prefix and you can also do geometric operations on them So I think this is actually pretty useful and we're working with the folk at open street map to start looking at Maria DB Now as well like ironically that's a skiest girl engineer who's was doing it in his spare time Anybody hosting on SSDs now all right a bunch of you anybody hosting on fusion IO cards also At least one person put up the answer. That's awesome so Fusion IO cards are much faster than your SD cards We have already started working with fusion IO and we now support atomic rights Much better performance than you'd get inside of inner DB Using fusion IOS direct FS direct FS is not a fast system. That's fully in production yet But if you're using fusion IO with things like XFS as well, you get amazing performance So if you're worried about Maria DB or inner DB the extra DB that we ship not using up your SSD to its fullest Rest assured we are using the SSDs to its fullest We're not using fusion IO cards to its fullest, but we're using it about 70% of the way already and we're improving it regularly Because the word literally started a few months ago and we managed to have a first release in April which fusion IO was very happy about so if you are Most of you are probably using spinning disk at the moment, but the next generation which is not far off will be probably all SSDs Because it's getting cheaper and cheaper and more reliable as well So we believe that Maria DB is already a super set of features We're going to merge in two steps. You're going to get 10, 0, 3 or 4 as a GA 10, 0, 3 is likely to be what will end up being the GA and you'll get 10, 1, 1 or 2 or 3 and By the time you have 10, 1, X you have the complete equivalent to my scale of 5, 6 So for all practical purposes 10, 1 will be a full drop-in replacement to my scale of 5, 6 We're lucky because it takes people time to Use a new release of my SQL and 5, 6 only became GA in January and there's still huge amounts of bugs being reported to make it Get fixed We're also integrating some other storage engines, which could be of interest to the Drupal community One of them is talkie DB talkie DB when open source a month ago as well You might be wondering why everything happens a month ago. April is traditionally when we have a huge my SQL Conference happens in Santa Clara. We like to make announcements there So talkie DB instead of using B trees uses fractal tree indexes It also has great great insert speed There there are benchmarks that are published today on planet my SQL that show great insert performance even compared to the archive storage engine It has great compression again something that's compared to the archives storage engine and it has online schema Flexibility, what does that mean? You can do hot schema changes and online changes if you use talkie DB because they've extended that feature inside of talkie DB Also, if you're using SSDs, it's got amazing amounts of compression Talkie DB was probably always an interesting storage engine But it never really made it into mainline Maria DB because it was not open source And many people don't want to use something. That's not open source. It is now. So totally check out talkie DB Think the other important thing about talkie DB is that it is also like a drop-in replacement for a no DB So it doesn't have weird features like the aria storage engine that we ship as well So I have tested talkie DB to run against Drupal It runs fine, including the tests We also included another storage engine called the connect storage engine Which can now allow you to read write or update to many different formats and probably the most interesting different format There is ODBC because now you can connect Using the connect engine to say Oracle or any other database that supports ODBC and then get return result sets and then join it again and Display this kind of data back out to your web app in Drupal So again more glue code that you need to write at the back But the database is giving you more so that you have you can still use your favorite frontend How many of you've heard of Marie DB Galar cluster before or per Kona extra DB cluster? Okay, more hands go up when I say per Kona actually be cluster awesome So it is based on the same you know DB that you really know how to use You know DB for what it was has its quirks, but it's also really well known how to work around those quirks This is something that many other database systems don't have because people don't know how to work around your quirks Marie DB Galar cluster. We believe is really made for today's environments. It is fully synchronous replication It is read as well as write scalable If you can have multi master topology something you could not do it my SQL before but should be able to do once GT ID works well and it guarantees no lag or lost transactions Overhead to have synchronous replication in a in the same data center is less than 300 milliseconds at the worst case In all benchmarks and real-world use cases that we've had so far So generally to Drupal your multi master cluster will just look like one big database with multiple entry points But generally speaking you will have many different databases in the cluster and Your clients can connect to any node You can have obviously several nodes Galara handles the odd the node provisioning automatically It has a load balancer as well and a demon and the replication is completely synchronous It is not a synchronous So when when you try when you commit a transaction to one node You are guaranteed to have committed that same transaction to at least one other node as well. If not all the nodes synchronous replication should should make sure it's on all the nodes But of course today you're running things in a cloud-based environment So you may have other concerns as well Which I will address in the next slide but first I need to tell you one very important thing If you're running Galara cluster because it is quorum-based if there are failures 50% does not constitute a quorum So if you have these two machines that is not enough the minimum configuration for Galara cluster is three machines so that you have More more when it comes to this to detecting a quorum So if at all you you you you see that there's a failure Galara decides that It cannot commit any more transactions. It will now just pass it on to the next node all transactions are processed independently of each cluster node and You can replace transaction rights at at commit time right before the commit and if there are any conflicts cluster-wide conflicts are Informed and the victim will have to do a rollback but generally speaking this is how Galara works and it's Pretty good because it's been around now for more than a couple of years But it only gained fame when extra DB cluster came out and then later on Galara cluster came out You may be using replication regularly My suggestion is always make sure you're using a modern MySQL server or MariaDB server with group commit in the binary log turned on That means sync bin log equals one. It will be flush log a transaction coming equals one If you want to have automated failover with regular replication use something like MHA and Then after that while you're at it turn on semi synchronous replication something that has been around since my skill 5 5 Why semi synchronous replication important because when you make a transaction commit You know that with semi synchronous replication at least one slave got it and with the way MHA works it will require that it finds the latest slave and then Merges the bin logs the differential relay logs between all the other slaves So MHA will allow you to run replication and have very low to zero downtime Where downtime is measured in less than 30 seconds if you have a master fail I Give a talk on MHA as well. I gave one about Three weeks ago. You can also reference that up there as well Another very common thing that we get is oh I'm in the cloud. I have some nodes in Oregon And I also want to have some nodes in North Virginia, and I want to have some more nodes in Singapore Galera works wonderful over multiple geographies You can get MySQL with regular replication as well as MariaDB regular replication to work with things like semi sync as well But with Galera what you want to do is synchronous in one data center So synchronous say in Oregon, but over the when you may use something like time-delayed replication or a synchronous replication to the next data center and Then you have your application decide logically where it should be serving things so your load balancer will always say Okay, maybe I want this this this this users come from this IP. So this IP will be served from one data center Again, we've done this relatively well across so far up to four data centers all using the Amazon EC2 and it's turned out to be fine There are so many benchmarks out there that if I if I tell you one benchmark You will probably go and try and get another vendor to tell you another benchmark and they may all be just lies My suggestion is find a benchmark you can repeat So every time we do benchmarks we publish them on launch pad so that you can download it and run this bench yourself and If you can't find a benchmark you can repeat create your own benchmark because benchmarks are very They're just statistic oriented, but your workload is very different from what you get out of cis bench for example So run your own benchmarks But the one good thing we've done is by us publishing benchmarks is that Oracle now notices that we exist So Oracle also performs benchmarks against Us so it's it's it's I believe the old marketing adage is never ever compare yourself to your competitor Especially they're smaller than you We're really small, but apparently Oracle now compares against us too Now as a Drupal user security is very important a Few days ago. I installed Drupal straight out of Ubuntu 1204 I think or something and the first thing you do when you get into the admin panel as it tells you there is a Newer version of Drupal available. Please download it because this will improve your security Now my scale used to be like that too every time we get a security release. We would make a release out within 48 hours However, now Oracle has moved to a more critical patch update phase. So they release Major security fixes once every three months and they create new new CVE bugs for those We and MariaDB are very committed to security and we release bug fixes in less than 48 hours It's our general commitment to all users This is what's made us really popular with Linux distributions, which is why they're shifting to using MariaDB as a default Another thing is we don't like regressions. We do testing heavily So imagine if suddenly you decide that you have partition tables Because you have large amounts of data that is stored inside Drupal maybe using views lots of large use partition archive data and Then suddenly you also depend on the query cache But then when you did a minor upgrade that feature went away the query cache was disabled You should never ever do that in a running release You'd say it's going to be deprecated in like the next in the next major release We don't like regressions so we ensure that you don't get these regressions inside of MariaDB And we introduce new features really carefully Maybe an option like reflex looks very hot now But after maybe three or four releases later you find out that this reflex option was really not well tested bug and We never introduced this one little feature for the you know DB adaptive checkpoint option So again stuff You have to be really careful with what you do and we don't just do blind merges of my SQL We actually pay close attention to make sure that nothing wonky is going on. We don't want we want to make sure you get the best MariaDB out there So we love we love the community. We love working with the community. Here are some stats The humble brag slides of lots of English downloads. We have, you know, huge amounts of downloads We have great active mailing lists We're part of Google summer of code in 2013. We have lots of active user groups, not in Portland, but some in San Francisco MariaDB is clearly gaining popularity Wikipedia migrated the entire English Wikipedia to MariaDB So that gave us some very good press Fedora and open Suza ship MariaDB as default. So now if you do yum install My SQL you actually get MariaDB server kind of counterintuitive because you could be expecting my SQL But then suddenly you got MariaDB server, but that goes to the show the fact that we are a drop-in replacement Many other Linux distributions have also followed suit We have many many stories in the knowledge base like limelight networks and nimbus. They actually moved from They use talk you DB as well as MariaDB paybox is Actually does stuff With credit cards, so they're like square, but in France they handle like 30 or 40 percent of all credit card transactions in France So I have a little slide of deployments that we've had a few stories craigslist For example is fully MariaDB and MongoDB and and Sphinx back So they they keep archive data in MongoDB, but current data inside of MariaDB Mozilla has moved from prokona's patched 5 1 to MariaDB 5 5 mainly because they like subquery optimizations for some of the databases Spam experts was actually the biggest migrator from the earliest migrator as well to MariaDB They migrated thousands of servers from my SQL to MariaDB. So we were very happy to have them They started with 300 servers and now they're in the thousands travelblog.org is a Drupal based site that Now daily optimization time has dropped from 24 minutes to four minutes. So MariaDB is clearly doing something right web hosting companies also like us Slashgear says that you know, he saved so much in infrastructure cost because he runs on the cloud So he's doing more with less So there are many more of these success stories as well Paybox is probably the coolest because for me anyway because I sat in a cab in France and you can see that Your your card is being served by paybox. So when you you know, you swipe your Amex at the end of the ride It's actually being served using MariaDB a Very commonly asked question is if I were to change across the MariaDB would my existing my SQL databases work without issue Or is there anything I need to consider or watch out for? Yes, it'll work fine Actually be really is a better a no DB you can uninstall my SQL install MariaDB and it will just work if it doesn't just work Follow bug. Let us know Today if you do app get install or app get update or yum update or yum or zipper update You you get upgrades in situ. So that's in place upgrades. So you will have downtime naturally during the upgrade But that's it Common migration tactics include upgrading a slave which you can later promote to a master So there's no downtime there. In fact, you can run performance to make sure that the slave is performing potentially better than your master And if you're upgrading to Galera, you just add a node first get it to replicate Create your Galera cluster externally and then you know kill off the old replicants a Very other very other common question is is this stuff supported? Yes, it is supported You can buy support from every MySQL vendor except Oracle Maria DB supported for five years from the date of release and this is a commitment. We have for security as well as minor feature updates Many hosting companies are getting on the bandwagon and I think the next thing we need to work on is make a huge long list of who supports it And in what regions? But why a tree? For example announced that they love Maria DB seep table performance and made a press release some time ago as well I'm sitting at the sky SQL booth like I spent the whole day today talking about Maria DB So I'm surprised I'm still talking now and We are backed by the foundation just like the Drupal Foundation So any tuning tips and I'm saving the best for last You should use my SQL NDMS This is a new driver. You can you can just do a pack or install my SQL NDMS and this one does Replication load balancing as well as read write splitting. It's pretty cool. You should definitely check it out. Anybody here running that? No, you should definitely update to this if you're going to use gallery cluster as well highly recommended that you use this Use extra DB, please please if you are still on my ISM ditch it transactions are the way to go We ship a more sane my dot CNF So by default when you install Maria DB, it should be ready for you to run but always configure your my dot CNF It's amazing how many times we find people never having configured my dot CNF Now there are tools available for you to help help Configure the skyscale.com or tools at prokona.com Both will allow you to configure a my dot CNF based on the kind of load you have Use your application Benefit from binary group log commit. So use your application seriously There are a couple of scripts tuning primer and mysql tuner They are pretty okay tools for you to try if you don't know how to configure your my dot CNF or other options even operating system options Prokona toolkit is indispensable Downloaded if you use mysql seriously, you must at least use PT mysql summary or PT query digest to see what's wrong Why are your queries doing what they're doing? PT mysql summary used to be called mysql report There is a fairly old documents circulating from I think tag one consulting on how you could You know configure mysql that document needs to be updated because lots of these tools have changed so I guess I'm gonna have many blog posts from this particular talk I'm going to expand on the on this and we have lots of knowledge inside the knowledge base as well We have a whole bunch of resources if you have a bug MariaDB.org slash Jira as a good place If you want to talk about MariaDB that's Maria MariaDB discuss or Maria developers We're on Facebook. So like us We're on Twitter. You can follow us. You can plus us on Google plus You want to chat with any of us just jump on hash Maria on free node? We're like always there because we kind of work in a distributed environment. I'm based in Malaysia sometimes and We have people all around the world. So you can always find answers to your your questions and the knowledge base It's indispensable. I suggest always reading it if you have a question go there because chances are it's already been answered And I have time for questions any questions. Oh There's Mike Do you have any plans to put MariaDB in like really simple one-click graphical installers like MAMP or WAMP to help increase adoption right We have a one-click installer for Windows That we also ship with that also comes with Heidi SQL. So you got a nice GUI as well It's not a one-click install. I mean you have to press next next next But we also have but but we do we do want to do that if anybody's interested in WAMP or ZAMP or something we would love to work with them or they should switch over to us But we don't have direct plans to actually have such a distribution because we figure most of our efforts should be focused on the Unix's and these cloud deployment platforms, which is where Not the developers may not be using it, but that's where the deployments happen. So But it might be something that even you would like to work on I'm incredibly lazy and just like for Windows or Macintosh for MAMP. Okay, man. Okay. We'll look into that Brew install MariaDB We client tools like PHP my admin or workbench So PHP my admin works with MariaDB if you go to PHP my admins website It tells you my SQL MariaDB or drizzle. So yes, we fully work with PHP my admin We highly recommend you use it they've improved the interface now to include things like monitoring and PHP my admin makes use of our GIS extensions because it also does do Display geospatial information now as well. So PHP my admin for all intents and purposes works really well Do we have an alternative to workbench workbench also works with this stuff? however, we ship Heidi SQL inside of the Windows version and I believe sky SQL sells SQL Yogg They call it some fangled something. What is it called? Sky SQL something So SQL Yogg is another option. So no, we don't we don't plan to make any GUI tools ourselves from the server side But they are definitely Alternatives and if you are using a Mac there is like a really beautiful cocoa application available That looks like a potentially Windows machine or okay Windows we ship Heidi SQL So we download MariaDB it comes with Heidi SQL by default and Heidi SQL supports all our extensions Including virtual columns dynamic columns and so on so download it and give it a go because Heidi SQL is actually pretty good Yes Does MariaDB do anything to improve the performance of temporary files that are being written to disk? Right, so we we definitely improve temporary file performance because we don't use my ISM internally for temporary tables We use aria aria as something like a eight times performance improvement over my ISM And it gives you the one added benefit of it's being crash safe So in the event that your my SQL server crashes my ISM will take a long time to recover that even though it's temporary data But aria doesn't aria will be instant recovery So yes, we have we have worked on that as well I have a few members on my team that work with aquea dev desktop. Are you working with them at all to integrate Maria into their releases as well, I Believe that there are some talks from sky SQL and aquea to maybe integrate more aquea dev desk But we Dev desktop. Yeah, we had we had team MariaDB have not worked on this per se But we would definitely like to have more partnerships and integrations. So it's something that we'll do as a to-do list Any other questions? Oh Thank you for the talk. It'd be good to see more documentation on gallery Gallera cluster sorry some of the config files don't really launch The cluster right away and there's a lot of errors. So okay So kb.askmonti.org has some documentation, but yeah, I agree We're definitely short on documentation for that and MHA even like I'm kind of embarrassed I have to recommend you to look at my slide deck for MHA, but Yeah, we need to definitely improve documentation and it's something we will definitely work on and if you if you find That there is a problem with the file. Why don't you just go to the knowledge base and open a question and say? Hey, this this file doesn't work or just report a bug because Yeah, I think we're we can do pretty well with writing code and Stuff, but it seems like we're pretty bad with writing documentation and the foundation has kind of improved that because we have hired documentation writer at the foundation We I sincerely hope it will improve our documentation But I will also take that as an action item to point him to improving it. Thank you Any other questions? All right, oh you have a question Yes, I believe we had some problems with the Drupal website like a couple of days ago But it will be it'll be on slide share as well as on the website. So I upload everything to slide share the username is By but Yeah, I I'm standing right between you and your beer. There's apparently beer at the expo hall any other questions All right. Well, thank you very much for listening