The metric statistics show the overall database usage and include information about SQL statements, tables, indexes, and background processes. The statistics are analogous to a physical examination report and show the problems and adjustment points. You can evaluate what can be optimized in your database based on the statistics.

PolarDB statistics

pg_stat_database

Column Example value Description
datid 13510 The object identifier (OID) of a database.
datname postgres The name of the database.
numbackends 98 The number of backends that are connected to the database.
xact_commit 14291309 The total number of transactions that are committed in the database.
xact_rollback 0 The total number of transactions that are rolled back in the database.
blks_read 536888 The total number of disk blocks that are physically read in the database.
blks_hit 261717850 The number of blocks that are hit in shared_buffer.
tup_returned 58521416 The total number of scanned rows in full table scans, or the number of index entries that are returned by using an index method. If the value is significantly greater than the value of the tup_fetched parameter, a large number of full table scans exist in the database.
tup_fetched 57193639 The number of rows that are returned by indexes.
tup_inserted 14293061 The number of inserted rows.
tup_updated 42868451 The number of updated rows.
tup_deleted 98 The number of deleted rows.
conflicts 0 The number of queries that are canceled due to conflicts with recovery. The conflicts occur only on read-only nodes.
temp_files 0 The number of generated temporary files. If the value is large, the value of the work_mem parameter needs to be increased.
temp_bytes 0 The size of the generated temporary files.
deadlocks 0 The number of deadlocks. If the value is large, the service logic is faulty.
blk_read_time 0 The time spent in reading data files in the database. If the value is large, the memory is small and data files need to be frequently read from disks.
blk_write_time 0 The time spent in writing data files in the database.
stats_reset 2019/11/9 14:06 None

The information about the TPS (Transactions per Second) and Scanned Rows metrics on the Monitoring page in the PolarDB console is retrieved from pg_stat_database. The following two figures show basic information about the database usage. You can locate obvious load changes. For more information about how to go to the Monitoring page, see Performance monitoring and alert configuration.

TPS-1TPS-2

pg_stat_user_tables

Column Example value Description
relid 16390 The OID of a table.
schemaname public The name of the schema.
relname pgbench_accounts The name of the table.
seq_scan 0 The number of full table scans on the table.
seq_tup_read 0 The number of rows scanned in all the full table scans. If the value is large, all the SQL statements that are executed for this table may perform full table scans.
idx_scan 29606482 The number of index scans.
idx_tup_fetch 29606482 The number of rows returned by index scans.
n_tup_ins 0 The number of inserted rows.
n_tup_upd 14803241 The number of updated rows.
n_tup_del 0 The number of deleted rows.
n_tup_hot_upd 14638544 The number of rows that are hot updated. A value that is similar to the value of n_tup_upd indicates excellent update performance. The indexes are not updated when data is updated.
n_live_tup 100012319 The number of live rows.
n_dead_tup 2403437 The number of dead rows.
n_mod_since_analyze 0 The estimated number of rows that are modified after the last analysis of the table.
last_vacuum None The time at which the table is manually vacuumed last time.
last_autovacuum None The time at which the table is vacuumed by the autovacuum daemon last time.
last_analyze None The time at which the table is manually analyzed last time.
last_autoanalyze 2019/4/9 14:12 The time at which the table is analyzed by the autovacuum daemon last time.
vacuum_count 0 The number of times the table is manually vacuumed.
autovacuum_count 0 The number of times the table is vacuumed by the autovacuum daemon.
analyze_count 0 The number of times the table is manually analyzed.
autoanalyze_count 1 The number of times the table is analyzed by the autovacuum daemon.
  • If the values of the seq_scan and seq_tup_read parameters are large, a large number of full table scans are performed on the table. In this case, you must locate the faulty SQL statements and optimize the SQL statements.
  • If the value of the n_dead_tup parameter is large, a large number of UPDATE and DELETE operations are performed on the table and a large amount of junk data is generated. In this case, you must perform the VACUUM operation on the table and set the autovacuum parameter to a proper value. If you encounter a similar situation, you can manually perform the VACUUM operation on the table, or specify the autovacuum parameter to trigger an automatic VACUUM operation.

