My MySQL tips valid-rss-rogers


Why this article?

First of all because I was having fun in digging in the code.

Then I was reading a lot about the improvements we will have in MySQL 5.6, and of some already present in 5.5.

Most of them are well cover by people for sure more expert then me, so I read and read, but after a while I start to be also curious, and I start to read the code, and do tests.

I start to do comparison between versions, like 5.1 - 5.5. - 5.6

One of the things I was looking to was how the new Purge thread mechanism works and his implications.

I have to say that it seems working better then the previous versions, and the Dimitry blog (see reference) seems confirm that.

So again why the article? Because I think there are some traps here and there and I feel the need to write about them.

The worse behaviour is when using MySQL 5.5, and this is because in 5.5 we have an intermediate situation, where the purge is not fully rewrite as in 5.6, but also not bound to the main thread.


What is the history list?

MySQL uses (from Innodb 1.1 MySQL 5.5) 1 to 128 Rollback segments, each able to support 1023 transactions.

Each transaction is assigned to one of the rollback segments, and remains tied to that rollback segment for the duration.

This enhancement improves both scalability (higher number of concurrent transactions) and performance (less contention when different transactions access the rollback segments).

History list is tightly bound to undo log representing the number of Header Pages related to undo log segments, segments that are related to finalize transactions,commit or roll back.

That's it, History list represent the number of not yet flush segments in the undo log.


Old and New

