Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL, MariaDB, MongoDB, and PostgreSQL server and system tasks that are too difficult or complex to perform manually.
You can get summaries of your database servers, or you verify replication lag on MySQL and PostgreSQL servers. You get thirty-eight scripts that can do any manner of actions, and you will find them very valuable in your regular database work. And did I mention they are open source?!
Percona Toolkit may already be in your distro by default in many Linux distros, such as Fedora, Homebrew, and Debian. And you can get it from us at Percona.
Installation is easy with the software comes in popular package formats. Follow the guide to installing the Percona repositories and then either sudo apt-get install percona-toolkit or sudo yum install percona-toolkit.
The one tricky part is installing the User-Defined Functions (UDF) needed for a few of the tools. I ran into the common error message:
1 | ERROR 1126 (HY000): Can't open shared library 'fnv_udf.so' (errno: 22 fnv_udf.so: cannot open shared object file: No such file or directory) |
But it is very simple to get the shared objects copied to where they need to be located.
1 2 3 | sudo cp -v /usr/lib/mysql/plugin/libfnv_udf.so /usr/lib sudo cp -v /usr/lib/mysql/plugin/libfnv1a_udf.so /usr/lib sudo cp -v /usr/lib/mysql/plugin/libmurmur_udf.so /usr/lib |
Then, those shared objects need to be loaded into into the server.
1 2 3 | mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" -u root -p mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" -u root -p mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'" -u root -p |
Pt-summary, pt-mysql-summary, and pt-pg-summary
For an example of what the Percona Toolkit offers, look at the output from pt-summary. This is a handy way to determine the basic parameters that the server is using.
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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 | $ pt-summary --user=root [?2004l# Percona Toolkit System Summary Report ###################### Date | 2023-09-25 14:43:34 UTC (local TZ: CDT -0500) Hostname | testbox Uptime | 44 min, 1 user, load average: 0.47, 0.67, 0.57 Platform | Linux Release | Ubuntu 22.04.3 LTS (jammy) Kernel | 6.2.0-33-generic Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.35 SELinux | No SELinux detected Virtualized | No virtualization detected # Processor ################################################## Processors | physical = 1, cores = 2, virtual = 4, hyperthreading = yes Speeds | 1x3000.651, 1x3061.574, 1x3069.932, 1x3093.881 Models | 4xIntel(R) Core(TM) i7-6500U CPU @ 2.50GHz Caches | 4x4096 KB # Memory ##################################################### Total | 15.4G Free | 9.4G Used | physical = 2.2G, swap allocated = 2.0G, swap used = 0.0, virtual = 2.2G Shared | 475.0M Buffers | 3.8G Caches | 12.4G Dirty | 928 kB UsedRSS | 5.8G Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 Locator Size Speed Form Factor Type Type Detail ========= ======== ================= ============= ============= =========== # Mounted Filesystems ######################################## Filesystem Size Used Type Opts Mountpoint /dev/nvme0n1p1 511M 2% vfat rw,relatime,fmask=0077,dmask=0077,codepage=437,iocharset=iso8859-1,shortname=mixed,errors=remount-ro /boot/efi /dev/nvme0n1p2 457G 4% ext4 rw,relatime,errors=remount-ro / tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,inode64 /run tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=5120k,inode64 /run tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,relatime,size=1612684k,nr_inodes=403171,mode=700,uid=1000,gid=1000,inode64 /run tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,inode64 /run/user/1000 tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=5120k,inode64 /run/user/1000 tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run/user/1000 tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run/user/1000 tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,relatime,size=1612684k,nr_inodes=403171,mode=700,uid=1000,gid=1000,inode64 /run/user/1000 tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,inode64 /run/lock tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=5120k,inode64 /run/lock tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run/lock tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run/lock tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,relatime,size=1612684k,nr_inodes=403171,mode=700,uid=1000,gid=1000,inode64 /run/lock tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,inode64 /dev/shm tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,noexec,relatime,size=5120k,inode64 /dev/shm tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /dev/shm tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /dev/shm tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,relatime,size=1612684k,nr_inodes=403171,mode=700,uid=1000,gid=1000,inode64 /dev/shm # Disk Schedulers And Queue Size ############################# nvme0n1 | [none] 1023 sda | [mq-deadline] 2 # Disk Partioning ############################################ # Kernel Inode State ######################################### dentry-state | 324004 295487 45 0 21657 0 file-nr | 9792 0 9223372036854775807 inode-nr | 301794 491 # LVM Volumes ################################################ Unable to collect information # LVM Volume Groups ########################################## Unable to collect information # RAID Controller ############################################ Controller | No RAID controller detected # Network Config ############################################# Controller | Intel Corporation Ethernet Connection I219-V (rev 21) FIN Timeout | 60 Port Range | 60999 # Interface Statistics ####################################### interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors ========= ========= ========== ========== ========== ========== ========== lo 400000 3500 0 400000 3500 0 enp0s31f6 350000000 250000 0 15000000 125000 0 wlp4s0 500000 3500 0 300000 2250 0 # Network Connections ######################################## Connections from remote IP addresses 18.238.132.68 1 52.55.200.219 1 104.17.24.14 1 104.18.28.243 1 142.251.4.188 2 147.135.54.159 1 162.247.241.2 1 172.67.218.245 1 192.168.1.105 1 Connections to local IP addresses 192.168.1.238 10 Connections to top 10 local ports 33332 1 35414 1 42744 1 47926 1 50922 1 52888 1 58112 1 60468 1 60748 1 60752 1 States of connections ESTABLISHED 8 LISTEN 6 TIME_WAIT 2 # Top Processes ############################################## PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8582 stoker 20 0 13212 4096 3328 R 12.5 0.0 0:00.02 top 1 root 20 0 168020 12908 8044 S 0.0 0.1 0:04.41 systemd 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd 3 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_gp 4 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_par+ 5 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 slub_fl+ 6 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 netns 8 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 kworker+ 10 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 mm_perc+ # Notable Processes ########################################## PID OOM COMMAND ? ? sshd doesn't appear to be running # Simplified and fuzzy rounded vmstat (wait please) ########## procs ---swap-- -----io---- ---system---- --------cpu-------- r b si so bi bo ir cs us sy il wa st 1 0 0 0 150 225 250 600 7 2 90 2 0 0 0 0 0 0 90 900 3000 3 4 92 1 0 0 0 0 0 0 350 600 1250 2 1 93 4 0 0 0 0 0 0 225 700 1500 3 2 93 2 0 0 0 0 0 0 100 800 1500 1 12 87 0 0 # Memory management ########################################## Transparent huge pages are enabled. # The End #################################################### |
The specifics for a MySQL server cover the details you will want to know about that instance can easily be had by using pt-mysql-sumary.
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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 | # Percona Toolkit MySQL Summary Report ####################### System time | 2023-09-25 15:50:16 UTC (local TZ: CDT -0500) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== # MySQL Executable ########################################### Path to executable | /usr/sbin/mysqld Has symbols | # Slave Hosts ################################################ No slaves found # Report On Port 3306 ######################################## User | root@localhost Time | 2023-09-25 10:50:16 (CDT) Hostname | testbox Version | 8.0.33-25 Percona Server (GPL), Release '25', Revision '60c9e2c5' Built On | Linux x86_64 Started | 2023-09-25 09:22 (up 0+01:28:02) Databases | 4 Datadir | /var/lib/mysql/ Processes | 1 connected, 2 running Replication | Is not a slave, has 0 slaves connected Pidfile | /var/run/mysqld/mysqld.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Daemon 1 1 5000 5000 Query 1 1 0 0 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- event_scheduler 1 1 5000 5000 root 1 1 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- localhost 2 2 5000 5000 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 2 2 5000 5000 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- init 1 1 0 0 Waiting on empty queue 1 1 5000 5000 # Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 11 secs Aborted_connects 150 Binlog_snapshot_position 12500 Binlog_cache_use 50 Bytes_received 200000 2 500 Bytes_sent 2250000 25 3000 Com_change_db 15 Com_create_db 15 Com_create_table 600 Com_create_udf 100 Com_flush 15 Com_select 700 2 Com_set_option 70 Com_show_binlogs 15 Com_show_databases 15 Com_show_engine_status 15 Com_show_master_status 15 Com_show_plugins 15 Com_show_processlist 15 Com_show_replicas 15 Com_show_slave_hosts 15 Com_show_replica_status 15 Com_show_slave_status 15 Com_show_status 80 Com_show_storage_engines 15 Com_show_variables 35 Connections 800 1 Created_tmp_disk_tables 35 Created_tmp_files 80 Created_tmp_tables 175 Error_log_buffered_bytes 50000 Error_log_buffered_events 350 Error_log_latest_write 30000000000000000 300000000000 Flush_commands 50 Handler_commit 10000 Handler_delete 125 Handler_external_lock 100000 1 2 Handler_prepare 200 Handler_read_first 800 Handler_read_key 30000 1 Handler_read_next 70000 Handler_read_rnd_next 125000 1 100 Handler_rollback 50 Handler_update 5000 Handler_write 60000 50 Innodb_buffer_pool_bytes_data 250000000 3000 Innodb_buffer_pool_pages_flushed 3500 Innodb_buffer_pool_pages_made_not_young 35 Innodb_buffer_pool_pages_made_young 250 Innodb_buffer_pool_pages_old 6000 Innodb_buffer_pool_read_requests 250000 3 7 Innodb_buffer_pool_reads 12500 Innodb_buffer_pool_write_requests 35000 4 Innodb_checkpoint_max_age 1500000000 17500 Innodb_data_fsyncs 2500 Innodb_data_read 225000000 2500 Innodb_data_reads 15000 Innodb_data_writes 6000 Innodb_data_written 60000000 700 1500 Innodb_dblwr_pages_written 1250 Innodb_dblwr_writes 350 Innodb_ibuf_segment_size 35 Innodb_redo_log_uuid 17500000000 200000 Innodb_redo_log_checkpoint_lsn 300000000 3500 Innodb_redo_log_current_lsn 300000000 3500 Innodb_redo_log_flushed_to_disk_lsn 300000000 3500 Innodb_redo_log_logical_size 8000 Innodb_redo_log_physical_size 50000000 600 Innodb_redo_log_capacity_resized 1750000000 20000 Innodb_log_write_requests 15000 Innodb_log_writes 1000 Innodb_lsn_current 300000000 3500 Innodb_lsn_flushed 300000000 3500 Innodb_lsn_last_checkpoint 300000000 3500 Innodb_master_thread_active_loops 125 Innodb_master_thread_idle_loops 90000 Innodb_max_trx_id 35000 Innodb_os_log_fsyncs 700 Innodb_os_log_written 1250000 15 Innodb_pages_created 2500 Innodb_pages_read 12500 Innodb_pages0_read 125 Innodb_pages_written 3500 Innodb_purge_trx_id 35000 Innodb_rows_inserted 1500 4 Innodb_rows_read 1500 4 Innodb_system_rows_deleted 125 Innodb_system_rows_inserted 175 Innodb_system_rows_read 80000 2 Innodb_system_rows_updated 5000 Innodb_num_open_files 250 Innodb_undo_tablespaces_total 35 Innodb_undo_tablespaces_implicit 35 Innodb_undo_tablespaces_active 35 Innodb_secondary_index_triggered_cluster_reads 35000 Mysqlx_port 500000 6 Mysqlx_ssl_ctx_verify_mode 80 Mysqlx_worker_threads 35 Net_buffer_length 250000 3 Open_table_definitions 800 Opened_files 35 Opened_table_definitions 1250 Opened_tables 3500 1 Performance_schema_session_connect_attrs_longest_seen 2000 Queries 1500 4 Questions 1500 4 Select_full_join 50 Select_scan 350 Sort_rows 70 Sort_scan 15 Ssl_session_cache_timeout 5000 Table_locks_immediate 125 Table_open_cache_hits 50000 Table_open_cache_misses 3500 1 Threads_created 35 Uptime 90000 1 1 # Table cache ################################################ Size | 4000 Usage | 3% # Key Percona Server features ################################ Table & Index Stats | Disabled Multiple I/O Threads | Enabled Corruption Resilient | Enabled Durable Replication | Not Supported Import InnoDB Tables | Not Supported Fast Server Restarts | Not Supported Enhanced Logging | Disabled Replica Perf Logging | Disabled Response Time Hist. | Not Supported Smooth Flushing | Not Supported HandlerSocket NoSQL | Not Supported Fast Hash UDFs | Enabled # Percona XtraDB Cluster ##################################### # Plugins #################################################### InnoDB compression | ACTIVE # Schema ##################################################### Specify --databases or --all-databases to dump and summarize schemas # Noteworthy Technologies #################################### SSL | No Explicit LOCK TABLES | No Delayed Insert | No XA Transactions | No NDB Cluster | No Prepared Statements | No Prepared statement count | 0 # InnoDB ##################################################### Version | 8.0.33-25 Buffer Pool Size | 128.0M Buffer Pool Fill | 10% Buffer Pool Dirty | 0% File Per Table | ON Page Size | 16k Log File Size | 2 * 48.0M = 96.0M Log Buffer Size | 16M Flush Method | fsync Flush Log At Commit | 1 XA Support | Checksums | Doublewrite | ON R/W I/O Threads | 4 4 I/O Capacity | 200 Thread Concurrency | 0 Concurrency Tickets | 5000 Commit Concurrency | 0 Txn Isolation Level | Adaptive Flushing | ON Adaptive Checkpoint | Checkpoint Age | 0 InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue Oldest Transaction | 0 Seconds History List Len | 0 Read Views | 0 Undo Log Entries | 0 transactions, 0 total undo, 0 max undo Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads 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 Pending I/O Flushes | 0 buf pool, 0 log Transaction States | 3xnot started # MyISAM ##################################################### Key Cache | 8.0M Pct Used | 20% Unflushed | 0% # Security ################################################### Users | 1 users, 0 anon, 0 w/o pw, 1 old pw Old Passwords | # Encryption ################################################# No keyring plugins found # Binary Logging ############################################# Binlogs | 2 Zero-Sized | 0 Total Size | 1.3k binlog_format | ROW expire_logs_days | 0 sync_binlog | 1 server_id | 1 binlog_do_db | binlog_ignore_db | # Noteworthy Variables ####################################### Auto-Inc Incr/Offset | 1/1 default_storage_engine | InnoDB flush_time | 0 init_connect | init_file | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION join_buffer_size | 256k sort_buffer_size | 256k read_buffer_size | 128k read_rnd_buffer_size | 256k bulk_insert_buffer | 0.00 max_heap_table_size | 16M tmp_table_size | 16M max_allowed_packet | 64M thread_stack | 1M log | log_error | /var/log/mysql/error.log log_warnings | log_slow_queries | log_queries_not_using_indexes | OFF log_slave_updates | ON # Configuration File ######################################### Config File | /etc/mysql/my.cnf # /etc/mysql/conf.d/mysql.cnf [mysqld] # /etc/mysql/conf.d/mysqldump.cnf [mysqldump] quick quote-names max_allowed_packet = 16M # /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # Memory management library ################################## jemalloc is not enabled in mysql config for process with id 6899 # The End #################################################### |
And pt-pg-summary is the analog for the PostgreSQL server.
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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 | $ t-pg-summary -U postgres INFO[0000] Connecting to the database server using: sslmode=disable dbname=postgres INFO[0000] Connection OK INFO[0000] Detected PostgreSQL version: 16.0.0 INFO[0000] Getting global information INFO[0000] Collecting global counters (1st pass) INFO[0000] Collecting Cluster information INFO[0000] Waiting 10 seconds to read counters INFO[0000] Collecting Connected Clients information INFO[0000] Collecting Database Wait Events information INFO[0000] Collecting Global Wait Events information INFO[0000] Collecting Port and Data Dir information INFO[0000] Collecting Tablespaces information INFO[0000] Collecting Instance Settings information INFO[0000] Collecting Slave Hosts (PostgreSQL 10+) INFO[0000] Waiting for counters information INFO[0010] Collecting global counters (2nd pass) INFO[0010] Collecting processes command line information INFO[0010] Finished collecting global information INFO[0010] Collecting per database information INFO[0010] Connecting to the "postgres" database INFO[0010] Collecting Table Access information INFO[0010] Collecting Table Cache Hit Ratio information INFO[0010] Collecting Index Cache Hit Ratio information INFO[0010] Connecting to the "test" database INFO[0010] Collecting Table Access information INFO[0010] Collecting Table Cache Hit Ratio information INFO[0010] Collecting Index Cache Hit Ratio information INFO[0010] Connecting to the "davetest" database INFO[0010] Collecting Table Access information INFO[0010] Collecting Table Cache Hit Ratio information INFO[0010] Collecting Index Cache Hit Ratio information INFO[0010] Connecting to the "postgis" database INFO[0010] Collecting Table Access information INFO[0010] Collecting Table Cache Hit Ratio information INFO[0010] Collecting Index Cache Hit Ratio information ##### --- Database Port and Data_Directory --- #### +----------------------+----------------------------------------------------+ | Name | Setting | +----------------------+----------------------------------------------------+ | data_directory | /var/lib/postgresql/16/main | +----------------------+----------------------------------------------------+ ##### --- List of Tablespaces ---- ###### +----------------------+----------------------+----------------------------------------------------+ | Name | Owner | Location | +----------------------+----------------------+----------------------------------------------------+ | pg_default | postgres | | | pg_global | postgres | | +----------------------+----------------------+----------------------------------------------------+ ##### --- Slave and the lag with Master --- #### There are no slave hosts ##### --- Cluster Information --- #### +------------------------------------------------------------------------------------------------------+ Usename : postgres Time : 2023-09-25 10:23:17.068273 -0500 CDT Client Address : 127.0.0.1 Client Hostname: Version : PostgreSQL 16.0 - Percona Distribution on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.... Started : 2023-09-25 08:58:45.535777 -0500 CDT Is Slave : false +------------------------------------------------------------------------------------------------------+ ##### --- Databases --- #### +----------------------+------------+ | Dat Name | Size | +----------------------+------------+ | postgres | 7508 kB | | test | 7644 kB | | template1 | 7580 kB | | template0 | 7353 kB | | davetest | 7596 kB | | postgis | 7580 kB | +----------------------+------------+ ##### --- Index Cache Hit Ratios --- #### Database: davetest +----------------------+------------+ | Index Name | Ratio | +----------------------+------------+ | index hit rate | 0.00 | +----------------------+------------+ Database: postgis +----------------------+------------+ | Index Name | Ratio | +----------------------+------------+ | index hit rate | 0.00 | +----------------------+------------+ Database: postgres +----------------------+------------+ | Index Name | Ratio | +----------------------+------------+ | index hit rate | 0.00 | +----------------------+------------+ Database: test +----------------------+------------+ | Index Name | Ratio | +----------------------+------------+ | index hit rate | 0.00 | +----------------------+------------+ ##### --- Table Cache Hit Ratios --- #### Database: davetest +----------------------+------------+ | Index Name | Ratio | +----------------------+------------+ | cache hit rate | 0.00 | +----------------------+------------+ Database: postgis +----------------------+------------+ | Index Name | Ratio | +----------------------+------------+ | cache hit rate | 0.00 | +----------------------+------------+ Database: postgres +----------------------+------------+ | Index Name | Ratio | +----------------------+------------+ | cache hit rate | 0.00 | +----------------------+------------+ Database: test +----------------------+------------+ | Index Name | Ratio | +----------------------+------------+ | cache hit rate | 0.00 | +----------------------+------------+ ##### --- List of Wait_events for the entire Cluster - all-databases --- #### +----------------------+----------------------+---------+ | Wait Event Type | Event | Count | +----------------------+----------------------+---------+ | Activity | BgWriterHibernate | 1 | | Activity | WalWriterMain | 1 | | Activity | LogicalLauncherMain | 1 | | Activity | CheckpointerMain | 1 | | Activity | AutoVacuumMain | 1 | +----------------------+----------------------+---------+ ##### --- List of users and client_addr or client_hostname connected to --all-databases --- #### +----------------------+------------+---------+----------------------+---------+ | Wait Event Type | Client | State | Count | +----------------------+------------+---------+----------------------+---------+ | postgres | 127.0.0.1/32 | active | 1 | +----------------------+------------+---------+----------------------+---------+ ##### --- Counters diff after 10 seconds --- #### +----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+ | Database | Numbackends | XactCommit | XactRollback | BlksRead | BlksHit | TupReturned | TupFetched | TupInserted | TupUpdated | TupDeleted | Conflicts | TempFiles | TempBytes | Deadlocks | +----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+ | | 0 | 0 | 0 | 0 | 25 | 68 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | davetest | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | postgis | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | postgres | 0 | 13 | 0 | 0 | 1480 | 1155 | 1155 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | template0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | template1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | test | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+ ##### --- Table access per database --- #### Database: davetest +----------------------------------------------------+------+--------------------------------+---------+ | Relname | Kind | Datname | Count | +----------------------------------------------------+------+--------------------------------+---------+ | pg_database_datname_index | i | | 1 | | pg_database | r | | 1 | | pg_class | r | davetest | 1 | | pg_locks | v | davetest | 1 | | pg_class_oid_index | i | davetest | 1 | | pg_stat_database | v | davetest | 1 | | pg_database_oid_index | i | | 1 | | pg_class_relname_nsp_index | i | davetest | 1 | | pg_class_tblspc_relfilenode_index | i | davetest | 1 | +----------------------------------------------------+------+--------------------------------+---------+ Database: postgis +----------------------------------------------------+------+--------------------------------+---------+ | Relname | Kind | Datname | Count | +----------------------------------------------------+------+--------------------------------+---------+ | pg_locks | v | postgis | 1 | | pg_class_tblspc_relfilenode_index | i | postgis | 1 | | pg_class_relname_nsp_index | i | postgis | 1 | | pg_database_datname_index | i | | 1 | | pg_class | r | postgis | 1 | | pg_database | r | | 1 | | pg_stat_database | v | postgis | 1 | | pg_class_oid_index | i | postgis | 1 | | pg_database_oid_index | i | | 1 | +----------------------------------------------------+------+--------------------------------+---------+ Database: postgres +----------------------------------------------------+------+--------------------------------+---------+ | Relname | Kind | Datname | Count | +----------------------------------------------------+------+--------------------------------+---------+ | pg_database_datname_index | i | | 1 | | pg_class | r | postgres | 1 | | pg_database | r | | 1 | | pg_stat_database | v | postgres | 1 | | pg_class_relname_nsp_index | i | postgres | 1 | | pg_class_oid_index | i | postgres | 1 | | pg_database_oid_index | i | | 1 | | pg_class_tblspc_relfilenode_index | i | postgres | 1 | | pg_locks | v | postgres | 1 | +----------------------------------------------------+------+--------------------------------+---------+ Database: test +----------------------------------------------------+------+--------------------------------+---------+ | Relname | Kind | Datname | Count | +----------------------------------------------------+------+--------------------------------+---------+ | pg_database_datname_index | i | | 1 | | pg_class_oid_index | i | test | 1 | | pg_class_tblspc_relfilenode_index | i | test | 1 | | pg_database | r | | 1 | | pg_locks | v | test | 1 | | pg_class | r | test | 1 | | pg_database_oid_index | i | | 1 | | pg_stat_database | v | test | 1 | | pg_class_relname_nsp_index | i | test | 1 | +----------------------------------------------------+------+--------------------------------+---------+ ##### --- Instance settings --- #### Setting Value allow_in_place_tablespaces : off allow_system_table_mods : off application_name : archive_cleanup_command : archive_command : (disabled) archive_library : archive_mode : off archive_timeout : 0 array_nulls : on authentication_timeout : 60 autovacuum : on autovacuum_analyze_scale_factor : 0.1 autovacuum_analyze_threshold : 50 autovacuum_freeze_max_age : 200000000 autovacuum_max_workers : 3 autovacuum_multixact_freeze_max_age : 400000000 autovacuum_naptime : 60 autovacuum_vacuum_cost_delay : 2 autovacuum_vacuum_cost_limit : -1 autovacuum_vacuum_insert_scale_factor : 0.2 autovacuum_vacuum_insert_threshold : 1000 autovacuum_vacuum_scale_factor : 0.2 autovacuum_vacuum_threshold : 50 autovacuum_work_mem : -1 backend_flush_after : 0 backslash_quote : safe_encoding backtrace_functions : bgwriter_delay : 200 bgwriter_flush_after : 64 bgwriter_lru_maxpages : 100 bgwriter_lru_multiplier : 2 block_size : 8192 bonjour : off bonjour_name : bytea_output : hex check_function_bodies : on checkpoint_completion_target : 0.9 checkpoint_flush_after : 32 checkpoint_timeout : 300 checkpoint_warning : 30 client_connection_check_interval : 0 client_encoding : UTF8 client_min_messages : notice cluster_name : 16/main commit_delay : 0 commit_siblings : 5 compute_query_id : auto config_file : /etc/postgresql/16/main/postgresql.conf constraint_exclusion : partition cpu_index_tuple_cost : 0.005 cpu_operator_cost : 0.0025 cpu_tuple_cost : 0.01 createrole_self_grant : cursor_tuple_fraction : 0.1 data_checksums : off data_directory : /var/lib/postgresql/16/main data_directory_mode : 0700 data_sync_retry : off DateStyle : ISO, MDY db_user_namespace : off deadlock_timeout : 1000 debug_assertions : off debug_discard_caches : 0 debug_io_direct : debug_logical_replication_streaming : buffered debug_parallel_query : off debug_pretty_print : on debug_print_parse : off debug_print_plan : off debug_print_rewritten : off default_statistics_target : 100 default_table_access_method : heap default_tablespace : default_text_search_config : pg_catalog.english default_toast_compression : pglz default_transaction_deferrable : off default_transaction_isolation : read committed default_transaction_read_only : off dynamic_library_path : $libdir dynamic_shared_memory_type : posix effective_cache_size : 524288 effective_io_concurrency : 1 enable_async_append : on enable_bitmapscan : on enable_gathermerge : on enable_hashagg : on enable_hashjoin : on enable_incremental_sort : on enable_indexonlyscan : on enable_indexscan : on enable_material : on enable_memoize : on enable_mergejoin : on enable_nestloop : on enable_parallel_append : on enable_parallel_hash : on enable_partition_pruning : on enable_partitionwise_aggregate : off enable_partitionwise_join : off enable_presorted_aggregate : on enable_seqscan : on enable_sort : on enable_tidscan : on escape_string_warning : on event_source : PostgreSQL exit_on_error : off extension_destdir : external_pid_file : /var/run/postgresql/16-main.pid extra_float_digits : 2 from_collapse_limit : 8 fsync : on full_page_writes : on geqo : on geqo_effort : 5 geqo_generations : 0 geqo_pool_size : 0 geqo_seed : 0 geqo_selection_bias : 2 geqo_threshold : 12 gin_fuzzy_search_limit : 0 gin_pending_list_limit : 4096 gss_accept_delegation : off hash_mem_multiplier : 2 hba_file : /etc/postgresql/16/main/pg_hba.conf hot_standby : on hot_standby_feedback : off huge_page_size : 0 huge_pages : try icu_validation_level : warning ident_file : /etc/postgresql/16/main/pg_ident.conf idle_in_transaction_session_timeout : 0 idle_session_timeout : 0 ignore_checksum_failure : off ignore_invalid_pages : off ignore_system_indexes : off in_hot_standby : off integer_datetimes : on IntervalStyle : postgres jit : on jit_above_cost : 100000 jit_debugging_support : off jit_dump_bitcode : off jit_expressions : on jit_inline_above_cost : 500000 jit_optimize_above_cost : 500000 jit_profiling_support : off jit_provider : llvmjit jit_tuple_deforming : on join_collapse_limit : 8 krb_caseins_users : off krb_server_keyfile : FILE:/etc/postgresql-common/krb5.keytab lc_messages : en_US.UTF-8 lc_monetary : en_US.UTF-8 lc_numeric : en_US.UTF-8 lc_time : en_US.UTF-8 listen_addresses : localhost lo_compat_privileges : off local_preload_libraries : lock_timeout : 0 log_autovacuum_min_duration : 600000 log_checkpoints : on log_connections : off log_destination : stderr log_directory : log log_disconnections : off log_duration : off log_error_verbosity : default log_executor_stats : off log_file_mode : 0600 log_filename : postgresql-%Y-%m-%d_%H%M%S.log log_hostname : off log_line_prefix : %m [%p] %q%u@%d log_lock_waits : off log_min_duration_sample : -1 log_min_duration_statement : -1 log_min_error_statement : error log_min_messages : warning log_parameter_max_length : -1 log_parameter_max_length_on_error : 0 log_parser_stats : off log_planner_stats : off log_recovery_conflict_waits : off log_replication_commands : off log_rotation_age : 1440 log_rotation_size : 10240 log_startup_progress_interval : 10000 log_statement : none log_statement_sample_rate : 1 log_statement_stats : off log_temp_files : -1 log_timezone : America/Chicago log_transaction_sample_rate : 0 log_truncate_on_rotation : off logging_collector : off logical_decoding_work_mem : 65536 maintenance_io_concurrency : 10 maintenance_work_mem : 65536 max_connections : 100 max_files_per_process : 1000 max_function_args : 100 max_identifier_length : 63 max_index_keys : 32 max_locks_per_transaction : 64 max_logical_replication_workers : 16 max_parallel_apply_workers_per_subscription : 2 max_parallel_maintenance_workers : 2 max_parallel_workers : 8 max_parallel_workers_per_gather : 2 max_pred_locks_per_page : 2 max_pred_locks_per_relation : -2 max_pred_locks_per_transaction : 64 max_prepared_transactions : 0 max_replication_slots : 10 max_slot_wal_keep_size : -1 max_stack_depth : 2048 max_standby_archive_delay : 30000 max_standby_streaming_delay : 30000 max_sync_workers_per_subscription : 2 max_wal_senders : 10 max_wal_size : 1024 max_worker_processes : 8 min_dynamic_shared_memory : 0 min_parallel_index_scan_size : 64 min_parallel_table_scan_size : 1024 min_wal_size : 80 old_snapshot_threshold : -1 parallel_leader_participation : on parallel_setup_cost : 1000 parallel_tuple_cost : 0.1 password_encryption : scram-sha-256 plan_cache_mode : auto port : 5432 post_auth_delay : 0 pre_auth_delay : 0 primary_conninfo : primary_slot_name : quote_all_identifiers : off random_page_cost : 4 recovery_end_command : recovery_init_sync_method : fsync recovery_min_apply_delay : 0 recovery_prefetch : try recovery_target : recovery_target_action : pause recovery_target_inclusive : on recovery_target_lsn : recovery_target_name : recovery_target_time : recovery_target_timeline : latest recovery_target_xid : recursive_worktable_factor : 10 remove_temp_files_after_crash : on reserved_connections : 0 restart_after_crash : on restore_command : row_security : on scram_iterations : 4096 search_path : "$user", public segment_size : 131072 send_abort_for_crash : off send_abort_for_kill : off seq_page_cost : 1 server_encoding : UTF8 server_version : 16.0 - Percona Distribution server_version_num : 160000 session_preload_libraries : session_replication_role : origin shared_buffers : 16384 shared_memory_size : 143 shared_memory_size_in_huge_pages : 72 shared_memory_type : mmap shared_preload_libraries : ssl : on ssl_ca_file : ssl_cert_file : /etc/ssl/certs/ssl-cert-snakeoil.pem ssl_ciphers : HIGH:MEDIUM:+3DES:!aNULL ssl_crl_dir : ssl_crl_file : ssl_dh_params_file : ssl_ecdh_curve : prime256v1 ssl_key_file : /etc/ssl/private/ssl-cert-snakeoil.key ssl_library : OpenSSL ssl_max_protocol_version : ssl_min_protocol_version : TLSv1.2 ssl_passphrase_command : ssl_passphrase_command_supports_reload : off ssl_prefer_server_ciphers : on standard_conforming_strings : on statement_timeout : 0 stats_fetch_consistency : cache superuser_reserved_connections : 3 synchronize_seqscans : on synchronous_commit : on synchronous_standby_names : syslog_facility : local0 syslog_ident : postgres syslog_sequence_numbers : on syslog_split_messages : on tcp_keepalives_count : 9 tcp_keepalives_idle : 7200 tcp_keepalives_interval : 75 tcp_user_timeout : 0 temp_buffers : 1024 temp_file_limit : -1 temp_tablespaces : TimeZone : America/Chicago timezone_abbreviations : Default trace_notify : off trace_recovery_messages : log trace_sort : off track_activities : on track_activity_query_size : 1024 track_commit_timestamp : off track_counts : on track_functions : none track_io_timing : off track_wal_io_timing : off transaction_deferrable : off transaction_isolation : read committed transaction_read_only : off transform_null_equals : off unix_socket_directories : /var/run/postgresql unix_socket_group : unix_socket_permissions : 0777 update_process_title : on vacuum_buffer_usage_limit : 256 vacuum_cost_delay : 0 vacuum_cost_limit : 200 vacuum_cost_page_dirty : 20 vacuum_cost_page_hit : 1 vacuum_cost_page_miss : 2 vacuum_failsafe_age : 1600000000 vacuum_freeze_min_age : 50000000 vacuum_freeze_table_age : 150000000 vacuum_multixact_failsafe_age : 1600000000 vacuum_multixact_freeze_min_age : 5000000 vacuum_multixact_freeze_table_age : 150000000 wal_block_size : 8192 wal_buffers : 512 wal_compression : off wal_consistency_checking : wal_decode_buffer_size : 524288 wal_init_zero : on wal_keep_size : 0 wal_level : logical wal_log_hints : off wal_receiver_create_temp_slot : off wal_receiver_status_interval : 10 wal_receiver_timeout : 60000 wal_recycle : on wal_retrieve_retry_interval : 5000 wal_segment_size : 16777216 wal_sender_timeout : 60000 wal_skip_threshold : 2048 wal_sync_method : fdatasync wal_writer_delay : 200 wal_writer_flush_after : 128 work_mem : 4096 xmlbinary : base64 xmloption : content zero_damaged_pages : off ##### --- Processes start up command --- #### PID : Command line 776 : /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf |
So why is this valuable?
All the information that can be had from pt-summary, pt-mysql-summary, and pt-pg-summary can be had with SQL queries. But under pressure, will you remember all the needed statements? These scripts make it easier to get the needed information in one swoop.
This is also a great source of information to archive for future reference. Need to know what the last known good configuration looked like? Were you running SELinux when you set up the server? This is the type of information that is invaluable if you have hit handy and frustrating when you do not have it when tracing issues.
In the future, we will look at more of the Percona Toolkit and its uses.
Free your DBAs to focus on higher priorities.
Symlinks.