Author: Serge

Last week in Paris I wen to Microsoft days show. It was a big event with steve Balmer, mainly focused on Cloud strategy, Window Phone 7 launch and other stuff. But beside that marketing part there was some interesting technical sessions. I attended one on the Microsoft Parallel Datawarehouse Solution (result of the madison project based on Datallegro acquisition).
This is a shared nothing architecture. Big tables data is distributed across node, smaller tables are replicated across node to avoid traffic network during join. The query is first issue on a coordinating node that then push pieces of it to the other nodes.
This is the same architecture that Postgres has with the greenplum solution.
For MySQL I do no know of similar architecture.
...continue reading "MySQL for a Massively Parallel Database ?"

Tracing per file IOs is a mandatory task for anyone doing performance issue analysis on a MySQL database. This could help to find candidates for innodb table compression, to decide to move data around on disks to optimize IOs, to decide what needs caching ...
There is no direct command to do it on linux. iostat gives you IOs per device or logical volumes.(I once thought of having loopback device mounted on my innodb datafile but it does not work).

ioprofile is a smart shell script developed by Baron Schwartz with the following principle :
...continue reading "Using ioprofile with MySQL is cool !"

This is a real question asked by Ivan Zoratti in his blog post.
Another frequently heard question is : Should I still use MySQL now that my business is turning into a very serious business or should I switch to Oracle 11G.

To illustrate this question let me talked about MySQL users I met last week.
Last week I went to a big e-commerce exhibition in Paris ( 3 days 27.500 visitors)

The e-commerce | m-commerce business is a booming market segment. It involves various activities : digital marketing, price comparators, web analytics, social commerce ...
But it also includes very critical areas like logistics, payment, monetization ... This business is now representing more than 100 Billions both in US and EU.

So I questioned many company in various area. I must confess that all of the one I met relied on MySQL except one that was using Microsoft + SAS ( the statistics tool to do market segmentation). None of them had really chosen MySQL vs Oracle. They chose MySQL when there were small start-ups companies and could not afford Oracle.
In fact many of them still use MySQL community (GPL product) for free.

But now they realized that their business is becoming a serious one generating nice turnover with profits and excellent growth perspective.
As volume, traffic, criticity increase many are worried about the ability of MySQL to fit with their growing business.
Will MySQL be technically able to handle the volume, the high transaction level, the high availability ? Will it technically allow to maintain a 100% on-line business.

They have been fully reassured by the commitment of Oracle to invest in MySQL and make it a better database.
The latest announces about MySQL 5.5 improvement in performance and scalability made them confident they made the right choice.
The improvement on replication reliability, the availability of online backup with MySQL, the improvement on partitioning, all this made them feel Oracle is truly making MySQL better.

They have been also totally convinced by the very lively and innovative MySQL community : spider storage engine , flexview materialized views, Qgraph storage engine, column based storage engine, smart patches or improvement developed by key users. They got the feeling that this is the place where things are happening and innovation is taking place openly.

The capacity to replicate from Oracle to MySQL through Goldengate made them confident they could integrate with more classic IT if necessary.

So for me the main question that was answered by Oracle for these customers was : Should I still rely on MySQL now that I am doing big business ?
and the answer is obviously : YES you can rely on MySQL.

I went to Microsoft techdays : huge 17000 visitors, 300 confs , .... C#, F#, Sqlserver 2008, cloud ...
I took part to the "Android Developer Labs World Tour" in a place called "La cantine" ( a french cafe + some chairs)
A few geeks there, a very nice event . Each participant went back home with a nexus one !
I delivered a MySQL and java pres. I had fun demoing JPA, JSF, RestFull web services on Glassfish.

But what I will definitely remember about this week is this news :

"Ken Jacobs leaving Oracle"

You will be missed by Oracle.
You will be missed by MySQL community.

You have been a key people of Oracle helping design the product that made all the rest possible for Oracle.
You have always played fair with the MySQL community.

