Paul Tuckfield of YouTube has spoken about how he sped up his replicas by pre-fetching the replica’s relay logs. I wrote an implementation of this, tried it on my workload, and it didn’t speed them up. (I didn’t expect it to; I don’t have the right workload). I had a few email exchanges with Paul and some other experts on the topic and we agreed my workload isn’t going to benefit from the pre-fetching.

I dashed off a hasty post about speeding up replication replicas, and gave no references or explanation. That’s what happens when I write quickly! This post explains what the heck I was talking about. I first heard Paul Tuckfield talk at the first MySQL Camp, in November 2006. He mentioned that he speeds up MySQL replication by “pre-fetching relay logs” on the replica. Actually, I think he used the term “pipelining” at that point.

This is great news - a beer that everybody knows is vegan is right there (see, even photographic proof). Now if only it wasn’t 10:50am….

InnoDB is a transaction-safe, ACID compliant MySQL storage engine. It has commit, rollback, and crash recovery capabilities, and offers row level locking. The engine's overview page explains, “InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.”

read more

We recommend two open source tools to help with the regular tuning and monitoring of your MySQL database: mysqlreport and mysqlsla. Your website is made from many complex systems. Rapid growth, changes to your site, and other systems can change the load on your MySQL database. It is important that your internal staff become familiar with using these tools and implement routine maintenance. An initial review often leads to significant improvements, and will also help you to implement a monitoring solution for your ongoing performance efforts.

read more

Whew! I just finished a marathon of revisions. It's been a while since I posted about our progress, so here's an update for the curious readers.

Last week I put the updated schedule online, with training courses in February (Sydney) and March (Melbourne). Other cities/topics coming up also.

As before, Open Query training is organised in independent one-day modules, so you pick the topic(s) you want, when you want them. Judging from feedback from the initial days done last year, this format has proven highly successful. Of course, it's combined with small (max 10-12) interactive classes, hands-on exercises, and trainers who know their stuff. Heck, I could run days without materials. I will want a whiteboard ;-)

I wrote trainers (plural) because Cybersite (Jonathon Coombes) has merged with Open Query. Apart from years of experience with MySQL training and consulting, Jonathon also …

mysql> CREATE TABLE `blah2` ( `hello` int(11) default NULL );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO blah2 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from blah2;
+-------+
| hello |
+-------+
| 1 |
| 1 |
+-------+
2 rows in set (0.00 sec)


mysql> show full processlist;
+--------+-------+-----------------+------+---------+------+----------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------+-----------------+------+---------+------+----------------+-----------------------+
| 503235 | root | localhost | NULL | Sleep | 742 | | NULL |
| 503238 | happy | localhost:36013 | test | Query | 0 | NULL | show full processlist |
| 503239 | happy | localhost:36014 | test | Query | 0 | Writing to net | …

mysql> CREATE TABLE `blah2` ( `hello` int(11) default NULL );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO blah2 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from blah2;
+-------+
| hello |
+-------+
| 1 |
| 1 |
+-------+
2 rows in set (0.00 sec)


mysql> show full processlist;
+--------+-------+-----------------+------+---------+------+----------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------+-----------------+------+---------+------+----------------+-----------------------+
| 503235 | root | localhost | NULL | Sleep | 742 | | NULL |
| 503238 | happy | localhost:36013 | test | Query | 0 | NULL | show full processlist |
| 503239 | happy | localhost:36014 | test | Query | 0 | Writing to net | …

we usually try to avoid doing releases on friday, but we had been trying to release mysql connector/odbc 3.51.23 for a while now, and if it didn?t happen today, there was a good chance it wouldn?t happen for a few more weeks. this release just contains a few bug fixes, but we have gotten the bug count down to 60. one of the things i hope we can accomplish at our big orlando meeting is doing some triage on those remaining bugs.