checkpoint_write_time, checkpoint_sync_time will added in pg_stat_bgwriter
来源：互联网 发布：linux crontab java 编辑：程序博客网 时间：2024/06/13 09:25
PostgreSQL的统计信息视图pg_stat_bgwriter中包含如下列的信息 : Column Type Description checkpoints_timedbigintNumber of scheduled checkpoints. This value can also be returned by directly calling the
pg_stat_bgwriter view
pg_stat_get_bgwriter_timed_checkpoints function.checkpoints_requestedbigintNumber of requested checkpoints. This value can also be returned by directly calling thepg_stat_get_bgwriter_requested_checkpoints function.checkpoint_write_timebigintTotal 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 thepg_stat_get_checkpoint_write_time function.checkpoint_sync_timebigintTotal 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 thepg_stat_get_checkpoint_sync_time function.buffers_checkpointbigintNumber of buffers written during checkpoints. This value can also be returned by directly calling thepg_stat_get_bgwriter_buf_written_checkpointsfunction.buffers_cleanbigintNumber 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_cleanbigintNumber 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 thepg_stat_get_bgwriter_maxwritten_clean function.buffers_backendbigintNumber of buffers written directly by a backend. This value can also be returned by directly calling thepg_stat_get_buf_written_backend function.buffers_backend_fsyncbigintNumber 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_allocbigintNumber of buffers allocated. This value can also be returned by directly calling thepg_stat_get_buf_alloc function.stats_resetbigintThe last time these statistics were reset. This value can also be returned by directly calling thepg_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}
【参考】
https://commitfest.postgresql.org/action/patch_view?id=794
- checkpoint_write_time, checkpoint_sync_time will added in pg_stat_bgwriter
- will be provided in
- life will in "if......then"
- Orientation in a UIView added to a UIWindow
- AIX6.1： vg is of unknown type and will not be added to the HACMP configuration的解决办法
- Namibian Commodities will appear in Yiwu Market
- Retargeting Will Feature in Your Future
- will
- will
- UPDATE of startups in CHINA in 2007!!! [More to be added]
- I will write articles both in chinese and in english
- warning: Signal <> missing in the sensitivity list is added for synthesis purposes
- Signal missing in the sensitivity list is added for synthesis purposes.
- uploaddify 报错 "ID SWFUpload_0 is already in use. The Flash Object could not be added"
- Dynamic Added...
- 又到预测时，what will be cool in 2006?
- In what situation, an IllegalThreadStateException will be thrown?
- 又到预测时，what will be cool in 2006?
- Use PostgreSQL collect and analyze Operation System statistics
- 如何与人相处
- mvc弹出提示窗口的方法
- Android HAL hardware module分析 以GPS module为例
- 基于linux下的dm9000网卡移植全分析
- checkpoint_write_time, checkpoint_sync_time will added in pg_stat_bgwriter
- 总结迁移工作
- 使用静态库时需要注意的事项
- 使用PACKET_MMAP + PF_PACKET实现ZERO COPY抓包和发包
- 子查询
- some kernel network configs needed to be check
- PostgreSQL数据库开源连接池pgbouncer的使用
- recovery 英文介绍
- jquery学习笔记
