Flickr’s upgraded shard
While going about my day, I stumbled upon John Allspaw’s tweet about his experience with MySQL 5.0.51 versus Percona’s 5.0.83 highperf Percona build. For those that don’t remember John from the MySQL Conference & Expo’s, he’s the guy managing operations at Flickr, and he recently even wrote a book about webops.
Click on the photo above, to see when the upgrade happened. Amazed?
Haven’t tested this out myself, though I’m curious to see how this stacks up against MySQL 5.4, which is also aimed at being a high performance release. In another note, it seems like the mysql-cacti-templates could use a bit more verbose documentation.
Would have been interesting to see the standard 5.0.83 against the percona hiperf version. That's a relatively large version change between the two. 5.0.51 is nearly two years old now and there have been a number of performance improvements in between the two releases in both InnoDB and myisam engines.
You're right. In fact, 5.0.51 itself, had some performance issues, that were fixed in later releases.
I sincerely hope John tries a few more combination's and posts the results
You can get much of that benefit today from the 1.0.4 InnoDB plugin for MySQL 5.1. It supports multiple background IO threads to process the aio reads.
Mark — how many is multiple? I just posted another comment here that showed that MySQL (5.0.27-standard-log) uses 1 thread each for reads and writes, whereas 5.1.36-percona-log has 8 read threads and 8 write threads…..how many does that have?
(on the percona build, the innodb_thread_concurrency is set to 0)
Percona, Google and Facebook patches made the number of threads for reads/writes configurable. I don't remember the defaults. I think the my.cnf variable names are innodb_read_io_threads/innodb_write_io_threads.
Mark,
Switching from 5.0 to 5.1+InnoDB-pluing maybe not so easy for many users.
Colin — what specifically makes you say that about the cacti templates? Just wondering, as I've used them, and found the docs verbose enough….but I'm also very familiar with cacti….but I don't see anything in the links that mentions struggling with the installation or whatever.
If you look at SHOW ENGINE INNODB STATUS for Percona high perf builds, you'll see a difference in the File I/O section. The MySQL manual says that on Unix, there are always 4 threads:
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
But on percona high performance versions:
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (write thread)
I/O thread 11 state: waiting for i/o request (write thread)
I/O thread 12 state: waiting for i/o request (write thread)
I/O thread 13 state: waiting for i/o request (write thread)
I/O thread 14 state: waiting for i/o request (write thread)
I/O thread 15 state: waiting for i/o request (write thread)
I/O thread 16 state: waiting for i/o request (write thread)
I/O thread 17 state: waiting for i/o request (write thread)
(this is from 5.1.36-percona-log, specifically).
What does SHOW ENGINE INNODB STATUS look like in 5.4?
This is from a stock 5.4.3-beta install:
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
4.0.40 has had these improvements way before any 5.0 patches existed :) o/
I've been adding a lot of documentation to the Cacti graphs. Hope it is helpful: http://code.google.com/p/mysql-cacti-templates/…