sorted by: recent | see : popular

Content Tagged with MySQL + perl

My post on what it’s like to write a technical book was a stream-of-consciousness look at the process of writing High Performance MySQL, Second Edition. I got a lot of responses from it and learned some neat things I wouldn’t have learned if I hadn’t written the post. I also got a lot of questions, and my editor wrote a response too. I want to follow up on these things.

Was I fair, balanced and honest?

I really intended to write the post as just “here’s what it’s like, just so you’re prepared.” But at some point I got really deep into it and lost my context. That’s when I started to write about the things that didn’t go so smoothly with the publisher, and some of these things had a little extra sting in them that I would have done well to edit out.

All of us are human and the process wasn’t that bad, all things considered — the book was just a massive project that put huge demands on all of us and stressed everything from the capabilities of our chosen tools to our patience. As the editor points out in his response to my blog post, this is precisely why nobody else has ever been able to pull this off. This book stands head and shoulders above the crowd. It’s just hard to write, and very few people in the world actually have the knowledge to do it, much less the time, inclination, and ability.

Everything I said was (I believe) factual and correct, although as the editor points out there are different stories behind them. I also want to mention that I’d shared all those concerns with my editor; I avoid criticizing people behind their backs. In hindsight, throwing all of my concerns onto a blog post without warning isn’t the kind of thing I like to do either.

So I believe I was honest, but unfair to the editor. I’ve apologized to him. And by the way, yes I would work with him again, and I fully expect that it would be easier because I have learned more about the process.

I ran this post by my editor before publishing it.

A deeper explanation of my heuristics

Several people asked me to say more about my heuristics for improving the quality of the writing. I’ve already explained many of them, but here’s more:

(were|was|is|are|has been|be)( [a-zA-Z]+)? [a-zA-Z]+ed\>

This regular expression can help find some occurrences of passive voice. It finds a word or phrase that’s some variation on the verb “to be,” usually in the past tense; followed by an optional word (probably an adjective); followed by another word that ends in “-ed,” which is also potentially a verb in the past tense. This is not the only way to write in the passive voice, but it’s kind of the classic. Here are some examples: “the blog post was posted,” “the benchmark was rapidly created.”

(were|was|is|are|ha[sd] been|be)( [a-zA-Z]+)? [a-zA-Z]+e[dn]\>

An enhanced version. As I looked at the preceding point, I saw some other simple examples it doesn’t catch. For example, it doesn’t catch “had been” and it doesn’t catch verbs like “written.” Ironically, the first thing that came to mind as I thought about examples was “the book had been written.”

while|since

There’s nothing wrong with these words, except when they’re used in lieu of “because” to indicate causality. This is a problem for non-native English speakers, because these words have a temporal meaning too. For example, “Since MySQL 4.1 has no stored procedures, you have to use MySQL 5.0 if you want stored procedures.” If you aren’t a native English speaker, and even if you are, it’s easy to read that as “MySQL has had no stored procedures since version 4.1, …” and then when your eyes reach the part about MySQL 5.0, it makes no sense. My rule for this is to say “because” when I mean “because.”

using

Real examples: “Using MyISAM tables works very well” can become “MyISAM tables work very well.” And “A final possibility is simply to switch to using a table” can become “Finally, you can use a table” instead.

in order

The phrase “in order to” can almost always be replaced by “to.” It also tends to show a rough transition between the first and second phrases in a sentence. Perhaps these phrases should be integrated into a single phrase. “You can use this regex in order to find poorly constructed sentences” can become “this regex can find poorly constructed sentences” or “You can find poorly constructed sentences with this regex.” I prefer the latter; it is very direct, and that straightforward, simple writing style is really important in complex subject matter.

of course|without saying|obviously|clearly|needless

It goes without saying, but of course these words obviously point out when I’m writing stupid things that I clearly need to take a closer look at. Needless to say, most of the phrases in this paragraph are indeed needless to say. They are a red flag for lazy writing, such as glossing over a difficult point that should instead be explained — hard work, but necessary.

whether

I found quite a few places where the phrase “whether or not” was used. This can be shortened: “to see whether or not the disk is the problem” can become “to see whether the disk is the problem.” But better yet, the phrase often glues together poorly written phrases into an awkward sentence, just as “in order to” does. Can “whether” be replaced by “if?” Or does the sentence or paragraph just need to be reworked completely?

