MCslp Coalface

Feeding Query Analyzer from DTrace

One of the new features in the new release of MySQL Enterprise Monitor is Query Analyzer. As the name suggests, the Query Analyzer provides information about the queries that are running on your server, the response times and row and byte statistics. The information provided is great, and it doesn’t take very long to see from the query data supplied that there are places where you could improve the the query, or even reduce the number of queries that you submit.

The system works by using the functionality of the MySQL Proxy to monitor the queries being executed and then provide that information up to the MySQL Enterprise Service Manager so that the information can be displayed within the Query Analyzer page. To get the queries monitored, you have to send the queries through the agent which both monitors their execution and sends the information on up to the Manager, along with all the other data being monitored.

The team, though, have been a bit clever and opened up the system to allow information to be sent to the Manager using a REST interface. This means that any system capable of providing information that you want to monitor can be sent up to the Manager. Of course, you can’t just send anything, the Manager needs to know how to handle it, but it shows the flexibility of the design and the potential for the future.

So how does this help us?

Well, one of the new features in MySQL 6.0 that I’ve been working on (with Mikael Ronstrom and Alexey Kopytov) is DTrace probes. We’ve added a bunch of static DTrace probes into MySQL 6.0 (the full set will appear in MySQL 6.0.8, I think) designed to let you monitor the execution of queries within the server. The probes will allow you to see both the top-level information, such as overall execution time, but also deeper so that you can get information about individual row operations, whether the query used the query cache, and whether it used a filesort operation.

I haven’t finished the DTrace probes documentation yet, but I have been demonstrating the probes at conferences and talks (including my MySQL on OpenSolaris university session this week). Trust me, you’ll be pleased. I’ve got a separate blog post detailing some of the specifics in the works at the moment.

For obvious reasons, there’s a synergy here that should be obvious. Why don’t we feed up data extracted using DTrace and provide that up to the Enterprise Manager?

To do this, there are two parts to the process, the DTrace probes and the script hat passes that information up in a suitable format to the manager.

The D script is quite straightforward, we initialize the structures, populate the core information that we need (query string, bytes, rows and the time), and the use the remainder of the probes to finalize that information. Let’s have look at the script and then go through the detail:

#!/usr/sbin/dtrace -s

#pragma D option quiet

mysql*:::query-start
{
   self->query = copyinstr(arg0);
   self->db    = copyinstr(arg2);
   self->rows  = 0;
   self->querystart = timestamp;
   self->bytes = 0;
}

mysql*:::select-done
{
        self->rows = arg1;
}

mysql*:::insert-done
{
        self->rows = arg1;
}

mysql*:::update-done
{
        self->rows = arg2;
}

mysql*:::multi-delete-done
{
        self->rows = arg1;
}

mysql*:::delete-done
{
        self->rows = arg1;
}

mysql*:::multi-update-done
{
        self->rows = arg2;
}

mysql*:::net-write-start
{
        self->bytes = self->bytes + arg0;
}

mysql*:::query-done
/self->query != NULL/
{
        printf("%s:%s:%d:%d:%d\n",
        self->query,
        self->db,
        ((timestamp - self->querystart)/1000),
        self->rows,self->bytes);
}

First, we set a pragma to quieten down the output so that the DTrace script only reports what we explicitly write out:

#pragma D option quiet

In DTrace, the individual execution points are called probes, and probes are triggered each time that point in the code is reached. To specify the probes we want to watch for, you use a special format, provider:module:function:name that identifies the probe by the name of the provider (the application), the module, the function, and the probe, each separated by a colon. We can just specify the provider and probe name, like mysql*:::query-start.

It should also be noted that probes are often provided in pairs at the start and end of an operation, so you can identify the start and end of a query by looking for the query-start and query-done probes.

The DTrace probes in the server are set-up in a sort of nested structure, going deeper into the query process as needed. Although not at the very top of the execution cycle, the start of the main query processing is identified by the query-start probe. Each time a query is submitted to MySQL, this probe will get triggered, so for us, it is the start of the process. The probe has a number of arguments, but for our purposes we only need the first (arg0), which contains the full query string, and the third (arg2) which contains the name of the database that the query was executed against.

We also initialize the row and byte counts, and the time when the query was executed using the built-in timestamp value. All of this information is placed into the special self structure, which is a persistent structure used to share information between the individual probes that get fired during execution.

mysql*:::query-start
{
   self->query = copyinstr(arg0);
   self->db    = copyinstr(arg2);
   self->rows  = 0;
   self->querystart = timestamp;
   self->bytes = 0;
}

To get the counts of the number of rows, we can’t get the information from the query-done probe. This is because different operations actually provide different levels of information. For example, the select-done and insert-done just provide a count of the rows. But the update-done probe provides information both about the number of rows that matched the original WHERE clause, and the count of the number rows actually modified.

To record the number of the rows modified by the query, we therefore need to pull out each piece of information individually:

mysql*:::select-done
{
        self->rows = arg1;
}
mysql*:::insert-done
{
        self->rows = arg1;
}

mysql*:::update-done
{
        self->rows = arg2;
}

mysql*:::multi-delete-done
{
        self->rows = arg1;
}

mysql*:::delete-done
{
        self->rows = arg1;
}

mysql*:::multi-update-done
{
        self->rows = arg2;
}

For the bytes retrieved by each query, the information is a bit more difficult to identify. I’m going to cheat a bit and use the bytes sent by mysqld during a net write to the client. There is a limitation here I’ve skipped, which is that we could report data sent to any client, since I haven’t bothered to track connection IDs. I could do this, but it would make the script a little more complicated. Since the net-write-start might be called multiple times for a long query, we calculate a cumulative byte count.

mysql*:::net-write-start
{
        self->bytes = self->bytes + arg0;
}

That’s all of the information collection; now we just need to print out the information when the query completes. We do this by writing out a colon separated list of the information that we’ve collected. One additional point here though is that to calculate the duration of the query, you take the timestamp recorded when query-start was called away from the current timestamp.

Timestamp information is recorded in nanoseconds (yes, you read that right, nanoseconds), so we divide it by a thousand to get it in microseconds, which is what the Enterprise Manager will expected.

mysql*:::query-done
/self->query != NULL/
{
        printf("%s:%s:%d:%d:%d\n",
        self->query,
        self->db,
        ((timestamp - self->querystart)/1000),
        self->rows,self->bytes);
}

If you run this script on it’s own (against a MySQL running on Solaris/OpenSolaris, with probes, of course), then you’ll get output like this:

SELECT DATABASE()::391:1:44
show databases:test:947:2:84
show tables:test:2018:3:74
select * from t limit 5:test:595:5:51

To provide the information up to the Enterprise Manager we cannot use D scripts. Instead, a wrapper around the D script will read the raw information produced and then pass that up to the Enterprise Manager.

Before we look at that process, it is worth looking at the REST API that has been built in to v2 of the Enterprise Monitor. The interface is available through the standard URL for the Enterprise service, typically your hostname and the port 18080 if you’ve used the default settings. Therefore we can access the interface using the url http://nautilus:18080/v2/rest/, assuming our host is nautilus.

From the base URL, you can start to get information, or put information, about the different entries in the repository using the path in the URL to signifiy what it is we are looking for. Information about instances is within the instance, with the provider as mysql, and the MySQL server as server. Or better put, the base URL would be http://nautilus:18080/v2/rest/instance/mysql/server/.

The last fragment of information we need is the UUID. All objects within the repository have a unique ID, and these are split at different levels. For example, an agent has a UUID, and so does the server it is monitoring. In our example, we want the UUID of the MySQL server, which is conveniently stored within the server itself in the mysql.inventory table.

Finally, we need the username and password of the agent user. Through the REST API we use basic HTTP authentication, to make the process easy.

Putting all of this together, we can get the core information about an instance using wget:

$ wget -qO mysql.server --http-user=agent --http-password=password \
    'http://nautilus:18080/v2/rest/instance/mysql/server/2b86b277-fb2b-492d-b946-3a2acaec0869'