Previously, the purge thread was directly controlled by the main thread in InnoDB causing serious side effects for description of which read (

The main change was to move out the purge thread and allow it to run it isolated, such that it will not impact other process.

The move was done in Innodb 1.1 (present in 5.1 and 5.5)

But that is not all, the new version of purge has a mechanism that allow it to be more or less aggressive, in relation to the increase or decrease of the History List length.

This option is enable in the 5.6 release and is taking the innodb_purge_threads as the "up to value" it can use to increase the number of threads for purging.


Different behaviour

What is quite obvious is that the behaviour of the new Purge mechanism, is quite different from the previous one, ie 5.0 (innoDB 1.0) or 5.1.

In the previous versions of InnoDB, we were educated to consider the History List something that should always be close to 0, not always but as soon as possible.

Frankly speaking that was always a little bit confuse, but as said the MANUAL, was suggesting in several place to keep it between reduced numbers:

I.e. (

"If you insert and delete rows in smallish batches at about the same rate in the table, the purge thread can start to lag behind and the table can grow bigger and bigger because of all the “dead” rows, making everything disk-bound and very slow In such a case, throttle new row operations, and allocate more resources to the purge thread by tuning the innodb_max_purge_lag system variable."


A classic scenario for such issue, is an application taking track of the activity on the network, that require to write huge number of small insert into the database.


From my tests I have seen an incredible number of entry in the history list in 5.5, that were not present in the previous InnoDB version, and that are not present again in 5.6.

The point is it could happen to have so many transactions, doing INSERT,UPDATE or DELETE that the History grows too much, and the un-flushed undo log as well. To prevent issues, we should tune the value of the innodb_max_purge_lag in order to allow InnoDB to complete the PURGE operations.


Innodb_max_purge_lag is the maximum number in history list we want to have, above which Innodb will start to apply an indiscriminate delay in pushing the operations.

the formula is quiet simple:

((purge_lag/innodb_max_purge_lag)×10)5 milliseconds.


Or following the code we have:


float ratio = (float) trx_sys->rseg_history_len/ srv_max_purge_lag;
((ratio - .5) * 10000);



If we have a History list of 1200

and we have set innodb_max_purge_lag to 1000

result should be:


((1200/1000)X10)-5= 7 ms delay for operation. following the manual
((1200/100) -5) * 10000 = 7000 <-- Microsecond following the code



All match and delay will be 7 ms.



Also the max limit in previous version for the delay was of 4295 seconds!!

While in 5.5/5.6 we see a different way of managing the max number of seconds:

if (delay > srv_max_purge_lag_delay) {
    delay = srv_max_purge_lag_delay;


Where srv_max_purge_lag_delay max value is 10 seconds.

So the max delay, the worse case will be 10 seconds.


In the past as said we were use to see the History list going up and down (relatively) fast, so the force delay was playing his role efficiently.

At the same time, we knew that all operations in the Main threads where slowed down, so the forced delay, was something we had to leave with, or worse things could happen, like the need to perform an emergency page flush from the buffer pool, to allow REDO to recover space.


But something has changed...

... in better obviously... but we must be careful.


Better because now the purge thread works independently, and that it could scale, pending undo flushes do not slow down the other operations.

Also in 5.6, MySQL could be more or less aggressive in relation to the History list to purge.


Those operation remain something we should  monitor and tune, for two main reasons:

- space taken by undo log segments is till an issue, and now that the number is increase, it could be even worse.

- Setting a wrong value for innodb_max_purge_lag could kill our performance.



Let us start digging a bit.

First of all History list and purge log thread are still very bad documented in the InnoDB Status Report.

In 5.5 we can count on the History list information, number of transaction purge has being up to,  then the number of purged record up to, finally in 5.6 we have the generic state.

Not too much here, better info like number or running threads, real segments used (1-128), number of Header Pages in the segments, and dimension (at least pages as Percona does) would be nice to have.


Undo log is currently stored inside the system tablespace, this means that IT WILL NOT BE POSSIBLE to shrink the size of the table space, once undo log have taken huge amount of space.

That's it, the 80% or more of the size of a system table space is because the undo log, when using innodb_file_per_table. and this was already true when InnoDB was using a single set of segments (1023), now that it can scale up to 130944, and that it supports better more transactions, the size on disk can explode.


Some numbers to have better understanding,

History list 359585
insert/s 35649.67
pages to flush in the undo log 429126
Means in the undo log a total size of ~ 6.7GB




Considering that normal block size is 4K in file system each page is 4 operation, we will have 1,716,504 operation, assuming that each page will be sequential, this means 3 ms for Seek+half rotation then, 1 ms transfer for the first operation then 2ms for the remaining will be 12ms for each page delete on disk.


Meaning 5149.512 seconds (85 minutes)  at 6.7 Mb/s given the partial random write, to flush the whole.


Obviously this number changes in respect of the available cache and available spindles.

Also more threads more capacity in write, less time, so the option innodb_purge_threads is more then welcome.


Setting the right value for innodb_max_purge_lag

In setting this value we must keep into consideration the following:

- the value is the number of head pages representing an undo log relation to a running/executed transaction.

- Purge can apply only to complete transaction

- delay apply to all write operation inside InnoDB


Nowadays is not uncommon to have high number in history list in 5.5, in this case "just" 359,585 head pages, is then very important to correctly balance the delay point of start with the real load, like transactions/sec and the kind of operations are ongoing on the server.


To clarify, the relevance also in case of reads, not only of writes, let me cite:


Running Very Long Transaction If you’re running very long transaction, be it even SELECT, Innodb will be unable to purge records for changes which are done after this transaction has started, in default REPEATABLE-READ isolation mode. This means very long transactions are very bad causing a lot of garbage to be accommodated in the database. It is not limited to undo slots. When we’re speaking about Long Transactions the time is a bad measure. Having transaction in read only database open for weeks does no harm, however if database has very high update rate, say 10K+ rows are modified every second even 5 minute transaction may be considered long as it will be enough to accumulate about 3 million of row changes.


(Peter Z)


But what can really harm your the system is the dealy define by the purge lag to improve the flushing.

Assume we define it to innodb_max_purge_lag=200,000, and we do have the number of pending flush as for the above 359585.

Doing calculation as for the previous formula


((359585/200000)X10)-5= 12.97925 ms delay for operation




Hey that's not too bad, I will delay only 12.97925 ms to operation/Insert to help the purge.

But what is not clear is what an operation is for the delay, or more correctly where do the delay really apply?


Ready? Are you sit comfortable?

2) row0mysql.c


Delays an INSERT, DELETE or UPDATE operation if the purge is lagging. */
if (srv_dml_needed_delay) {





The thread sleeps at least the time given in microseconds. */
ulint tm) /*!< in: time in microseconds */
#ifdef __WIN__
Sleep((DWORD) tm / 1000);
struct timeval t;
t.tv_sec = tm / 1000000;
t.tv_usec = tm % 1000000;
select(0, NULL, NULL, NULL, &t);



Do you get it?

delay is per ROW.


So assume you have a system checking connections status and traffic, collecting statistics every minute for your 100000 connected users, each usert generate at least 8 insert, plus a consolidation operation to get average 10 minutes each insert. Each insert per user taking, 0.002 second. All traffic manage by 300 threads.



100000 x 8 = 800000/300 = 2666 insert for transactions x 0.002ms each = 5.3sec to run all the operations.



Now what if we apply the delay given we have History list quite above as say before?


I have to sum the 12ms to the 0.002 which will give us 0.122 sec, which means 325 seconds (5.4 minutes) for each thread!!!!

Do not forget the consolidation process, who needs to run each 10 minutes.

So it has to process (1 reads for user per minute x 100000 users) x 10 minutes, split in 10 threads doing it by user id set, assuming each read per user will take 0.001 ms (given already in memory) and 0.002 ms for write.

Without delay we will have = 1,000,000 / 10 = 100,000 x 0.003 = 300sec (5 minutes) for thread.

With delay it will be for each operation 0.122 = 12200 seconds (203 minutes).


Last but not least the consolidation will collide with the inserts, causing possible increase of the delay because the REPEATBLE_READ, and another possible issue is... the consolidation will cause pages to remain in a dirty state for too long, possibly causing serious issue in the REDO log in case of need to free space.


I did push some number a little bit but not too much and only to make the scenario more clear.


Now just to remove some possible doubt:


Are you sure that it really push it by ROW?

let us create a working test.



CREATE TABLE `City_test2` (
`ID` int(11) NOT NULL DEFAULT '0',
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
`satisfaction_grade` char(50) DEFAULT NULL,
`previous_satgrade` varchar(50) DEFAULT NULL,
KEY `satisfaction_grade` (`satisfaction_grade`,`previous_satgrade`)
insert into City_test2 select * from city;
mysql> truncate  table City_test2;insert into City_test2 select * from city limit 1000;
Query OK, 0 rows affected (0.24 sec)
Query OK, 1000 rows affected (0.10 sec)
Records: 1000  Duplicates: 0  Warnings: 0




So running the query we see the History going up and down but never to 0 as before.

but changing the innodb_max_purge_lag and setting it to a lower value then the history say history is 137, set the lag to 100

in theory there should be the following delay as for instructions:

((purge_lag/innodb_max_purge_lag)×10)–5 milliseconds.

result should be


((137/100)X10)-5=8.7 ms





mysql> set global innodb_max_purge_lag=100;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate  table City_test2;insert into City_test2 select * from city limit 1000;
Query OK, 0 rows affected (0.10 sec)
Query OK, 1000 rows affected (7.40 sec)
Records: 1000  Duplicates: 0  Warnings: 0



Close enough from my point of view, and it increase at the increasing of the number of rows.

Changing :





Will change how the purge will work but not the delay, innodb_max_purge_lag is the only possible parameter to use, and is not enough.



The new mechanism for the purge is much more flexible, and capable to scale. The fact that is now separate from the main thread reduce a lot the negative effects.

Never the less the capacity that the undo log purge have now, has also possible risks, huge pending flushes means possible huge space on disk and/or huge delay.

The delay behaviour is not new and it was already present MySQL 5.0, but the new capacity bring it to a higher level of risk, specially in 5.5.

In 5.6 the purging thread is much more efficient and I was having really hard time to do get huge history list, but when I get it I had same behaviour.

Whenever we need to tune the purge lag settings, the value needs to be set not in relation to the history list, but in relation to the maximum acceptable delay in the inserts as cumulative value.





 MySQL Cluster Performance Tuning


In this session we will look at different tuning aspects of MySQL Cluster.

As well as going through performance tuning basics in MySQL Cluster, we will look closely at the new parameters and status variables of MySQL Cluster 7.2 to determine issues with e.g disk data performance and query (join) performance.

This was the last session I attend, and for me is alway a great pleasure to be at Johan presentations, for many reasons:

- he is probably the best cluster expert (in service delivery)

- he knows a lot of distinctiveness and insight that no one else knows


Speech taken by Johan "my Viking" Andersson


MySQL/Oracle has released a new version of MySQL cluster recently, and I had the opportunity to test a little bit but not really in depth as I would like.


But one of the aspect that I was looking for what the way how the "condition pushdown" was modified.


Johan confirm by tests (empiric is always more trustable then commercial announcements), the way it is working now.  NDBcluster returns the full result set, no sub set to MySQL and additional requests, so less roundtrip, and the relations by ID are resolved at local data node level.


Interesting the way it is also possible to partition data by Key associating that on the Data node, that would increase the locality of reference also if not implementing a real partition pruning.


Only negative note is that we still have the annoying problem with the MySQL SQL node/connection pool, it is still taking 1 slot out of the 253 available for each connection in the pool. This means that if I have 4 MySQL SQL nodes each allocating 10 connection in the pool, I will take out 40 connections from the 253, instead 4.


This cannot be a problem, but given I have done several MySQL cluster implementations, I can say that when MySQL is really used it needs to heave a huge number of Data nodes and MySQL nodes, resulting to a very high number of connections slot used.


Подобного рода сделка между двумя белыми "Песни скачать свеча"считалась бы крупным мошенничеством, преступлением.

При этом он то и дело повторял, что они совершенно не схожи характерами, хотя, по словам Римо, один зануда придерживается на этот счет иного мнения.

Он-то как раз нормальным себя числил, а посему хотел смыться от голубоволоски и как минимум доесть остывающий эскалоп.

Эти мелочи мы "Игра говорящий попугай"оставим императору Смиту.

Воздух, который я вдыхал, напоминал пар, вылетающий из клапана паровой машины.

Приблизив ткань к лицу, он внимательно разглядывал ее на свет.

Все ответы на твои вопросы содержатся именно в них.

Он-то понимал могущество информации, помноженной на силу.

Они побежали за кислородом, и вскоре появились парамедики в оранжевых куртках с переносным баллоном.

И влепил две пули в мертвое лицо Ильи.

Потом он тоже женится на кореянке, и вскоре все забудут, что на славном знамени Дома Синанджу было когда-то позорное белое пятно.

Вот он-то "Футбол. Подробный путеводитель"мне и "Модный декупаж: пошаговые мастер-классы для начинающих"нужен, переулок, называемый "Руслан и Людмила"обычно Закоулком Смерти.

Сперва "Мифы Первой мировой"мы подверглись нападению.

Тянемся через "Книга для бизнес-тренера: технологии и искусство"внезапно похолодевшую "Крестоносцы"Карту.

Но теперь, когда родился "X-Polyglossum English. Полный аудиокурс английского: лексика + грамматика (3CDmp3)"наследник.

Я пришел бросить "Собачья жизнь и другие рассказы"тебе вызов, "Мальдивы: Путеводитель"Транто.

В свете факелов "Летят перелетные птицы"он действительно напоминал Снежного Старика.

Так "скачать песню самый лучший"как я теперь видел возможность возникновения именно такой ситуации.

Вот если бы "скачать книги по оригами"еще нашлась пиццерия где-нибудь поблизости.

Затем он, казалось, погрузился "скачать книгу буря мечей"в глубокий сон без сновидений, чтобы наконец проснуться в скромно обставленной "аська для телефона нокиа скачать"комнате, на диване, обитом зеленым атласом.

Многие люди чувствуют "симс история робинзонов скачать"себя бессильными и обесчеловеченными, будто они колесики машины.

Признаться, "скачать антивирусы для смартфона"я очень рассчитывал на "скачать фильм в качестве"то, что королева отправится вовремя.

Затем дядя медленно развел "зайти в аську не скачивая"руки в стороны, и линии извивающейся черноты "руки вверх ай яй яй скачать"последовали за ним, растекаясь в обоих направлениях из того участка Хаоса, что был "скачать песню кавказскую"валуном, прокладывая длинную темную траншею, в коей я узрел парадокс.

Генерал Блейд "Убить Ланселота" приехала, чтобы познакомиться с нашей веселой "Бианки Рассказы и сказки" шайкой.

Исцеляющие бактерии размножались в нижней части "Необыкновенное варенье Маша и Медведь" здания, в старом римском бассейне, "Окно в Европу" при реконструкции которого нашли множество старинных монет "Голубой ангел" и других предметов римской эпохи.

Кстати, "Грибы 15 заготовок на зиму" я хотел бы услышать ваше мнение по поводу "Загадки про героев любимых сказок и мультиков с развивающимися заданиями" происходящего.

Я не хочу, чтобы "Экономика переходного периода" Джей оставил какие-нибудь следы.

С самого начала Адам и Ева были "Париж и Северная Франция" единственными людьми "Финансовый инжиринг" на Земле.

Амнет дважды отвергал эту честь.


Boost Your Replication Throughput with Parallel Apply, Prefetch, and Batching


Slave lag is the bane master/slave replication. This talk will explain why slave lag occurs and show you three important ways that Tungsten Replicator can banish it for MySQL slaves. Parallel apply uses multiple threads to execute slave transactions. Prefetch uses parallel threads to read ahead of the slave position and fetch pages that will be needed by the slave. Batching uses CSV files to load row updates in extremely large transactions that bypass SQL completely. We will explain each technique, show you how to use it, and provide performance numbers that illustrate the gain you can expect. We will round the talk out with a discussion of non-Tungsten tools that offer similar benefits. With these techniques in hand, you'll be well-prepared to attack any replication performance problem.

The talk taken by Robert Hodges with Stephane Giron, was as expected very interesting, and give to the audience a good insight abut how to implement Replicator efficiently.

I also think that at the current moment Continuent Replicator, is the only production ready solution that can be use for:

- Parallel replication by schema (or combination of them)

- Multi master one slave solution

- on process filtering and data processing

- Oracle to MySQL

- MySQL to oracle.


I enjoy it, and I have immediate plan to use the solution for solving current customer need.

I am in particular interested in the FILTER option, and to see how it can really become helpful when talking of data processing.


Finally a small note some times ago I publish a blog describing a dream about replication, my dream, and Rob was the only one of the mega-expert in the fields, who honors me with an answer and a good explanation why it could not work, at the current state of arts.


My blog

Rob clear and really appreciated explanation

Using and benchmarking Galera in different architectures


What I was interested most during the second day was again, synchronous replication and Replication solutions provide from Continuent.

The first I attend in the day was the Galera one, done Henrik and Alexey.

The presentation was going to talk about:

"We will present results from benchmarking a MySQL Galera cluster under various workloads and also compare them to how other MySQL high-availability approaches perform. We will also go through the different ways you can setup Galera, some of its architectures are unique among MySQL clustering solutions.

* MySQL Galera

** Synchronous multi-master clustering, what does it mean?

** Load balancing and other options

** WAN replication

** How split brain is handled

** How split brain is handled in WAN replication

* How does it perform?

** In memory workload

** Scale-out for writes - how is it possible?

** Disk bound workload

** WAN replication

** Parallel slave threads

** Allowing slave to replicate (commit) out-of-order


I know how passionate is Henrik when talking about Galera and I partially shares the feeling. I said partially because I am still not fully convinced about the numbers, but I am working on that.

Anyhow, a side the part related to bench marching, I have found interesting the combination of blocks and element for the HA solution.

Including redundant load balancer and use MySQL JDBC with Galera is a simple but efficient way to provide HA.

Also important we can finally drop the DRBD solution that has being for too long the only syncronous solution for MySQL. DRBD was forcing to have one PRIMARY (RW) node, and one SECONDARY completely useless.

I have also appreciated the honesty from Alexey about scaling.

Galera will not scale to infinite as some foul state, but it could have a decent number of nodes.

Now the limit of it is obviously to discover and calibrate against the real load pushed against the nodes, it cannot be define as an absolute abstract limit.

Interesting also how the Galera team is managing by "quorum" the server synchronization. In short having 3 nodes if one will not be able to access the other two but still get writes (split brain), at the moment of re-union, the other two will take over by "quorum".

Obvious and immediate problem is in case of having 3 datacenter one with 6 nodes and the others with 2 nodes each. If the DC with 6 nodes gets disconnected, the valid data will be in the 2 remaining data centre, but at the moment of reunion, the DC with 6 nodes will take the majority, and all data set will become invalid.

Alexey is working on a way to calculate the "weight" by proximity to fix this issue.

Honestly, I am not sure that Galera is production ready, but is for sure the most interesting and easy solution for simple write scale.

Reference for Galera at

Key Notes

It was simply amazing for me as ex-MySQL AB to be at the conference today.
I was really emoted seeing so much people most of the ones I know, all together again.
The spirit was again the right one, with the will to say WE ARE HERE!
Impressive, and I am happy to say once more, “I was there”.
I am not going to comment the keynote speeches, but want to share the Baron message.
We are here to share, and help each other to make better, help each other to go beyond our current limit.
The spirit was the right one, the people there probably the most smart in the field, so why not.
I have only one world AMAZING.

Topic: Measuring Scalability and Performance With TCP

What if you had all the data you needed to measure system performance
and scalability at any tier, discover performance and stability
problems before they happen, and plan for capacity and performance by
modeling the system's behavior at greater load than you currently have?
Now it is as easy as running tcpdump and processing the result with a
tool. In this two-part talk you will first learn how to do black-box
performance analysis to discover hidden problems in your systems. In
the second part you will learn about mathematical performance and
scalability models, how the inputs can be computed from TCP packet
headers, and how to derive and interpret the results with free tools
from Percona Toolkit.



Good Talk, as we are now used to get from Baron, also if the topic was touched on the Percona Live event, Baron had review and refine the slides, which are now much more clear.
The proposed method for the quick analysis of the performance using TCP dump is simple and efficient.
Honesty we do use it already but Baron add the scientific notations that makes an empiric measurement more objective.
Specially in regards to the immediate issue identification and the concurrency calculation. On this specific topic I still need to digest/elaborate.
Like the formula for the concurrency:

GOOD reading:
Neil J. Gunther's book
? Guerrilla Capacity Planning

Topic: Hibernate and Connector/J Tuning

Many Java developers using MySQL as a data backend rely on Hibernate to bridge their OO designs with the relational database world.
This talk will review Hibernate and some of it's related projects, with a focus on performance.
We will also cover performance related considerations about Connector/J, discussing settings and usage scenarios that will be useful even for Java developers not using Hibernate.


I did attend this talk, hoping in something more and less.
More focus on Hibernate possible problems, that we find every day because customers have no idea how to use Hibernate.
Less because it was going too much in details of few Select, and was too fast in describing the solutions.
Anyhow, given my huge background in programming, I was not really enlighten by the information, and was able to follow the flow the information, which are base on good sense in using the standard feature and definitions in Hibernate, regarding the Lazy load of the collections, and the way SELECT … JOIN(s) needs to be done.
Finally a good review of what the MySQL JDBC can really do, which is not common given the most of the user just

Topic:Build simple and complex replication clusters with Tungsten Replicator

Tired of the intricacies of circular replication? Dreaming of real multiple masters solutions for MySQL replication?
Dream no more. Tungsten Replicator, a free and open source replacement for MySQL replication,
can build clusters of asynchronous nodes in a matter of minutes.
This workshop will explain the basics of Tungsten Replicator, and it will show how to start your multiple master cluster in a few minutes.
There will be examples of several topologies: from the simplest bi-directional replication to the ambitious all-to-all (every node is a master),
fan-in (multiple masters to a single slave), the star (a central hub connected to several bi-directional masters).


Mysql 5.6 is going to be GA soon, probably at the end of September, by then most product that use customize replication solutions will be obsolete, but not the replicator.
Continuent has develop a good solution for the multi master/multi master single slave solution that will remain valid in the time.
Also replicator offers MySQL -> Oracle replication, and Oracle -> MySQL replication.
It is going to be the perfect solution for many customers that will need to have scalable replication solution, and/or relation with Oracle databases.
The parallel replication is and will remain by schema, also no real mechanism to guarantee the data integrity between masters/slave given the checksum will be calculated on the command and not on the data.
Installation was facilitated a lot with the replicator installer.
Last but not least the product has already in place the possibility to support “FILTERS” develop in Java or JavaScript, this will allow the implementation of possible DATA transformation at replication level, which is a very important factor.
I was already discussing how this solution could solve several issue for some of our customers.

Topic:MySQL Optimizer Standoff MySQL 5.6 and MariaDB 5.3

Both MySQL 5.6 and MariaDB 5.3 introduced advanced game changing optimizer features.
In this presentation we will look in details and comparison on these changes as well as perform benchmarks to show which version is able to handle complex queries better.
If you're working with application using complex queries with MySQL this presentation is for you.


This speech was some how a little bit strange.
From one side Peter presenting his results on the evolution on the optimizer, on the other side few of developers from MariaDB team discussing most of the result.
The most important point is and remain that the MySQL Optimizer, one of the most important element of the MySQL DB platform, is finally revisited from both sides MariaDB and Oracle MySQL.
Optimizer was revisited in full to be included in the MySQL 6.0. This MySQL version was never released, as consequences all the improvements done so far on the Optimizer, where forgotten and leave aside the release delivery.
Optimizer is the core of any DB platform, it mainly decide how to physically access the data, reading the SQL statements, and translating it in to action plan against Indexes, and table(s) reads. The optimizer goes for lower cost not for execution time, to do so the optimizer use statistics, if a DBA do not collect accurate statistics the optimizer will not be able to identify an efficient action plan. Is a fact that changes to the optimizer are always scaring, giving they can overturn successful SQL statements in very bad SQL statement.
The MySQL optimizer still has serious limitations like not using prepare statement to execute the queries, such that any query will invoke/involve the optimizer.
From the many improvements done both side, Maria DB and MySQL 5.6 are very much more efficient then 5.5, of an order of 5.5. 900 sec 5.6 maria 180 sec.
Not only in many cases MariaDB is much more efficient then MySQL 5.6
Speaker: Peter Zaitsev

Latest conferences


We have 145 guests and no members online