PostgreSQL的统计信息视图pg_stat_bgwriter中包含如下列的信息 :
pg_stat_bgwriter view
Column | Type | Description |
---|---|---|
checkpoints_timed | bigint | Number of scheduled checkpoints. This value can also be returned by directly calling the pg_stat_get_bgwriter_timed_checkpoints function. |
checkpoints_requested | bigint | Number of requested checkpoints. This value can also be returned by directly calling the pg_stat_get_bgwriter_requested_checkpoints function. |
checkpoint_write_time | bigint | Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds. This value can also be returned by directly calling the pg_stat_get_checkpoint_write_time function. |
checkpoint_sync_time | bigint | Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in milliseconds. This value can also be returned by directly calling the pg_stat_get_checkpoint_sync_time function. |
buffers_checkpoint | bigint | Number of buffers written during checkpoints. This value can also be returned by directly calling the pg_stat_get_bgwriter_buf_written_checkpoints function. |
buffers_clean | bigint | Number of buffers written by the background writer. This value can also be returned by directly calling thepg_stat_get_bgwriter_buf_written_clean function. |
maxwritten_clean | bigint | Number of times the background writer stopped a cleaning scan because it had written too many buffers. This value can also be returned by directly calling the pg_stat_get_bgwriter_maxwritten_clean function. |
buffers_backend | bigint | Number of buffers written directly by a backend. This value can also be returned by directly calling the pg_stat_get_buf_written_backend function. |
buffers_backend_fsync | bigint | Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write) |
buffers_alloc | bigint | Number of buffers allocated. This value can also be returned by directly calling the pg_stat_get_buf_alloc function. |
stats_reset | bigint | The last time these statistics were reset. This value can also be returned by directly calling the pg_stat_get_bgwriter_stat_reset_time function. |
来自SQL
SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,pg_stat_get_buf_written_backend() AS buffers_backend,pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,pg_stat_get_buf_alloc() AS buffers_alloc,pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
其中
checkpoint_write_time和checkpoint_sync_time是PostgreSQL 9.2新增的, 用来记录累计的checkpoint时写文件到磁盘的时间和 累计的同步时间.
在postgresql.conf中配置log_checkpoints=on的话, 这两项统计会记录到log文件中. 并且log记录的是每次checkpoint后的统计信息, 不是累计的.
log 文件中截取一段如下 :
2012-04-12 10:51:07.635 CST,,,21703,,4f864226.54c7,26,,2012-04-12 10:47:02 CST,,0,LOG,00000,"checkpoint complete: wrote 3466 buffers (84.6%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=102.725 s, sync=0.103 s, total=102.829 s; sync files=1, longest=0.103 s, average=0.103 s",,,,,,,,,""
由于我这里配置的 shared_buffers = 32MB
wrote 3466 buffers (84.6%) 也可以反推出来 shared_buffers = block_size*(
3466/
84.6%
) .
其中
write=102.725 s, sync=0.103 s 指的就是checkpoint_write_time, checkpoint_sync_time.
pg_stat_bgwriter中这两项的统计信息与log_checkpoints无关, 例如 :
postgres=# show log_checkpoints;-[ RECORD 1 ]---+----log_checkpoints | offpostgres=# select pg_stat_reset_shared('bgwriter');-[ RECORD 1 ]--------+-pg_stat_reset_shared |postgres=# select * from pg_stat_bgwriter;-[ RECORD 1 ]---------+------------------------------checkpoints_timed | 0checkpoints_req | 0checkpoint_write_time | 0checkpoint_sync_time | 0buffers_checkpoint | 0buffers_clean | 0maxwritten_clean | 0buffers_backend | 0buffers_backend_fsync | 0buffers_alloc | 1stats_reset | 2012-04-12 11:06:00.737366+08postgres=# insert into test select generate_series(1,5000000);INSERT 0 5000000postgres=# select * from pg_stat_bgwriter;-[ RECORD 1 ]---------+------------------------------checkpoints_timed | 1checkpoints_req | 6checkpoint_write_time | 118778checkpoint_sync_time | 673buffers_checkpoint | 22262buffers_clean | 1125maxwritten_clean | 0buffers_backend | 22165buffers_backend_fsync | 0buffers_alloc | 22130stats_reset | 2012-04-12 11:06:00.737366+08
最后附一个查询统计信息的函数列表 :
select pg_catalog.obj_description(p.oid, 'pg_proc') as desc,proname,proargnames from pg_proc p where pg_catalog.obj_description(p.oid, 'pg_proc') ~ 'statistics' order by proname;-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: current backend PIDproname | pg_backend_pidproargnames |-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: discard current transaction's statistics snapshotproname | pg_stat_clear_snapshotproargnames |-[ RECORD 3 ]-------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: information about currently active backendsproname | pg_stat_get_activityproargnames | {pid,datid,procpid,usesysid,application_name,current_query,waiting,xact_start,query_start,backend_start,client_addr,client_hostname,client_port}-[ RECORD 4 ]-------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of manual analyzes for a tableproname | pg_stat_get_analyze_countproargnames |-[ RECORD 5 ]-------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of auto analyzes for a tableproname | pg_stat_get_autoanalyze_countproargnames |-[ RECORD 6 ]-------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of auto vacuums for a tableproname | pg_stat_get_autovacuum_countproargnames |-[ RECORD 7 ]-------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: current query of backendproname | pg_stat_get_backend_activityproargnames |-[ RECORD 8 ]-------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: start time for current query of backendproname | pg_stat_get_backend_activity_startproargnames |-[ RECORD 9 ]-------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: address of client connected to backendproname | pg_stat_get_backend_client_addrproargnames |-[ RECORD 10 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: port number of client connected to backendproname | pg_stat_get_backend_client_portproargnames |-[ RECORD 11 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: database ID of backendproname | pg_stat_get_backend_dbidproargnames |-[ RECORD 12 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: currently active backend IDsproname | pg_stat_get_backend_idsetproargnames |-[ RECORD 13 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: PID of backendproname | pg_stat_get_backend_pidproargnames |-[ RECORD 14 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: start time for current backend sessionproname | pg_stat_get_backend_startproargnames |-[ RECORD 15 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: user ID of backendproname | pg_stat_get_backend_useridproargnames |-[ RECORD 16 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: is backend currently waiting for a lockproname | pg_stat_get_backend_waitingproargnames |-[ RECORD 17 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: start time for backend's current transactionproname | pg_stat_get_backend_xact_startproargnames |-[ RECORD 18 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of buffers written by the bgwriter during checkpointsproname | pg_stat_get_bgwriter_buf_written_checkpointsproargnames |-[ RECORD 19 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of buffers written by the bgwriter for cleaning dirty buffersproname | pg_stat_get_bgwriter_buf_written_cleanproargnames |-[ RECORD 20 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of times the bgwriter stopped processing when it had written too many buffers while cleaningproname | pg_stat_get_bgwriter_maxwritten_cleanproargnames |-[ RECORD 21 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of backend requested checkpoints started by the bgwriterproname | pg_stat_get_bgwriter_requested_checkpointsproargnames |-[ RECORD 22 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: last reset for the bgwriterproname | pg_stat_get_bgwriter_stat_reset_timeproargnames |-[ RECORD 23 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of timed checkpoints started by the bgwriterproname | pg_stat_get_bgwriter_timed_checkpointsproargnames |-[ RECORD 24 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of blocks fetchedproname | pg_stat_get_blocks_fetchedproargnames |-[ RECORD 25 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of blocks found in cacheproname | pg_stat_get_blocks_hitproargnames |-[ RECORD 26 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of buffer allocationsproname | pg_stat_get_buf_allocproargnames |-[ RECORD 27 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of backend buffer writes that did their own fsyncproname | pg_stat_get_buf_fsync_backendproargnames |-[ RECORD 28 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of buffers written by backendsproname | pg_stat_get_buf_written_backendproargnames |-[ RECORD 29 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: blocks fetched for databaseproname | pg_stat_get_db_blocks_fetchedproargnames |-[ RECORD 30 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: blocks found in cache for databaseproname | pg_stat_get_db_blocks_hitproargnames |-[ RECORD 31 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: recovery conflicts in databaseproname | pg_stat_get_db_conflict_allproargnames |-[ RECORD 32 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: recovery conflicts in database caused by shared buffer pinproname | pg_stat_get_db_conflict_bufferpinproargnames |-[ RECORD 33 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: recovery conflicts in database caused by relation lockproname | pg_stat_get_db_conflict_lockproargnames |-[ RECORD 34 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: recovery conflicts in database caused by snapshot expiryproname | pg_stat_get_db_conflict_snapshotproargnames |-[ RECORD 35 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: recovery conflicts in database caused by buffer deadlockproname | pg_stat_get_db_conflict_startup_deadlockproargnames |-[ RECORD 36 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: recovery conflicts in database caused by drop tablespaceproname | pg_stat_get_db_conflict_tablespaceproargnames |-[ RECORD 37 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of backends in databaseproname | pg_stat_get_db_numbackendsproargnames |-[ RECORD 38 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: last reset for a databaseproname | pg_stat_get_db_stat_reset_timeproargnames |-[ RECORD 39 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: tuples deleted in databaseproname | pg_stat_get_db_tuples_deletedproargnames |-[ RECORD 40 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: tuples fetched for databaseproname | pg_stat_get_db_tuples_fetchedproargnames |-[ RECORD 41 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: tuples inserted in databaseproname | pg_stat_get_db_tuples_insertedproargnames |-[ RECORD 42 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: tuples returned for databaseproname | pg_stat_get_db_tuples_returnedproargnames |-[ RECORD 43 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: tuples updated in databaseproname | pg_stat_get_db_tuples_updatedproargnames |-[ RECORD 44 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: transactions committedproname | pg_stat_get_db_xact_commitproargnames |-[ RECORD 45 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: transactions rolled backproname | pg_stat_get_db_xact_rollbackproargnames |-[ RECORD 46 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of dead tuplesproname | pg_stat_get_dead_tuplesproargnames |-[ RECORD 47 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of function callsproname | pg_stat_get_function_callsproargnames |-[ RECORD 48 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: self execution time of functionproname | pg_stat_get_function_self_timeproargnames |-[ RECORD 49 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: execution time of functionproname | pg_stat_get_function_timeproargnames |-[ RECORD 50 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: last manual analyze time for a tableproname | pg_stat_get_last_analyze_timeproargnames |-[ RECORD 51 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: last auto analyze time for a tableproname | pg_stat_get_last_autoanalyze_timeproargnames |-[ RECORD 52 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: last auto vacuum time for a tableproname | pg_stat_get_last_autovacuum_timeproargnames |-[ RECORD 53 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: last manual vacuum time for a tableproname | pg_stat_get_last_vacuum_timeproargnames |-[ RECORD 54 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of live tuplesproname | pg_stat_get_live_tuplesproargnames |-[ RECORD 55 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of scans done for table/indexproname | pg_stat_get_numscansproargnames |-[ RECORD 56 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples deletedproname | pg_stat_get_tuples_deletedproargnames |-[ RECORD 57 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples fetched by idxscanproname | pg_stat_get_tuples_fetchedproargnames |-[ RECORD 58 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples hot updatedproname | pg_stat_get_tuples_hot_updatedproargnames |-[ RECORD 59 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples insertedproname | pg_stat_get_tuples_insertedproargnames |-[ RECORD 60 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples read by seqscanproname | pg_stat_get_tuples_returnedproargnames |-[ RECORD 61 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples updatedproname | pg_stat_get_tuples_updatedproargnames |-[ RECORD 62 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of manual vacuums for a tableproname | pg_stat_get_vacuum_countproargnames |-[ RECORD 63 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: information about currently active replicationproname | pg_stat_get_wal_sendersproargnames | {procpid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}-[ RECORD 64 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of blocks fetched in current transactionproname | pg_stat_get_xact_blocks_fetchedproargnames |-[ RECORD 65 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of blocks found in cache in current transactionproname | pg_stat_get_xact_blocks_hitproargnames |-[ RECORD 66 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of function calls in current transactionproname | pg_stat_get_xact_function_callsproargnames |-[ RECORD 67 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: self execution time of function in current transactionproname | pg_stat_get_xact_function_self_timeproargnames |-[ RECORD 68 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: execution time of function in current transactionproname | pg_stat_get_xact_function_timeproargnames |-[ RECORD 69 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of scans done for table/index in current transactionproname | pg_stat_get_xact_numscansproargnames |-[ RECORD 70 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples deleted in current transactionproname | pg_stat_get_xact_tuples_deletedproargnames |-[ RECORD 71 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples fetched by idxscan in current transactionproname | pg_stat_get_xact_tuples_fetchedproargnames |-[ RECORD 72 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples hot updated in current transactionproname | pg_stat_get_xact_tuples_hot_updatedproargnames |-[ RECORD 73 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples inserted in current transactionproname | pg_stat_get_xact_tuples_insertedproargnames |-[ RECORD 74 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples read by seqscan in current transactionproname | pg_stat_get_xact_tuples_returnedproargnames |-[ RECORD 75 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: number of tuples updated in current transactionproname | pg_stat_get_xact_tuples_updatedproargnames |-[ RECORD 76 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: reset collected statistics for current databaseproname | pg_stat_resetproargnames |-[ RECORD 77 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: reset collected statistics shared across the clusterproname | pg_stat_reset_sharedproargnames |-[ RECORD 78 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: reset collected statistics for a single function in the current databaseproname | pg_stat_reset_single_function_countersproargnames |-[ RECORD 79 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics: reset collected statistics for a single table or index in the current databaseproname | pg_stat_reset_single_table_countersproargnames |-[ RECORD 80 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics of tsvector columnproname | ts_statproargnames | {query,word,ndoc,nentry}-[ RECORD 81 ]------------------------------------------------------------------------------------------------------------------------------------------------desc | statistics of tsvector columnproname | ts_statproargnames | {query,weights,word,ndoc,nentry}
【参考】