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.

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.

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.

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

Podcast Producer Variables

The first of a new series of articles on using and extending the functionality of Apple’s Podcast Producer has just been published (see Podcast Producer: Anatomy of a Workflow).

One of the things that you might find useful when working with Workflows in Podcast Producer are the properties that are defined automatically when a podcast is submitted for processing. These runtime properties are used to specify information such as the source file name and job name. You need these within the action specification to select the input file, Podcast title, description and other parameters to process the content.

The combination of standard properties, and job specific properties, are combined together into a file called properties.plist that becomes part of the Workflow specification that is submitted for processing. Because global properties can change or be modified, by copying the specification into the properties.plist file during assembly, the system can ensure that the configuration at the time of submission of the podcast is used. This helps to prevent problems if the job gets queued and the configuration changes between submission and processing.

The dynamic properties submitted as part of the job will differ depending on the submission type, but the main properties generated are shown in the table below.

These dynamic properties are vital to the execution of an individual action as they provide the unique properties required to process an individual podcast processing request.

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!

Solaris 08/07 impressions

New versions of Solaris keep coming out apace, and with each new version we see improvements and enhancements, both from the core Solaris group and from the cooperation with the OpenSolaris teams.

Solaris 10 8/07 came out last year, but it takes a while to go through and look at all of the new features, and in the time it’s taken me to fully understand all the new stuff, they’ve come out with another new version, but the functionality in the 8/07 release warrants some closer inspection. You can get the full details with the official documentation.

First up, this is one of the the first releases where I feel comfortable using it pretty much out of the box without modification. Most of the tools and functionality are available with a standard install. You get Firefox 2.0 and Thunderbird 2.0 as standard, and combined with the improvements to the X Window System, we have an OS that can be used as a desktop without any additional installs.

There are still some things I prefer to re-install (perl, for example), but the point is you no longer have to install many things. With Sun Studio being available, there’s little reason not to use Solaris 10 as a good replacement for Linux - in fact, one of the other elements I want to talk about might help there.

Of course, its not all plain sailing. One of the problems I encountered is that I installed onto a machine with three identical drives - but when prompting you to partition disks, it shows you all of the disks without any kind of indication which one is which. You just get the size of the volume to choose from (a little difficult to choose when your machine has three identical drives).

Luckily this was an empty machine that I could trash, but I wouldn’t want to identify the drive during installation just based on the partitions each device had. Actually, it turned out worse than this - after installation, Solaris had actually ended up on my second HD (the first attached to the second IDE controller), not my first. I had to swap the disks around to get it started properly. Really it’s a minor issue, but one to be wary of.

One of the big improvements throughout are the changes and enhancements to the ZFS file system. One of my favourites is that you can now create a ZFS pool and then share it as an iSCSI device to other systems. This means that if I had a big server with loads of space, I could create a ZFS pool, get the benefits of ZFS on the server side, and share portions of the disk for storage over to other machines.

Enabling iSCSI sharing is just a case of setting the shareiscsi

# zfs set shareiscsi=on mainpool/ishare

On a potential client, you add the server sharing the pool to the list of iSCSI targets, and then run format. With iSCSI, you are effectively sharing the physical device, so it just appears like a another disk, which you partition and organize as you want. I can see a real benefit here for providing users with a dedicated disk that they can organize how they like, while still giving you control the disk space and allocation at the server end.

ZFS also has a huge range of other improvements. There’s no ZFS booting in this release (it’s in b84 of the regular OpenSolaris releases), but ZFS support and facilities just keeps going on and up.

Using a single OS is often no longer a reasonable choice. Different companies release their products on different OS, and sometimes you just have to install another OS. Virtualization is one solution (and there’s plenty of news about that in later builds of Solaris), but in 8/07 we have another alternative, BrandZ.

BrandZ builds on Solaris containers - logical divisions in the running environment that allow you to divide, allocate and isolate resources on a system into discrete, well, containers. Each container is effectively running another instance of Solaris (although it’s not as resource hungry as I make that sound).

