MCslp Coalface

Reorganizing the documentation

Those of you that know the documentation well will be aware of the old page we used to have for the MySQL documentation. It was huge, and over the years we’d done a number of things to try and improve the layout and make it easier to find what you wanted. We had in-age links to jump to the different documentation types, and the old topic table that allowed you to jump to specific parts of the documentation.

The problem was that the more documentation that we produced (and there are over a thousand docs in various formats now), the bigger the page got. When we added the individual topic guides, for example, we trebled the size of the page by adding the links for each individual topic guide.

Ultimately that makes it increasingly difficult for you guys to find what you are looking for, despite the quick links and other elements.

We’ve now changed all this and split the single, big, monolithic page of *every* piece of documentation that we create, and instead spread the documentation out over a number of pages. The actual documentation itself remains the same, and we still have the same range of documentation (in fact, it’s increased slightly as I’ve been able to squeeze in a few more formats and topic guide docs), but everything is still there.

The key is the new sub-navigation bar that the Web team have provided us with:

The pages have been split out as follows:

• MySQL Manual — the full, complete reference manuals
• Workbench — the Workbench manuals
• Expert Guides — the standalone guides for some of our more detailed products and system such as the Falcon storage engine and the MySQL Test Framework
• Topic Guides — the topic reference, with the topic table at the top providing direct links into the 5.1 manual or standalone guides, and the full list of downloadable standalone guides.
• MySQL Cluster — the full cluster manuals, including the guide to the MySQL Cluster API (NDBAPI)
• Other docs — other documentation, not already mentioned, including the sample databases (Sakila, World, Employee), the help tables you can import into MySQL, and printed material and links elsewhere.
• MySQL Uni — a page about the MySQL University, which is run by the documentation team, and which provides links to the MySQL Uni pages on Forge
• About — information about the documentation team, who we are, and some statistics on the documentation we produce
• Archives — archives of older manuals

We are aware of a few issues with some of the links to some documentation, and I’m working right now to address those problems, but all the documentation should be there and available. If it isn’t, please report a Bug.

Rebuilding the installation chapter

We have lots of things on the go right now (over and above the normal process of keeping things up to date), and one of the main projects for me is to do a complete rebuild of the installation chapter (Installing and Upgrading MySQL). I’ll be starting with the 5.1 manual, then the 5.4 manual. Any future manuals should be based on these so we should be up to date for future generations.

What I’m doing:

• Re-structuring the chapter to make it easier to follow on a platform basis. The old structure mixed content for different binary and source types, and different platforms, across a number of sections, making it very difficult to follow the instructions for your chosen platform.
• Make some things generic. There are sections which are generic and apply to all (or at least many) different installation types.
• Make some things more specific. Equally, there are some things that need to be spelled out more uniquely.
• Remove some old, old, advice. We have notes in there going back 10 years or more. Among the favorite examples I’ve found is a piece of advice that says ‘If your machine has more than 16MB of RAM…’. These things are not helpful in the manual, and may just serve to confuse some people.
• Remove some older platforms. Some of the platforms and advice go back and predate MySQL 5.1, and even MySQL 5.0 and 4.1. In many cases the OS information is for a system either no longer actively developed or supported (FreeBSD 3.x, or Solaris 2.5, for example). Again, we want to remove some ambiguous and potentially confusing information and advice here for platforms which we simply can no longer monitor.
• Make it easier to keep up to date. The problem with the old organic structure is that knowing where to add new content, improvements, extensions, etc. becomes harder and harder. by merging and unifying the structure we will improve this, and in turn, improve the ability to find information.

In practice that means for at least the next month or so you will see a number of improvements and restructuring in the installation chapter for 5.1 and later manuals.

I already have a list of about 35 items that need to be addressed, over and above the list above, but feel free to provide any additional suggestions and I’ll see what I can do to fit them in.

Dojo examples from UC2009

I know, I know, loads of people have been waiting for these…

So here we go, I’ve finally sorted a downloaded version of the Dojo examples from the presentation I provided at the MySQL Users Conference 2009.

There are three examples:

• The auto-paging table example, which uses the functionality of the Dojo Toolkit and the QueryReadStore to automatically load content from a table.
  
• The basic graphing example, which loads data dynamically and plots a graph.
  
• And the zooming version of the same basic graph interface

There’s a README in the download that contains instructions on getting everything up to speed, although it should be fairly obvious. It’s attached to the bottom of this post too.

Any questions for getting this to work, please go ahead and ask!

Download the package here


UC2009 Working with MySQL and Dojo
==================================

MC Brown, 2009, http://mcslp.com and http://coalface.mcslp.com

Components:

There are three examples here:

- Dojo table with auto-paging
(table_autopage.html and table_autopage.cgi)

