 Hello, my name is Sergey Petrounia. I'm a member of MariaDB project, and this talk is about MariaDB and the extra features that make it a better branch of MySQL. Well, the first, let's have a slide about MariaDB, what it is. So we position ourselves as a branch of MySQL with extra features. When we say branch, not a fork, we mean that we still periodically merge with MySQL, so we haven't divulged it with them. Unlike Drizl, which is a fork, which don't merge from MySQL anymore, we still remain compatible with MySQL. It's possible to merge changes from MySQL to MariaDB with some effort. We have only GPL, offer only GPL on the license, unlike Sun MySQL, which offers both GPL and commercial license. We strive to have more open development. We have all source code in public repository in Louchpad. We simply don't have any private trees. We try to be more open towards external contributions, and our current state is that MariaDB 5.1.32, the number is based on MySQL release, which was merged into it, was made stable release on February 1st. Just avoid confusion, Maria is also name for the storage engine, one of the storage engines that ships inside MariaDB. MariaDB is the name for the whole database system. So the extra features that we have at the moment in MariaDB 5.1 and our stable release are, well, in the order of their relative importance or impact, it's approximate. Of course, first is the extra DB storage engine, then they have extended statistics in the slow query log. Microsecond precision shown in process list. We've got table illumination optimization. We've got PBXT storage engine. We've got Maria storage engine, as I've already mentioned, and we've got thread pull support. We've got support for creation collisions. And last, and it was added, last is the federated extra storage engine. This talk will be mainly dedicated to overview of what these features are and how they can be useful. Before we go to that, not all of the features are developed by MariaDB crew. Basically, this is a slide that highlights the colors that for extra DB, PBXT and federated X, X basically for a storage engine, we have an upstream which we periodically merge from. And the developers of these storage engines are not members of MariaDB team, but we collaborate with them on getting their code to work with ours. Slow query log and microsecond precision in process list, we'll base it on a third party patch for MySQL, which has never made it into, which wasn't merged into MySQL mainline. So we took it, improved it and merged it into MariaDB. Table elimination optimization was developed at MariaDB from scratch, so you won't find it anywhere except MariaDB. And then Maria storage engine, thread pull support and creation collision are the features that were developed at Sun, partly by current MariaDB members, partly by those who remain Sun-wise column, please, but they have never been released in this table release, but they have been published, their source code has been published, so we was able to take it and integrate it into MariaDB with some fixes and certain improvements. So if we go to the first feature, which is the extra DB storage engine, and for extra DB storage engine, we'll have only a couple of slides dedicated to it because it has a lot of improvements and we could have a talk only about extra DB. So be quite brief here. Basically this is a patched version of the DB plugin, where in the DB plugin is a version of the DB which was produced by Oracle with more features than the regular in the DB. And extra DB was produced by a consulting company called Percona. Those are the people who are behind the MySQLPerformanceBlog.com website, which publishes a lot of articles on MySQL performance. And within extra DB, the primary thing is that they have performance improvements for multi-CPU systems similar to MySQL 5.4. I have to remind that MySQL 5.4 have never been released. It has been available as a preview. And while extra DB has these features in a released version and we have it in MariaDB, they provide more diagnostic information. And the whole array of miscellaneous features that the DBA offer highly loaded and highly concurrent that database might find useful like ability to save and preload back buffer pool. Buffer pool is sort of in the DB's disk cache. So you can start with the warm cache and not have any effects of starting with the cold cache. They have index statistics collection fixes and many more. Unfortunately, I can't describe them in detail because it will just take too long. If you are running a website in the DB and experience performance issues, you really should bookmark the MySQL performance block website and check out their recommendations and knowledge that they post there. So now let's move to more easier and comprehensible to understand features like slow, query log and microsecond precision to process list. Slow, query log is a standard is a mask feature that has been locked long ago in MySQL. Basically it could specify slow query log file and MySQL would print their text files to print the queries which took more than certain given amount of time to execute. Extended statistics add log slow verbosity option, which at the moment has only one valid value query plan and it adds a filtering where you can get names. You could filter administrative queries like alter table, create drop and other detail operations. You could filter select queries by the fact whether they do file sort, whether they do file sort with putting data on disk, whether they run full joins, full scans. And you could also fit the query by the fact whether they fit into query, whether they hit the query cache or miss and you could filter whether the queries that use temporary tables. The log slow rate limit shows how allows to print not every slow query which can be quite a lot but every slow query. So you can have a sample. Basically before in MySQL a typical contents of slow query log was likely above and likely below you see that edit items highlighted in red. It prints thread ID, it prints schema and then you get the important part where you see that the query did the full scan, where did the full join, where it used temporary tables and so forth. It has become especially useful when query execution plan depends on its parameters. So it might be the case where you get query to run slow for some parameters but then you run it for the other so it runs fast. So it gives more observability. Microsecond precision in process list it's another patch by Prokona and the idea that it displays milliseconds with fraction in show process list and that's useful for analyzing load of small queries. When one does a select from information schema dot process list in MySQL, one gets only number of seconds in time column while in MariaDB there is also time milliseconds which shows time in milliseconds and that's useful when you if you got queries which run less than a second and you need to discriminate and find out which ones of them take more than the others. They left two patches have been used as a patches for MySQL so they're reasonably safe to use because there has been some field testing. Then I move to the feature that is unique to MariaDB it's stable elimination optimization. This is an optimization for queries over highly normalized data. It's present under its name table nation and big databases like it's present in Oracle and SQL server. As far as I'm informed it's not present in PostgreSQL. The basic idea is that it detects outer joints which have unused inner sides and then delet those inner sides. Basically when you have an outer join of two tables and such that the select list does not include the inner table and the where condition does not refer to inner table either then that means that and also the condition in don't expression is such that we see that it has table two dot primary key equals something from table one. Basically that means that for each record of table one there can be not more than one match or that condition gives us that. And since it's outer join if there are no matches we'll get an all complemented record and that means that there will be always one match. It will be either another record or not. So and in that case it is apparent that we don't really care whether table two had any matches and if they had what they were so we can remove table two. So far this has looked like a relational algebra exercise. Now let's see how that is practically useful. Suppose we have a data and we want to store it in a highly normalized way. For instance, if you get a list of actors with names, dates of births and their ratings and normally the sort of default approach is to store just create a table with three columns name, date and rating. And the problem with that is with all denormalized data storage is that you can't easily add attribute because it requires a long alter table. It's difficult to have attributes which apply only to a few, only to a small fraction of actors because the dataset grows bigger. If most of your actors are unrated you get nulls in many columns and that will store it quite inefficiently in a row-based storage. So the standard approach to that is to create three tables instead, create a base table with AC, let's call here anchor which will denote anchors and then create a table for names, create a table for dates of births and create table for ratings. Over here the table for ratings can also change, we assume that ratings also change over time so we've got a from date column which from date column so we can store the history. With this scheme we are much more flexible about our data storage, we can add or remove attributes as we please, we can have attributes that other users don't care about but when we want to query things we would like to, we would like to actually, if you want to get just a list of actors with their names and birth dates and current ratings we have to write this huge outer join. Here the colors denote access to attributes so for the name we write in left join with the name and then for the birth date we join with birth date and for rating we not only do a join we also select from all possible ratings the one that is the last one and currently valid. That's what the sub query does here. From date equals maximum date for this actor. So apparently we don't want to write this selects all the time when we want to get actor and his name so we just create a view. After we have created the view we can access it as written below, we just access it as a regular table, we just select for example rating from actors where his name is some name and it looks like a table. The problem is that it is a join inside it is a join so every time you access this MySQL would run the underlying join and then just, and then just, and that would be an excessive. So here is a demonstration how table elimination works. The first query is about selecting rating and birth date by name. So basically it uses all three attributes and you get what you would typically, what you would always get for, in MySQL you would get all four table success. The second query is more interesting. It doesn't care about dates of birth. So when you run it in explain in MariaDB you will see that date of birth table is gone from explain so we don't access it. That shows that if you don't, you can, this scheme allows one to easily add attributes which other users don't care about because they will be removed by the optimizer. That date of birth will have no overhead for those who don't, who do not need it. The third query is another example here we don't care about ratings and you see that the ratings table is gone from the explain plan. And so it wouldn't be accessed so we can have totally optional attributes. If you want to sum it up, basically with table elimination you can do normalization on optional historic data which will cause you to run, to want to execute auto joins and you will be able to create and normalize it, denormalize it view using left join constructs and use this view and you will get for it something like index on the scans where you, if you don't care about columns you don't need it. Tables here, service indexes. That's what table elimination optimization does and that's feature that's unique to MariaDB. So what do we got next? Next, the next feature is a prime basis XT storage engine and prime basis XT storage engine is a third party development that's developed by the company called prime base technologies. We only merge it to MariaDB and it is a transactional is it compliant and multi-version concurrency control based storage engine. It is developed by, so how is it different from all other engines? The design choices are that it uses certain write once methodology so it stores its date in the, they call it D log permanently. When they get new data they just store it in the log and then the table just keeps referring to the place in the log where the data was originally stored. Unlike other transaction engine which would first write to certain log then update the place where the data is really to be stored. They do disk-based MVCC basically means that they store, they're able to store different versions of the records on disk so they're not limited by, so the size of all uncommitted data is not limited by the size of memory like it is the case for other engines. This gives them fairly fast commit because they're all of their data. When they need to do a commit they have all of their data on disk already and they actually don't care about the versions, the wrong versions because they are collected afterwards by a certain garbage collection process. They don't do any in-place updates. Since they are a log base they are always append new data. They don't update anything in place. They just write a new version and change pointers. And then rely on that it is, the unused data is garbage collected at some point. All this can be both strength and weakness. The strength that they commit fast, the weakness is that we rely on how quick the garbage collector will be and whether it become critical that the data file grows to be all of the versions of the data. They don't have an undo log. They recover just URLs for what based on the log or the forward log. They're similar to my ISOM and in the regard that they store every table in a separate file and the set of its indexes in yet another separate file. So you can move the tables around just like in my ISOM. You can put them on different storage. Index. If you look more at Pbxt, storage engine design. So it is, indexes don't, I've sort of already mentioned that indexes do not need to be flushed on transaction commit because the changes are made by the unneeded versions are removed by garbage collector threads. Indexes are updated in background by the garbage collector thread. They got operation IDs. That means that modifications normally require simultaneous update of cash and transaction log. I'm not sure what the last means. I'm not that fluent with Pbxt. Okay, so they basically batch the updates. When they don't do updates on the fly, they write the updates to the log and then there is a write or thread which collects all of the updates, groups them together. Actually it sorts them which gets the updates to the same locations grouped and writes them out. So this gives them a reduced number of write operations and they write in sorted order. So what does this mean for the end user? Why would one want to use the Pbxt? Well, first, according to the benchmarks, they are an emerging competitor to NDB in general high performance LTP. They show benchmarks which show that they are better than NDB on nearly every MySQL gathering. They are new kid on the block so they sort of have a user-friendly developer model where if you ask for something, you can make more impact than with NDB. According to what classical Pbxt actually takes, Pbxt better takes advantage of properties of solid state drives because they don't, they write first, they do fewer writes, they do random reads and that is exactly the IO pattern that Pbxt so that solid state drive is good at. They also group updates so that they write at once and since they write to their log changes together and sequentially that is exactly what solid state disk is good at as opposed to rewriting a block every time. Their upcoming features are, they'll have in-memory tables. They already have one in actually Pbxt and three that merge it into Maria yet and they will have an engine replication for chase it ups. At this point, I have to point out to effective SSD for a database session which has already concluded but I guess their slides will be published. Okay, besides Pbxt, we've got Maria storage engine and Maria storage engine is based initially on MyISOM code so it's very much MyISOM like it got full text, it got GIS indexes. The goal for Maria was to, sorry this is my scroll, to have a transactional MyISOM but at this point it is only crash safe MyISOM where you can set the table to be crash safe and unlike MyISOM it wouldn't require any repair operations and will roll back or roll forward all changes if it crashes in the middle but it's not fully transactional yet. It's primary use at the moment is for a scroll runtime temporary tables instead of MyISOM and that means that queries that need to use temporary table like group by queries, union, distinct, count distinct and such basically everything that shows using temporary in the explain plan. They would not touch the disk until Maria's buffers are overflowed so if you, for example, need to write a query which does a group by and also selects blob or does a distinct with Maria tables it wouldn't hit the disk unless it exhausts all memory and it will have to do so. Which is, which gives some improvement for this queries over regular MySQL where it will touch the disk because it will have to write to MyISOM. Another feature which is a bit controversial is the thread pooling support. The traditional MySQL model is that there is one process and one MySQL deep process which uses a separate client connection, a separate thread for each client connection so the number of connected client means that the number of existing thread is equal to the number of existing threads and the thread pooling model we aim to serve and queries with mthreads where m is fixed, it is activated as follows you specify thread handling one thread per connection and then you specify thread pool size and that is a store and you can use and that is how many threads will be used to serve all of the client connections. There is some problem with that, it is one can connect with 20 clients, start 20 long queries and totally block the server because we will be unable to, because any other incoming requests will be just waiting in the queue and for these 20 queries to compete. For that we've got an extra port parameter and extra max connections. It's basically another port that MySQL will listen on which you can allow access only to administrative user too and it will be able to log in and kill the query and kill the fencing queries. Typically one would expect that thread pooling would, to a great extent solve the problem of performance degradation with too many connections. Unfortunately it is not the case yet. For example queries that run for a long time are great for IO or LOX or any other reason still occupy the thread pool. So if you manage to get a dead lock inside in the DB such that the one that causes the threads to wait till time out, they will sit and wait till time out, so the threads in the pool will be busy waiting. And another problem is that the experiments have shown that thread pooling can be slower than the traditional model for in high-conquerance scenario. That's a problem because it's kind of exactly one of the things that we, it was expected to solve. However we decided to put the thread pooling in anyway because it allows one to, oops. We decided to include the thread and the release thread pooling anyway because we've seen a real world user case where the application was making a lot of connections while not having a lot of actually load. It was having purposes between connections because it was doing some other processing. And in that case thread pool allowed to have lots of active connections, you serve it without lots of threads, which saves memory when you have few threads. So if that's the user, your case and you might benefit from thread pooling at this point. But I would not recommend to use thread pooling right now if you are after solving the problem of performance degradation with too many clients. The next feature is creation, collections for Unicode. Basically, MySQL has a creation, collections, case insensitive for latent and CP1250 charts. And when you use default Unicode collection, you get the case sensitive creation collection due to native sorting. But what you cannot do with MySQL at the moment is that if you got a creation, if you want to have creation, case insensitive sorting and store Unicode data at the same time, that means that you are unable to do so. You either get creation, case insensitive, sonic with Latin charts set, or you get Unicode with case sensitive sorting. So Alexander Barkov, the MySQL Charset Specialist produced the patch, but somehow it wasn't included in MySQL, it was not released. So we took it, integrated into MariaDB, released it and here you have it. We've got creation for UTF-8 and UKS-2, which is basically the two charts set that you need for Unicode. And the last feature is federated extort engine. It was added shortly before the release, so we might rely on its author for the stability of this. This feature, the good thing is that you don't use it, it is unlikely to have any impact. It is developed, this is an engine which is developed by the original author of federated engine, Patrick Galberth. It is an improved version of that engine. It includes bug fixes and transaction support. As I've already said, this engine has not received much testing, so I'll do that in release. I'll, if something doesn't work, you'll have to blame mostly Patrick, not us. We can only say that if you don't use it, you wouldn't be affected. Okay, so here's a list of the features again and that's what we have to offer in the release. So at our stand, we are frequently asked how can one actually upgrade to MariaDB from MySQL and how much effort does it require? Here is the whole slide dedicated to compatibility. MariaDB and MySQL are compatible with client libraries. Everything that's linked with MySQL will work against MariaDB library. For this reason, MariaDB library is called LibMySQL still. We're compatible with clients, so our protocol, it means that you can use any MySQL client application to connect to MariaDB server and vice versa. If you go to MariaDB client, which is at the moment the same as MySQL, you can connect back. We are compatible with regards to command line tool names, locations, syntax of arguments, of utilities and so forth. And that means that MariaDB by default has occupies the same part, socket names as MySQL, so you can't install both at the same time. All of our packaging scripts at the moment will not refuse to install if you have MySQL, so you will have to remove MySQL first and then install MariaDB. And then MariaDB is supposed to just pick up the data, all of the data files and start and working in the same way like MySQL did only better with extra features. That's what, we have a fully compatible SQL dialect yet, there is no difference. We haven't tested any server plugins. So if you got any UDF functions or any plugin storage engines, you need to exercise caution because we're not gonna guarantee that it works. I'm not sure whether, I'm not aware of any explicit compatibility breakages though, but that's something that hasn't been checked. We are compatible with the replication master slave, so you can attach MariaDB slave to MySQL master in all cases and then hence bring up another host running MariaDB and check and experiment on that. You can run in reverse direction also assuming that you don't use, that MariaDB server do not use features that are not in MySQL. For example, if you are using PBXT tables and apparently that wouldn't be able to replicate to MySQL because MySQL has no PBXT or if you're using creation collations. It is the same with the data directory. You can just stop the MySQL server and start MariaDB and it will work without need to any data conversions or upgrade or export import procedure. But it will work also backwards as long as MariaDB server did not use any features that are not present in MySQL. So trying MariaDB should be extremely easy. Just stop your MySQL server, put, start a MariaDB server with the same parameters instead and it should work out of the box. Our further directions are first, the next upcoming release is MariaDB 5.2 and we've got plans fixed for it. Basically we intend to include for that only small and safe features. At the moment we have their virtual columns patch which is basically a contribution by Andrey Zhakov. Basically a table can have columns which are calculated from other columns as a function. The important limitation here is that you cannot have indexes on these columns yet but you can use them in anywhere where columns. Sorry? Well, I don't think you can have indexes at the moment. There is an option to have them higher persistent or not but I think it's not possible to have index on them. That's on our, having indexes is on our to-do, it's a much wanted feature because one would have essentially functional indexes when we get that, if you get a, but that's not the case yet, unfortunately. The next is a well-probably known user starts version two patch which shows statistics per table and per index of how many times, basically per table and per index counters, how many records have been read from certain table, from certain index or by certain users. What do we have? Yeah, my scope in log got minus minus right database so you can, if you want to change from, if you have an application running on one database and then you want to get the minus code in log output for it and then import data into another database that will work and it will work for a raw-based application also. Expected updates are first updated upstream components, whatever extra DBPBXT and federated managed to release. We're also working on partitioning it MyISOM key cache. Basically, key cache is partitioned by hash function and that allows to reduce concurrency and contention. So that allows to reduce contention in certain high concurrency scenario. I'm not able to show any figures for that yet. And we're working on fixes which will provide better observability for our basic replication. Basically, one feature that if you're running raw-based you will be able to see which statement causes this raw-based event. There will be stored in the binary log. It will be optional so it will be possible to analyze binary log and see which statement causes every change. Beyond MariaDB, which is a smaller reason, we don't have any plans set. It depends on what will be ordered and contributed first because we accept contributions. Features we are working on right now are basically, we took features from MySQL 6.0 which we think were back porting and ported it into five, two basic tree and that would be the next release. So we'll port batched key access feature and fix no issues with it. And we'll port subquery optimizations. We'll back port them from 6.0 and back port those that were targeted from MySQL 6.0 and also have a plan to add additional improvements. So this is what, at the moment, our idea of what will we have beyond five, two somewhere. Well, I don't have any dates but my expectation, my personal expectation is that it will be, we'll have a beta of sometime this year of these features. So that's all what we have, it concludes the talk and if you have any questions, I'm here to answer. If you didn't catch it from the start, we've got two leaflets, one at least stable features and another which shows that how MariaDB team is different from MySQL here. Thank you.