If we now look at the output file, mysql.server:

{
    "name": "2b86b277-fb2b-492d-b946-3a2acaec0869",
    "parent": "/instance/os/Host/ssh:{88:e1:fc:6d:99:69:e4:5f:b4:0a:ec:5a:09:c0:6a:24}",
    "values":     {
        "blackout": "false",
        "displayname": null,
        "registration-complete": "true",
        "repl.groupName": null,
        "server.connected": 1,
        "server.last_error": null,
        "server.reachable": 1,
        "transport": "a3113263-4993-4890-8235-cadef9617c4b",
        "visible.displayname": "bear:3306"
    }
}

I wont go into detail about what is here, most of it should be self explanatory. However, there are a few things of note. First, the information is in JSON format. This makes it easy to read and more importantly create.

Second, note the notation. The item is identified by its name, and also by it’s parent. This is an important construct because it helps identify the different elements with each other. In this case, the MySQL server is associated with a physical host (/instance/os/Host) and the individual host is identified by a SSH key, which is one of the alternative UUID formats support by the Enterprise Server to identify individual entities.

When submitting information, we need to flip the process around. We don’t use a GET request to obtain the information, we use a PUT to send up a JSON packet containing the information we want. The URL for sending the information depends on what we are uploading. The main element for the statements used for Query Analyzer is the statementsummary.

The URL for this is http://nautilus:18080/v2/rest/instance/mysql/statementsummary/. For the identifier at the end of the URL, you use a period-separated list that includes the UUID of the MySQL server, the name of the MySQL database the SQL statement relates to, and an MD5 hash of the SQL statement text.

For the actual packet, we use the following format, taken here from the Perl script:

{
    "name": "$server_uuid.$quanbase->{dbname}.$md5",
    "parent": "/instance/mysql/server/$server_uuid",
    "values" : {
	"count": "$quanbase->{count}",
	"text": "$quanbase->{query}",
	"query_type": "$quanbase->{qtype}",
	"text_hash": "$md5",
	"max_exec_time": "$quanbase->{max_exec_time}",
	"min_exec_time": "$quanbase->{min_exec_time}",
	"exec_time": "$quanbase->{exec_time}",
	"rows": "$quanbase->{rows}",
	"max_rows": "$quanbase->{max_rows}",
	"min_rows": "$quanbase->{min_rows}",
	"database": "$quanbase->{dbname}",
	"bytes": "$quanbase->{bytes}",
	"max_bytes": "$quanbase->{max_bytes}",
	"min_bytes": "$quanbase->{min_bytes}",
    }
}

Most of this should be self-explanatory. Remember that this is a statement summary, which means that we can send up information about multiple invocations of the same statement in one packet. Thus, within the statementsummary packet we have information about the count of invocations of the statement, execution, row and byte counts and maximum/minimum of each of them, and then the core information like the actual query text, database name, and query type (SELECT, INSERT, etc).

Once again, note the name and parent. Here the name is the same tuple as used in the URL, the UUID of the MySQL server, the database, and the hash of the query. This is used as the identifier for this query within the repository and allows us to uniquely identify the query, and the query execution on this server. The parent is the location of, and UUID of, the MySQL server.

Now, the Perl script that collates the information from our D script has to do two things, first read the raw output that we create with the D script, and second, supply this up as a PUT request to the Enterprise Server.

Dealing with the latter part first, I’ve used Perl and LWP (libwww-perl) module to construct a suitable request object with the HTTP authorization attached:

my $header = HTTP::Headers->new;
$header->content_type('text/text');
$header->authorization_basic('agent','password');
my $res = LWP::UserAgent->new();

Once we’ve constructed a packet, sending it is a case of specifying the URL, the header, and the content:

$header->content_length(length $bio);
my $req = HTTP::Request->new(PUT => $url, $header, $bio);

$res->request($req);

The bulk of the rest of the script is devoted to reading the information from the D script output, and assembling the packet and min/max values per query.

Within the Query Analyzer, the SQL statements are normalized, or canonicalized so that variables are replaced with a question mark. This ensures that we are tracking the query and not the individual values. The significance here is that we want to compare the raw SQL statement, of which there may only be a few hundred in a typical application, not each individual query with it’s WHERE and other clauses.

Hence, the statement:

SELECT photoid,title from media_photos where photoid > 23785 limit 15

Would be normalized to:

SELECT photoid,title from media_photos where photoid > ? limit ?

For the Perl script, I do just one type of normalization, removing the value from a LIMIT clause.

#!/usr/bin/perl
use Data::Dumper;
use LWP;
use HTTP::Request;
use Digest::MD5 qw/md5_hex/;

my $server_uuid = '2b86b277-fb2b-492d-b946-3a2acaec0869';

my $header = HTTP::Headers->new;
$header->content_type('text/text');
$header->authorization_basic('agent','password');
my $res = LWP::UserAgent->new();

my $interval = shift || 20;

print "Sending queries every $interval statement(s)\n";

open(DTRACE,"./merlin.d|") or die "Couldn't open DTRACE\n";

my $counter = 1;
my $querybase = {};

while(
)
{
    chomp;
    my ($origquery,$dbname,$time,$rows,$bytes) = split m{:};

    my $query = $origquery;
    $query =~ s/limit \d+/limit ?/g;

    $querybase->{$query}->{dbname} = $dbname;
    $querybase->{$query}->{query} = $query;
    $querybase->{$query}->{count}++;
    $querybase->{$query}->{rows} += $rows;
    $querybase->{$query}->{bytes} += $bytes;
    $querybase->{$query}->{exec_time} += $time;

    if (exists($querybase->{$query}))
    {
	$querybase->{$query}->{max_rows} = $rows if ($rows > $querybase->{$query}->{max_rows});
	$querybase->{$query}->{min_rows} = $rows if ($rows < $querybase->{$query}->{min_rows});
	$querybase->{$query}->{max_bytes} = $bytes if ($bytes > $querybase->{$query}->{max_bytes});
	$querybase->{$query}->{min_bytes} = $bytes if ($bytes < $querybase->{$query}->{min_bytes});
	$querybase->{$query}->{max_exec_time} = $time if ($time > $querybase->{$query}->{max_exec_time});
	$querybase->{$query}->{min_exec_time} = $time if ($time < $querybase->{$query}->{min_exec_time});
    }
    else
    {
	$querybase->{$query}->{max_rows} = $rows;
	$querybase->{$query}->{min_rows} = $rows;
	$querybase->{$query}->{max_bytes} = $bytes;
	$querybase->{$query}->{min_bytes} = $bytes;
	$querybase->{$query}->{max_exec_time} = $time;
	$querybase->{$query}->{min_exec_time} = $time;
    }	

    if (($counter % $interval) == 0)
    {
	print STDERR "Writing quan packets ($counter queries sent)\n";
        foreach my $query (keys %{$querybase})
        {
            send_quandata($querybase->{$query});
	    delete($querybase->{$query});
        }
    }
    $counter++;
}

sub send_quandata
{
    my ($quanbase) = @_;

    my $urlbase = 'http://nautilus:18080/v2/rest/instance/mysql/statementsummary/%s.%s.%s';

    my $md5 = md5_hex($quanbase->{query});
    my $url = sprintf($urlbase,$server_uuid,$quanbase->{dbname},$md5);

my $bio = < {dbname}.$md5",
    "parent": "/instance/mysql/server/$server_uuid",
    "values" : {
        "count": "$quanbase->{count}",
        "text": "$quanbase->{query}",
        "query_type": "$quanbase->{qtype}",
        "text_hash": "$md5",
        "max_exec_time": "$quanbase->{max_exec_time}",
        "min_exec_time": "$quanbase->{min_exec_time}",
        "exec_time": "$quanbase->{exec_time}",
        "rows": "$quanbase->{rows}",
        "max_rows": "$quanbase->{max_rows}",
        "min_rows": "$quanbase->{min_rows}",
        "database": "$quanbase->{dbname}",
        "bytes": "$quanbase->{bytes}",
        "max_bytes": "$quanbase->{max_bytes}",
        "min_bytes": "$quanbase->{min_bytes}",
    }
}
EOF