- Dojo Basic Graph
(graph_basic.html and graph_ajax.cgi)

- Dojo Zooming Graph
(graph.html and graph_ajaz_zoom.cgi)

You will also need the Dojo/Dijit and DojoX toolkit package from here:

http://www.dojotoolkit.org/downloads

Download the combined package, and then extract the contents and place in the same directory as the CGI scripts

Intructions for use:

For the Table example, you can create a table (or use an existing DB) from Wordpress using the wp_posts tabls.

In practice you need only create and populate a table with the following columns:

id (int)
user_nicename (char)
post_date (datetime)
post_title (char)

To be compatible with the example without any more modifications.

For the Graphing examples, you need a table as follows:

CREATE TABLE `currencies` (
`currencyid` bigint(20) unsigned NOT NULL auto_increment,
`currency` char(20) default NULL,
`value` float default NULL,
`datetime` int(11) default NULL,
`new` int(11) default NULL,
PRIMARY KEY (`currencyid`),
UNIQUE KEY `currencyid` (`currencyid`),
KEY `currencies_currency` (`currency`,`datetime`),
KEY `currencies_datetime` (`datetime`)
) ENGINE=MyISAM AUTO_INCREMENT=170048 DEFAULT CHARSET=latin1

And then populate with date/value data according to the information you want to store.

Once done, make sure you change the database information in the CGI scripts to populate the information correctly during operation.


MySQL 5.1 in OpenSolaris

If you’ve attended just one of my recent talks, either at the UC, LOSUG or MySQL University, you should know that MySQL 5.1.30 will be in the next official drop of OpenSolaris.

In fact, you can find MySQL 5.1 in the current pre-release builds – I just download build 111 of the future 2009.06 release.

Key things about the new MySQL 5.1 in OpenSolaris:

1. Contains the set of DTRACE probes that also exists in MySQL 5.4 (see DTrace Documentation)
2. Like the 5.0, we have SMF integration, so you can start, stop, monitor and change some of the core configuration through SMF
3. Directory layout is similar to 5.0, with a version specific directory (/usr/mysql/5.1), and the two can coexist if you want to handle a migration from 5.0 to 5.1

To install MySQL 5.1, use the pkg tool to perform the installation. We’ve split the components into three packages:

1. SUNWmysql51 contains the server and client binaries, and associated scripts.
2. SUNWmysql51libs contains the client libraries, which you’ll need for all external (i.e. MySQL) tools, like DBD::mysql for Perl connectivity)
3. SUNWmysql51test contains the MySQL test suite

To install:

$ pfexec pkg install SUNWmysql51

Once installed, you can start MySQL server through SMF:

$ pfexec svcadm enable mysql:version_51

You can set properties, like the data directory, by using svccfg:

$ svccfg
svc:> select mysql:version_51
svc:/application/database/mysql:version_51> setprop mysql/data=/data0/mysql
svc:/application/database/mysql:version_51> setprop mysql/enable_64bit=1

Any questions, please ask!

Speaking at CommunityOne West

Sorry for the (relatively) short notice, but I will be talking at Sun’s CommunityOne conference in San Francisco on June 1st.

I’ll be talking about, and demonstrating, the DTrace probes we have put into MySQL in a joint presentation with Robert Lor who will be doing the same for Postgres.

CommunityOne West Badge

Our presentation is on the Monday afternoon.

Check out the CommunityOne West Conference Site for more details and registration.

Using MySQL with the Dojo Toolkit

The presentation file for the Dojo toolkit presentation at the Users Conference is now available.

You can find it on the conference session page.

I’ll be uploading the example scripts that work to produce the examples I gave once I’m back in the office after the conference.

Tags: mysqlconf09, presentation

Presentations now on MySQL Conf Site Too

So I talked to the wonderful JB from O’Reilly who is responsible for all sorts of parts of the conference, and she’s now enabled my presentations on the MySQL Conference Presentations page.

While you’re there downloading mine, make sure you go and download some of the others.

In particular, you might want to try:

• DTrace presentation which Vince Carbone did (and which I co-presented to provide a live demo of DTrace)
• Ann and Kevin’s Falcon Presentation
• Andrey’s look at the new Connector/C++
• Sheeri and Patrick’s Metadata piece, especially if you are interested in what is going on inside MySQL when you work with different components. I wish I’d been able to see this live.

Tags: mysqlconf09, presentation

UC2009: Scale Up, Scale Out and High Availability: Solutions and Combinations

My presentation slides for the Scale Up, Scale Out and High Availability tutorial here at the MySQL Users Conference are now available for download:

Scale Up, Scale Out and High Availability: Solutions and Combinations

These have been on the MySQL Conference website for days now, but for some reason they don’t seem to have been freed yet.

To those waiting I’m sorry for the delay in getting these uploaded.

