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 |
sudo cp -v /usr/lib/mysql/plugin/libfnv_udf.so /usr/lib<br>sudo cp -v /usr/lib/mysql/plugin/libfnv1a_udf.so /usr/lib<br>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 |
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" -u root -p<br>mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" -u root -p<br>mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'" -u root -p |
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 |
$ pt-summary --user=root<br>[?2004l# Percona Toolkit System Summary Report ######################<br>Date | 2023-09-25 14:43:34 UTC (local TZ: CDT -0500)<br>Hostname | testbox<br>Uptime | 44 min, 1 user, load average: 0.47, 0.67, 0.57<br>Platform | Linux<br>Release | Ubuntu 22.04.3 LTS (jammy)<br>Kernel | 6.2.0-33-generic<br>Architecture | CPU = 64-bit, OS = 64-bit<br>Threading | NPTL 2.35<br>SELinux | No SELinux detected<br>Virtualized | No virtualization detected<br># Processor ##################################################<br>Processors | physical = 1, cores = 2, virtual = 4, hyperthreading = yes<br>Speeds | 1x3000.651, 1x3061.574, 1x3069.932, 1x3093.881<br>Models | 4xIntel(R) Core(TM) i7-6500U CPU @ 2.50GHz<br>Caches | 4x4096 KB<br># Memory #####################################################<br>Total | 15.4G<br>Free | 9.4G<br>Used | physical = 2.2G, swap allocated = 2.0G, swap used = 0.0, virtual = 2.2G<br>Shared | 475.0M<br>Buffers | 3.8G<br>Caches | 12.4G<br>Dirty | 928 kB<br>UsedRSS | 5.8G<br>Swappiness | 60<br>DirtyPolicy | 20, 10<br>DirtyStatus | 0, 0<br>Locator Size Speed Form Factor Type Type Detail<br>========= ======== ================= ============= ============= ===========<br># Mounted Filesystems ########################################<br>Filesystem Size Used Type Opts Mountpoint<br>/dev/nvme0n1p1 511M 2% vfat rw,relatime,fmask=0077,dmask=0077,codepage=437,iocharset=iso8859-1,shortname=mixed,errors=remount-ro /boot/efi<br>/dev/nvme0n1p2 457G 4% ext4 rw,relatime,errors=remount-ro /<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,inode64 /run<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=5120k,inode64 /run<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,relatime,size=1612684k,nr_inodes=403171,mode=700,uid=1000,gid=1000,inode64 /run<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,inode64 /run/user/1000<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=5120k,inode64 /run/user/1000<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run/user/1000<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run/user/1000<br>tmpfs 1.6G 1% tmpfs rw,nosuid,nodev,relatime,size=1612684k,nr_inodes=403171,mode=700,uid=1000,gid=1000,inode64 /run/user/1000<br>tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,inode64 /run/lock<br>tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=5120k,inode64 /run/lock<br>tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run/lock<br>tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /run/lock<br>tmpfs 5.0M 1% tmpfs rw,nosuid,nodev,relatime,size=1612684k,nr_inodes=403171,mode=700,uid=1000,gid=1000,inode64 /run/lock<br>tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,inode64 /dev/shm<br>tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,noexec,relatime,size=5120k,inode64 /dev/shm<br>tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /dev/shm<br>tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,noexec,relatime,size=1612688k,mode=755,inode64 /dev/shm<br>tmpfs 7.7G 2% tmpfs rw,nosuid,nodev,relatime,size=1612684k,nr_inodes=403171,mode=700,uid=1000,gid=1000,inode64 /dev/shm<br># Disk Schedulers And Queue Size #############################<br>nvme0n1 | [none] 1023<br>sda | [mq-deadline] 2<br># Disk Partioning ############################################<br># Kernel Inode State #########################################<br>dentry-state | 324004 295487 45 0 21657 0<br>file-nr | 9792 0 9223372036854775807<br>inode-nr | 301794 491<br># LVM Volumes ################################################<br>Unable to collect information<br># LVM Volume Groups ##########################################<br>Unable to collect information<br># RAID Controller ############################################<br>Controller | No RAID controller detected<br># Network Config #############################################<br>Controller | Intel Corporation Ethernet Connection I219-V (rev 21)<br>FIN Timeout | 60<br>Port Range | 60999<br># Interface Statistics #######################################<br>interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors<br>========= ========= ========== ========== ========== ========== ==========<br>lo 400000 3500 0 400000 3500 0<br>enp0s31f6 350000000 250000 0 15000000 125000 0<br>wlp4s0 500000 3500 0 300000 2250 0<br># Network Connections ########################################<br>Connections from remote IP addresses<br>18.238.132.68 1<br>52.55.200.219 1<br>104.17.24.14 1<br>104.18.28.243 1<br>142.251.4.188 2<br>147.135.54.159 1<br>162.247.241.2 1<br>172.67.218.245 1<br>192.168.1.105 1<br>Connections to local IP addresses<br>192.168.1.238 10<br>Connections to top 10 local ports<br>33332 1<br>35414 1<br>42744 1<br>47926 1<br>50922 1<br>52888 1<br>58112 1<br>60468 1<br>60748 1<br>60752 1<br>States of connections<br>ESTABLISHED 8<br>LISTEN 6<br>TIME_WAIT 2<br># Top Processes ##############################################<br>PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND<br>8582 stoker 20 0 13212 4096 3328 R 12.5 0.0 0:00.02 top<br>1 root 20 0 168020 12908 8044 S 0.0 0.1 0:04.41 systemd<br>2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd<br>3 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_gp<br>4 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_par+<br>5 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 slub_fl+<br>6 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 netns<br>8 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 kworker+<br>10 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 mm_perc+<br># Notable Processes ##########################################<br>PID OOM COMMAND<br>? ? sshd doesn't appear to be running<br># Simplified and fuzzy rounded vmstat (wait please) ##########<br>procs ---swap-- -----io---- ---system---- --------cpu--------<br>r b si so bi bo ir cs us sy il wa st<br>1 0 0 0 150 225 250 600 7 2 90 2 0<br>0 0 0 0 0 90 900 3000 3 4 92 1 0<br>0 0 0 0 0 350 600 1250 2 1 93 4 0<br>0 0 0 0 0 225 700 1500 3 2 93 2 0<br>0 0 0 0 0 100 800 1500 1 12 87 0 0<br># Memory management ##########################################<br>Transparent huge pages are enabled.<br># 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 |
# Percona Toolkit MySQL Summary Report #######################<br>System time | 2023-09-25 15:50:16 UTC (local TZ: CDT -0500)<br># Instances ##################################################<br>Port Data Directory Nice OOM Socket<br>===== ========================== ==== === ======<br># MySQL Executable ###########################################<br>Path to executable | /usr/sbin/mysqld<br>Has symbols | <br># Slave Hosts ################################################<br>No slaves found<br># Report On Port 3306 ########################################<br>User | root@localhost<br>Time | 2023-09-25 10:50:16 (CDT)<br>Hostname | testbox<br>Version | 8.0.33-25 Percona Server (GPL), Release '25', Revision '60c9e2c5'<br>Built On | Linux x86_64<br>Started | 2023-09-25 09:22 (up 0+01:28:02)<br>Databases | 4<br>Datadir | /var/lib/mysql/<br>Processes | 1 connected, 2 running<br>Replication | Is not a slave, has 0 slaves connected<br>Pidfile | /var/run/mysqld/mysqld.pid (exists)<br># Processlist ################################################<br><br>Command COUNT(*) Working SUM(Time) MAX(Time)<br>------------------------------ -------- ------- --------- ---------<br>Daemon 1 1 5000 5000<br>Query 1 1 0 0<br><br>User COUNT(*) Working SUM(Time) MAX(Time)<br>------------------------------ -------- ------- --------- ---------<br>event_scheduler 1 1 5000 5000<br>root 1 1 0 0<br><br>Host COUNT(*) Working SUM(Time) MAX(Time)<br>------------------------------ -------- ------- --------- ---------<br>localhost 2 2 5000 5000<br><br>db COUNT(*) Working SUM(Time) MAX(Time)<br>------------------------------ -------- ------- --------- ---------<br>NULL 2 2 5000 5000<br><br>State COUNT(*) Working SUM(Time) MAX(Time)<br>------------------------------ -------- ------- --------- ---------<br>init 1 1 0 0<br>Waiting on empty queue 1 1 5000 5000<br><br># Status Counters (Wait 10 Seconds) ##########################<br>Variable Per day Per second 11 secs<br>Aborted_connects 150 <br>Binlog_snapshot_position 12500 <br>Binlog_cache_use 50 <br>Bytes_received 200000 2 500<br>Bytes_sent 2250000 25 3000<br>Com_change_db 15 <br>Com_create_db 15 <br>Com_create_table 600 <br>Com_create_udf 100 <br>Com_flush 15 <br>Com_select 700 2<br>Com_set_option 70 <br>Com_show_binlogs 15 <br>Com_show_databases 15 <br>Com_show_engine_status 15 <br>Com_show_master_status 15 <br>Com_show_plugins 15 <br>Com_show_processlist 15 <br>Com_show_replicas 15 <br>Com_show_slave_hosts 15 <br>Com_show_replica_status 15 <br>Com_show_slave_status 15 <br>Com_show_status 80 <br>Com_show_storage_engines 15 <br>Com_show_variables 35 <br>Connections 800 1<br>Created_tmp_disk_tables 35 <br>Created_tmp_files 80 <br>Created_tmp_tables 175 <br>Error_log_buffered_bytes 50000 <br>Error_log_buffered_events 350 <br>Error_log_latest_write 30000000000000000 300000000000 <br>Flush_commands 50 <br>Handler_commit 10000 <br>Handler_delete 125 <br>Handler_external_lock 100000 1 2<br>Handler_prepare 200 <br>Handler_read_first 800 <br>Handler_read_key 30000 1<br>Handler_read_next 70000 <br>Handler_read_rnd_next 125000 1 100<br>Handler_rollback 50 <br>Handler_update 5000 <br>Handler_write 60000 50<br>Innodb_buffer_pool_bytes_data 250000000 3000 <br>Innodb_buffer_pool_pages_flushed 3500 <br>Innodb_buffer_pool_pages_made_not_young 35 <br>Innodb_buffer_pool_pages_made_young 250 <br>Innodb_buffer_pool_pages_old 6000 <br>Innodb_buffer_pool_read_requests 250000 3 7<br>Innodb_buffer_pool_reads 12500 <br>Innodb_buffer_pool_write_requests 35000 4<br>Innodb_checkpoint_max_age 1500000000 17500 <br>Innodb_data_fsyncs 2500 <br>Innodb_data_read 225000000 2500 <br>Innodb_data_reads 15000 <br>Innodb_data_writes 6000 <br>Innodb_data_written 60000000 700 1500<br>Innodb_dblwr_pages_written 1250 <br>Innodb_dblwr_writes 350 <br>Innodb_ibuf_segment_size 35 <br>Innodb_redo_log_uuid 17500000000 200000 <br>Innodb_redo_log_checkpoint_lsn 300000000 3500 <br>Innodb_redo_log_current_lsn 300000000 3500 <br>Innodb_redo_log_flushed_to_disk_lsn 300000000 3500 <br>Innodb_redo_log_logical_size 8000 <br>Innodb_redo_log_physical_size 50000000 600 <br>Innodb_redo_log_capacity_resized 1750000000 20000 <br>Innodb_log_write_requests 15000 <br>Innodb_log_writes 1000 <br>Innodb_lsn_current 300000000 3500 <br>Innodb_lsn_flushed 300000000 3500 <br>Innodb_lsn_last_checkpoint 300000000 3500 <br>Innodb_master_thread_active_loops 125 <br>Innodb_master_thread_idle_loops 90000 <br>Innodb_max_trx_id 35000 <br>Innodb_os_log_fsyncs 700 <br>Innodb_os_log_written 1250000 15 <br>Innodb_pages_created 2500 <br>Innodb_pages_read 12500 <br>Innodb_pages0_read 125 <br>Innodb_pages_written 3500 <br>Innodb_purge_trx_id 35000 <br>Innodb_rows_inserted 1500 4<br>Innodb_rows_read 1500 4<br>Innodb_system_rows_deleted 125 <br>Innodb_system_rows_inserted 175 <br>Innodb_system_rows_read 80000 2<br>Innodb_system_rows_updated 5000 <br>Innodb_num_open_files 250 <br>Innodb_undo_tablespaces_total 35 <br>Innodb_undo_tablespaces_implicit 35 <br>Innodb_undo_tablespaces_active 35 <br>Innodb_secondary_index_triggered_cluster_reads 35000 <br>Mysqlx_port 500000 6 <br>Mysqlx_ssl_ctx_verify_mode 80 <br>Mysqlx_worker_threads 35 <br>Net_buffer_length 250000 3 <br>Open_table_definitions 800 <br>Opened_files 35 <br>Opened_table_definitions 1250 <br>Opened_tables 3500 1<br>Performance_schema_session_connect_attrs_longest_seen 2000 <br>Queries 1500 4<br>Questions 1500 4<br>Select_full_join 50 <br>Select_scan 350 <br>Sort_rows 70 <br>Sort_scan 15 <br>Ssl_session_cache_timeout 5000 <br>Table_locks_immediate 125 <br>Table_open_cache_hits 50000 <br>Table_open_cache_misses 3500 1<br>Threads_created 35 <br>Uptime 90000 1 1<br># Table cache ################################################<br>Size | 4000<br>Usage | 3%<br># Key Percona Server features ################################<br>Table & Index Stats | Disabled<br>Multiple I/O Threads | Enabled<br>Corruption Resilient | Enabled<br>Durable Replication | Not Supported<br>Import InnoDB Tables | Not Supported<br>Fast Server Restarts | Not Supported<br>Enhanced Logging | Disabled<br>Replica Perf Logging | Disabled<br>Response Time Hist. | Not Supported<br>Smooth Flushing | Not Supported<br>HandlerSocket NoSQL | Not Supported<br>Fast Hash UDFs | Enabled<br># Percona XtraDB Cluster #####################################<br># Plugins ####################################################<br>InnoDB compression | ACTIVE<br># Schema #####################################################<br>Specify --databases or --all-databases to dump and summarize schemas<br># Noteworthy Technologies ####################################<br>SSL | No<br>Explicit LOCK TABLES | No<br>Delayed Insert | No<br>XA Transactions | No<br>NDB Cluster | No<br>Prepared Statements | No<br>Prepared statement count | 0<br># InnoDB #####################################################<br>Version | 8.0.33-25<br>Buffer Pool Size | 128.0M<br>Buffer Pool Fill | 10%<br>Buffer Pool Dirty | 0%<br>File Per Table | ON<br>Page Size | 16k<br>Log File Size | 2 * 48.0M = 96.0M<br>Log Buffer Size | 16M<br>Flush Method | fsync<br>Flush Log At Commit | 1<br>XA Support | <br>Checksums | <br>Doublewrite | ON<br>R/W I/O Threads | 4 4<br>I/O Capacity | 200<br>Thread Concurrency | 0<br>Concurrency Tickets | 5000<br>Commit Concurrency | 0<br>Txn Isolation Level | <br>Adaptive Flushing | ON<br>Adaptive Checkpoint | <br>Checkpoint Age | 0<br>InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue<br>Oldest Transaction | 0 Seconds<br>History List Len | 0<br>Read Views | 0<br>Undo Log Entries | 0 transactions, 0 total undo, 0 max undo<br>Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads<br>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<br>Pending I/O Flushes | 0 buf pool, 0 log<br>Transaction States | 3xnot started<br># MyISAM #####################################################<br>Key Cache | 8.0M<br>Pct Used | 20%<br>Unflushed | 0%<br># Security ###################################################<br>Users | 1 users, 0 anon, 0 w/o pw, 1 old pw<br>Old Passwords | <br># Encryption #################################################<br>No keyring plugins found<br># Binary Logging #############################################<br>Binlogs | 2<br>Zero-Sized | 0<br>Total Size | 1.3k<br>binlog_format | ROW<br>expire_logs_days | 0<br>sync_binlog | 1<br>server_id | 1<br>binlog_do_db | <br>binlog_ignore_db | <br># Noteworthy Variables #######################################<br>Auto-Inc Incr/Offset | 1/1<br>default_storage_engine | InnoDB<br>flush_time | 0<br>init_connect | <br>init_file | <br>sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION<br>join_buffer_size | 256k<br>sort_buffer_size | 256k<br>read_buffer_size | 128k<br>read_rnd_buffer_size | 256k<br>bulk_insert_buffer | 0.00<br>max_heap_table_size | 16M<br>tmp_table_size | 16M<br>max_allowed_packet | 64M<br>thread_stack | 1M<br>log | <br>log_error | /var/log/mysql/error.log<br>log_warnings | <br>log_slow_queries | <br>log_queries_not_using_indexes | OFF<br>log_slave_updates | ON<br># Configuration File #########################################<br>Config File | /etc/mysql/my.cnf<br># /etc/mysql/conf.d/mysql.cnf<br><br>[mysqld]<br># /etc/mysql/conf.d/mysqldump.cnf<br><br>[mysqldump]<br>quick<br>quote-names<br>max_allowed_packet = 16M<br># /etc/mysql/mysql.conf.d/mysqld.cnf<br><br>[mysqld]<br>pid-file = /var/run/mysqld/mysqld.pid<br>socket = /var/run/mysqld/mysqld.sock<br>datadir = /var/lib/mysql<br>log-error = /var/log/mysql/error.log<br># Memory management library ##################################<br>jemalloc is not enabled in mysql config for process with id 6899<br># The End #################################################### |
And pt-pg-summary is the analog for the PostgreSQL server.
|
1 |
$ t-pg-summary -U postgres<br>INFO[0000] Connecting to the database server using: sslmode=disable dbname=postgres <br>INFO[0000] Connection OK <br>INFO[0000] Detected PostgreSQL version: 16.0.0 <br>INFO[0000] Getting global information <br>INFO[0000] Collecting global counters (1st pass) <br>INFO[0000] Collecting Cluster information <br>INFO[0000] Waiting 10 seconds to read counters <br>INFO[0000] Collecting Connected Clients information <br>INFO[0000] Collecting Database Wait Events information <br>INFO[0000] Collecting Global Wait Events information <br>INFO[0000] Collecting Port and Data Dir information <br>INFO[0000] Collecting Tablespaces information <br>INFO[0000] Collecting Instance Settings information <br>INFO[0000] Collecting Slave Hosts (PostgreSQL 10+) <br>INFO[0000] Waiting for counters information <br>INFO[0010] Collecting global counters (2nd pass) <br>INFO[0010] Collecting processes command line information <br>INFO[0010] Finished collecting global information <br>INFO[0010] Collecting per database information <br>INFO[0010] Connecting to the "postgres" database <br>INFO[0010] Collecting Table Access information <br>INFO[0010] Collecting Table Cache Hit Ratio information <br>INFO[0010] Collecting Index Cache Hit Ratio information <br>INFO[0010] Connecting to the "test" database <br>INFO[0010] Collecting Table Access information <br>INFO[0010] Collecting Table Cache Hit Ratio information <br>INFO[0010] Collecting Index Cache Hit Ratio information <br>INFO[0010] Connecting to the "davetest" database <br>INFO[0010] Collecting Table Access information <br>INFO[0010] Collecting Table Cache Hit Ratio information <br>INFO[0010] Collecting Index Cache Hit Ratio information <br>INFO[0010] Connecting to the "postgis" database <br>INFO[0010] Collecting Table Access information <br>INFO[0010] Collecting Table Cache Hit Ratio information <br>INFO[0010] Collecting Index Cache Hit Ratio information<br><br>##### --- Database Port and Data_Directory --- ####<br>+----------------------+----------------------------------------------------+<br>| Name | Setting |<br>+----------------------+----------------------------------------------------+<br>| data_directory | /var/lib/postgresql/16/main |<br>+----------------------+----------------------------------------------------+<br><br>##### --- List of Tablespaces ---- ######<br>+----------------------+----------------------+----------------------------------------------------+<br>| Name | Owner | Location |<br>+----------------------+----------------------+----------------------------------------------------+<br>| pg_default | postgres | |<br>| pg_global | postgres | |<br>+----------------------+----------------------+----------------------------------------------------+<br><br>##### --- Slave and the lag with Master --- ####<br>There are no slave hosts<br><br>##### --- Cluster Information --- ####<br>+------------------------------------------------------------------------------------------------------+<br>Usename : postgres<br>Time : 2023-09-25 10:23:17.068273 -0500 CDT<br>Client Address : 127.0.0.1<br>Client Hostname: <br>Version : PostgreSQL 16.0 - Percona Distribution on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11....<br>Started : 2023-09-25 08:58:45.535777 -0500 CDT<br>Is Slave : false<br>+------------------------------------------------------------------------------------------------------+<br><br>##### --- Databases --- ####<br>+----------------------+------------+<br>| Dat Name | Size |<br>+----------------------+------------+<br>| postgres | 7508 kB |<br>| test | 7644 kB |<br>| template1 | 7580 kB |<br>| template0 | 7353 kB |<br>| davetest | 7596 kB |<br>| postgis | 7580 kB |<br>+----------------------+------------+<br><br>##### --- Index Cache Hit Ratios --- ####<br><br>Database: davetest<br>+----------------------+------------+<br>| Index Name | Ratio |<br>+----------------------+------------+<br>| index hit rate | 0.00 |<br>+----------------------+------------+<br><br>Database: postgis<br>+----------------------+------------+<br>| Index Name | Ratio |<br>+----------------------+------------+<br>| index hit rate | 0.00 |<br>+----------------------+------------+<br><br>Database: postgres<br>+----------------------+------------+<br>| Index Name | Ratio |<br>+----------------------+------------+<br>| index hit rate | 0.00 |<br>+----------------------+------------+<br><br>Database: test<br>+----------------------+------------+<br>| Index Name | Ratio |<br>+----------------------+------------+<br>| index hit rate | 0.00 |<br>+----------------------+------------+<br><br>##### --- Table Cache Hit Ratios --- ####<br>Database: davetest<br>+----------------------+------------+<br>| Index Name | Ratio |<br>+----------------------+------------+<br>| cache hit rate | 0.00 |<br>+----------------------+------------+<br>Database: postgis<br>+----------------------+------------+<br>| Index Name | Ratio |<br>+----------------------+------------+<br>| cache hit rate | 0.00 |<br>+----------------------+------------+<br>Database: postgres<br>+----------------------+------------+<br>| Index Name | Ratio |<br>+----------------------+------------+<br>| cache hit rate | 0.00 |<br>+----------------------+------------+<br>Database: test<br>+----------------------+------------+<br>| Index Name | Ratio |<br>+----------------------+------------+<br>| cache hit rate | 0.00 |<br>+----------------------+------------+<br><br>##### --- List of Wait_events for the entire Cluster - all-databases --- ####<br>+----------------------+----------------------+---------+<br>| Wait Event Type | Event | Count |<br>+----------------------+----------------------+---------+<br>| Activity | BgWriterHibernate | 1 |<br>| Activity | WalWriterMain | 1 |<br>| Activity | LogicalLauncherMain | 1 |<br>| Activity | CheckpointerMain | 1 |<br>| Activity | AutoVacuumMain | 1 |<br>+----------------------+----------------------+---------+<br><br>##### --- List of users and client_addr or client_hostname connected to --all-databases --- ####<br>+----------------------+------------+---------+----------------------+---------+<br>| Wait Event Type | Client | State | Count |<br>+----------------------+------------+---------+----------------------+---------+<br>| postgres | 127.0.0.1/32 | active | 1 |<br>+----------------------+------------+---------+----------------------+---------+<br><br>##### --- Counters diff after 10 seconds --- ####<br><br>+----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+<br>| Database | Numbackends | XactCommit | XactRollback | BlksRead | BlksHit | TupReturned | TupFetched | TupInserted | TupUpdated | TupDeleted | Conflicts | TempFiles | TempBytes | Deadlocks |<br>+----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+<br>| | 0 | 0 | 0 | 0 | 25 | 68 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |<br>| davetest | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |<br>| postgis | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |<br>| postgres | 0 | 13 | 0 | 0 | 1480 | 1155 | 1155 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |<br>| template0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |<br>| template1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |<br>| test | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |<br>+----------------------+-------------+------------+--------------+-------------+------------+-------------+------------+-------------+------------+------------+-----------+-----------+-----------+------------+<br><br>##### --- Table access per database --- ####<br>Database: davetest<br>+----------------------------------------------------+------+--------------------------------+---------+<br>| Relname | Kind | Datname | Count |<br>+----------------------------------------------------+------+--------------------------------+---------+<br>| pg_database_datname_index | i | | 1 |<br>| pg_database | r | | 1 |<br>| pg_class | r | davetest | 1 |<br>| pg_locks | v | davetest | 1 |<br>| pg_class_oid_index | i | davetest | 1 |<br>| pg_stat_database | v | davetest | 1 |<br>| pg_database_oid_index | i | | 1 |<br>| pg_class_relname_nsp_index | i | davetest | 1 |<br>| pg_class_tblspc_relfilenode_index | i | davetest | 1 |<br>+----------------------------------------------------+------+--------------------------------+---------+<br>Database: postgis<br>+----------------------------------------------------+------+--------------------------------+---------+<br>| Relname | Kind | Datname | Count |<br>+----------------------------------------------------+------+--------------------------------+---------+<br>| pg_locks | v | postgis | 1 |<br>| pg_class_tblspc_relfilenode_index | i | postgis | 1 |<br>| pg_class_relname_nsp_index | i | postgis | 1 |<br>| pg_database_datname_index | i | | 1 |<br>| pg_class | r | postgis | 1 |<br>| pg_database | r | | 1 |<br>| pg_stat_database | v | postgis | 1 |<br>| pg_class_oid_index | i | postgis | 1 |<br>| pg_database_oid_index | i | | 1 |<br>+----------------------------------------------------+------+--------------------------------+---------+<br>Database: postgres<br>+----------------------------------------------------+------+--------------------------------+---------+<br>| Relname | Kind | Datname | Count |<br>+----------------------------------------------------+------+--------------------------------+---------+<br>| pg_database_datname_index | i | | 1 |<br>| pg_class | r | postgres | 1 |<br>| pg_database | r | | 1 |<br>| pg_stat_database | v | postgres | 1 |<br>| pg_class_relname_nsp_index | i | postgres | 1 |<br>| pg_class_oid_index | i | postgres | 1 |<br>| pg_database_oid_index | i | | 1 |<br>| pg_class_tblspc_relfilenode_index | i | postgres | 1 |<br>| pg_locks | v | postgres | 1 |<br>+----------------------------------------------------+------+--------------------------------+---------+<br>Database: test<br>+----------------------------------------------------+------+--------------------------------+---------+<br>| Relname | Kind | Datname | Count |<br>+----------------------------------------------------+------+--------------------------------+---------+<br>| pg_database_datname_index | i | | 1 |<br>| pg_class_oid_index | i | test | 1 |<br>| pg_class_tblspc_relfilenode_index | i | test | 1 |<br>| pg_database | r | | 1 |<br>| pg_locks | v | test | 1 |<br>| pg_class | r | test | 1 |<br>| pg_database_oid_index | i | | 1 |<br>| pg_stat_database | v | test | 1 |<br>| pg_class_relname_nsp_index | i | test | 1 |<br>+----------------------------------------------------+------+--------------------------------+---------+<br><br>##### --- Instance settings --- ####<br>Setting Value <br>allow_in_place_tablespaces : off<br>allow_system_table_mods : off<br>application_name : <br>archive_cleanup_command : <br>archive_command : (disabled)<br>archive_library : <br>archive_mode : off<br>archive_timeout : 0<br>array_nulls : on<br>authentication_timeout : 60<br>autovacuum : on<br>autovacuum_analyze_scale_factor : 0.1<br>autovacuum_analyze_threshold : 50<br>autovacuum_freeze_max_age : 200000000<br>autovacuum_max_workers : 3<br>autovacuum_multixact_freeze_max_age : 400000000<br>autovacuum_naptime : 60<br>autovacuum_vacuum_cost_delay : 2<br>autovacuum_vacuum_cost_limit : -1<br>autovacuum_vacuum_insert_scale_factor : 0.2<br>autovacuum_vacuum_insert_threshold : 1000<br>autovacuum_vacuum_scale_factor : 0.2<br>autovacuum_vacuum_threshold : 50<br>autovacuum_work_mem : -1<br>backend_flush_after : 0<br>backslash_quote : safe_encoding<br>backtrace_functions : <br>bgwriter_delay : 200<br>bgwriter_flush_after : 64<br>bgwriter_lru_maxpages : 100<br>bgwriter_lru_multiplier : 2<br>block_size : 8192<br>bonjour : off<br>bonjour_name : <br>bytea_output : hex<br>check_function_bodies : on<br>checkpoint_completion_target : 0.9<br>checkpoint_flush_after : 32<br>checkpoint_timeout : 300<br>checkpoint_warning : 30<br>client_connection_check_interval : 0<br>client_encoding : UTF8<br>client_min_messages : notice<br>cluster_name : 16/main<br>commit_delay : 0<br>commit_siblings : 5<br>compute_query_id : auto<br>config_file : /etc/postgresql/16/main/postgresql.conf<br>constraint_exclusion : partition<br>cpu_index_tuple_cost : 0.005<br>cpu_operator_cost : 0.0025<br>cpu_tuple_cost : 0.01<br>createrole_self_grant : <br>cursor_tuple_fraction : 0.1<br>data_checksums : off<br>data_directory : /var/lib/postgresql/16/main<br>data_directory_mode : 0700<br>data_sync_retry : off<br>DateStyle : ISO, MDY<br>db_user_namespace : off<br>deadlock_timeout : 1000<br>debug_assertions : off<br>debug_discard_caches : 0<br>debug_io_direct : <br>debug_logical_replication_streaming : buffered<br>debug_parallel_query : off<br>debug_pretty_print : on<br>debug_print_parse : off<br>debug_print_plan : off<br>debug_print_rewritten : off<br>default_statistics_target : 100<br>default_table_access_method : heap<br>default_tablespace : <br>default_text_search_config : pg_catalog.english<br>default_toast_compression : pglz<br>default_transaction_deferrable : off<br>default_transaction_isolation : read committed<br>default_transaction_read_only : off<br>dynamic_library_path : $libdir<br>dynamic_shared_memory_type : posix<br>effective_cache_size : 524288<br>effective_io_concurrency : 1<br>enable_async_append : on<br>enable_bitmapscan : on<br>enable_gathermerge : on<br>enable_hashagg : on<br>enable_hashjoin : on<br>enable_incremental_sort : on<br>enable_indexonlyscan : on<br>enable_indexscan : on<br>enable_material : on<br>enable_memoize : on<br>enable_mergejoin : on<br>enable_nestloop : on<br>enable_parallel_append : on<br>enable_parallel_hash : on<br>enable_partition_pruning : on<br>enable_partitionwise_aggregate : off<br>enable_partitionwise_join : off<br>enable_presorted_aggregate : on<br>enable_seqscan : on<br>enable_sort : on<br>enable_tidscan : on<br>escape_string_warning : on<br>event_source : PostgreSQL<br>exit_on_error : off<br>extension_destdir : <br>external_pid_file : /var/run/postgresql/16-main.pid<br>extra_float_digits : 2<br>from_collapse_limit : 8<br>fsync : on<br>full_page_writes : on<br>geqo : on<br>geqo_effort : 5<br>geqo_generations : 0<br>geqo_pool_size : 0<br>geqo_seed : 0<br>geqo_selection_bias : 2<br>geqo_threshold : 12<br>gin_fuzzy_search_limit : 0<br>gin_pending_list_limit : 4096<br>gss_accept_delegation : off<br>hash_mem_multiplier : 2<br>hba_file : /etc/postgresql/16/main/pg_hba.conf<br>hot_standby : on<br>hot_standby_feedback : off<br>huge_page_size : 0<br>huge_pages : try<br>icu_validation_level : warning<br>ident_file : /etc/postgresql/16/main/pg_ident.conf<br>idle_in_transaction_session_timeout : 0<br>idle_session_timeout : 0<br>ignore_checksum_failure : off<br>ignore_invalid_pages : off<br>ignore_system_indexes : off<br>in_hot_standby : off<br>integer_datetimes : on<br>IntervalStyle : postgres<br>jit : on<br>jit_above_cost : 100000<br>jit_debugging_support : off<br>jit_dump_bitcode : off<br>jit_expressions : on<br>jit_inline_above_cost : 500000<br>jit_optimize_above_cost : 500000<br>jit_profiling_support : off<br>jit_provider : llvmjit<br>jit_tuple_deforming : on<br>join_collapse_limit : 8<br>krb_caseins_users : off<br>krb_server_keyfile : FILE:/etc/postgresql-common/krb5.keytab<br>lc_messages : en_US.UTF-8<br>lc_monetary : en_US.UTF-8<br>lc_numeric : en_US.UTF-8<br>lc_time : en_US.UTF-8<br>listen_addresses : localhost<br>lo_compat_privileges : off<br>local_preload_libraries : <br>lock_timeout : 0<br>log_autovacuum_min_duration : 600000<br>log_checkpoints : on<br>log_connections : off<br>log_destination : stderr<br>log_directory : log<br>log_disconnections : off<br>log_duration : off<br>log_error_verbosity : default<br>log_executor_stats : off<br>log_file_mode : 0600<br>log_filename : postgresql-%Y-%m-%d_%H%M%S.log<br>log_hostname : off<br>log_line_prefix : %m [%p] %q%u@%d <br>log_lock_waits : off<br>log_min_duration_sample : -1<br>log_min_duration_statement : -1<br>log_min_error_statement : error<br>log_min_messages : warning<br>log_parameter_max_length : -1<br>log_parameter_max_length_on_error : 0<br>log_parser_stats : off<br>log_planner_stats : off<br>log_recovery_conflict_waits : off<br>log_replication_commands : off<br>log_rotation_age : 1440<br>log_rotation_size : 10240<br>log_startup_progress_interval : 10000<br>log_statement : none<br>log_statement_sample_rate : 1<br>log_statement_stats : off<br>log_temp_files : -1<br>log_timezone : America/Chicago<br>log_transaction_sample_rate : 0<br>log_truncate_on_rotation : off<br>logging_collector : off<br>logical_decoding_work_mem : 65536<br>maintenance_io_concurrency : 10<br>maintenance_work_mem : 65536<br>max_connections : 100<br>max_files_per_process : 1000<br>max_function_args : 100<br>max_identifier_length : 63<br>max_index_keys : 32<br>max_locks_per_transaction : 64<br>max_logical_replication_workers : 16<br>max_parallel_apply_workers_per_subscription : 2<br>max_parallel_maintenance_workers : 2<br>max_parallel_workers : 8<br>max_parallel_workers_per_gather : 2<br>max_pred_locks_per_page : 2<br>max_pred_locks_per_relation : -2<br>max_pred_locks_per_transaction : 64<br>max_prepared_transactions : 0<br>max_replication_slots : 10<br>max_slot_wal_keep_size : -1<br>max_stack_depth : 2048<br>max_standby_archive_delay : 30000<br>max_standby_streaming_delay : 30000<br>max_sync_workers_per_subscription : 2<br>max_wal_senders : 10<br>max_wal_size : 1024<br>max_worker_processes : 8<br>min_dynamic_shared_memory : 0<br>min_parallel_index_scan_size : 64<br>min_parallel_table_scan_size : 1024<br>min_wal_size : 80<br>old_snapshot_threshold : -1<br>parallel_leader_participation : on<br>parallel_setup_cost : 1000<br>parallel_tuple_cost : 0.1<br>password_encryption : scram-sha-256<br>plan_cache_mode : auto<br>port : 5432<br>post_auth_delay : 0<br>pre_auth_delay : 0<br>primary_conninfo : <br>primary_slot_name : <br>quote_all_identifiers : off<br>random_page_cost : 4<br>recovery_end_command : <br>recovery_init_sync_method : fsync<br>recovery_min_apply_delay : 0<br>recovery_prefetch : try<br>recovery_target : <br>recovery_target_action : pause<br>recovery_target_inclusive : on<br>recovery_target_lsn : <br>recovery_target_name : <br>recovery_target_time : <br>recovery_target_timeline : latest<br>recovery_target_xid : <br>recursive_worktable_factor : 10<br>remove_temp_files_after_crash : on<br>reserved_connections : 0<br>restart_after_crash : on<br>restore_command : <br>row_security : on<br>scram_iterations : 4096<br>search_path : "$user", public<br>segment_size : 131072<br>send_abort_for_crash : off<br>send_abort_for_kill : off<br>seq_page_cost : 1<br>server_encoding : UTF8<br>server_version : 16.0 - Percona Distribution<br>server_version_num : 160000<br>session_preload_libraries : <br>session_replication_role : origin<br>shared_buffers : 16384<br>shared_memory_size : 143<br>shared_memory_size_in_huge_pages : 72<br>shared_memory_type : mmap<br>shared_preload_libraries : <br>ssl : on<br>ssl_ca_file : <br>ssl_cert_file : /etc/ssl/certs/ssl-cert-snakeoil.pem<br>ssl_ciphers : HIGH:MEDIUM:+3DES:!aNULL<br>ssl_crl_dir : <br>ssl_crl_file : <br>ssl_dh_params_file : <br>ssl_ecdh_curve : prime256v1<br>ssl_key_file : /etc/ssl/private/ssl-cert-snakeoil.key<br>ssl_library : OpenSSL<br>ssl_max_protocol_version : <br>ssl_min_protocol_version : TLSv1.2<br>ssl_passphrase_command : <br>ssl_passphrase_command_supports_reload : off<br>ssl_prefer_server_ciphers : on<br>standard_conforming_strings : on<br>statement_timeout : 0<br>stats_fetch_consistency : cache<br>superuser_reserved_connections : 3<br>synchronize_seqscans : on<br>synchronous_commit : on<br>synchronous_standby_names : <br>syslog_facility : local0<br>syslog_ident : postgres<br>syslog_sequence_numbers : on<br>syslog_split_messages : on<br>tcp_keepalives_count : 9<br>tcp_keepalives_idle : 7200<br>tcp_keepalives_interval : 75<br>tcp_user_timeout : 0<br>temp_buffers : 1024<br>temp_file_limit : -1<br>temp_tablespaces : <br>TimeZone : America/Chicago<br>timezone_abbreviations : Default<br>trace_notify : off<br>trace_recovery_messages : log<br>trace_sort : off<br>track_activities : on<br>track_activity_query_size : 1024<br>track_commit_timestamp : off<br>track_counts : on<br>track_functions : none<br>track_io_timing : off<br>track_wal_io_timing : off<br>transaction_deferrable : off<br>transaction_isolation : read committed<br>transaction_read_only : off<br>transform_null_equals : off<br>unix_socket_directories : /var/run/postgresql<br>unix_socket_group : <br>unix_socket_permissions : 0777<br>update_process_title : on<br>vacuum_buffer_usage_limit : 256<br>vacuum_cost_delay : 0<br>vacuum_cost_limit : 200<br>vacuum_cost_page_dirty : 20<br>vacuum_cost_page_hit : 1<br>vacuum_cost_page_miss : 2<br>vacuum_failsafe_age : 1600000000<br>vacuum_freeze_min_age : 50000000<br>vacuum_freeze_table_age : 150000000<br>vacuum_multixact_failsafe_age : 1600000000<br>vacuum_multixact_freeze_min_age : 5000000<br>vacuum_multixact_freeze_table_age : 150000000<br>wal_block_size : 8192<br>wal_buffers : 512<br>wal_compression : off<br>wal_consistency_checking : <br>wal_decode_buffer_size : 524288<br>wal_init_zero : on<br>wal_keep_size : 0<br>wal_level : logical<br>wal_log_hints : off<br>wal_receiver_create_temp_slot : off<br>wal_receiver_status_interval : 10<br>wal_receiver_timeout : 60000<br>wal_recycle : on<br>wal_retrieve_retry_interval : 5000<br>wal_segment_size : 16777216<br>wal_sender_timeout : 60000<br>wal_skip_threshold : 2048<br>wal_sync_method : fdatasync<br>wal_writer_delay : 200<br>wal_writer_flush_after : 128<br>work_mem : 4096<br>xmlbinary : base64<br>xmloption : content<br>zero_damaged_pages : off<br><br>##### --- Processes start up command --- ####<br>PID : Command line<br>776 : /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf |
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.
