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
Post a Comment