I've got many questions during User Conference about InnoDB Purge Thread feature. And I've promised finally to write a short blog post explaining this feature in details and specially why do you need it :-) Don't know if it'll be short, but hope you'll have less questions by the end of this article.. :-)

So far, during the User Conference we presented the following graph to demonstrate the InnoDB Purge Thread in action:

This graph represents 40 min of non-stop activity under dbSTRESS Read+Write workload with 32 user sessions. On the left part of graph you may see the TPS level observed while a purge thread is turned off. And on the right part when it's turned on. From the graph you may see that once purge thread is activated it makes transactional throughput free of all periodic freezes of activity (and seen here as TPS level drops)... And curiously many people manly retain just "ok, it makes the picture more beautiful, but it …

From Sunday 11.4.2010 to Thursday 22.4.2010, I used my Twitter account @kajarno to send

• 26 Swedish tweets
• 41 German tweets
• 69 English tweets

(not counting direct replies) of which a clear majority (but not all) were of a volcanic nature.

Most Swedish readers can follow German and English too, and most German readers also follow English. Hence the large number of English tweets and the larger number of German tweets.

Here’s a subset of the English tweets. The dates and times are local (California 9 h different from Munich; Chicago 7 h different).

Thursday 15.4.2010

• 13:12 Kaj Arnö wonders whether LH459 will leave at all MUC-SFO tonight 21:00, and if so, which route #iceland #volcao #ash
• 20:18 Damn, flight was cancelled. #lh #iceland #volcano SFO-MUC LH459 …

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 …

I had an interesting IRC discussion the other day with Monty Taylor about what turned out to be a limitation in Valgrind with respect to debugging memory leaks in dynamically loaded plugins.

Monty Taylor's original problem was with Drizzle, but as it turns out, it is common to all of the MySQL-derived code bases. When there is a memory leak from an allocation in a dynamically loaded plugin, Valgrind will detect the leak, but the part of the stack trace that is within the plugin shows up as an unhelpful three question marks "???":

==1287== 400 bytes in 4 blocks are definitely lost in loss record 5 of 8
==1287==    at 0x4C22FAB: malloc (vg_replace_malloc.c:207)
==1287==    by 0x126A2186: ???
==1287==    by 0x7C8E01: ha_initialize_handlerton(st_plugin_int*) (handler.cc:429)
==1287==    by 0x88ADD6: plugin_initialize(st_plugin_int*) …

As the the Icelandic volcano ashes are clearing out and we finally have high hopes of flight home, I want to post the slides of the two presentations I did.

My first presentation was a double slot session about Oracle Clusterware internals. Presenting first thing in the morning on the first day is not easy at this event. Miracle Open World traditionally organized as 160% conference with 80% of technical content and 80% of networking and social interactions. Of course, the last 80% go deep into the night. Needless to say that 5am wake up call was tough — I had to craft few more slides to add some 11gR2 information and publish the first production of We Do Not Use TV Studio.

But I felt surprisingly well and fresh. The presentation itself was quite dynamic and all demos worked as planned except pausing OPROCD — 50/50 …

Answering questions asked on the site.

Nate asks:

I know you’ve addressed similar issues related to the greatest-per-group query but this seems to be a different take on that.

Example table:

Five days of uncertainty, trapped far away from home by an Icelandic volcano, provide plenty of time to think. Here is my attempt at drawing conclusions the day after returning home: partly on a personal level, partly on a general human level, partly on a societal level.

To recap what has happened: Ten minutes before the intended boarding time of LH459 from San Francisco home to Munich, I got an SMS about the flight being cancelled. This was Thursday 15.4.2010 at 20:20. Thereafter I lived in uncertainty in airport hotels, most of the time 40 km south of San Francisco, to finally arrive home five days later than plan on Wednesday 21.4.2010 at 13:00..

Personal conclusion

Conclusion 1: Uncertainty and losing freedom are hard to cope …

Here’s a matrix of all the videos up on YouTube for the 2010 O’Reilly MySQL Conference and Expo. The matrix includes the title, presenter, slide link (if it exists), video link, and link to the official conference detail page, where you can rate the session and provide feedback that the presenter will see. They are grouped mostly by topic, except for the main stage events (keynote, ignite) and interviews.

If there’s a detail missing (ie, slides, or there are other videos you know about), please add a comment so I can make this a complete matrix.