BrandZ extends that container functionality with an additional layer that provides binary compatibility for other operating systems, without going to the same level of complexity or resource requirements as the virtualization options.

There are limits, of course, to that - you are not running a copy of the corresponding OS kernel, instead, an interface layer is providing the compatibility between the linux kernel and library calls and the corresponding interfaces on the Solaris side.

You can’t run device drivers, and you also can’t run applications that require access to the graphics card; you have to run any X applications by using an X server in the global zone. That means you can’t run X.org in Linux, but since you can attach to the main X service, it still means you can run Firefox and other applications running as Linux binaries but displaying on your main host (or indeed, anywhere with a running X server).

For the most part, that means you can run Linux binaries in a BrandZ zone without resorting to full virtualization. It works fine- I was able to run a whole range of apps without any problems. The downside? Currently it’s x86 only. I’d love to be able to run SPARC Linux apps on my SPARC servers, for testing purposes only it would fill a hole I otherwise have to fill with another machine running another OS.

BrandZ is a convenient way to run Linux applications without running a full Linux distro either on another machine or in a virtualization. This means it will appeal either to the user with a Linux binary legacy application, or the casual Linux application user without the complexities of a VM layer or dual booting.

A good example here is something like Skype, available on Linux, but not Solaris. There are many examples of people using BrandZ on Solaris (see here for an example).

All in all, there’s a lot to like in this release. ZFS is maturing and extending nicely, and I certainly want to play with the iSCSI feature a bit more to see where it can be used effectively (I’ve got a new server on it’s way soon to help with that test) .

BrandZ, on the other hand, will solve the issue of running Linux applications to the point where I dont need VMware or Parallels for some jobs. I wont completely eliminate the need, but for one of my desktops it will make life much easier.

New VoiceXML/XQuery Demo

I’ve got a new VoiceXML/XQuery article coming out, and IBM have asked that a demo of the service is live.

The service is an interface RSS reader - you get to choose the topic and the feed (currently only four static feeds are provided), then it will read out the feed content.

You can try out the demo by calling:

• Skype: +99000936 9991260725
• US (freephone): (800) 289-5570, then using PIN 9991260725

Occasionally the hosting times out, in which case, please contact me and I’ll check it out and restart or reboot the service.

Acorn, Pixelmator and Iris alternatives to Photoshop

I’ve had Acorn in my list of applications to review for months, and I’ve only just got round to it.

I wish I’d got there earlier. Acorn is quick and powerful, and that’s because it employs your GPU to do soe of the processing, and it includes a number of filters (based on OS X’s CoreImage interface), all of which is wrapped up into a nice little application. If you can’t find what you want, there are ObjectiveC and Pythong plugin interfaces, but I haven’t investigated it yet.

Of the alternatives, the most talked about is Pixelmator, closely followed by Iris.

Pixelmator is a closer approximation to the way that Photoshop operates, and in some respects I prefer the functionality and the feel of Pixelmator if I was looking for a Photoshop replacement, but there are other elements I don’t appreciate. The flashy graphics and animations when you do different operations seem superfluous to me.

There are nice touches in both applications - the stamp tool in Pixelmator is particularly good (although I prefer Photoshop), while in Acorn the crop and select tools provide much better feedback during the select operation than even Photoshop.

Iris is less polished, but shows some promise. There are some annoying oddities (I used 1.0b2, 367), like the image opening at pixel resolution, rather than being scaled to screen size, and the lack of specialized cursors can make identifying what you are doing and the potential effects of that process difficult, but the image editing and manipulation is very quick (particularly on stamp and touch up operations). It is, however, a bit memory hungry at the moment.

Any of these solutions would make a good alternative Photoshop and Photoshop Elements if you don’t want to go down the Adobe route.

Of these I currently prefer Acorn - it’s small and lightweight and the interface feels much more polished and easy to use. Certainly I’d consider it as an alternative to the larger packages on a laptop if you wanted something while you were traveling. I can’t get by without Photoshop because of the image scanning and editing I do, but occasionally I want something more extensive than Preview when I’m on the move.