$header->content_length(length $bio);
my $req = HTTP::Request->new(PUT => $url, $header, $bio);

$res->request($req);
}

The basic structure is:

1. Open the DTrace script
2. Read a line
3. Add that to the temporary list of queries I know about, adding stats
4. When I’ve read N queries, send up the stats about each query as a JSON packet to the Enterprise Manager
5. Repeat

Depending on how busy your server is, you may want to adjust the interval when the stats data is uploaded. The default is every 20 queries, but when running on a really busy server, or when running benchmarks, you might want to up that to prevent the script spending too much time sending fairly small packets of stats up.

If you run the script, it should just work in the background:

$ ./dtrace_merlin.pl
Sending queries every 20 statement(s)
Writing quan packets (20 queries sent)
Writing quan packets (40 queries sent)
Writing quan packets (60 queries sent)

That’s it!

I set this up and then sent some random queries to the server. The following graphic shows the query data only from the DTrace sourced information.

There are some limitations to the current script. I don’t do full normalization, for example, and I dont send the detailed information about individual statements up at the moment. There is also an EXPLAIN packet that you can send that contains the output from an EXPLAIN on a long running query. I could do that by opening a connection to the server and picking out the information.

But what I’d really like to do is use the DTrace-based output to show the detail of each part of the query process and the EXPLAIN output. I’m sure I can work on that with the Enterprise team.

MySQL on OpenSolaris Presentation/Transcript Now Available

As I mentioned earlier this week, I did a presentation on MySQL in OpenSolaris today.

The presentation (audio and slides) is now viewable online (and downloadable), and you can also get hold of the transcript of the questions: here (or download). The original presentation is here.

One minor difference from the presentation is that we have upgraded MySQL to 5.0.67 in 2008.11. I had forgotten we’d agreed to do this after the 5.1 pushback. Thanks to Matt Lord for the heads up.

And thanks to everybody for attending. Up next week, memcached!

MySQL University: MySQL and OpenSolaris

On Thursday, November 13, 2008 (14:00 UTC / 14:00 BST / 15:00 CET), I’ll be presenting a MySQL University session on MySQL and OpenSolaris.

The presentation will be similar to the presentation I did at the London OpenSolaris Users Group in July, you can see that presentation by visiting the LOSUG: July 2008 page.

The presentation on thursday will be slightly different - I’ll be providing a bit more hands-on information about how to install MySQL, how to configure and change the configuration and some more detail on solutions like the Webstack and Coolstack distributions.

I’ll also cover our plans for the inclusion of MySQL 5.1 in OpenSolaris, which will happen next year, and provide some examples on the new DTrace probes that we have been adding to MySQL generally.

Of course, if there’s anything specific you want me to talk about, comment here and I’ll see if I can squeeze it into the presentation before thursday.

Compiling MySQL Workbench on Gentoo

The Workbench team have just announced the release of Workbench for Linux, including binary packages and source packages with instructions on how to build.

I’m a Gentoo Linux user, so I prefer building from source, and you’ll need to emerge the following packages (and note the USE) requirement as part of the source build process:

# USE="svg" emerge libzip libxml2 libsigc++ libglade libgtksourceviewmm media-libs/glut mysql lua ossp-uuid libpcre libgnome gtk+ pango cairo

Depending on your config and platform, you may need to bypass some package masking by adding the packages to your /etc/portage/package.keywords file.

Then download and install the ctemplate library from google code page. The current Gentoo version is 0.90, and you really should install the 0.91 version.

With the required packages and libraries in place, download the Workbench sources and then build:

# cd mysql-workbench-5.1.4alpha
# ./autogen.sh
# make
# make install

That should build and install MySQL Workbench for you.

Just to confirm, here’s a screenshot of the built Workbench running on Gentoo Linux and displaying to my Mac OS X-based desktop.

ZFS Replication for MySQL data

At the European Customer Conference a couple of weeks back, one of the topics was the use of DRBD. DRBD is a kernel-based block device that replicates the data blocks of a device from one machine to another. The documentation I developed for that and MySQL is available here.

Fundamentally, with DRBD, you set up a physical device, configure DRBD on top of that, and write to the DRBD device. In the background, on the primary, the DRBD device writes the data to the physical disk and replicates those changed blocks to the seconday, which in turn writes the data to it’s physical device. The result is a block level copy of the source data. In an HA solution, which means that you can switch over from your primary host to your secondary host in the event of system failure and be sure pretty certain that the data on the primary and seconday are the same.

In short, DRBD simplifies one of the more complex aspects of the typical HA solution by copying the data needed during the switch. Because DRBD is a Linux Kernel module you can’t use it on other platforms, like Mac OS X or Solaris. But there is another solution: ZFS.

ZFS supports filesystem snapshots. You can create a snapshot at any time, and you can create as many snapshots as you like.

Let’s take a look at a typical example. Below I have a simple OpenSolaris system running with two pools, the root pool and another pool I’ve mount at /opt:

Filesystem             size   used  avail capacity  Mounted on
rpool/ROOT/opensolaris-1
                       7.3G   3.6G   508M    88%    /
/devices                 0K     0K     0K     0%    /devices
/dev                     0K     0K     0K     0%    /dev
ctfs                     0K     0K     0K     0%    /system/contract
proc                     0K     0K     0K     0%    /proc
mnttab                   0K     0K     0K     0%    /etc/mnttab
swap                   465M   312K   465M     1%    /etc/svc/volatile
objfs                    0K     0K     0K     0%    /system/object
sharefs                  0K     0K     0K     0%    /etc/dfs/sharetab
/usr/lib/libc/libc_hwcap1.so.1
                       4.1G   3.6G   508M    88%    /lib/libc.so.1
fd                       0K     0K     0K     0%    /dev/fd
swap                   466M   744K   465M     1%    /tmp
swap                   465M    40K   465M     1%    /var/run
rpool/export           7.3G    19K   508M     1%    /export
rpool/export/home      7.3G   1.5G   508M    75%    /export/home
rpool                  7.3G    60K   508M     1%    /rpool
rpool/ROOT             7.3G    18K   508M     1%    /rpool/ROOT
opt                    7.8G   1.0G   6.8G    14%    /opt

I’ll store my data in a directory on /opt. To help demonstrate some of the basic replication stuff, I have other things stored in /opt as well:

total 17
drwxr-xr-x  31 root     bin           50 Jul 21 07:32 DTT/
drwxr-xr-x   4 root     bin            5 Jul 21 07:32 SUNWmlib/
drwxr-xr-x  14 root     sys           16 Nov  5 09:56 SUNWspro/
drwxrwxrwx  19 1000     1000          40 Nov  6 19:16 emacs-22.1/
lrwxrwxrwx   1 root     root          48 Nov  5 09:56 uninstall_Sun_Studio_12.class -> SUNWspro/installer/uninstall_Sun_Studio_12.class

To create a snapshot of the filesystem, you use zfs snapshot, and then specify the pool and the snapshot name:

# zfs snapshot opt@snap1

To get a list of snapshots you’ve already taken:

# zfs list -t snapshot
NAME                                         USED  AVAIL  REFER  MOUNTPOINT
opt@snap1                                       0      -  1.03G  -
rpool@install                               19.5K      -    55K  -
rpool/ROOT@install                            15K      -    18K  -
rpool/ROOT/opensolaris-1@install            59.8M      -  2.22G  -
rpool/ROOT/opensolaris-1@opensolaris-1       100M      -  2.29G  -
rpool/ROOT/opensolaris-1/opt@install            0      -  3.61M  -
rpool/ROOT/opensolaris-1/opt@opensolaris-1      0      -  3.61M  -
rpool/export@install                          15K      -    19K  -
rpool/export/home@install                     20K      -    21K  -

