I’ve been benchmarking and testing TokuDB for a few months now. One goal of benchmarking is to understand what is limiting the performance of a particular configuration. I frequently use “show engine [innodb/tokudb] status;” from within the MySQL command line client as part of my research.
As I run most of my benchmarks on InnoDB as well as TokuDB, I noticed that there are significant differences in the way each present status information. InnoDB returns a single row, with various sections and carriage returns to maintain readability. In contrast, TokuDB presents one piece of status information per row (currently 139 rows as of TokuDB v5.0.5). This is an important distinction if you want to parse, compare, or store discrete status values. Here is sample output from each engine. I’ve cut out portions of each to maintain readability.
InnoDB plugin v1.0.13
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
mysql> show engine innodb status; +--------+------+-------------------------------------------+ | Type | Name | Status + +--------+------+-------------------------------------------+ | InnoDB | | ===================================== 111018 7:05:21 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 59 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread log flush and writes: 1 -------- FILE I/O -------- I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout <<**** MANY LINES REMOVED FOR READABILITY ****>> Pages read ahead 0.00/s, evicted without access 0.00/s LRU len: 15, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Number of rows inserted 0, updated 0, deleted 0, read 0 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ +--------+------+-------------------------------------------+ 1 row in set (0.00 sec) |
TokuDB v5.0.5
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> show engine tokudb status; +--------+------+-------------------------------------------+ | Type | Name | Status + +--------+------+-------------------------------------------+ | TokuDB | checkpoint period | 60 | | TokuDB | checkpoint status code (0 = idle) | 0 | | TokuDB | last complete checkpoint LSN | 196 | | TokuDB | txn oldest live | 0 | | TokuDB | next LSN | 201 | <<**** MANY LINES REMOVED FOR READABILITY ****>> | TokuDB | original version | 14 | | TokuDB | version at startup | 14 | | TokuDB | last LSN of version 13 | 0 | | TokuDB | malloc count | 482 | | TokuDB | free count | 322 | | TokuDB | realloc count | 6 | | TokuDB | malloc fail | 0 | | TokuDB | realloc fail | 0 | +--------+------+-------------------------------------------+ 139 rows in set (0.00 sec) |
MySQL provides 3 columns for the results of show engine status: Type, Name, and Status. I’m not sure why InnoDB pushes all output into the Status column of a single row, maybe there are historical reasons. Our implementation uses one Name / Status pair per row, which is much easier to read, parse, or sort.
One of our developers created a simple Python script that polls MySQL to get the current TokuDB engine status, compares the values to the last values read, and outputs the delta of the two. Pretty handy when trying to understand what the storage engine is up to. The script is named “tokustat.py”, but I alias it as tokutop.
The source code of the python script is available via this link. Because our engine status information is so well organized, it is only 86 lines of code (and could probably be much smaller).
I’d be interested to hear how others are reading and parsing the status of other engines, email me at [email protected].
Resources
RELATED POSTS