allow

This word can usually be replaced by “let.” “The remaining settings allow MySQL to allocate more RAM” can become “The remaining settings let MySQL allocate more RAM.” Occasionally, it is part of a larger phrase or thought needs to be shortened and clarified. “When nobody is writing, readers obtain read locks that allow other readers to do the same” became “When nobody is writing, readers can obtain read locks, which don’t conflict with other read locks.”

ensure

I found that this word is often subtly misused. It really means “guarantee” but is often used as “double-check” or “make sure.” I don’t want to be too dogmatic about this word: its usage in modern English is complex (see the usage note on assure here; that in itself might be a reason to avoid it). But I found many places where I wanted to remove it in favor of an explicit instruction that tells the reader to take action. “Ensure” as an instruction is kind of a politically correct way to tell someone to do something, and I’m not afraid to just tell you to do it if I think you need to. I don’t want you to miss my meaning.

only

I have a habit of using this word incorrectly. “I only have ten fingers” should be “I have only ten fingers.”

as (we|you)|again,

These phrases usually show a place where the writing is confused and redundant. They show up in places like “as we already said, you should tune your server” and “again, you should tune your server.” Any instruction to the reader to break the narrative flow is a place to examine whether the concepts are in the right order. Cross-references, footnotes, and reminders are not always evil, but they’re to be regarded with suspicion.

Readability metrics

The tools I used to find sentences and phrases that score badly on some readability metric were pretty helpful to me as I tightened the writing up more and more. Nobody has reviewed the book yet, but I think when they do, they’ll be unlikely to mention “oh, and by the way the writing is wonderfully compact!” If we pulled this off right, you won’t notice that the writing is clear and compact. Writing is like a stereo system: you’re supposed to hear the music, not the speakers.

Anyway, my point is that we expanded the first edition’s actual coverage many times over, and ended up with only 658 pages of actual material. So the writing is much more compressed, and to do that you have to find and eliminate confusing writing. Confusing writing usually means that the concepts don’t flow clearly, and it takes more words to say the same thing because you’re kind of bumbling about, gesturing at your meaning from several angles instead of saying it clearly just once.

Here’s how I analyzed each chapter:

• I used OpenOffice’s export feature to export the file to MediaWiki format. This is a plain-text markup format. I forget now why I didn’t just export to text, but there was something about MediaWiki format that made it easier to munge with Perl.
• I ran my clean_text.pl program against the exported file to convert the format to a simpler one without special characters and markup. Some of the markup (footnotes, for example) stayed in the text and confused the metrics, but that’s life.
• I ran my analyze_text.pl program against this to find the “worst” places.

As I wrote in my previous post, the analyzer uses a combination of readability metrics and “other stuff” to measure the badness of each sentence and paragraph. It aggregates sentences and paragraphs by the metrics. I calculated the number of words, percent of complex words, syllables per word, number of sentences, words per sentence, and a bunch of other things, as well as the standard readability metrics. Each sentence and paragraph got scored on these. Then I printed overall metrics, and sorted the sentences and paragraphs worst-first and printed out a snippet of the offending text. Here’s a sample of chapter 3’s metrics (originally numbered chapter 4) at some intermediate stage in the writing process.

This was a lot of work. If I had been writing with Vim, I could have done better. I could have used the compiler integration and set my “make” program to the analysis program. If you use Vim and you don’t know about this, it’s a pity. My next book will be written in Vim, by the way.

Actually, I probably could have done better regardless, but this was good enough. I just searched for the snippets and then examined what was going on.

There were some false positives. For example, bullet-points often scored badly on the readability metrics, and so a five-word bullet point item would look like terrible writing just because it was short enough that it had a high percentage of complex words. It’s not an exact science. Maybe next time will be better.

If you’d like to see the source code, here’s the clean_text.pl and here’s the analyze_text.pl. Enjoy!

I have been using Maatkit in a different way since I joined Percona as a consultant. When I’m working on a system now, it’s a new, unfamiliar system — not one where I have already installed my favorite programs. And that means I want to grab my favorite productivity tools fast.

I intentionally wrote the Maatkit tools so they don’t need to be “installed.” You just run them, that’s all. But I never made them easy to download.

I fixed that. Now, at the command line, you can just run this:

