Pythian Group

A calm week this time,
I was really busy on daily work, but had some spare time that I have use to do simple implementation and tests.

My work was related to Tablespace management in 5.6 and Table partition EXCHANGE, you can read the articles here .

I have also start to dig a little bit more in details on Mongo architecture, given the need to have it correctly review and implemented in parallel with MySQL installations.

As news review I was more interested in a couple of articles, as for below:

Interesting article from Vadim about SSD, more a suggested reading then able to comment.
Read the rest of this entry . . .

Why this article?

First of all because I had fun 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.
Read the rest of this entry . . .

Following a brief list of what I have found more interesting during the last two weeks.

Up to now, April has being a great month for MySQL.

MySQL Conference  – Percona conference 2012

The Percona MySQL 2012 conference, has seen the MySQL community, interact as it was doing many years ago,

re-creating the dynamic and creative environment that allow MySQL to become the most adopted open source database.
Read the rest of this entry . . .

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, two reasons from many:

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

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

————————————————————————–
Read the rest of this entry . . .

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.
Read the rest of this entry . . .

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.
Read the rest of this entry . . .

Migrating to MySQL might seem attractive from a cost perspective, but is your application really a good candidate for this?
What will you need to change?
What features of your current database do you rely on, and which features in MySQL might be a good replacement or substitute?
What is the likely cost and effort of a typical migration project?

We will go through several aspects like:
1) Why use MySQL,
2) What are the most common cases,
3) What kind of reporting is useful to produce for good migration planning,
4) What needs to be done to reduce the risk of failures, and
5) Last but not least, what are the similarities and the differences we should take into consideration.
Personal experience and real world cases will be discussed during the presentation.

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.

Speaker:Baron

Comment:

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.

Comment:

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).

Comment:

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.

Comment:

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

I am reading the announcement about the Percona MySQL event in place of the O’Reilly.
What I see and what I feel is that the whole MySQL community, including Oracle, Percona, SkySQL,MariaDB and any other company doing business with MySQL have lost a good opportunity (so far).

For many years we have seen and hear comments about having this event more focus on users, and less bound to a single company. Now what? We will have a single company promoting the event. I don’t care if it is done by Percona instead Oracle or SkySQL, actually I like all of them and I knew personally almost all of them as well.

What I care/like is to have this event NOT limited to a single company.

There was an attempt to have it organized in a different way (see Giuseppe and Sheeri comments), but then what?

I remember a long time ago (1983) when I was in Delhi and we have to run from the waiting room to the aeroplane in order to take a sit, that because the lack of coordination at the airport.
Are we in the same situation today?
The one that will move faster will get the place and the other will need to find a place after?

As a user I don’t see that as a good thing, lacking in coordination, not be able to find a way to agree on how to do things, is never good.
As someone coming from MySQL I don’t like it at all, where is the spirit of cooperation we had in MySQL is it completely gone?

After the MySQL diaspora, I have seen many time personal interest (and company interest) take over the common benefit, but at the end what happened is that we have now a potentially stronger MySQL community.
This because we need to see all of us (Oracle, Percona, SkySQL, MariaDB and any other related like Pythian where I am currently working) as competitor but also as part of the same “community”, we can compete for crumbs or we can compete for excellence, to be better. Wondering how many others community have such huge opportunity.

So I am really wonder what happened here? Are all of us so weak that we are not be able to organize an event as the MySQL conference?

I think that, as already happened in the past, all the actors would like to see this event happening as an OPEN and Independent event, focus on users and … on business as well.

Said that, is Percona acting bad? No I don’t think so, they are moving ahead, period. It is in their DNA, acting, do things.

Are them not following their own previous statements? Is too early to say that, but what I see here is also an incitation to all others to move on, and act.

What I really hope is to do not have in the next feature a SkyMySQL MySQL event, a MariaDB MySQL event and so on.

What I hope is to see all of us converging and helping each other in having this event as a common event and not as a single company.
Percona did the first move, but this doesn’t prevent all others to join and have it to become a MySQL Community event. It will be great to recognize to Percona the role of initiators, but it would also be great to see Percona able to modify his position in respect to the event, from single company event to community open event.

As always I am a dreamer, and my dream here is that $$$ are less relevant than the great work we can do all together.
Despite the fact that doing it as single company will awaken less interest (also in $$$) then doing it as community event.

Remain one big question mark … who will be in? Who is really ready to start putting effort and money to have a successful MySQL global event?

Will be nice to see statements of real commitment coming in from all actors.

Disclaimer

What I wrote is my own thinking I am writing in my own blog, and also if my employer does not censor my writing, this do not means that my writings match the company official opinion.

Great MySQL to every one.

Overview

We know that having the chance to split a table in different and smaller chunk helps.
It helps because performance searches, insert, index handling and data management as well.
All fine we are all happy, and very often we push on our customer to go for partition in order to solve part of their problems.
What happens quite often is that customer (and sometime MySQL dba as well) see partitions as separate tables.
Unfortunately is not like that, there are some operations that are (still?) creating unexpected results.
One of this is the OPTIMIZATION of a partition.

The case

Some time ago, almost 16 months I was at customer site and we were working with MySQL 5.5 rc.
We were really happy with the new version of MySQL, and being working for MySQL/SUN I was quite proud of it as well.
Then we run the ALTER TABLE X OPTIMIZE PARTITION Y;

Suddenly we realize something was not working as expected because all partitions had temporary tables.
We know and were expecting the LOCK while doing the exercise, but this was not expected at all.

I was attending several internal presentations, and I was in many internal calls,
all stating that the operation should not affect all partitions but only one.
It was not nice, and it was a bug (42822) marked non critical, referring to another one 46158,
which was close because referring to the previous as a duplicate.

Funny thing at the end is … we still have it in 5.5.12.
Read the rest of this entry . . .