In my previous life at Oracle I remember 7.0, 7.1 , 8.0, 8i, 9i, 10g, 11g ....
You sometimes came to Paris and gave us not just the lengthy list of new functionalities but the reason why it was build that way, the internals and the interest it represents.
When you took InnoDB I was surprised by the dedication you put in this wonderful piece of software.
But you were right. Technology is just about details and elegance.
You brought a lot of insight to the MySQL community.

And do not forget that the MySQL community is not a company. You cannot resign.
You can take your distance, do whatever please you, come back.

You will always be welcomed.

It is now possible to use the UNIX_TIMESTAMP() function in partitioning expressions using TIMESTAMP columns.

MySQL 5.1.43 can be downloaded from

http://dev.mysql.com/downloads/

For example, it now possible to create a partitioned table such as this one :

CREATE TABLE t (id INTEGER, ts TIMESTAMP, col1 VARCHAR(64), ... )
PARTITION BY RANGE ( UNIX_TIMESTAMP(ts) ) (
PARTITION p0 VALUES LESS THAN (631148400),
PARTITION p1 VALUES LESS THAN (946681200),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

All other expressions involving TIMESTAMP values are now rejected with an error when attempting to create a new partitioned table or to alter an existing partitioned table.

To go from the unix timestamp to a more readable value it is possible to use the FROM_UNIXTIME(ts) function :

mysql> SELECT FROM_UNIXTIME(631148400);
+--------------------------------------+
| FROM_UNIXTIME(631148400) |
+--------------------------------------+
| 1990-01-01 00:00:00
+--------------------------------------+

With MySQL 5.1.43 it is important to note that partitioning can only be done on an integer value column or through the following restricted set of functions that return integer :

ABS() MOD() CEILING() FLOOR() EXTRACT() DATEDIFF()
DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() TO_DAYS() WEEKDAY()
HOUR() MINUTE() SECOND() TIME_TO_SEC() MICROSECOND()
YEAR() YEARWEEK() QUARTER() MONTH()

To help Giuseppe Maxia has proposed some tricks that make partitioning by date in 5.1 a bit less painful :
A quick usability hack with partitioning

The partition helper - Improving usability with MySQL 5.1 partitioning

And to partition on a non integer column :
Partitioning with non integer values using triggers

And do not forget that in 5.1 :
All the columns used in the table's partitioning expression mus t be used in every unique key on the table.
This also includes the table's primary key, since it is by definition a unique key.
So in the above example with the UNIX_TIMESTAMP function the timestamp column must be part of the primary key and also part of all the unique keys existing on the table.
An important restriction also to remember is that the number of partitions is limited to 1024 (including sub partitions).

MySQL 5.5. partitioning offers more functionalities :
With RANGE and LIST partitioning it will be possible to partition by non-integer columns like dates or strings (using the new COLUMNS keyword).
MySQL 5.5 Partitioning with the he COLUMNS keyword also allow to use multiple columns to define a partition.
The new TRUNCATE PARTITION allow to empty a partition in a more efficient way than delete or drop partition + recreation of the partition.
The new function TO_SECONDS is available to partition on time intervals smaller than one day. It is taken into account by partition pruning.

To explain 5.5 partitioning new features there is the interesting article by Giuseppe Maxia :
A deep look at MySQL 5.5 partitioning enhancements

In my previous post "InnoDB : Why not use the same isolation level as ORACLE" I suggested that like ORACLE it might now be a good idea to use READ COMMITTED as the default isolation level for InnoDB.

Marc Callaghan made interesting measures that illustrate a case where READ COMMITTED mode degraded performance.
("Repeatable read versus read committed for InnoDB ")

Can we draw conclusions from what has been measured ?

Let us look in more detail to what has been measured. The workload is generated by sysbench :
...continue reading "InnoDB : Any real performance improvement when using READ COMMITED isolation level ?"