Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Postgresql pg_stat_bgwriter and pg_stat_database

pg_stat_bgwriter view will always have a single row, containing global data for the cluster.
Column
Type
Description
checkpoints_timed
bigint
Number of scheduled checkpoints that have been performed
checkpoints_req
bigint
Number of requested checkpoints that have been performed
checkpoint_write_time
double precision
Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds
checkpoint_sync_time
double precision
Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in milliseconds
buffers_checkpoint
bigint
Number of buffers written during checkpoints
buffers_clean
bigint
Number of buffers written by the background writer
maxwritten_clean
bigint
Number of times the background writer stopped a cleaning scan because it had written too many buffers
buffers_backend
bigint
Number of buffers written directly by a backend
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
stats_reset
timestamp with time zone
Time at which these statistics were last reset


 pg_stat_database view will contain one row for each database in the cluster, showing database-wide statistics.
Column
Type
Description
datid
oid
OID of a database
datname
name
Name of this database
numbackends
integer
Number of backends currently connected to this database. This is the only column in this view that returns a value reflecting current state; all other columns return the accumulated values since the last reset.
xact_commit
bigint
Number of transactions in this database that have been committed
xact_rollback
bigint
Number of transactions in this database that have been rolled back
blks_read
bigint
Number of disk blocks read in this database
blks_hit
bigint
Number of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache)
tup_returned
bigint
Number of rows returned by queries in this database
tup_fetched
bigint
Number of rows fetched by queries in this database
tup_inserted
bigint
Number of rows inserted by queries in this database
tup_updated
bigint
Number of rows updated by queries in this database
tup_deleted
bigint
Number of rows deleted by queries in this database
conflicts
bigint
Number of queries canceled due to conflicts with recovery in this database. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.)
temp_files
bigint
Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting.
temp_bytes
bigint
Total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting.
deadlocks
bigint
Number of deadlocks detected in this database
blk_read_time
double precision
Time spent reading data file blocks by backends in this database, in milliseconds
blk_write_time
double precision
Time spent writing data file blocks by backends in this database, in milliseconds
stats_reset
timestamp with time zone
Time at which these statistics were last reset
  • I'm using postgreSQL-9.1.6 and trying to build monitoring application for postgreSQL server.
  • I'm planning to select PHYSICAL and LOGICAL I/O stat from pg_stat_* information tables.
  • According to MANUAL unit of fields in PG_STAT_DATABASE is BLOCK which means size of 8KB.
postgres=# select * from pg_stat_database where datname='postgres';    
-[ RECORD 3 ]-+------------------------------
datid         | 12780
datname       | postgres
numbackends   | 2
xact_commit   | 974
xact_rollback | 57
blks_read     | 210769
blks_hit      | 18664177
tup_returned  | 16074339
tup_fetched   | 35121
tup_inserted  | 18182015
tup_updated   | 572
tup_deleted   | 3075
conflicts     | 0
I could figure out size of PHYSICAL READ usging blks_read * 8KB. However, there is no comments on the unit of stats in PG_STAT_BGWRITER.
postgres=# select * from pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed     | 276
checkpoints_req       | 8
buffers_checkpoint    | 94956
buffers_clean         | 0
maxwritten_clean      | 0
buffers_backend       | 82618
buffers_backend_fsync | 0
buffers_alloc         | 174760
stats_reset           | 2013-07-15 22:27:05.503125+09                             
How can I calculate the size of PHYSICAL WRITE through the buffers_checkpoint?
Any advice wold be very appreciated.

Comments

Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Ora2PG - Oracle/MySQL to Postgres DB migration Version 20.0

PostgreSQL Introduction