wget http://www.maatkit.org/get/mk-table-sync

Now it’s ready to run. Behind the scenes are some Apache mod_rewrite rules, a Perl script or two, and Subversion. When you do this, you’re getting the latest code from Subversion’s trunk.[1][2] (I like to run on the bleeding edge. Releases are for people who want to install stuff.)

Because there’s some Perl magic behind it, I made it even easier — it does pattern-matching on partial names and Does The Right Thing:

baron@kanga:~$ wget http://www.maatkit.org/get/sync
--21:38:50--  http://www.maatkit.org/get/sync
           => `sync'
Resolving www.maatkit.org... 64.130.10.15
Connecting to www.maatkit.org|64.130.10.15|:80... connected.
HTTP request sent, awaiting response... 302 Moved
Location: http://www.maatkit.org/get/mk-table-sync [following]
--21:38:50--  http://www.maatkit.org/get/mk-table-sync
           => `mk-table-sync'
Connecting to www.maatkit.org|64.130.10.15|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/x-perl]

    [      <=>                            ] 163,259      136.51K/s             

21:38:51 (136.13 KB/s) - `mk-table-sync' saved [163259]

The redirection is there because otherwise wget will save the file under the name ’sync’ instead of ‘mk-table-sync’.

And if you’ve forgotten which tools exist, you can just click on over to http://www.maatkit.org/get/ and see.

A quick poll: instead of getting the latest trunk, should this give you the code from the last release? I can do that, if you want.

[1] OK, it’s only refreshed every hour. So you’re getting code that’s up to an hour old.

[2] update: now /get/foo gets the latest release, and /trunk/foo gets the latest trunk code.

It’s been a while since I had chance to work on Informeer as my itch was one of multi-user web based password management (AuthStor). Oh and moving house.

Now that things are settling down again (Servers back up and running) I decided to take a break from AuthStor and focus on something new - Informeer.

The concept is simple, Centralised Notification.

I am forever configuring notifications from several sources, be it backup alerts, host monitoring notification and even simple applications that send mail via SMTP. When living in a world of change, both software and business, having to visit every application to change an e-mail address or add a new user to a notification schedule can be quite time consuming. Add to that the effort of having to modify firewalls, SMTP servers and XMPP settings etc etc. The idea of a single web interface where all your notifications can be configured is quite appealing. Informeer aims to make that a reality (one day).

While I take my time with the implementation I thought I would post a basic intro to RPC-XML using Perl. The RPC-XML Perl module makes writing your own client/server application a piece of cake, and while not quite point and click, it will give you enough flexibility to centralise your own notifications (or anything else for that matter). It’s not rocket science, but it works!

If you have been looking for a flexible quick and easy fix to the centralised notification problem, or even if you are just looking to push some data from a firewalled site to one of your servers This Tutorial should help get you started.

In between moving house and playing with notifications I found time to upgrade to Wordpress 2.5 and MoinMoin 1.6, and wow what an improvement in both camps.
The Wordpress dashboard is the most visible improvement - it’s amazingly clean! If you have been waiting for 2.5 to settle down or just cautious about upgrading, I would say take the plunge, you won’t regret it. As for MoinMoin 1.6, there really is no better Wiki engine out there (my opinion) and it just keeps getting better with every release!.

That just leaves me to post a quick MySQL tip that may well be obvious, but happens to catch me out from time to time.

MySQL Tip of the month

If you ever get the urge to convert a 1Gig MyISAM table (with 34 million rows) to InnoDB within MySQL, try to avoid the simple ALTER TABLE tablename ENGINE=INNODB; method.
I made the mistake of running that command on a fairly decent server with bags of space and memory only to find the command running over 5 hours later with an InnoDB tablespace at around 4Gig (ok a bit of tuning might have helped)

Your best bet is to dump the table using mysqldump, drop the existing table and re-import the table after modifying the definition to be InnoDB. That worked for me in less than 10 minutes. Alternatively you can create a new InnoDB table and insert directly from the existing table as per MySQL documentation e.g INSERT INTO newtable SELECT * FROM oldtable.

I’m sure I am not the first to make this mistake, and may not be the last…..

I am happy to announce the release of mylvmbackup version 0.8. mylvmbackup is a tool for quickly creating backups of a MySQL server's data files. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, makes an LVM snapshot of the volume containing the MySQL data directory, and unlocks the tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup proceeds.

Below is the list of changes since version 0.6. You may wonder what happened to version 0.7 - it had a rather short life cycle as I was informed about a bug that I fixed quickly before I made a wider release announcement of 0.7.

• Fixed a bug in the InnoDB recovery function: the second mysqld process clobbered the socket file of the primary MySQL instance (thanks to Alain Hoang for reporting this)
• Updated the man page, noted some other limitations of the InnoDB recovery function
• Bug fix: use the correct mysqld parameter to provide an alternative PID file (--pid-file instead of --pidfile) - thanks to Guillaume Boddaert and Jim Wilson for reporting this!
• Added option "--skip_mycnf" to skip including a copy of the MySQL configuration file in the backup, added a safety check that the file actually exists prior to backing it up.

Updated package are available from the home page and via the openSUSE Build Service as usual. Updated packages for Debian/Ubuntu and Gentoo Linux should also be available shortly. Enjoy!

Speaking of LVM snapshot backups: I will be giving a talk about this subject at our MySQL Conference 2008 in Santa Clara, CA next week. If you are curious about how MySQL can be backed up using this technology, please consider to stop by!

our %summary=();
sub aggregate_add($$)
{
  my ($value,$group)= @_;
  if (defined $value)
  {
    $summary{$group}= {value=>0.0, value2=>0.0, count=>0}
      if !defined $summary{$group};
    my $scalar= scalar $value;
    $summary{$group}{value}+= $scalar;
    $summary{$group}{value2}+= $scalar * $scalar;
    $summary{$group}{count}++;
  }
  return $value;
}
sub aggregate_result($$)
{
  my ($value,$group)= @_;
  return undef if !defined $summary{$group};
  my $count= $summary{$group}{count};
  my $average= $summary{$group}{value} / $count;
  my $sqavg= $summary{$group}{value2} / $count;
  my $variance= $sqavg - ($average * $average);
  my $stddev= sqrt $variance;
  return sprintf("count=%d avg=%0.3f var=%0.3f std=%0.3f",
                 $count, $average, $variance, $stddev);
}

mysql> CREATE TABLE t1 (grp INT, a DOUBLE);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1,1), (2,2), (2,3), (3,4), (3,5), (3,6);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE FUNCTION test.agg_result(value DOUBLE, grp INT) RETURNS CHAR(128)
    -> LANGUAGE Perl NO SQL EXTERNAL NAME 'Foo::aggregate_result';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE FUNCTION test.agg_add(value DOUBLE, grp INT) RETURNS DOUBLE
    -> LANGUAGE Perl NO SQL EXTERNAL NAME 'Foo::aggregate_add';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(a),
    ->        CAST(AVG(a) AS DECIMAL(7,3)) 'AVG',
    ->        CAST(VARIANCE(a) AS DECIMAL(7,3)) 'VAR',
    ->        CAST(STD(a) AS DECIMAL(7,3)) 'STD',
    ->        test.agg_result(MAX(test.agg_add(a,grp)),grp) 'TEST'
    ->        FROM t1 GROUP BY grp;
+----------+-------+-------+-------+---------------------------------------+
| COUNT(a) | AVG   | VAR   | STD   | TEST                                  |
+----------+-------+-------+-------+---------------------------------------+
|        1 | 1.000 | 0.000 | 0.000 | count=1 avg=1.000 var=0.000 std=0.000 |
|        2 | 2.500 | 0.250 | 0.500 | count=2 avg=2.500 var=0.250 std=0.500 |
|        3 | 5.000 | 0.667 | 0.816 | count=3 avg=5.000 var=0.667 std=0.816 |
+----------+-------+-------+-------+---------------------------------------+
3 rows in set (0.04 sec)

This release contains bug fixes and new features. It also contains a new tool: my implementation of Paul Tuckfield's relay log pipelining idea. I have had quite a few responses to that blog post, and requests for the code. So I'm releasing it as part of Maatkit.

I dashed off a hasty post about speeding up replication slaves, and gave no references or explanation. That's what happens when I write quickly! This post explains what the heck I was talking about.

My posts lately have been mostly progress reports and release notices. That's because we're in the home stretch on the book, and I don't have much spare time. However, a lot has also been changing with Maatkit, and I wanted to take some time to write about it properly.