The snapshots themselves are stored within the filesystem metadata, and the space required to keep them will vary as time goes on because of the way the the snapshots are created. The initial creation of a snapshot is really quick, because instead of taking an entire copy of the data and metadata required to hold the entire snapshot, ZFS merely records the point in time and metadata of when the snaphot was created.

As you make more changes to the original filesystem, the size of the snapshot increases because more space is required to keep the record of the old blocks. Furthermore, if you create lots of snapshots, say one per day, and then delete the snapshots from earlier in the week, the size of the newer snapshots may also increase, as the changes that make up the newer state have to be included in the more recent snapshots, rather than being spread over the seven snapshots that make up the week.

The result is that creating snapshots is generally very fast, and storing snapshots is very efficient. As an example, creating a snapshot of a 40GB filesystem takes less than 20ms on my machine.

The only issue, from a backup perspective, is that snaphots exist within the confines of the original filesystem. To get the snapshot out into a format that you can copy to another filesystem, tape, etc. you use the zfs send command to create a stream version of the snapshot.

For example, to write out the snapshot to a file:

# zfs send opt@snap1 >/backup/opt-snap1

Or tape, if you are still using it:

# zfs send opt@snap1 >/dev/rmt/0

You can also write out the incremental changes between two snapshots using zfs send:

# zfs send opt@snap1 opt@snap2 >/backup/opt-changes

To recover a snapshot, you use zfs recv which applies the snapshot information either to a new filesytem, or to an existing one. I’ll skip the demo of this for the moment, because it will make more sense in the context of what we’ll do next.

Both zfs send and zfs recv work on streams of the snapshot information, in the same way as cat or sed do. We’ve already seen some examples of that when we used standard redirection to write the information out to a file.

Because they are stream based, you can use them to replicate information from one system to another by combining zfs send, ssh, and zfs recv.

For example, let’s say I’ve created a snapshot of my opt filesystem and want to copy that data to a new system into a pool called slavepool:

# zfs send opt@snap1 |ssh mc@slave pfexec zfs recv -F slavepool

The first part, zfs send opt@snap1, streams the snapshot, the second, ssh mc@slave, and the third, pfexec zfs recv -F slavepool, receives the streamed snapshot data and writes it to slavepool. In this instance, I’ve specified the -F option which forces the snapshot data to be applied, and is therefore destructive. This is fine, as I’m creating the first version of my replicated filesystem.

On the slave machine, if I look at the replicated filesystem:

# ls -al /slavepool/
total 23
drwxr-xr-x   6 root     root           7 Nov  8 09:13 ./
drwxr-xr-x  29 root     root          34 Nov  9 07:06 ../
drwxr-xr-x  31 root     bin           50 Jul 21 07:32 DTT/
drwxr-xr-x   4 root     bin            5 Jul 21 07:32 SUNWmlib/
drwxr-xr-x  14 root     sys           16 Nov  5 09:56 SUNWspro/
drwxrwxrwx  19 1000     1000          40 Nov  6 19:16 emacs-22.1/
lrwxrwxrwx   1 root     root          48 Nov  5 09:56 uninstall_Sun_Studio_12.class -> SUNWspro/installer/uninstall_Sun_Studio_12.class

Wow - that looks familiar!

Once you’ve snapshotted once, to synchronize the filesystem again, I just need to create a new snapshot, and then use the incremental snapshot feature of zfs send to send the changes over to the slave machine again:

# zfs send -i opt@snapshot1 opt@snapshot2 |ssh mc@192.168.0.93 pfexec zfs recv slavepool

Actually, this operation will fail. The reason is that the filesystem on the slave machine can currently be modified, and you can’t apply the incremental changes to a destination filesystem that has changed. What’s changed? The metadata about the filesystem, like the last time it was accessed - in this case, it will have been our ls that caused the problem.

To fix that, set the filesystem on the slave to be read-only:

# zfs set readonly=on slavepool

Setting readonly means that we can’t change the filesystem on the slave by normal means - that is, I can’t change the files or metadata (modification times and so on). It also means that operations that would normally update metadata (like our ls) will silently perform their function without attempting to update the filesystem state.

In essence, our slave filesystem is nothing but a static copy of our original filesystem. However, even when enabled to readonly, a filesystem can have snapshots applied to it. Now it’s read only, re-run the initial copy:

# zfs send opt@snap1 |ssh mc@slave pfexec zfs recv -F slavepool

Now we can make changes to the original and replicate them over. Since we’re dealing with MySQL, let’s initialize a database on the original pool. I’ve updated the configuration file to use /opt/mysql-data as the data directory, and now I can initialize the tables:

# mysql_install_db --defaults-file=/etc/mysql/5.0/my.cnf --user=mysql

Now, we can synchronize the information to our slave machine and filesystem by creating another snapshot and then doing an incremental zfs send:

# zfs snapshot opt@snap2

Just to demonstrate the efficiency of the snapshots, the size of the data created during initialization is 39K:

# du -sh /opt/mysql-data/
  39K	/opt/mysql-data

If I check the size used by the snapshots:

# zfs list -t snapshot
NAME                                         USED  AVAIL  REFER  MOUNTPOINT
opt@snap1                                     47K      -  1.03G  -
opt@snap2                                       0      -  1.05G  -

The size of the snapshot is 47K. Note, by the way, that it is 47K in snap1, because currently snap2 should be more or less equal to our current filesystem state.

Now, let’s synchronize this over:

# zfs send -i opt@snap1 opt@snap2|ssh mc@192.168.0.93 pfexec zfs recv slavepool

Note we don’t have to force the operation this time - we’re synchronizing the incremental changes from what are identical filesystems, just on different systems.

And double check that the slave has it:

# ls -al /slavepool/mysql-data/

Now we can start up MySQL, create some data, and then synchronize the information over again, replicating the changes. To do that, you have to create a new snapshot, then do the send/recv to the slave to synchronize the changes.

The rate at which you do it is entirely up to you, but keep in mind that if you have a lot of changes then doing it as frequently as once a minute may lead to your data becoming behind the because of the time taken to transfer the filesystem changes over the network - running snapshot with MySQL running in the background still takes comparatively little time.

To demonstrate that, here’s the time taken to create a snapshot mid-way through a 4 million row insert into an InnoDB table:

# time zfs snapshot opt@snap3

real    0m0.142s
user    0m0.006s
sys     0m0.027s

I told you it was quick

However, the send/recv operation took a few minutes to complete, with about 212MB of data transferred over a very slow network connection, and the machine was busy writing those additional records.

Ideally you want to set up a simple script that will handle that sort of snapshot/replication for you and run it past cron to do the work for you. You might also want to try ready-made tools like Tim Foster’s zfs replication tool, which you can find out about here. Tim’s system works through SMF to handle the replication and is very configurable. It even handles automatic deletion of old, synchronized, snapshots.

Of course, all of this is useless unless once replicated from one machine to another we can actually use the databases. Let’s assume that there was a failure and we needed to fail over to the slave machine. To do:

1. Stop the script on the master, if it’s still up and running.
2. Set the slave filesystem to be read/write:
   

   # zfs set readonly=off slavepool 

3. Start up mysqld on the slave. If you are using InnoDB, Falcon or Maria you should get auto-recovery, if it’s needed, to make sure the table data is correct, as shown here when I started up from our mid-INSERT snapshot:
   

   InnoDB: The log sequence number in ibdata files does not match
   InnoDB: the log sequence number in the ib_logfiles!
   081109 15:59:59  InnoDB: Database was not shut down normally!
   InnoDB: Starting crash recovery.
   InnoDB: Reading tablespace information from the .ibd files...
   InnoDB: Restoring possible half-written data pages from the doublewrite
   InnoDB: buffer...
   081109 16:00:03  InnoDB: Started; log sequence number 0 1142807951
   081109 16:00:03 [Note] /slavepool/mysql-5.0.67-solaris10-i386/bin/mysqld: ready for connections.
   Version: '5.0.67'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
   