Tags: mysqlconf09, presentation

MySQL Conf 2009 Preview: Scalability and HA Tutorial

Like most people, and with just over a week to go before the conference, I’m putting the finishing touchs on my various presentations.

First up for me, on Monday afternoon, is my tutorial: Scale Up, Scale Out, and High Availability: Solutions and Combinations.

What will be doing?

Very simply: Looking at every potential solution for maximum scalability and availability for your database environment.

If you are attending, be prepared to:

• Expand your mind as we think about scaling up.
• Expand your horizons as we think about scaling out.
• Divide and conquer, as we think about high-availability.

We’re not not hands on in this session – but I will expect you to be brains on!

Reverse Changelog

One of the pain points of upgrading any software package is knowing which version to upgrade to when the time comes. With software that is frequently updated, like MySQL, choosing a version that provides new features and addresses issues you are aware of, without also being exposed to other issues is an added complexity.

This week, I added another new feature to our documentation, spurred on by Baron Schwarz’s idea for a ‘reverse changelog’.

As Baron noted, the idea is to report the bugs reported in a specific version, and then provide information about the version in which each bug was fixed, so that you can determine which version you need to choose when upgrading to avoid that bug.

You can see the output for 5.1: Bugs Reported and Fixed in 5.1.

That page contains every version of 5.1, a list of the bugs known to be reported against that specific version, the bug synopsis/description, and the version in which the bug was known to be fixed. The reported version information is extracted from our bugs database. The bug fixed version is extracted from the changelog information – another part of the dynamic changelog functionality is the ability to find out information like this super easy when generating changelog related output.

I’ve done a lot of work over the last couple of years to improve the quality and content of our changelog. Although the basic structure of the changelog hasn’t changed much on the outside, from the inside, I’ve simplified and expanded the content and information that we track, and made the documentation team’s work processes easier to handle too. About 20% of our time in the docs team is tied up in keeping the changelogs up to date with both the changelog content and any relevant changes to the manual, so keeping that time down enables us to focus on the content.

On the outside, for our users, we’ve been publishing the enhanced ‘key changes’ information for more than a year now. This uses tags that we add to the changelog entries when we write them, and allow us to pull out the ‘key changes’, that is bugs tagged with Important Change, Incompatible Change, etc. into a single view.

Hopefully both the reverse changelog and key changes will be useful to everybody – and I thank (and will thank) Baron for the inspiration for the idea, largely made possible through our dynamic changelog system.

For those curious about the dynamic changelog and what it can do, you might also want to check out the Open Bugs for 5.1 list which also uses the information provided by the dynamic changelog to tell you when known bugs are expected to be fixed.

If anybody has any further ideas about the sort of information they might want out of the changelog, please feel free to let me know. As a rough guide, pulling out information based on specific tags (for example, all the bugs affecting a particular storage engine, partitioning, or specific platform) are easy to produce, as are changes between specific versions, ranges, or combinations of the above.

MySQL University Needs You!!

MySQL University keeps rolling along. We’ve had some fantastic sessions just recently (not including my own, of course!), such as Lenz’s presentation of backing up MySQL with filesystem snapshots, Allan Packer’s presentation on the optimization of MySQL and, going back a little further, David Van Couvering and Petr Pisl’s talk on using PHP and MySQL within Netbeans.

Remember that all of these presentations can be viewed again online if you missed them first time round!

We’ve got some good topics coming up, but we ned more!!

Got some hot topic that you want to tell the world about?

Using MySQL in an interesting way?

Got a good MySQL scalability story that you want to tell?

Developed a great storage engine?

Any and all of these topics are welcome for discussion and presentation at MySQL University.

On the other hand, perhaps you’ve seen one of our existing presentations and would like an update or follow-on session that goes deeper, or that enables you to ask more questions.

Whatever it is you want to see on MySQL University, let me know here or through the contact form, and get these things scheduled in before the slots all disappear!

LOSUG Presentation Slides Now Available

My presentation at LOSUG on tuesday went down like a house on fire – I think it would be safe to say that the phrase for the evening was ‘It’s a cache!’.

For that to make sense, you need to look at the slides, which are now available here.

Attendance was great, but it seems the last minute change of day meant that some people missed the session. We had 151 people register, and about 80 turned up on the night.

Tags: DTrace, Memcached, MySQL, OpenSolaris

I love my Moleskine

Not remotely related to computing at all, but I’ve just been updating my diaries, and I use Moleskine. I go everywhere with a Moleskine of some description (they’ve recently released some really tiny notebooks, which make it easier to carry your life around with you).

Despite having computers, organization tools, and email, there is something decidedly comforting about writing, by hand, into a physical notebook.

Of course, you write in pencil so that the contents don’t smudge, and somehow that just feels even better.

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.