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
Hello,
To my understanding the InnoDB output is supposed to be read by humans. The same information (or a subset of it?) is also presented in the INFORMATION_SCHEMA database which is more suitable for machine parsing.
Cheers, Joe
Joe,
I’ve looked around the information schema and saw some of the information in global_status, but significantly less than “show engine innodb status;” provides. Is more available?
-Tim
Many things are missing from SHOW STATUS, but that is getting better in recent 5.6 releases. The Facebook patch adds many of the missing values to SHOW STATUS.
What I do like in the output of SHOW ENGINE INNODB STATUS in MariaDB is that the size of the buffer pool is not only shown in a number of pages but also in the number of megabytes.
This is related an issue with the table_cache: some folks think that the value is in bytes, which isn’t true. This is why I created bugreport #58229.
So if it’s possible you should add a column to the SHOW ENGINE TOKUDB STATUS output with the corresponding units.