Yay - we’re back up and running. On MyISAM, or other tables, you need to run REPAIR TABLE, and you might even have lost some information, but it should be minor.

The point is, a mid-INSERT ZFS snapshot, combined with replication, could be a good way of supporting a hot-backup of your system on Mac OS X or Solaris/OpenSolaris.

Probably, the most critical part is finding the sweet spot between the snapshot replication time, and how up to date you want to be in a failure situation. It’s also worth pointing out that you can replicate to as many different hosts as you like, so if you want wanted to replicate your ZFS data to two or three hosts, you could.

MySQL on Solaris Best Practices Presentation

A couple of weeks ago I was at the MySQL European Customer Conference in London, where I was presenting my talk on deploying MySQL on Solaris best practices. You can download a copy of the presentation here: MySQL on Solaris Best Practices.

I cover both choosing the best release version, using tricks like mtmalloc (the threaded malloc library) before moving on to UFS and ZFS tricks, using DTrace and MySQL Cluster and Sun Cluster.

MySQL Documentation Myths

There are a few myths surrounding the MySQL documentation and how it works, and I thought I’d try and dispel some of those myths if I can. If you have any more questions or misunderstandings you want clarified, let me know.

Myth:

MySQL Documentation is written by the developers.

Reality

MySQL Documentation is written by a dedicated team of writers with help and input from the developers. There are four main writers, Paul DuBois, Tony Bedford, Jon Stephens, and MC Brown (me!), plus our Team Lead, Stefan Hinz.

All the documentation staff are employed full time for the sole purpose of writing documentation. Sure, some of us get involved in other things too, but that’s basically the nature of the job. Some of us simply cannot help ourselves.

Myth

Docs team members are just writers and have no technical expertise.

Reality

It’s tempting to come back with a rude response to this one, but it is a comment I heard from someone at a conference. The reality is that all of us have some technical background, unsurprisingly often with MySQL. Some of us have expertise elsewhere too. Speaking only for myself, go look at MCslp.com for more info. If you want details, feel free to ask, but know that this myth is definitely busted.

Myth

The documentation is updated very rarely.

Reality

Our main tree, mysqldoc, is publicly available, and if you want to go view the commits to that tree, please feel free. It doesn’t take much to see that we commit to that tree all day, and every time we change something, the documentation gets rebuilt. How frequently? Well, on a typical day we will generate 10-15 new versions of each reference manual. It’s actually difficult to rebuild more frequently than that due to the sheer size of the documentation.

If you want to check the build date of the documentation, check the intro/preface of each document. The build and build date information is included there.

Myth

The MySQL documentation is small and unused.

Reality

You’d be amazed how many people need to be told RTFM, but a surprising number of people who criticize the MySQL documentation have actually never read it, or, they looked at it years ago and haven’t bothered to look recently because they couldn’t find what they were looking for before.

The reality is that our documentation is over 2000 pages per reference manual, which means over 10,000 pages now just for MySQL. There are hundreds more pages on the GUI tools, Workbench, Cluster/NDBAPI, and the Enterprise Monitor.

As to the popularity, the hits to the online pages of our manual exceed the hits to every other section of the MySQL website by a significant factor. For the downloadable formats we get an average of 200,000 downloads in all the various formats each month, with occasional spikes up to 800,000. For the online manuals, the documentation pages make up about 45% of all the traffic on mysql.com. Or to say it another way, we account for almost half of all the web traffic that MySQL receives, including downloads.

In short, we have no shortage of interested readers.

Myth

Docs team don’t read comments

Reality

Actually, we all get an email each time you post a comment and all of us will read it, determine whether it is suitable, useful, or (occasionally) spam, and either ignore it, delete it, or comment on it accordingly. Often that will happen within minutes of your leaving the comment. If there’s a non-standard reply to your comment, you’ll get that too.

Now, we are aware that the comments system has it’s faults. For one, we have one comment system for all the different versions of the manual, which means comments can be confusing and even misleading. We’re fixing that. We’re also trying to address the problem that some comments are really tips, while others are just plain comments and observations.

Any other comments or criticisms, let us know. We may not be unaware of the problem, but if we know your pain we can do something about it.

Myth

Docs team don’t accept bugs or corrections

Reality

You can report a bug or correction to us using the standard bugs.mysql.com, or drop us an email to docs@mysql.com.

Myth

Docs are ‘closed source’

Reality

The docs are not closed source - you can download the DocBook XML and the files and tools required to build them (well, beyond the XML parsers, Perl, and other bits and pieces). You can get hold of the repos (via SVN), on the Tech Resources page.

That said, we don’t allow anybody to commit changes, but see the response above for information on how to provide changes and fixes. Again this is something we are working to improve on.

Myth

MySQL Documentation is not distributable

Reality

This mostly comes out of the fuss around Debian dropping the man pages from their MySQL distributions You can see the description why here: MySQL Documentation and Debian/Ubuntu.

The short answer is that it is a mis-understanding in our license for the documentation, which is not released under the same license as MySQL. You can provide documentation if you provide MySQL, but not on it’s own. The reason for this is that our documentation is updated so regularly that we want to ensure that we only get genuine, up to date, versions of our documentation out there. Trust me, do a search for MySQL and some term and you will find versions of the manual that are months, or even years out of date, which is no help to anybody.

It’s about trying to make our documentation readable and usable and not misleading.

OK that’s enough myths busted today, but if you hear any more, or just have additional questions, feel free to ask.

DimDim and MySQL University

Stop the press! My boss, Stefan Hinz, has just started blogging, with his first post here: Using NetBeans with MySQL.

So who is he? Well, Stefan is the guy that keeps the rest of us in the docs team in check and makes sure we do what we’re asked, when we’re are asked and that all of the machinery, legalities and management tasks happen in the background. Without him we really couldn’t function as effectively as we do.

It’s wonderful to see some other Docs team members getting in on the act (to be fair to the rest of the team, Jon is also a blogger). We are all writers, you would think the blogging would come as a natural extension.

Behind the tease is the simple fact that the improved system for MySQL University I was talking about is getting a trial run this week.

We’ve been trying out Dimdim for web conferencing with David van Couvering and I have to say I’m pretty impressed.

We chatted, we tried out screen-sharing, presentations and the whiteboard functionality and it all worked really nicely.

We’re going to be using it for the MySQL University session this week, Using MySQL with NetBeans. Space will be limited, but feel free to join us if you can.

Using the MySQL Doc source tree

I’ve mentioned a number of times that the documentation repositories that we use to build the docs are freely available, and so they are, but how do you go about using them?

More and more people are getting interested in being able to work with the MySQL docs, judging by the queries we get, and internally we sometimes get specialized requests.

There are some limitations - although you can download and access the docs and generate your own versions in various formats, you are not allowed to distribute or supply that iinformation, it can only be employed for personal use. The reasons and disclaimer for that are available on the main page for each of the docs, such as the one on the 5.1 Manual.

Those issues aside, if you want to use and generate your own docs from the Subversion source tree then you’ll need the following:

• Subversion to download the sources
• XML processors to convert the DocBook XML into various target formats; we include DocBook XML/XSLT files you’ll need.
• Perl for some of the checking scripts and the ID mapping parts of the build process
• Apache’s FOP if you want to generate PDFs, if not, you can ignore.

To get you started, you must download the DocBook XML source from the public subversion repository. We recently split a single Subversion tree with the English language version into two different repositories, one containing the pure content, and the other the tools that required to build the docs. The reason for that is consistency across all of our repositories, internally and externally, for the reference manual in all its different versions.

Therefore, to get started, you need both repositories. You need check them out into the same directory:

$ svn checkout http://svn.mysql.com/svnpublic/mysqldoc
$ svn checkout http://svn.mysql.com/svnpublic/mysqldoc-toolset

