Preface
We are always obliged to analyze many outputs generated by various tools directly even in flat text files in server performance diagnosis.Further more,we need to compare the differences depend on these results such as the output of “mysqladmin” tool.
Introduce
pt-mext is tht very tool which can help you to accomplish the work mentioned above easily.It can execute the command which you specify then print the result side-by-side in one line.Not only can it print results from a running program but also can it print results from a flat text file.
Procedure
Usage
1 pt-mext [OPTIONS] -- COMMAND
Common Parameters
1 --relative //Each column substracts the value of the column before it.
Example
Collect the global status of MySQL with 1s interval and 3 times in all by mysqladmin.
1 [root@zlm2 10:43:03 ~] 2 #mysqladmin ext -i1 -c3 > mysqladmin.log 3 4 [root@zlm2 10:43:16 ~] 5 #ls -l 6 total 2554836 7 -rw-------. 1 root root 1431 Jul 16 2015 anaconda-ks.cfg 8 -rwxr-xr-x 1 root root 2611024896 Jun 13 03:03 mysql-5.7.21-linux-glibc2.12-x86_64.tar 9 -rw-r--r-- 1 root root 108476 Jul 12 10:43 mysqladmin.log 10 -rwxr-xr-x 1 root root 54 Jun 13 04:16 mysqld.sh 11 -rwxr-xr-x 1 root root 5000812 Jun 26 08:59 percona-toolkit-3.0.4-1.el7.x86_64.rpm 12 drwxr-xr-x 13 root root 4096 Jul 4 03:21 sysbench-1.0
The “mysqladmin.log” file contains all the output of the three times individually.
1 [root@zlm2 10:49:28 ~] 2 #cat mysqladmin.log 3 +-----------------------------------------------+--------------------------------------------------+ 4 | Variable_name | Value | 5 +-----------------------------------------------+--------------------------------------------------+ 6 | Aborted_clients | 0 | 7 | Aborted_connects | 0 | 8 | Binlog_cache_disk_use | 0 | 9 | Binlog_cache_use | 0 | 10 | Binlog_stmt_cache_disk_use | 0 | 11 | Binlog_stmt_cache_use | 0 | 12 | Bytes_received | 2142 | 13 | Bytes_sent | 155447 | 14 | Com_admin_commands | 0 | 15 | Com_assign_to_keycache | 0 | 16 | Com_alter_db | 0 | 17 | Com_alter_db_upgrade | 0 | 18 ... 19 20 +-----------------------------------------------+--------------------------------------------------+ 21 | Variable_name | Value | 22 +-----------------------------------------------+--------------------------------------------------+ 23 | Aborted_clients | 0 | 24 | Aborted_connects | 0 | 25 | Binlog_cache_disk_use | 0 | 26 | Binlog_cache_use | 0 | 27 | Binlog_stmt_cache_disk_use | 0 | 28 | Binlog_stmt_cache_use | 0 | 29 | Bytes_received | 2177 | 30 | Bytes_sent | 165275 | 31 | Com_admin_commands | 0 | 32 | Com_assign_to_keycache | 0 | 33 | Com_alter_db | 0 | 34 | Com_alter_db_upgrade | 0 | 35 ... 36 37 +-----------------------------------------------+--------------------------------------------------+ 38 | Variable_name | Value | 39 +-----------------------------------------------+--------------------------------------------------+ 40 | Aborted_clients | 0 | 41 | Aborted_connects | 0 | 42 | Binlog_cache_disk_use | 0 | 43 | Binlog_cache_use | 0 | 44 | Binlog_stmt_cache_disk_use | 0 | 45 | Binlog_stmt_cache_use | 0 | 46 | Bytes_received | 2212 | 47 | Bytes_sent | 175103 | 48 | Com_admin_commands | 0 | 49 | Com_assign_to_keycache | 0 | 50 | Com_alter_db | 0 | 51 | Com_alter_db_upgrade | 0 |
Execute pt-mext to print them again with the “mysqladmin.log” file.
1 [root@zlm2 10:56:56 ~] 2 #pt-mext -- cat mysqladmin.log 3 Aborted_clients 0 0 4 Aborted_connects 0 0 5 Binlog_cache_disk_use 0 0 6 Binlog_cache_use 0 0 7 Binlog_stmt_cache_disk_use 0 0 8 Binlog_stmt_cache_use 0 0 9 Bytes_received 2142 2177 10 Bytes_sent 155447 165275 11 Com_admin_commands 0 0 12 Com_alter_db 0 0 13 Com_alter_db_upgrade 0 0 14 ... 15 16 //pt-mext arranges the results of those three times side-by-side and print them. 17 //The first column shows the first collected result and the second column shows the second one. 18 //The number of columns is n-1(n is the collection times here).
Specify the “–relative” option to get the differential data.
1 [root@zlm2 11:05:50 ~] 2 #pt-mext --relative -- cat mysqladmin.log 3 Aborted_clients 0 0 4 Aborted_connects 0 0 5 Binlog_cache_disk_use 0 0 6 Binlog_cache_use 0 0 7 Binlog_stmt_cache_disk_use 0 0 8 Binlog_stmt_cache_use 0 0 9 Bytes_received 2142 35 //2277-2142=35 10 Bytes_sent 155447 9828 //165275-155447=9828 11 Com_admin_commands 0 0 12 Com_alter_db 0 0 13 Com_alter_db_upgrade 0 0 14 15 //Differential data is more distinct for us to know about the variation of performance. 16 //Even though we use "--relative" option,there`re still two columns here.
Execute pt-mext without flat text file.
1 [root@zlm2 11:07:16 ~] 2 #pt-mext -r -- mysqladmin ext -i1 -c3 3 Aborted_clients 0 0 4 Aborted_connects 0 0 5 Binlog_cache_disk_use 0 0 6 Binlog_cache_use 0 0 7 Binlog_stmt_cache_disk_use 0 0 8 Binlog_stmt_cache_use 0 0 9 Bytes_received 2453 35 10 Bytes_sent 185024 9828 11 Com_admin_commands 0 0 12 Com_alter_db 0 0 13 Com_alter_db_upgrade 0 0 14 ... 15 16 [root@zlm2 11:13:28 ~] 17 #pt-mext -- mysqladmin ext -i1 -c3 18 Aborted_clients 0 0 19 Aborted_connects 0 0 20 Binlog_cache_disk_use 0 0 21 Binlog_cache_use 0 0 22 Binlog_stmt_cache_disk_use 0 0 23 Binlog_stmt_cache_use 0 0 24 Bytes_received 2764 2799 25 Bytes_sent 214601 224429 26 Com_admin_commands 0 0 27 Com_alter_db 0 0 28 Com_alter_db_upgrade 0 0 29 ...
Summary
- pt-mext has only three options and it is rather easy to use.
- pt-mext is a useful tool in anaylzing differential data of global status of MySQL.
- pt-mext can be used not not only in “mysqladmin” program but also others.