pg_stat_user_indexes

Column Example value Description
relid 16390 The OID of a table.
indexrelid 16404 The OID of an index.
schemaname public The name of the schema.
relname pgbench_accounts The name of the table.
indexrelname pgbench_accounts_pkey The name of the index.
idx_scan 29606482 The number of index scans that are initiated on the index. If the value is small, the index is rarely used and can be deleted.
idx_tup_read 29949698 The number of index entries that are returned by using an index method.
idx_tup_fetch 29606482 The number of rows that are returned by using an index method.

Based on the statistics of pg_stat_user_indexes, you can identify the frequently used indexes and invalid indexes in the database. You can delete invalid indexes to reduce the disk usage and improve the performance of INSERT, UPDATE, and DELETE operations.

pg_statio_user_tables

Column Example value Description
relid 16390 The OID of a table.
schemaname public The name of the schema.
relname pgbench_accounts The name of the table.
heap_blks_read 414012 The number of disk blocks that are read from the table.
idx_heap_blks_hit 44710713 The number of shared_buffer hits in the table.
idx_blks_read 67997 The number of disk blocks that are read from all the indexes on the table.
idx_blks_hit 89424015 The number of shared_buffer hits in all the indexes on the table.
toast_blks_read None The number of disk blocks that are read from the TOAST table of the table. TOAST is the acronym of The Oversized-Attribute Storage Technique.
toast_blks_hit None The number of shared_buffer hits in the TOAST table of the table.
tidx_blks_read None The number of disk blocks that are read from the TOAST table indexes of the table.
tidx_blks_hit None The number of shared_buffer hits in the TOAST table indexes.

pg_stat_bgwriter

Column Example value Description
checkpoints_timed 1050 The number of checkpoints that are triggered after the time specified by checkpoint_timeout has elapsed.
checkpoints_req 1 The number of checkpoints that are triggered manually or when the number of write-ahead logging (WAL) files reaches the value of the max_wal_size parameter.
checkpoint_write_time 659728 The amount of time spent in writing data from shared_buffer to disks.
checkpoint_sync_time 549 The amount of time spent in calling fsync to synchronize dirty data to disks during checkpoints.
buffers_checkpoint 122383 The number of dirty blocks that are written during checkpoints.
buffers_clean 60723 The number of blocks that are written by the bgwriter process.
maxwritten_clean 583 The number of times the bgwriter process stops writing data because the amount of the data written at a time exceeds the value of the bgwriter_lru_maxpages parameter.
buffers_backend 306521 The number of blocks that are written by a backend.
buffers_backend_fsync 0 The number of times a backend needs to call fsync.
buffers_alloc 317113 The number of allocated buffers.
stats_reset 2019-03-28 16:54:45 The time at which the statistics are reset.

The pg_stat_bgwriter view allows you to check whether the parameters relevant to checkpoints and max_wal_size are set to proper values. You can also check whether the parameters relevant to the bgwriter process are set to proper values.

pg_stat_statements

