In Issue 3 of MySQL Magazine Peter Zaitsev writes, ” I would set [innodb_log_file_size] to 256 megabytes for small sized boxes up to 5% of the total memory on the big boxes.” I tend to set my log file size to around 128M. Depending on your system setting the log file size around a few hundred megs should be the right balance between preventing checkpointing and still having fast recovery times.

The manual states, “Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group.” The sample my.cnf files that come with MySQL state to set the innodb_log_file_size to 25% of the innodb buffer pool size. I think this mistakenly advises people to set their log files much larger than they need to be causing unnecessarily long recovery times.

Crash recovery time is an important feature of InnoDB. In the event of a server crash large log files could mean waiting hours for …

Sometimes I don't like MySQL for the way it documents its software. And I don't like people who would try to define something and get the definition from the MySQL documentation. Let me give an example, MySQL defines Handler_read_key server status variable as the number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don't use keys properly.

If you search around the web, people will also use this definition. But what does this actually? No one would try to go deeper because they actually don't know. Don't get me wrong, I also don't know what this actually means.

Let me dissect the way this definition is written, what does number of requests means? Is it the number of queries? What does a "fixed position" mean? And how is this …

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 | …