Assuming you have the downloaded the XML toolkit already, make sure you have the necessary Perl modules installed. You’ll need Expat library, and the following Perl modules:

• Digest::MD5
• XML::Parser::PerlSAX
• IO::File
• IO::String

If you have CPAN installed, you can install them automatically using perl -MCPAN -e 'install modulename', or use your respective package management system to install the modules for you. You’ll get an error message if there is something missing.

OK, with everything in place you are ready to try building the documentation. You can change into most directories and convert the XML files there into a final document. For example, to build the Workbench documentation, change into the Workbench directory. We use make to build the various files and dependencies.

To build the full Workbench documentation, specify the main file, workbench, as the target, and the file format you want to produce as the extension. For example, to build a single HTML file, the extension is html. I’ve included the full output here so that you can see the exact output you will get:

make workbench.html
set -e; \
../../mysqldoc-toolset/tools/dynxml-parser.pl \
--infile=news-workbench-core.xml --outfile=dynxml-local-news-workbench.xml-tmp-$$ --srcdir=../dynamic-docs --srclangdir=../dynamic-docs; \
mv dynxml-local-news-workbench.xml-tmp-$$ dynxml-local-news-workbench.xml
make -C ../refman-5.1 metadata/introduction.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
../../mysqldoc-toolset/tools/idmap.pl refman/5.1/en introduction.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
make -C ../refman-5.1 metadata/partitioning.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
../../mysqldoc-toolset/tools/idmap.pl refman/5.1/en partitioning.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
make -C ../refman-5.1 metadata/se-merge.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
../../mysqldoc-toolset/tools/idmap.pl refman/5.1/en se-merge.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
make -C ../refman-5.1 metadata/se-myisam-core.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
../../mysqldoc-toolset/tools/idmap.pl refman/5.1/en se-myisam-core.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
make -C ../refman-5.1 metadata/sql-syntax-data-definition.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
../../mysqldoc-toolset/tools/idmap.pl refman/5.1/en sql-syntax-data-definition.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/refman-5.1′
make -C ../workbench metadata/documenting-database.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en documenting-database.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/foreign-key-relationships.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en foreign-key-relationships.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/forward-engineering.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en forward-engineering.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/grt-shell.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en grt-shell.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/images.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en images.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/installing.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en installing.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/layers.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en layers.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/notes.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en notes.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/plugins.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en plugins.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/printing.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en printing.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/reference.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en reference.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/reverse-engineering.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en reverse-engineering.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/server-connection-wizard.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en server-connection-wizard.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/stored-procedures.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en stored-procedures.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/tables.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en tables.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/text-objects.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en text-objects.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/tutorial.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en tutorial.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/validation-plugins.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en validation-plugins.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
make -C ../workbench metadata/views.idmap
make[1]: Entering directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
../../mysqldoc-toolset/tools/idmap.pl workbench//en views.xml
make[1]: Leaving directory `/nfs/mysql-live/mysqldocs/working/Docs/mysqldoc/workbench’
XML_CATALOG_FILES=”../../mysqldoc-toolset//catalog.xml” xsltproc –xinclude –novalid \
–stringparam repository.revision “`../../mysqldoc-toolset/tools/get-svn-revision`” \
–param map.remark.to.para 0 \
–stringparam qandaset.style “” \
../../mysqldoc-toolset/xsl.d/dbk-prep.xsl workbench.xml > workbench-prepped.xml.tmp2
../../mysqldoc-toolset/tools/bug-prep.pl < workbench-prepped.xml.tmp2 > workbench-prepped.xml.tmp
../../mysqldoc-toolset/tools/idremap.pl –srcpath=”../workbench ../gui-common ../refman-5.1 ../refman-common ../refman-5.0″ –prefix=”workbench-” workbench-prepped.xml.tmp > workbench-prepped.xml.tmp2
mv workbench-prepped.xml.tmp2 workbench-prepped.xml
rm -f workbench-prepped.xml.tmp
XML_CATALOG_FILES=”../../mysqldoc-toolset//catalog.xml” xsltproc –xinclude –novalid \
–stringparam l10n.gentext.default.language en \
–output workbench.html-tmp \
../../mysqldoc-toolset/xsl.d/mysql-html.xsl \
workbench-prepped.xml
../../mysqldoc-toolset/tools/add-index-navlinks.pl workbench.html-tmp
mv workbench.html-tmp workbench.html


There’s lots in the output above, and I’ll describe the content as best I can without going in to too much detail in this piece.

First off, the make triggers some dependencies, which are the creation of a number of ‘IDMap’ files. These files contain information about the content of the files and are used to help produce valid links in to other parts of the documentation. I’ll talk about ID mapping more in a later post.

The next stage is to build the ‘prepped’ version of the documentation, which combines all of the individual files into one large file and does some pre-processing to ensure that we get the output that we want.

The next is the remapping. This uses the IDMap information built in the first stage and ensures that any links in the documentation that go to a document we know about, like the reference manual, point to the correct online location. It is the ID mapping (and remapping) that allows us to effectively link between documents (such as the Workbench and Refman) without us having to worry about creating a complex URL link. Instead, we just include a link to the correct ID within the other document and let the ID mapping system do the rest.

The final stage takes our prepped, remapped, DocBook XML source and converts it into the final XML using the standard DocBook XSL templates.

One of the benefits of us using make is that because we build different stages in the build process, when we build another target, we dont have to repeat the full process. For example, to build a PDF version of the same document, the prepping, remapping and other stages are fundamentally the same, which is why we keep the file, workbench-prepped.xml. Building the PDF only requires us to build the FO (Formatting Objects) output, and then use fop to turn this into PDF:

$ make workbench.pdf
XML_CATALOG_FILES="../../mysqldoc-toolset//catalog.xml" xsltproc --xinclude --novalid \
--output - ../../mysqldoc-toolset/xsl.d/strip-remarks.xsl workbench-prepped.xml \
| XML_CATALOG_FILES="../../mysqldoc-toolset//catalog.xml" xsltproc --xinclude --novalid \
--stringparam l10n.gentext.default.language en \
\
--output workbench.fo-tmp ../../mysqldoc-toolset/xsl.d/mysql-fo.xsl -
Making portrait pages on USletter paper (8.5inx11in)
mv workbench.fo-tmp workbench.fo
set -e; \
if [ -f ../../mysqldoc-toolset/xsl.d/userconfig.xml ]; then \
../../mysqldoc-toolset/tools/fixup-multibyte.pl workbench.fo workbench.fo.multibyte; \
mv workbench.fo.multibyte workbench.fo; \
fop -q -c ../../mysqldoc-toolset/xsl.d/userconfig.xml workbench.fo workbench.pdf-tmp > workbench.pdf-err; \
else \
fop -q workbench.fo workbench.pdf-tmp > workbench.pdf-err; \
fi
mv workbench.pdf-tmp workbench.pdf
sed -e ‘/hyphenation/d’ < workbench.pdf-err
[ERROR] Areas pending, text probably lost in lineWhen synchronizing the database, table comments were not updated. However, column comments worked as expected.
rm -f workbench.pdf-err


You can see in this output that the prepping and remapping processes don’t even take place - the process immediately converts the prepped file into FO and then calls fop.

That completes our whirlwind tour of the basics of building MySQL documentation, I’ll look at some more detailed aspects of the process in future blog posts. Until then, you might want to read our metadocs on the internals in MySQL Guide to MySQL Documentation.

Tags: DocBook, Documentation, MySQL

MySQL on Solaris at the MySQL European Customer Conference

I’m speaking at the MySQL European Customer Conference this week (Thursday, 23rd), on the topic of the best deployment practices for using MySQL on Solaris.

I’ll be covering a number of topics, including:

• Overview of MySQL availability on Solaris
• General tips for MySQL on Solaris
• MySQL on ZFS
• DTrace and the new DTrace Probes
• Using MySQL with containers and zones
• Using Sun Cluster and MySQL Cluster for HA

Some of the material I’ve already covered before (see my presentation at the London Solaris User’s Group, but most of the content will be new and more focused than the top level LOSUG presentation.

There are similar presentations being presented at the Paris and Munich conferences by Eric Bezille and Franz Haberhauer, and we’re all presenting the same basic content as we’ve been working together on the presentation.

If you are in the region and can make it to the conference, I suggest you come. Not just for my presentation, there are other topics including performance tuning, HA, MySQL Proxy and using MySQL and memcached.

Replicating multiple masters to one slave

As standard, MySQL allows replication from one master to multiple slaves, and that is a common scale-out scenario, but there have been a few comments recently, and some longer standing queries about having a setup that works the other way round, that is, multiple slaves replicating into a single master.

This a common enough scenario in data logging systems, where the data is collected locally and then distributed up to a central database, or in EPOS (Electronic Point of Sale) systems where you want the transactions logs from the tills logged up to the database at head office. There are many other situations where you want that merging of information.

Although MySQL doesn’t support what is called ‘multiple master, single slave’ solution, you can simulate the general approach by using a combination of replication and federated tables.

Replication allows for different table types on the master (the source of the data) and the slave. There are many advantages to this, for example, using InnoDB on the master to take advantage of transactions, while using MyISAM on the slave for read performance.

Federation allows you to access the tables of a remote server as if it were a local table. You can set up a federated table to access a remote table from as many machines as you like. That means that you can have two, or more, MySQL instances set up to use the remote table using the federated engine. You can execute any queries you like on the remote table, but you need to take care when using multiple hosts to access the remote table. Particularly when doing INSERT from multiple hosts, using InnoDB, Falcon, Maria or another table that supports multiple writers can be a good idea, although I’ll cover some workarounds for that later.

Using federated gives us the ability to write to the same table from multiple hosts, but you dont want to read and write from the same remote table all the time, especially if on your local machine (your till, or data collector) you want to be able to run your own queries.

This is where the replication fits in, if you set up replication from the master to another instance of MySQL, let’s call it ‘Fed Slave’ (which works both ways). On the Fed Slave, you configure the table or tables that you want to merge on the final ‘Slave’ machine to be federated tables. What happens is that data is replicated from the master to the Fed Slave, and on Fed Slave the queries are sent to the Merge Slave via federation. You can probably see this more clearly in the figure below.

To re-iterate:

1. INSERT on Master 1 is replicated to Fed Slave 1
2. Fed Slave 1 executes the INSERT on a Federated table which points to Merge Slave
3. Merge Slave executes the federated statement on its local table

Each Fed Slave is relatively lightweight - all it’s doing is executing a statement and sending the statement over the network to the Merge Slave, so you could run it on the same machine as Master 1.

There are few problems with this design:

1. Updating the same federated table from multiple hosts can get messy. There are a few ways you can get get round this, one is to stop the query execution on the slaves and only allow them to run during a set period of time. For example, let Fed Slave 1 execute the queries in the log from 1am to 2am, and Fed Slave 2 from 2am to 3am, and so on.
2. Federation doesn’t get round the problems of duplicate IDs - if you try to run a statement on a federated table that inserts a duplicate ID it will fail just as will locally. You can get round this by making sure that the tables that hold the merge data on your Merge Slave dont have unique ID constraints, and that your Masters and all the table definitions contain a field to identify the source of the data in each case.
3. Load can be an issue. One of the reasons I suggested InnoDB/Falcon/Maria is to help get round the multiple-insert and locking that is normally applied, but the very nature of the system means that locks and delays might still occur. You can’t eliminate it, but you can ease it.

I’ve tried and used this method in a number of situations, actually not for the reasons given above, but for performance logging from multiple hosts onto one. I’ll be honest and say that I’ve never seen a problem, but, at the same time, the type of data that I am collecting means that I would have been unlikely to notice a missing data point or two.

MySQL University: Checking Threading and Locking With Helgrind

This Thursday, Stewart Smith will give a MySQL University session:

Checking Threading and Locking With Helgrind

Note that this particular session starts 9:00 BST / 10:00 CET /
18:00 Brisbane/Melbourne

Stewart is always enjoyable to listen to, both because he knows his stuff and because he is a really fun guy (heads up for the MySQL Conference 09, the Monty Taylor/Stewart Smith double act at this years conference was one of the most interesting and information sessions I went to).

Please register for this session by filling in your name on the session
Wiki page. Registering is not required but appreciated. That Wiki page
also contains a section to post questions. Please use it!

MySQL University sessions normally start at 13:00 UTC (summer) or 14:00
UTC (winter); see: MySQL University for more time zone information.

Those planning to attend a MySQL University session for the very first
time should probably read the instructions for attendees,
Instructions for Attendees.

See Upcoming Sessions for the complete list of upcoming University sessions.

How to analyze memory leaks on Windows

We use valgrind to find memory leaks in MySQL on Linux. The tool is a convenient, and often enlightening way of finding out where the real and potential problems are location.

On Windows, you dont have valgrind, but Microsoft do provide a free native debugging tool, called the user-mode dump heap (UMDH) tool. This performs a similar function to valgrind to determine memory leaks.

Vladislav Vaintroub, who works on the Falcon team and is one of our resident Windows experts provides the following how-to for using UMDH:

1. Download and install debugging tools for Windows from here
   MS Debugging Tools
   Install 64 bit version if you’re on 64 bit Windows and 32 bit version
   otherwise.

2. Change the PATH environment variable to include bin directory of Debugging tools.
   On my system, I added
   C:\Program Files\Debugging Tools for Windows 64-bit to the PATH.

3. Instruct OS to collect allocation stack for mysqld with gflags -i
mysqld.exe +ust.
   On Vista and later, this should be done in “elevated” command prompt,
   it requires admin privileges.

   Now collect the leak information. The mode of operation is that: take the
   heap snapshot once, and after some load take it once again. Compare
   snapshots and output leak info.

4. Preparation : setup debug symbol path.
   In the command prompt window, do

   set _NT_SYMBOL_PATH= srv*C:\websymbols*http://msdl.microsoft.com/download/symbols;G:\bzr\mysql-6.0\sql\Debug

   Adjust second path component for your needs, it should include directory
   where mysqld.exe is.

5. Start mysqld and run it for some minutes

6. Take first heap snapshot

   umdh -p:6768 -f:dump1

   Where -p: actually, PID of my mysqld was 6768.

7. Let mysqld run for another some minutes

8. Take second heap snapshot

   umdh -p:6768 -f:dump2

9. Compare snapshots

   umdh -v dump1 dump2 > dump.compare.txt

10. Examine the result output file. It is human readable, but all numbers are
    in hex, to scare everyone except geeks.

11. gflags -i mysqld.exe -ust

    Instruct OS not to collect mysqld user mode stacks for allocations
    anymore.

These are 10 steps and it sounds like much work, but in reality it takes 15
minutes first time you do it and 5 minutes next time.

Additional information is given in Microsoft KB article about UMDH
KB 268343.

Tags: debugging, MySQL, Windows

Book: Intellectual Property and Open Source - the solution to IANAL

I’m reading Intellectual Property and Open Source by Van Lindberg at the moment, and despite being about a relatively dry topic, I must admit that it’s a fascinating read.

Van Lindberg introduces the book by talking about the comments that end up on Slashdot.org, almost certainly prefixed by the expression IANAL (I Am Not A Lawyer) where people defend, discuss, and rip people up about the legalities of open source and the various licenses. Van Lindberg also talks about how he spends much of his time translating the contents of various legal documents into engineer speak and back again.

Despite being a proponent and long time user of free software and open source for the best part of my working life, I’ll admit to being completely ignorant of many of the issues. This isn’t through lack of interest, but I’d rather leave those discussions and decisions to people who know, and it’s clear that Van Lindberg not only knows the subject, but he also knows how to make it interesting to those of us who actually have to work within the confines of rules and regulations.

I’m still reading and learning a lot of the ins and outs of copyright, company agreements, and individual licenses and details. There’s a lot of material and detail included here.

I’ll have a full review when I’ve finished. Until then, if you have even a passing interest in the various licensing, legal and IP issues with open source, check out the book for a proper read.

MySQL University - quick survey

MySQL University has been running for the last 18 months, and we’ve covered a wide range of topics, from the internals of MySQL right up to Amazon’s EC2, using MySQL in the Solaris/OpenSolaris Webstack and a description of the forthcoming MySQL Online Backup.

Personally, I think they’re great. Obviously many times I am scribe and am there for the sessions, but I listen to lots of the sessions anyway, and I’m yet to be disappointed by the content. What’s really great is that in all the cases the person you are listening to is probably the person that either developed, or helped drive development of the particular function, or, in the case of some of the external tools (EC2, for example), these guys are expert in it. The experience is not quite as thrilling as attending the MySQL User Conference, but the content is just the same.

The problem is that despite all the work we do to get the presenters, interesting topics, and promotion of the upcoming sessions, we don’t always get as many attendees as we want or expect.

So, I’m wondering why this should be the case. We know that the current presentation system is not ideal (and we’re working on that), but I’m interested to hear people’s opinions on MySQL University. If you want to help shape the future of MySQL University, then comment here, and either answer the questions below, or make up your own.

• Have you attended any MySQL University sessions. How many?
• How would you rate the sessions generally? A simple good or bad will do
• If you haven’t attended any sessions, or don’t regularly attend them, why not?
• Have you ever looked at/listened to the past sessions that provide on MySQL Forge?

Please, I’m interested to hear.

Tags: MySQL

MySQL on i5/OS

i5/OS doesn’t immediately strike you as the most natural environment for running MySQL, but in fact, there some advantages and benefits of making use of the hardware and i5/OS environment. The System i environment used with i5/OS is scalable, and the i5/OS itself provides lots of benefits over the control and separate of work.

Obviously another key advantage is that if you are already using i5/OS for your application, then being able to plug in MySQL into that equation on the same machine makes a big difference. For those companies and organizations that already have a business application on their server, you can use MySQL in combination with ODBC or more direct interfaces such as PHP to provide a web interface to your business application all in the same box.

MySQL works through PASE (Portable Application Solutions Environment) which allows AIX applications to run directly on i5/OS through a direct application binary interface.

As a supported platform for MySQL 5.0 we obviously have instructions for installing MySQL into your i5/OS environment. Once installed, MySQL on i5/OS works just like any other MySQL installation.

However, if you want a more complete view of the support, environment, and deployment of MySQL on i5/OS and more detailed instructions for setting PASE and your system to accept MySQL, then check out the IBM Redbook Discovering MySQL on IBM i5/OS.

MySQL Topics now added directly to the reference manual

Last week I mentioned that we had added a topic-based interface to the MySQL documentation to make it easier to go to specific topics, identified either by your needs, user type or technology.

It occurred to me at the end of the week that the information is just as useful when reading the documentation, so you can go direct to a topic within the online reference manual, rather than trying to work out what chapter it is in.

This works in all reference manuals, whether you are viewing the online HTML version:

Topics embedded in the reference manual, HTML

It also works in all the offline versions, including HTML and PDF, as here:

Topics in the reference manual, PDF

For those topics that are outside the normal scope of the manual, such as all the standalone guides, the links take you to the online version of those guides directly.

MySQL 5.1 Cluster DBA Certification Study Guide now everywhere

If you want to get certified for MySQL Cluster then there is only one guide that you need, the MySQL 5.1 Cluster DBA Certification Study Guide.

Since the move to Sun, we have changed the method that you can get the title. You can now order a ‘print-on-demand version of the guide directly from Vervante. You can go ahead and order a copy now using this link.

If you think you want the book, or just want a read anyway, but don’t want to commit to buying the full guide, then you can read three of the key chapters in their entirety on docs.sun.com. You can read the chapters online here.

As always, let us know what you think, whether thats about the chapters on docs.sun.com, the guide itself, or what you think of the printed Vervante guide.

Wanted: GUI developer for MySQL Enterprise Monitor

One of the great things about working on great products is that you get to meet such intelligent and interesting people. I can apply that to everybody that I work with, but there are some teams where not only are they working on great products, they are also all great people just to spend time with.

I have had the good fortune of working with the MySQL Enterprise Monitor team as an advisor, and more recently in writing the documentation, for the last 18 months. We’ve had some great fun at meetings in Amsterdam, Heidelberg, Santa Cruz and recently Riga. In the meeting rooms we are professional, but fun. But in the evenings we’ve gone out and just had plain good fun. Unless you’ve experienced a full day, or even week, of non-stop meetings for 12 hours a day you have no idea how important it is to kick back in the evenings. That, as a team, we are still able to have a good time at the end of each day while still being in the same room together is a good indication of how well we all get on.

Why am I telling you this? Because there is an opportunity to come and work for MySQL Enterprise Monitor team as a GUI developer, and if you are going to join us, you need to be as much fun at the dinner table as you are in the meeting room.

We are looking for talented people, obviously, and you are going to need to be good both with the design and the programming aspects of the GUi development. We use frameworks like Hibernate and Spring, and build our interfaces using DHTML and AJAX. If you know about MySQL and scale-up/out environments, that would be even better.

If it sounds like a good fit for you, and you just happen to want to have a good time to boot, apply here.

What they don’t tell you on the official pages is how much you will be expected to also get on with the rest of the team. It’s really important, because when we have those meetings we’ll be looking to the new guy to provide us with entertainment and interesting stories, at least until the next guy comes along.

MySQL Documentation by Topic

The MySQL documentation is huge. In fact, it can sometimes be difficult to get a handle on exactly how big. The main reference manual is large by most documentation standards, the MySQL 6.0 reference manual is almost 2400 pages in length now. Keep in mind that we also have 4.1, 5.0, 5.1 and 5.1-Maria manuals, in addition to the GUI tools, NDBAPI and other material that we produce and you realize that there are thousands of pages of documentation to wade through, if you want.

We’ve toyed for ages with different methods of splitting up the manual and making it more accessible, but the reality is that different manuals introduce other issues, and wouldn’t actually solve the issue of finding information in the online manual. The situation isn’t helped by the fact that we have traditionally listed our manuals by their titles, not, necessarily, by what they contain and what you might want to know.

For an example, imagine that you are a Java developer working with MySQL. If you went to the main documentation page how would you know to look in the reference manual, then the Connectors and APIs chapter, and finally the Connector/J section. Let’s ignore, for the moment, the issue of different versions.

This weekend we have hopefully solved all of that by introducing a new table at the top of the page that provides you with a direct interface into parts of the manual by topic. You can see a screenshot of the new topic table here:

MysQL Documentation by Topic

The topics are divided by the potential user that you are - for example, Developer, Administrator, etc - and some are by functionality or feature, such as HA/Scalability or the Connectors.

There is a mixture of documentation provided through the table:

• Some are links straight into a corresponding section of the 5.1 reference manual. For example, the Spatial Extensions link takes you to that chapter in the manual.
• Some are standalone guides. We have had the MySQL Version Reference and Connector manuals as standalone for a while now, and you can download HTML and PDF versions of those guides like before. Others are new. The Security Guide, for example, takes information straight from the reference manual and puts it into a new standalone, downloadable, guide.

All of the documentation is up to date - the standalone guides are not snapshots of the documentation. If we update the main reference manuals, the standalone guides will be rebuilt too.

Already we have a pretty good list of top-level topics here, and standalone guides. Windows users, for example, should appreciate the new Windows-specific deployment guides.

But we want more ideas for standalone guides, and more ideas for topics and sections of the manual to be highlighted on this page. What don’t we have listed in that table that you want to see. Let me know!