Of course, this could change - all of these tools are being actively developed and so it’s likely that there will be some leapfrogging along the way.

MySQL Documentation and Debian/Ubuntu

We’ve got a lot of queries recently on the MySQL docs team address about the documentation (particularly man pages) for MySQL on Debian/Ubuntu.

The source of the original problem was reported as a Debian bug. The assumption from the reading of the license in this instance is that you are not allowed to distribute MySQL documentation unless you’ve asked first, and that the documentation is not released under the GPL license.

The original license was misunderstood in this respect.

In fact, the license as originally quoted in that bug does allow you to provide the documentation if you are providing the MySQL software.

In addition, regardless of how you interpret the license, all of our documentation, including installable man pages, has been available on http://dev.mysql.com/doc. You can find online HTML, offline HTML, PDF, CHM and the man pages for all of our reference manuals (on a version by version basis), along with the main HTML/PDF formats for all of the remaining documentation.

We have never tried to limit the availability of the documentation (that’s why we provide it in so many formats).

However, as soon as this issue was reported on to us by the folks at Debian we agreed with our legal department to put the man pages under a GPL license. This affects only the man pages, but gets round the misunderstanding above by allowing the man pages to be distributed under the same GPL license as the software.

Why did we only change our man page license?

MySQL documentation is updated and released very often, in fact as often
as ten times per day. Allowing anyone to create static copies of an
arbitrary documentation release would lead to many outdated copies on
the ‘Net. This is bad for users doing Google searches for MySQL
documentation, and bad for us (we’ve seen complaints about “our” 5.0.0
Manual being badly outdated when MySQL 5.1.20 was out). We appreciate
anyone mirroring the MySQL Dev Zone which contains all MySQL
documentation.

So where does that leave the man pages in Debian/Ubuntu?

I’m pleased to say that the new 5.0.51-1 package for MySQL that has gone into the latest Debian release (actually, in December). That means that MySQL and the corresponding man pages should appear already in the latest Debian “unstable” branch, and the next major Debian release should include everything you need.

Thanks to Christian Hammers (Debian) and Norbert Tretkowski (Debian) for their help on getting this all sorted!

Setting up the developer stack issues

There’s a great post on Coding Horror about Configuring the Stack.

Basically the gripe is with the complexity of installing the typical developer stack, in this case on Windows, using Visual Studio. My VS setup isn’t vastly different to the one Jeff mentions, and I have similar issues with the other stacks I use.

I’ve just set up the Ultra3 mobile workstation again for building MySQL and other stuff on, and it took about 30 packages (from Sun Freeware) just to get the basics like gcc, binutils, gdb, flex, bison and the rest set up. It took the best part of a day to get everything downloaded, installed, and configured. I haven’t even started on modules for Perl yet.

The Eclipse stack is no better. On Windows you’ll need the JDK of your choice, plus Eclipse. Then you’ll have to update Eclipse. Then add in the plugins and modules you want. Even though some of that is automated (and, annoyingly some of it is not although it could be), it generally takes me a few hours to get stuff installed.

Admittedly on my Linux boxes it’s easier - I use Gentoo and copy around a suitable make.conf with everything I need in it, so I need only run emerge, but that can still take a day or so to get everything compiled.

Although I’m sure we can all think of easier ways to create the base systems - I use Parallels for example and copy VM folders to create new environments for development - even the updating can take a considerable amount of time.

I suggest the new killer app is one that makes the whole process easier.

XBOX360 and XBOX games

You know, I am consistently (and pleasantly) surprised at the compatibility of old XBOX games on the on the XBOX360. When you consider that the games are running and emulation (the platforms are completely different), the speed difference between the two is difficult to discern.

It is also interesting to see which games look as good when viewed at the higher res. Black, for example, looks just as good on my 20″ LCD panel and the XBOX360 at more than twice the resolution than it did on the XBOX. Far Cry, however, looks particularly pixellated at times.

The only time I use the original XBOX now is when playing Dancing Stage Unleashed where the screen and mat are easier to use together.