SPJ (preview, not production ready) is a new feature allowing some types of JOINs to be pushed down and executed inside the data nodes! This allows for, in many cases, much faster JOIN execution.

Now we would love to get your feedback on this new feature:

• Does what we have right now improve performance for you?
• Are there other types of JOINs we should support in order to improve performance in you application (currently only eq_ref is supported)?
• What application are you using?

There are some limitations currently:

• node failure handling of SPJ is not complete, so if a data node crash, there are side-effects.
• only eq_ref is supported - other JOINs are executed as normal.
• Don't put this in production.
  

Obtaining the MySQL Cluster SPJ preview version:

• You must know how to build MySQL Cluster …

A special extended edition of Tech Messages for 2010-04-15 through 2010-04-24:

• The Onion Uses Django, And Why It Matters To Us
  I love Django too.
• Open Source Network Monitoring and Systems Management - Zenoss Blog: No Node Left Behind: Datacenter Barometer: BitNami Simplifies Stacks
• mysql-cacti-templates
  Offers complete templates and a method for adding your own. Highly recommended.
• Increase maximum size of PST files
  Microsoft Outlook …

This is the third and final article in a series about group commit in MySQL. The first article discussed the background: group commit in MySQL does not work when the binary log is enabled. The second article explained the part of the InnoDB code that is responsible for the problem.

So how do we fix group commit in MySQL? As we saw in the second article of this series, we can just eliminate the prepare_commit_mutex from InnoDB, extend the binary logging to do group commit by itself, and that would solve the problem.

However, we might be able to do even better. As explained in the first article, with …

This is the second in a series of three articles about ideas for implementing full support for group commit in MariaDB. The first article discussed the background: group commit in MySQL does not work when the binary log is enabled. See also the third article.

Internally, InnoDB (and hence XtraDB) do support group commit. The way this works is seen in the innobase_commit() function. The work in this function is split into two parts. First, a "fast" part, which registers the commit in memory:

    trx->flush_log_later = TRUE;
    innobase_commit_low(trx);
    trx->flush_log_later = FALSE;

Second, a "slow" part, which writes and fsync's the commit to disk to make it durable:

    trx_commit_complete_for_mysql(trx)

While …

This is the first in a series of three articles about ideas for implementing full support for group commit in MariaDB (for the other parts see the second and third articles). Group commit is an important optimisation for databases that helps mitigate the latency of physically writing data to permanent storage. Group commit can have a dramatic effect on performance, as the following graph shows:

The rising blue and yellow lines show transactions per second when group commit is working, showing greatly improved throughput as the parallelism (number of concurrently running transactions) increases. The flat red and green lines show transactions per second with no group commit, with no scaling at all as parallelism increases. As can be seen, the effect of group commit on …

Oracle managed to score a major victory last week at the MySQL Conference by announcing performance gains of 200-360% in the forthcoming version 5.5.  This is a tremendous improvement and comes in part due to closer collaboration between what were historically two distinct (and occasionally competitive) groups: the InnoBase team and the MySQL Server team.  Bringing the InnoBase team under the direction of the MySQL Server team under Tomas Ullin is a great benefit not only to MySQL developers, but also for MySQL users.  No doubt these performance gains are a result of many months of hard work by not only Tomas, but also a good number of folks on both teams including guys like Mikael Ronstrum, Kojstja, Calvin Sun and others.  

Reaction to the new release has been positive in the community from the likes of …

In the last post choosing about the right type; there is a case about quoting the tuple values; that I forgot to mention which is pretty much a common mistake when string data types are used for storing int or float/double representation (well sometimes you need to use string due to length or to avoid [...]

Today evening one of my friend asked me in the IM to look into one of his production server where a query was taking ~11 seconds to run on 20 million row table, even though the query is using the right index and the plan as shown below:

mysql> explain SELECT channel, COUNT(channel) AS visitors FROM [...]

I have uploaded my three presentations from the 2010 MySQL Users Conference in Santa Clara, California which was my 5th consecutive year appearing as a speaker.

• IGNITION – MySQLCamp for Oracle DBA – Volume I
• LIFTOFF – MySQLCamp for Oracle DBA – Volume 2
• 10x Performance Improvements – A Case Study

A full history of my MySQL presentations can be found on the Presenting page.

…

Ever since MySQL 5.5 beta has been announced by Edward Screven, Oracle’s chief corporate architect; there is lot of positive buzz (here, here, …) about the performance and scalability improvements added in this release. We should all be thankful to Michael Ronstrom (as most of the key developers are already working on different forks), who [...]