Archive for the ‘Kevin Burton’ tag
One of the enhancements I added to MySQL Archiver in the recent release was listed innocently in the changelog as “Destination plugins can now rewrite the INSERT statement.” Not very exciting or informative, huh? Keep reading.
If you’ve used plugins with MySQL Archiver you know that I created a series of “hooks” where plugins can take some action: before beginning, before archiving each row, etc etc. This lets plugins do things like create new destination tables, aggregate archived rows to summary tables during archiving (great for building data warehouses, though not as sophisticated as Kettle), and so on. Well, this release added a new hook for plugins:
This lets a plugin override the prepared statement the tool will use to insert rows into the archive. By default the prepared statement just inserts into the destination table. But the
custom_sth hook lets the plugin inspect the row that’s about to be archived and decide what to do with it. This lets it do interesting things like archive rows to different tables.
This came up because some of the tables I’m archiving to suddenly hit the bend in the hockey-stick curve. I diagnosed the problem very simply: inserts began taking most of the time during archiving. As you might know, MySQL Archiver has a statistics mode where it profiles every operation and reports the stats at the end. I’m archiving out of InnoDB into MyISAM; take a look at the stats:
Action Count Time Pct inserting 800584 12722.8245 88.35 deleting 800584 1464.1040 10.17 print_file 800584 58.3453 0.41 commit 3204 29.4391 0.20 select 1602 8.5654 0.06 other 0 116.5321 0.81
Inserting suddenly took 88% of the time spent archiving, when it had been taking a very small fraction of the time. I’d been meaning to split the archived data out by date and/or customer, and this convinced me it was time to stop procrastinating. There are columns in the archived rows for both of these dimensions in the data, so it shouldn’t be hard. So I added the custom_sth hook, wrote a 40-line plugin, and did it. Results:
Action Count Time Pct deleting 51675 525.2777 87.62 inserting 51675 49.3903 8.24 print_file 51675 4.4639 0.74 commit 208 2.1553 0.36 custom_sth 51675 1.4575 0.24 select 104 0.6714 0.11 before_insert 51675 0.1135 0.02 before_begin 1 0.0001 0.00 plugin_start 1 0.0000 0.00 after_finish 1 0.0000 0.00 other 0 15.9868 2.67
(You can see the effect of having a plugin, because the time taken for all the hooks is listed in the stats. There was no plugin previously.)
Now inserting takes only 8% of the time required to archive. Put another way, it used to insert 63 rows per second, now it inserts 1046 rows per second. This is single-row inserts. (It is not intended to archive fast; it is intended to archive without disturbing the OLTP processes. Obviously this server can do a lot more inserts and deletes than this.)
What had happened? The MyISAM tables on the destination end had just gotten too big for their indexes to fit in memory, and the inserts had suddenly slowed dramatically. I didn’t want to give them a lot more memory, because I want the memory to be used for the InnoDB data on that machine. This is the same kind of thing, I’d guess, that Kevin Burton just wrote about.
Oh yeah, while I was at it, I totally rewrote the archiver with unit-tested, test-driven, test-first, other-buzzword-compliant code. I added a lot of other improvements, too. For example, it can now archive tables that have much harder keys to optimize efficiently, such as nullable non-unique non-primary keys.
Kevin Burton wrote recently about why
SHOW SLAVE STATUS is really not a good way to monitor how far behind your slave servers are, and how slave network timeouts can mess up the slave lag. I’d like to chime in and say this is exactly why I thought Jeremy Cole‘s MySQL Heartbeat script was such a natural fit for the MySQL Toolkit. It measures slave lag in a “show me the money” way: it looks for the effects of up-to-date replication, rather than asking the slave how far behind it thinks it is.
The slave doesn’t even need to be running. In fact, the tool doesn’t use
SHOW SLAVE STATUS at all. This has lots of advantages: for example, it tells you how far the slave lags behind the ultimate master, no matter how deep in the replication daisy-chain it is. In other words, unlike
SHOW SLAVE STATUS, it won’t tell you a slave is up-to-date just because it’s caught up to its master. If a slave’s master is an hour behind, it will report that the slave is an hour behind, too — because it is.
It’s a really smart approach. And you can daemonize it, and it’ll keep a file up-to-date with running averages (by default it averages the last one, five and fifteen minutes, but of course you can choose that). Now your monitoring scripts can be as simple as “cat /var/log/slave-delay” or some such.
It’s not a hard tool to write, and I suspect lots of people have done it, but I bet that between Jeremy, whoever worked on it at Six Apart, and me, we’ve produced a pretty good version of the tool. It’s part of the MySQL Toolkit, and the full manual is online.