Column Example value Description
userid 10 The ID of the user who executes the statement.
dbida 12917 The OID of the database in which the statement is executed.
queryid 4390283800491518311 The hash value that is obtained after data normalization in the SQL query.
query select version() The content that is obtained after data normalization in the SQL query.
calls 1 The number of times the SQL statement is executed.
total_time 0.208 The total time spent in executing the SQL statement.
min_time 0.208 The minimum time spent in executing the SQL statement.
max_time 0.208 The maximum time spent in executing the SQL statement.
mean_time 0.208 The mean time spent in executing the SQL statement.
stddev_time 0 The overall standard deviation of the time that is spent in executing the SQL statement. Unit: milliseconds.
rows 1 The number of rows that are returned or affected by the SQL statement.
shared_blks_hit 0 The number of blocks that are hit by the SQL statement in shared_buffer.
shared_blks_read 0 The number of disk blocks that are read by the SQL statement.
shared_blks_dirtied 0 The number of blocks that are dirtied by the SQL statement in shared_buffer.
shared_blks_written 0 The number of blocks that are written by the SQL statement.
local_blks_hit 0 The number of blocks that are hit by the SQL statement in a temporary table.
local_blks_read 0 The number of blocks that need to be read by the SQL statement from a temporary table.
local_blks_dirtied 0 The number of blocks that are dirtied by the SQL statement in a temporary table.
local_blks_written 0 The number of blocks that are written by the SQL statement to a temporary table.
temp_blks_read 0 The number of blocks that are read by the SQL statement from a temporary file.
temp_blks_written 0 The number of blocks that are written by the SQL statement to a temporary file.
blk_read_time 0 The total time spent in reading disk blocks.
blk_write_time 0 The total time spent in writing disk blocks.

pg_stat_activity

Column Example value Description
datid 12630 The OID of the database to which the backend is connected.
datname postgres The name of the database to which the backend is connected.
pid 19239 The process ID of the backend.
usesysid 10 The OID of the user who logs on to the backend.
usename postgres The name of the user who logs on to the backend.
application_name psql The name of the application that is connected to the backend.
client_addr None The IP address of the client that is connected to the backend.
client_hostname None The host name of the connected client.
client_port -1 The TCP port that is used for the communications between the client and the backend.
backend_start 2020-02-22 18:52:16 The time when the process is started.
xact_start 2020-02-23 15:25:46 The time when the current transaction of the process is started.
query_start 2020-02-23 15:25:46 The time when the active query starts.
state_change 2020-02-23 15:25:46 The time when the state is last changed.
wait_event_type None The type of the event for which the backend is waiting in a session.
wait_event None The name of the event for which the backend is waiting.
state active The session status of the backend. Valid values: active, idle, idle in transaction, and idle in transaction (aborted).
backend_xid None The transaction identifier of the backend.
backend_xmin 1089 The xmin scope of the backend.
query select 1; The SQL statement of the query.
backend_type client backend The type of the backend. Valid values: autovacuum launcher, autovacuum worker, client backend, and checkpointer.

pg_locks

Column Example value Description
locktype relation The type of lockable objects. Valid values: relation, extend, page, tuple, transactionid, virtualxid, object, userlock, and advisory.
database 12630 The OID of the database where the lock target exists. The value is 0 if the target is a shared object. This column is empty if the target is a transaction ID.
relation 11645 The OID of the relation that is targeted by the lock. This column is empty if the target is not a relation or is only part of a relation.
page None The page number that is targeted by the lock within the relation. This column is empty if the target is not a relation page or a tuple.
tuple None The tuple number that is targeted by the lock within the page. This column is empty if the target is not a tuple.
virtualxid None The virtual ID of the transaction that is targeted by the lock. This column is empty if the target is not a virtual transaction ID.
transactionid None The ID of the transaction that is targeted by the lock. This column is empty if the target is not a transaction ID.
classid None The OID of the system catalog that contains the lock target. This column is empty if the target is not a general database object.
objid None The OID of the lock target in its system catalog. This column is empty if the target is not a general database object.
objsubid None The column number that is targeted by the lock.
virtualtransaction 3/220 The virtual ID of the transaction that holds the lock or is waiting for the lock.
pid 19239 The process ID (PID) of the server process that holds the lock or is waiting for the lock.
mode AccessShareLock The lock mode that the process already holds or wants to hold.
granted t Specifies whether the process holds the lock mode or wants to hold the lock mode.
fastpath t Specifies whether the lock is obtained by using the fast path. If the lock is obtained by using the fast path, the value is t. If the lock is obtained by using the main lock table, the value is f.