Preface
Sometimes we need to collect information of MySQL server as a report when we first time take over the system by someone else.Espcially there`re lots of servers for you to check them one by one with manual command.We need a simple tool to increase the efficiency when doing so.
Introduce
pt-mysql-summary can help us to print information of our MySQL servers nicely.It`s not a tuning or diagnostic tool but a tool just provides us a report with neat format.
Procedure
Usage
1 pt-mysql-summary [OPTIONS]
Common Parameters
1 --all-databases //Specify the scope is all databases for summarizing. 2 --databases //Specify the only database for summarizing. 3 --default-files //Specify the configuration file to use. 4 --list-encrypted-tables //Specify to contain encrypted tables in all databases. 5 --read-samples //Create a report from this file you sepcified. 6 --save-samples //Save to the direcotry you want to store the summary. 7 --sleep //Seconds to sleep for counter.
Example
Execute pt-mysql-summary with just connect options(single master).
1 [root@zlm1 08:57:10 ~] 2 #pt-mysql-summary --user=root --password=Passw0rd --host=localhost 3 mysql: [Warning] Using a password on the command line interface can be insecure. 4 # Percona Toolkit MySQL Summary Report ####################### 5 System time | 2018-08-12 06:57:37 UTC (local TZ: CEST +0200) 6 # Instances ################################################## 7 Port Data Directory Nice OOM Socket 8 ===== ========================== ==== === ====== 9 # MySQL Executable ########################################### 10 # Slave Hosts ################################################ 11 No slaves found //The slave server is not started up yet. 12 # Report On Port 3306 ######################################## //Summary of overall of MySQL server. 13 User | root@localhost 14 Time | 2018-08-12 08:57:37 (CEST) 15 Hostname | zlm1 16 Version | 5.7.21-log MySQL Community Server (GPL) 17 Built On | linux-glibc2.12 x86_64 18 Started | 2018-08-12 08:57 (up 0+00:00:37) 19 Databases | 7 20 Datadir | /data/mysql/mysql3306/data/ 21 Processes | 2 connected, 1 running 22 Replication | Is not a slave, has 0 slaves connected 23 Pidfile | mysql.pid (does not exist) 24 # Processlist ################################################ //This is all the contents in processlist. 25 26 Command COUNT(*) Working SUM(Time) MAX(Time) 27 ------------------------------ -------- ------- --------- --------- 28 Query 1 1 0 0 29 Sleep 1 0 1 1 30 31 User COUNT(*) Working SUM(Time) MAX(Time) 32 ------------------------------ -------- ------- --------- --------- 33 monitor 1 0 0 0 34 root 1 1 0 0 35 36 Host COUNT(*) Working SUM(Time) MAX(Time) 37 ------------------------------ -------- ------- --------- --------- 38 localhost 1 1 0 0 39 zlm1 1 0 0 0 40 41 db COUNT(*) Working SUM(Time) MAX(Time) 42 ------------------------------ -------- ------- --------- --------- 43 NULL 2 1 0 0 44 45 State COUNT(*) Working SUM(Time) MAX(Time) 46 ------------------------------ -------- ------- --------- --------- 47 1 0 0 0 48 starting 1 1 0 0 49 50 # Status Counters (Wait 10 Seconds) ########################## //This paragragh shows the global status of MySQL and with 10 secs` differential values. 51 Variable Per day Per second 10 secs 52 Bytes_received 4500000 50 600 53 Bytes_sent 100000000 1250 3000 54 Com_admin_commands 9000 55 Com_select 60000 3 56 Com_set_option 2250 57 Com_show_slave_status 15000 58 Com_show_status 4500 59 Com_show_variables 2250 60 Connections 15000 1 61 Created_tmp_files 12500 62 Created_tmp_tables 7000 6 63 Flush_commands 2250 64 Handler_commit 22500 65 Handler_external_lock 500000 6 66 Handler_read_first 30000 67 Handler_read_key 25000 68 Handler_read_next 4500 69 Handler_read_rnd_next 4000000 50 80 70 Handler_write 2000000 25 35 71 Innodb_buffer_pool_bytes_data 20000000000 225000 3500 72 Innodb_buffer_pool_pages_flushed 80000 73 Innodb_buffer_pool_read_requests 4000000 45 25 74 Innodb_buffer_pool_reads 1250000 15 75 Innodb_buffer_pool_write_requests 800000 8 25 76 Innodb_data_fsyncs 17500 77 Innodb_data_read 17500000000 225000 78 Innodb_data_reads 1250000 15 79 Innodb_data_writes 125000 1 80 Innodb_data_written 1500000000 17500 7000 81 Innodb_dblwr_pages_written 4500 82 Innodb_dblwr_writes 2250 83 Innodb_log_writes 4500 84 Innodb_os_log_fsyncs 9000 85 Innodb_os_log_written 2500000 30 86 Innodb_pages_created 80000 87 Innodb_pages_read 1000000 15 88 Innodb_pages_written 80000 89 Innodb_rows_read 25000 4 90 Innodb_num_open_files 80000 91 Innodb_available_undo_logs 300000 3 92 Key_read_requests 15000 93 Key_reads 7000 94 Open_table_definitions 250000 2 95 Opened_files 350000 3 1 96 Opened_table_definitions 250000 2 97 Opened_tables 250000 3 98 Queries 100000 1 6 99 Questions 90000 1 6 100 Select_scan 15000 101 Table_locks_immediate 225000 2 102 Table_open_cache_hits 12500 103 Table_open_cache_misses 250000 3 104 Threads_created 4500 105 Uptime 90000 1 1 106 # Table cache ################################################ 107 Size | 2048 108 Usage | 5% 109 # Key Percona Server features ################################ //Because mine is not percona version,so most of the features are not supported. 110 Table & Index Stats | Not Supported 111 Multiple I/O Threads | Enabled 112 Corruption Resilient | Not Supported 113 Durable Replication | Not Supported 114 Import InnoDB Tables | Not Supported 115 Fast Server Restarts | Not Supported 116 Enhanced Logging | Not Supported 117 Replica Perf Logging | Enabled 118 Response Time Hist. | Not Supported 119 Smooth Flushing | Not Supported 120 HandlerSocket NoSQL | Not Supported 121 Fast Hash UDFs | Unknown 122 # Percona XtraDB Cluster ##################################### //If we are using PXC structure,this paragraph will show the detail of it. 123 # Plugins #################################################### 124 InnoDB compression | ACTIVE 125 # Query cache ################################################ 126 query_cache_type | OFF 127 Size | 0.0 128 Usage | 0% 129 HitToInsertRatio | 0% 130 # Semisynchronous Replication ################################ //Information of semi-replication if there`s a master-slave replication. 131 Master | Disabled 132 Slave | Disabled 133 # Schema ##################################################### 134 Specify --databases or --all-databases to dump and summarize schemas 135 # Noteworthy Technologies #################################### 136 SSL | No 137 Explicit LOCK TABLES | No 138 Delayed Insert | No 139 XA Transactions | No 140 NDB Cluster | No 141 Prepared Statements | No 142 Prepared statement count | 0 143 # InnoDB ##################################################### 144 Version | 5.7.21 145 Buffer Pool Size | 100.0M 146 Buffer Pool Fill | 8% 147 Buffer Pool Dirty | 0% 148 File Per Table | ON 149 Page Size | 16k 150 Log File Size | 3 * 100.0M = 300.0M 151 Log Buffer Size | 8M 152 Flush Method | O_DIRECT 153 Flush Log At Commit | 2 154 XA Support | ON 155 Checksums | ON 156 Doublewrite | ON 157 R/W I/O Threads | 4 4 158 I/O Capacity | 2000 159 Thread Concurrency | 0 160 Concurrency Tickets | 5000 161 Commit Concurrency | 0 162 Txn Isolation Level | READ-COMMITTED 163 Adaptive Flushing | ON 164 Adaptive Checkpoint | 165 Checkpoint Age | 9 166 InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue 167 Oldest Transaction | 0 Seconds 168 History List Len | 0 169 Read Views | 0 170 Undo Log Entries | 0 transactions, 0 total undo, 0 max undo 171 Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads 172 Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites 173 Pending I/O Flushes | 0 buf pool, 0 log 174 Transaction States | 1xnot started 175 # MyISAM ##################################################### 176 Key Cache | 8.0M 177 Pct Used | 20% 178 Unflushed | 0% 179 # Security ################################################### 180 Users | 5 users, 0 anon, 0 w/o pw, 0 old pw 181 Old Passwords | 0 182 # Encryption ################################################# 183 mysql: [Warning] Using a password on the command line interface can be insecure. 184 No keyring plugins found 185 # Binary Logging ############################################# 186 Binlogs | 6 187 Zero-Sized | 0 188 Total Size | 190.0k 189 binlog_format | ROW 190 expire_logs_days | 10 191 sync_binlog | 0 192 server_id | 1003306 193 binlog_do_db | 194 binlog_ignore_db | 195 # Noteworthy Variables ####################################### 196 Auto-Inc Incr/Offset | 1/1 197 default_storage_engine | InnoDB 198 flush_time | 0 199 init_connect | 200 init_file | 201 sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 202 join_buffer_size | 128k 203 sort_buffer_size | 128k 204 read_buffer_size | 2M 205 read_rnd_buffer_size | 16M 206 bulk_insert_buffer | 0.00 207 max_heap_table_size | 96M 208 tmp_table_size | 96M 209 max_allowed_packet | 4M 210 thread_stack | 192k 211 log | 212 log_error | ./error.log 213 log_warnings | 2 214 log_slow_queries | 215 log_queries_not_using_indexes | OFF 216 log_slave_updates | ON 217 # Configuration File ######################################### 218 Config File | /etc/my.cnf 219 220 [mysqld] 221 datadir = /var/lib/mysql 222 socket = /var/lib/mysql/mysql.sock 223 symbolic-links = 0 224 225 [mysqld_safe] 226 log-error = /var/log/mariadb/mariadb.log 227 pid-file = /var/run/mariadb/mariadb.pid 228 229 [mysql] 230 prompt = "\u@\h:\p [\d]\>" 231 user = zlm 232 password = zlmzlm 233 host = 192.168.56.100 234 235 [client] 236 user = zlm 237 password = zlmzlm 238 socket = /tmp/mysql3306.sock 239 host = 192.168.56.100 240 # Memory management library ################################## 241 jemalloc is not enabled in mysql config for process with id 4159 242 # The End #################################################### 243 244 [root@zlm1 08:57:48 ~] 245 #
Execute it again while slave is working normally with option “–database” and “–save-samples”
1 [root@zlm1 09:15:24 ~] 2 #pt-mysql-summary --user=root --password=Passw0rd --host=localhost --databases zlm --save-samples=/root/zlm.rpl 3 mysql: [Warning] Using a password on the command line interface can be insecure. 4 # Percona Toolkit MySQL Summary Report ####################### 5 System time | 2018-08-12 07:15:49 UTC (local TZ: CEST +0200) 6 # Instances ################################################## 7 Port Data Directory Nice OOM Socket 8 ===== ========================== ==== === ====== 9 # MySQL Executable ########################################### 10 # Slave Hosts ################################################ //Now,it shows the slave`s information. 11 *************************** 1. row *************************** 12 Server_id: 1013306 13 Host: 14 Port: 3306 15 Master_id: 1003306 16 Slave_UUID: 2b199d1f-94df-11e8-ae46-080027de0e0e 17 # Report On Port 3306 ######################################## 18 User | root@localhost 19 Time | 2018-08-12 09:15:49 (CEST) 20 Hostname | zlm1 21 Version | 5.7.21-log MySQL Community Server (GPL) 22 Built On | linux-glibc2.12 x86_64 23 Started | 2018-08-12 08:57 (up 0+00:18:49) 24 Databases | 7 25 Datadir | /data/mysql/mysql3306/data/ 26 Processes | 4 connected, 2 running 27 Replication | Is not a slave, has 1 slaves connected 28 Pidfile | mysql.pid (does not exist) 29 # Processlist ################################################ 30 31 Command COUNT(*) Working SUM(Time) MAX(Time) 32 ------------------------------ -------- ------- --------- --------- 33 Binlog Dump GTID 1 1 800 800 34 Query 1 1 0 0 35 Sleep 2 0 7 5 36 37 User COUNT(*) Working SUM(Time) MAX(Time) 38 ------------------------------ -------- ------- --------- --------- 39 monitor 2 0 0 0 40 repl 1 1 800 800 41 root 1 1 0 0 42 43 Host COUNT(*) Working SUM(Time) MAX(Time) 44 ------------------------------ -------- ------- --------- --------- 45 localhost 1 1 0 0 46 zlm1 2 0 0 0 47 zlm2 1 1 800 800 48 49 db COUNT(*) Working SUM(Time) MAX(Time) 50 ------------------------------ -------- ------- --------- --------- 51 NULL 4 2 800 800 52 53 State COUNT(*) Working SUM(Time) MAX(Time) 54 ------------------------------ -------- ------- --------- --------- 55 2 0 0 0 56 Master has sent all binlog to 1 1 800 800 //This is the dump thread of master working in replicatoin. 57 starting 1 1 0 0 58 59 # Status Counters (Wait 10 Seconds) ########################## 60 Variable Per day Per second 10 secs 61 Bytes_received 4000000 45 700 62 Bytes_sent 60000000 700 4000 63 Com_admin_commands 9000 64 Com_select 60000 3 65 Com_set_option 1000 66 Com_show_binlogs 150 67 Com_show_databases 150 68 Com_show_engine_status 150 69 Com_show_master_status 150 70 Com_show_plugins 150 71 Com_show_processlist 150 72 Com_show_slave_hosts 150 73 Com_show_slave_status 17500 74 Com_show_status 600 75 Com_show_storage_engines 150 76 Com_show_variables 225 77 Connections 6000 1 78 Created_tmp_disk_tables 2000 1 79 Created_tmp_files 400 80 Created_tmp_tables 10000 8 81 Flush_commands 80 82 Handler_commit 800 83 Handler_external_lock 20000 1 84 Handler_read_first 1500 85 Handler_read_key 1250 86 Handler_read_next 150 9 87 Handler_read_rnd_next 600000 7 300 88 Handler_write 300000 3 150 89 Innodb_buffer_pool_bytes_data 600000000 8000 22500 90 Innodb_buffer_pool_pages_flushed 4000 1 91 Innodb_buffer_pool_read_requests 175000 1 70 92 Innodb_buffer_pool_reads 35000 93 Innodb_buffer_pool_write_requests 60000 60 94 Innodb_data_fsyncs 500 95 Innodb_data_read 600000000 7000 96 Innodb_data_reads 40000 97 Innodb_data_writes 5000 1 98 Innodb_data_written 70000000 800 30000 99 Innodb_dblwr_pages_written 150 100 Innodb_dblwr_writes 80 101 Innodb_log_writes 150 102 Innodb_os_log_fsyncs 300 103 Innodb_os_log_written 80000 104 Innodb_pages_created 3000 1 105 Innodb_pages_read 35000 106 Innodb_pages_written 4000 1 107 Innodb_rows_inserted 15000 10 108 Innodb_rows_read 15000 9 109 Innodb_num_open_files 2500 110 Innodb_available_undo_logs 10000 111 Key_read_requests 450 112 Key_reads 225 113 Open_table_definitions 8000 114 Opened_files 12500 1 115 Opened_table_definitions 8000 116 Opened_tables 9000 117 Queries 100000 1 9 118 Questions 90000 1 9 119 Select_scan 2500 1 120 Table_locks_immediate 8000 121 Table_open_cache_hits 500 122 Table_open_cache_misses 9000 123 Threads_created 300 124 Uptime 90000 1 1 125 # Table cache ################################################ 126 Size | 2048 127 Usage | 5% 128 # Key Percona Server features ################################ 129 Table & Index Stats | Not Supported 130 Multiple I/O Threads | Enabled 131 Corruption Resilient | Not Supported 132 Durable Replication | Not Supported 133 Import InnoDB Tables | Not Supported 134 Fast Server Restarts | Not Supported 135 Enhanced Logging | Not Supported 136 Replica Perf Logging | Enabled 137 Response Time Hist. | Not Supported 138 Smooth Flushing | Not Supported 139 HandlerSocket NoSQL | Not Supported 140 Fast Hash UDFs | Unknown 141 # Percona XtraDB Cluster ##################################### 142 # Plugins #################################################### 143 InnoDB compression | ACTIVE 144 # Query cache ################################################ 145 query_cache_type | OFF 146 Size | 0.0 147 Usage | 0% 148 HitToInsertRatio | 0% 149 # Semisynchronous Replication ################################ //The semi-sync replication is still not used. 150 Master | Disabled 151 Slave | Disabled 152 # Schema ##################################################### //Because of the option of "--databases",it shows the detail of it. 153 154 Database Tables Views SPs Trigs Funcs FKs Partn 155 zlm 1 156 157 Database InnoDB 158 zlm 1 159 160 Database 161 zlm 162 163 i c 164 n h 165 t a 166 r 167 Database === === 168 zlm 1 1 169 170 # Noteworthy Technologies #################################### 171 Full Text Indexing | No 172 Geospatial Types | No 173 Foreign Keys | No 174 Partitioning | No 175 InnoDB Compression | No 176 SSL | No 177 Explicit LOCK TABLES | No 178 Delayed Insert | No 179 XA Transactions | No 180 NDB Cluster | No 181 Prepared Statements | No 182 Prepared statement count | 0 183 # InnoDB ##################################################### 184 Version | 5.7.21 185 Buffer Pool Size | 100.0M 186 Buffer Pool Fill | 8% 187 Buffer Pool Dirty | 0% 188 File Per Table | ON 189 Page Size | 16k 190 Log File Size | 3 * 100.0M = 300.0M 191 Log Buffer Size | 8M 192 Flush Method | O_DIRECT 193 Flush Log At Commit | 2 194 XA Support | ON 195 Checksums | ON 196 Doublewrite | ON 197 R/W I/O Threads | 4 4 198 I/O Capacity | 2000 199 Thread Concurrency | 0 200 Concurrency Tickets | 5000 201 Commit Concurrency | 0 202 Txn Isolation Level | READ-COMMITTED 203 Adaptive Flushing | ON 204 Adaptive Checkpoint | 205 Checkpoint Age | 9 206 InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue 207 Oldest Transaction | 0 Seconds 208 History List Len | 0 209 Read Views | 0 210 Undo Log Entries | 0 transactions, 0 total undo, 0 max undo 211 Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads 212 Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites 213 Pending I/O Flushes | 0 buf pool, 0 log 214 Transaction States | 1xnot started 215 # MyISAM ##################################################### 216 Key Cache | 8.0M 217 Pct Used | 20% 218 Unflushed | 0% 219 # Security ################################################### 220 Users | 5 users, 0 anon, 0 w/o pw, 0 old pw 221 Old Passwords | 0 222 # Encryption ################################################# 223 mysql: [Warning] Using a password on the command line interface can be insecure. 224 No keyring plugins found 225 # Binary Logging ############################################# 226 Binlogs | 6 227 Zero-Sized | 0 228 Total Size | 190.0k 229 binlog_format | ROW 230 expire_logs_days | 10 231 sync_binlog | 0 232 server_id | 1003306 233 binlog_do_db | 234 binlog_ignore_db | 235 # Noteworthy Variables ####################################### 236 Auto-Inc Incr/Offset | 1/1 237 default_storage_engine | InnoDB 238 flush_time | 0 239 init_connect | 240 init_file | 241 sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 242 join_buffer_size | 128k 243 sort_buffer_size | 128k 244 read_buffer_size | 2M 245 read_rnd_buffer_size | 16M 246 bulk_insert_buffer | 0.00 247 max_heap_table_size | 96M 248 tmp_table_size | 96M 249 max_allowed_packet | 4M 250 thread_stack | 192k 251 log | 252 log_error | ./error.log 253 log_warnings | 2 254 log_slow_queries | 255 log_queries_not_using_indexes | OFF 256 log_slave_updates | ON 257 # Configuration File ######################################### 258 Config File | /etc/my.cnf 259 260 [mysqld] 261 datadir = /var/lib/mysql 262 socket = /var/lib/mysql/mysql.sock 263 symbolic-links = 0 264 265 [mysqld_safe] 266 log-error = /var/log/mariadb/mariadb.log 267 pid-file = /var/run/mariadb/mariadb.pid 268 269 [mysql] 270 prompt = "\u@\h:\p [\d]\>" 271 user = zlm 272 password = zlmzlm 273 host = 192.168.56.100 274 275 [client] 276 user = zlm 277 password = zlmzlm 278 socket = /tmp/mysql3306.sock 279 host = 192.168.56.100 280 # Memory management library ################################## 281 jemalloc is not enabled in mysql config for process with id 4159 282 # The End #################################################### 283 284 [root@zlm1 09:16:00 ~] 285 #ls -l|grep rpl 286 drwxr-xr-x 2 root root 4096 Aug 12 09:15 zlm.rpl 287 288 [root@zlm1 09:16:40 ~] 289 #cd zlm.rpl 290 291 [root@zlm1 09:16:42 ~/zlm.rpl] 292 #ls -l 293 total 88 294 -rw-r--r-- 1 root root 880 Aug 12 09:15 collect.err 295 -rw-r--r-- 1 root root 4051 Aug 12 09:15 innodb-status 296 -rw-r--r-- 1 root root 777 Aug 12 09:15 mysql-config-file 297 -rw-r--r-- 1 root root 66 Aug 12 09:15 mysql-databases 298 -rw-r--r-- 1 root root 0 Aug 12 09:15 mysqld-executables 299 -rw-r--r-- 1 root root 118 Aug 12 09:15 mysqld-instances 300 -rw-r--r-- 1 root root 642 Aug 12 09:15 mysqldump 301 -rw-r--r-- 1 root root 130 Aug 12 09:15 mysql-master-logs 302 -rw-r--r-- 1 root root 114 Aug 12 09:15 mysql-master-status 303 -rw-r--r-- 1 root root 2276 Aug 12 09:15 mysql-plugins 304 -rw-r--r-- 1 root root 796 Aug 12 09:15 mysql-processlist 305 -rw-r--r-- 1 root root 0 Aug 12 09:15 mysql-roles 306 -rw-r--r-- 1 root root 0 Aug 12 09:15 mysql-slave 307 -rw-r--r-- 1 root root 182 Aug 12 09:15 mysql-slave-hosts 308 -rw-r--r-- 1 root root 8730 Aug 12 09:15 mysql-status 309 -rw-r--r-- 1 root root 9682 Aug 12 09:15 mysql-status-defer 310 -rw-r--r-- 1 root root 8 Aug 12 09:15 mysql-users 311 -rw-r--r-- 1 root root 15562 Aug 12 09:15 mysql-variables 312 -rw-r--r-- 1 root root 0 Aug 12 09:15 ndb-status 313 314 //The directory of "zlm.rpl" contains individual collecting information of the connected MySQL server.You can check them for future use.
Summary
- pt-mysql-summary is a report tool for collecting information of MySQL servers.
- It`s easy enough to use by merely specify several connection options.
- It can tremendously increase the efficiency of summaring a large amount of MySQL servers.