Welcome to Log Buffer, the weekly news update of happenings in the database world.

Its the holiday season and many DBAs would rather cuddle at home with their family rather than do exciting work and blog about it. Can you imagine? Many thanks to Fahd Mizra who helped me by collecting DB2 news. Grab your snuggie and cuddle up with Log Buffer #210.

MySQL:

Giuseppe Maxia, the Data Charmer looked for a way to add comments into the binary log and created an interesting discussion on the topic.

You’d think that having some idle …

Poul-Henning Kamp, of FreeBSD fame, posted a year-end question (in Danish) on his blog at the IT rag version2.dk: What would be the computer equivalent of Shannon’s ultimate machine?

Based on one of the comment suggestions, inspired by the fact that I’d been messing around with automating the Windows cursor some months back, and having half an hour too much spare time, I came up with my own contender for the world’s most useless computer program:

(http://www.youtube.com/watch?v=9WTq2nRgYeQ)

If you’re intensely interested, the interesting part of the C# program is here:

using System;
using System.Runtime.InteropServices;
using …

New Year is coming! And it brings us a new MySQL User Conference! As always, me and my colleagues will be attending — we have great talks to offer:

• Monty is giving a keynote State of MariaDB. Just like the last year, Monty will tell you how we are doing, what we have spent the last year on, what we are working on now.
• Colin presents A Beginner’s Guide to MariaDB talk. If you have heard of MariaDB, but don’t quite know what it is and why you should care — go and attend his talk.
• Sergei (that’s me) together with Andrew Hutchings will give a tutorial …

Inspired by Baron's earlier post, here is one I hear quite frequently -

"If you enable innodb_file_per_table, each table is it's own .ibd file.  You can then relocate the heavy hit tables to a different location and create symlinks to the original location."

There are a few things wrong with this advice:

1. InnoDB does not support these symlinks.  If you run an ALTER TABLE command, what you will find is that a new temporary table is created (in the original location!), the symlink is destroyed, and the temporary table is renamed.  Your "optimization" is lost.
2. Striping (with RAID) is usually a far better optimization.  Striping a table across multiple disks effectively balances the  'heavy hit' access across many more disks.  With 1 disk/table you are …

Here at Recorded Future we use Ubuntu (running on Amazon EC2), but so far we have not explored Ubuntu Upstart that much. During the holidays I made an effort to get acquainted with Upstart and to implement proper MySQL start and stop with it.

If you do not know Upstart, this is the way you start and stop services in Ubuntu, and it serves the same purpose as the old /etc/init.d scripts, but are a bit more structured and powerful. That said, Upstart is regrettably far from complete, although the functionality is much better and Upstart has some cool features, some things do not work that well. For one thing, documentation, where it exists, is useless, at best. Secondly, there is very limited ability to test and develop Upstart scripts. And this is made worse by the fact that the documentation is so bad. Another thing is that Upstart insist on stopping services, by default, by …

I manage a few systems that every hundred million disk temporary table deletions or so one of them will fail. MySQL will delete the MYI file but leave the MYD behind. It’s very strange. There isn’t an error in the error log but subsequent queries that try to use the same temporary table name will error because the MYD file still exists. The queries fail with an error like ERROR 1 (HY000): Can’t create/write to file ‘/tmp/#sql_25d1_0.MYD’. Fortunately the client error gives the temporary table name so it’s easy to clean up by deleted the MYD file. While I still don’t know what the root cause is but I was able to patch MySQL to fix the issue.

The old mi_delete_table function which is responsible for deleting only the MYI and MYD file would delete the MYI file and leave the MYD file behind. This function would try to delete the MYI file, get an error back from the filesystem and not attempt to delete the corresponding MYD file. …

I’ve taken the part of the tivo patch that includes table and index statistics and broken it out into it’s own patch. This patch includes the ability to do show [session | global ] table_statistics and the same for index_statistics. In this version the row stats are also logged in the slow query log. To log per query stats I had to track them separately from the per session stats. Because the tracking was already done for the slow query log I’ve modified the command to allow uses to access row stats for the previous query separate from the sum for the current session. The flush commands also act similarly.

Along with changing the slow query log format I’ve also change it to log the timestamp with every query. This made it easier to do automated parsing of the slow query log.

The queries now support three different modes. I’ve detailed how the …

Previously, I wrote about using coroutines in RethinkDB. Coroutines are a nice alternative to callbacks because they are easier to program in, and they are a nice alternative to threads because of their greater performance. But how fast are they?

Just using an off-the-shelf library like libcoroutine isn't as fast as you might think. The graph below shows the huge performance degradation of a naive implementation of coroutines (the short red bar) compared to the old callback-based code (the blue bar on the left). But with the right set of optimizations, we can recover a level of performance about equal to the non-coroutine version. With these optimizations, throughput is recorded as the pink bar on the right, which is within the margin of error of the original version.

…

Previously, I wrote about using coroutines in RethinkDB. Coroutines are a nice alternative to callbacks because they are easier to program in, and they are a nice alternative to threads because of their greater performance. But how fast are they?

Just using an off-the-shelf library like libcoroutine isn’t as fast as you might think. The graph below shows the huge performance degradation of a naive implementation of coroutines (the short red bar) compared to the old callback-based code (the blue bar on the left). But with the right set of optimizations, we can recover a level of performance about equal to the non-coroutine version. With these optimizations, throughput is recorded as the pink bar on the right, which is within the margin of error of the original version.

The improvement comes from  two …

In my last post I installed the HandlerSocket plugin into MariaDB and tested it.  Like the last post these examples are done with Linux CentOS 5.5.

HandlerSocket some with Perl code for the Net:: module group. When you build and install the HandlerSocket plugin it does not build the Perl module. I looked and it is not included in CPAN.

If you have already downloaded (git) and installed the plugin, you can install the Perl module by:

cd perl-Net-HandlerSocket
perl Makefile.PL
make
make test
make install

Most of the sample apps I’ve found do little more the prove it works.  I found the protocol (API) is very simple.  This makes it fast but may give you some trouble coding for it.  I created test table and my own version of the example code.

CREATE TABLE `user` (
 `user_id` int(10) unsigned NOT NULL,
 `user_name` varchar(50),
 `user_email` …