Dynamic Runtime Instrumentation Tools Architecture (DRITA) v10
The Dynamic Runtime Instrumentation Tools Architecture (DRITA) allows a DBA to query catalog views to determine the wait events that affect the performance of individual sessions or the system as a whole. DRITA records the number of times each event occurs as well as the time spent waiting; you can use this information to diagnose performance problems. DRITA offers this functionality, while consuming minimal system resources.
DRITA compares snapshots to evaluate the performance of a system. A snapshot is a saved set of system performance data at a given point in time. Each snapshot is identified by a unique ID number; you can use snapshot ID numbers with DRITA reporting functions to return system performance statistics.
Configuring and Using DRITA
Advanced Server's postgresql.conf file includes a configuration parameter named timed_statistics that controls the collection of timing data. The valid parameter values are TRUE or FALSE; the default value is FALSE.
This is a dynamic parameter which can be modified in the postgresql.conf file, or while a session is in progress. To enable DRITA, you must either:
Modify the postgresql.conf file, setting the timed_statistics parameter to TRUE.
or
Connect to the server with the EDB-PSQL client, and invoke the command:
SET timed_statistics = TRUE
After modifying the timed_statistics parameter, take a starting snapshot. A snapshot captures the current state of each timer and event counter. The server will compare the starting snapshot to a later snapshot to gauge system performance.
Use the edbsnap() function to take the beginning snapshot:
edb=# SELECT * FROM edbsnap();
edbsnap
----------------------
Statement processed.
(1 row)Then, run the workload that you would like to evaluate; when the workload has completed (or at a strategic point during the workload), take another snapshot:
edb=# SELECT * FROM edbsnap();
edbsnap
----------------------
Statement processed.
(1 row)You can capture multiple snapshots during a session. Then, use the DRITA functions and reports to manage and compare the snapshots to evaluate performance information.
DRITA Functions
You can use DRITA functions to gather wait information and manage snapshots. DRITA functions are fully supported by Advanced Server 10 whether your installation is made compatible with Oracle databases or is made in PostgreSQL-compatible mode.
get_snaps()
The get_snaps() function returns a list of the current snapshots. The signature is:
get_snaps()
The following example demonstrates using the get_snaps() function to display a list of snapshots:
edb=# SELECT * FROM get_snaps();
get_snaps
------------------------------
1 11-FEB-10 10:41:05.668852
2 11-FEB-10 10:42:27.26154
3 11-FEB-10 10:45:48.999992
4 11-FEB-10 11:01:58.345163
5 11-FEB-10 11:05:14.092683
6 11-FEB-10 11:06:33.151002
7 11-FEB-10 11:11:16.405664
8 11-FEB-10 11:13:29.458405
9 11-FEB-10 11:23:57.595916
10 11-FEB-10 11:29:02.214014
11 11-FEB-10 11:31:44.244038
(11 rows)The first column in the result list displays the snapshot identifier; the second column displays the date and time that the snapshot was captured.
sys_rpt()
The sys_rpt() function returns system wait information. The signature is:
sys_rpt(<beginning_id>, <ending_id>, <top_n>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
top_n
top_n represents the number of rows to return
This example demonstrates a call to the sys_rpt() function:
SELECT * FROM sys_rpt(9, 10, 10);
sys_rpt
-----------------------------------------------------------------------------
WAIT NAME COUNT WAIT TIME % WAIT
---------------------------------------------------------------------------
wal write 21250 104.723772 36.31
db file read 121407 72.143274 25.01
wal flush 84185 51.652495 17.91
wal file sync 712 29.482206 10.22
infinitecache write 84178 15.814444 5.48
db file write 84177 14.447718 5.01
infinitecache read 672 0.098691 0.03
db file extend 190 0.040386 0.01
query plan 52 0.024400 0.01
wal insert lock acquire 4 0.000837 0.00
(12 rows)The information displayed in the result set includes:
| Column Name | Description |
|---|---|
WAIT NAME | The name of the wait. |
COUNT | The number of times that the wait event occurred. |
WAIT TIME | The time of the wait event in milliseconds. |
% WAIT | The percentage of the total wait time used by this wait for this session. |
sess_rpt()
The sess_rpt() function returns session wait information. The signature is:
sess_rpt(<beginning_id>, <ending_id>, <top_n>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
top_n
top_n represents the number of rows to return
The following example demonstrates a call to the sess_rpt() function:
SELECT * FROM sess_rpt(18, 19, 10);
sess_rpt
--------------------------------------------------------------------------------------
ID USER WAIT NAME COUNT TIME(ms) %WAIT SES %WAIT ALL
--------------------------------------------------------------------------------------
17373 enterprise db file read 30 0.175713 85.24 85.24
17373 enterprise query plan 18 0.014930 7.24 7.24
17373 enterprise wal flush 6 0.004067 1.97 1.97
17373 enterprise wal write 1 0.004063 1.97 1.97
17373 enterprise wal file sync 1 0.003664 1.78 1.78
17373 enterprise infinitecache read 38 0.003076 1.49 1.49
17373 enterprise infinitecache write 5 0.000548 0.27 0.27
17373 enterprise db file extend 190 0.04.386 0.03 0.03
17373 enterprise db file write 5 0.000082 0.04 0.04
(11 rows)The information displayed in the result set includes:
| Column Name | Description |
|---|---|
ID | The processID of the session. |
USER | The name of the user incurring the wait. |
WAIT NAME | The name of the wait event. |
COUNT | The number of times that the wait event occurred. |
TIME (ms) | The length of the wait event in milliseconds. |
% WAIT SES | The percentage of the total wait time used by this wait for this session. |
% WAIT ALL | The percentage of the total wait time used by this wait (for all sessions). |
sessid_rpt()
The sessid_rpt() function returns session ID information for a specified backend. The signature is:
sessid_rpt(<beginning_id>, <ending_id>, <backend_id>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
backend_id
backend_id is an integer value that represents the backend identifier.
The following code sample demonstrates a call to sessid_rpt():
SELECT * FROM sessid_rpt(18, 19, 17373);
sessid_rpt
-------------------------------------------------------------------------------------
ID USER WAIT NAME COUNT TIME(ms) %WAIT SES %WAIT ALL
-------------------------------------------------------------------------------------
17373 enterprise db file read 30 0.175713 85.24 85.24
17373 enterprise query plan 18 0.014930 7.24 7.24
17373 enterprise wal flush 6 0.004067 1.97 1.97
17373 enterprise wal write 1 0.004063 1.97 1.97
17373 enterprise wal file sync 1 0.003664 1.78 1.78
17373 enterprise infinitecache read 38 0.003076 1.49 1.49
17373 enterprise infinitecache write 5 0.000548 0.27 0.27
17373 enterprise db file extend 190 0.040386 0.03 0.03
17373 enterprise db file write 5 0.000082 0.04 0.04
(11 rows)The information displayed in the result set includes:
| Column Name | Description |
|---|---|
ID | The process ID of the wait. |
USER | The name of the user that owns the session. |
WAIT NAME | The name of the wait event. |
COUNT | The number of times that the wait event occurred. |
TIME (ms) | The length of the wait in milliseconds. |
% WAIT SES | The percentage of the total wait time used by this wait for this session. |
% WAIT ALL | The percentage of the total wait time used by this wait (for all sessions). |
sesshist_rpt()
The sesshist_rpt() function returns session wait information for a specified backend. The signature is:
sesshist_rpt(<snapshot_id>, <session_id>)
Parameters
snapshot_id
snapshot_id is an integer value that identifies the snapshot.
session_id
session_id is an integer value that represents the session.
The following example demonstrates a call to the sesshist_rpt() function:
edb=# SELECT * FROM sesshist_rpt (9, 5531); sesshist_rpt ---------------------------------------------------------------------------- ID USER SEQ WAIT NAME ELAPSED(ms) File Name # of Blk Sum of Blks ---------------------------------------------------------------------------- 5531 enterprise 1 db file read 18546 14309 session_waits_pk 1 1 5531 enterprise 2 infinitecache read 125 14309 session_waits_pk 1 1 5531 enterprise 3 db file read 376 14304 edb$session_waits 0 1 5531 enterprise 4 infinitecache read 166 14304 edb$session_waits 0 1 5531 enterprise 5 db file read 7978 1260 pg_authid 0 1 5531 enterprise 6 infinitecache read 154 1260 pg_authid 0 1 5531 enterprise 7 db file read 628 14302 system_waits_pk 1 1 5531 enterprise 8 infinitecache read 463 14302 system_waits_pk 1 1 5531 enterprise 9 db file read 3446 14297 edb$system_waits 0 1 5531 enterprise 10 infinitecache read 187 14297 edb$system_waits 0 1 5531 enterprise 11 db file read 14750 14295 snap_pk 1 1 5531 enterprise 12 infinitecache read 416 14295 snap_pk 1 1 5531 enterprise 13 db file read 7139 14290 edb$snap 0 1 5531 enterprise 14 infinitecache read 158 14290 edb$snap 0 1 5531 enterprise 15 db file read 27287 14288 snapshot_num_seq 0 1 5531 enterprise 16 infinitecache read (17 rows)
The information displayed in the result set includes:
| Column Name | Description |
|---|---|
ID | The system-assigned identifier of the wait. |
USER | The name of the user that incurred the wait. |
SEQ | The sequence number of the wait event. |
WAIT NAME | The name of the wait event. |
ELAPSED (ms) | The length of the wait event in milliseconds. |
File | The relfilenode number of the file. |
Name | If available, the name of the file name related to the wait event. |
# of Blk | The block number read or written for a specific instance of the event . |
Sum of Blks | The number of blocks read. |
purgesnap()
The purgesnap() function purges a range of snapshots from the snapshot tables. The signature is:
purgesnap(<beginning_id>, <ending_id>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
purgesnap() removes all snapshots between beginning_id and ending_id (inclusive):
SELECT * FROM purgesnap(6, 9);
purgesnap
------------------------------------
Snapshots in range 6 to 9 deleted.
(1 row)A call to the get_snaps() function after executing the example shows that snapshots 6 through 9 have been purged from the snapshot tables:
SELECT * FROM get_snaps();
get_snaps
------------------------------
1 11-FEB-10 10:41:05.668852
2 11-FEB-10 10:42:27.26154
3 11-FEB-10 10:45:48.999992
4 11-FEB-10 11:01:58.345163
5 11-FEB-10 11:05:14.092683
10 11-FEB-10 11:29:02.214014
11 11-FEB-10 11:31:44.244038
(7 rows)truncsnap()
Use the truncsnap() function to delete all records from the snapshot table. The signature is:
truncsnap()
For example:
SELECT * FROM truncsnap();
truncsnap
----------------------
Snapshots truncated.
(1 row)A call to the get_snaps() function after calling the truncsnap() function shows that all records have been removed from the snapshot tables:
SELECT * FROM get_snaps(); get_snaps ----------- (0 rows)
Simulating Statspack AWR Reports
The functions described in this section return information comparable to the information contained in an Oracle Statspack/AWR (Automatic Workload Repository) report. When taking a snapshot, performance data from system catalog tables is saved into history tables. The reporting functions listed below report on the differences between two given snapshots.
stat_db_rpt()stat_tables_rpt()statio_tables_rpt()stat_indexes_rpt()statio_indexes_rpt()
The reporting functions can be executed individually or you can execute all five functions by calling the edbreport() function.
edbreport()
The edbreport() function includes data from the other reporting functions, plus additional system information. The signature is:
edbreport(<beginning_id>, <ending_id>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
The call to the edbreport() function returns a composite report that contains system information and the reports returned by the other statspack functions.
SELECT * FROM edbreport(9, 10);
edbreport
--------------------------------------------------------------------------------------
EnterpriseDB Report for database edb 23-AUG-15
Version: EnterpriseDB 10.0.0 on i686-pc-linux-gnu
Begin snapshot: 9 at 23-AUG-15 13:45:07.165123
End snapshot: 10 at 23-AUG-15 13:45:35.653036
Size of database edb is 155 MB
Tablespace: pg_default Size: 179 MB Owner: enterprisedb
Tablespace: pg_global Size: 435 kB Owner: enterprisedb
Schema: pg_toast_temp_1 Size: 0 bytes Owner: enterprisedb
Schema: public Size: 0 bytes Owner: enterprisedb
Schema: enterprisedb Size: 143 MB Owner: enterprisedb
Schema: pgagent Size: 192 kB Owner: enterprisedb
Schema: dbms_job_procedure Size: 0 bytes Owner: enterprisedbThe information displayed in the report introduction includes the database name and version, the current date, the beginning and ending snapshot date and times, database and tablespace details and schema information.
Top 10 Relations by pages TABLE RELPAGES ----------------------------------------------------- pgbench_accounts 15874 pg_proc 102 edb$statio_all_indexes 73 edb$stat_all_indexes 73 pg_attribute 67 pg_depend 58 edb$statio_all_tables 49 edb$stat_all_tables 47 pgbench_tellers 37 pg_description 32
The information displayed in the Top 10 Relations by pages section includes:
| Column Name | Description |
|---|---|
TABLE | The name of the table. |
RELPAGES | The number of pages in the table. |
Top 10 Indexes by pages INDEX RELPAGES ----------------------------------------------------- pgbench_accounts_pkey 2198 pg_depend_depender_index 32 pg_depend_reference_index 31 pg_proc_proname_args_nsp_index 30 pg_attribute_relid_attnam_index 23 pg_attribute_relid_attnum_index 17 pg_description_o_c_o_index 15 edb$statio_idx_pk 11 edb$stat_idx_pk 11 pg_proc_oid_index 9
The information displayed in the Top 10 Indexes by pages section includes:
| Column Name | Description |
|---|---|
INDEX | The name of the index. |
RELPAGES | The number of pages in the index. |
Top 10 Relations by DML SCHEMA RELATION UPDATES DELETES INSERTS ---------------------------------------------------------------------------- enterprisedb pgbench_accounts 10400 0 1000000 enterprisedb pgbench_tellers 10400 0 100 enterprisedb pgbench_branches 10400 0 10 enterprisedb pgbench_history 0 0 10400 pgagent pga_jobclass 0 0 6 pgagent pga_exception 0 0 0 pgagent pga_job 0 0 0 pgagent pga_jobagent 0 0 0 pgagent pga_joblog 0 0 0 pgagent pga_jobstep 0 0 0
The information displayed in the Top 10 Relations by DML section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the table resides. |
RELATION | The name of the table. |
UPDATES | The number of UPDATES performed on the table. |
DELETES | The number of DELETES performed on the table. |
INSERTS | The number of INSERTS performed on the table. |
DATA from pg_stat_database DATABASE NUMBACKENDS XACT COMMIT XACT ROLLBACK BLKS READ BLKS HIT BLKS ICACHE HIT HIT RATIO ICACHE HIT RATIO ---------------------------------------------------------------------------- edb 0 142 0 78 10446 0 99.26 0.00
The information displayed in the DATA from pg_stat_database section of the report includes:
| Column Name | Description |
|---|---|
DATABASE | The name of the database. |
NUMBACKENDS | 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 | Number of transactions in this database that have been committed. |
XACT ROLLBACK | Number of transactions in this database that have been rolled back. |
BLKS READ | Number of disk blocks read in this database. |
BLKS HIT | Number of times disk blocks were found already in the buffer cache (when a read was not necessary). |
BLKS ICACHE HIT | The number of blocks found in Infinite Cache. |
HIT RATIO | The percentage of times that a block was found in the shared buffer cache. |
ICACHE HIT RATIO | The percentage of times that a block was found in Infinite Cache. |
DATA from pg_buffercache RELATION BUFFERS ------------------------------------------- pgbench_accounts 16671 pgbench_accounts_pkey 2745 pgbench_history 590 pg_statistic 39 edb$statio_all_indexes 31 edb$stat_all_indexes 31 edb$statio_all_tables 21 edb$stat_all_tables 20 pg_depend 20 pg_operator 15
The information displayed in the DATA from pg_buffercache section of the report includes:
| Column Name | Description |
|---|---|
RELATION | The name of the table. |
BUFFERS | The number of shared buffers used by the relation. |
Note
In order to obtain the report for DATA from pg_buffercache, the pg_buffercache module must have been installed in the database.Perform the installation with the CREATE EXTENSION command.
For more information on the CREATE EXTENSION command, see the PostgreSQL Core documentation at:
https://www.postgresql.org/docs/10/static/sql-createextension.html
DATA from pg_stat_all_tables ordered by seq scan SCHEMA RELATION SEQ SCAN REL TUP READ IDX SCAN IDX TUP READ INS UPD DEL -------------------------------------------------------------------------------- pg_catalog pg_class 16 7162 546 319 0 1 0 pg_catalog pg_am 13 13 0 0 0 0 0 pg_catalog pg_database 4 16 42 42 0 0 0 pg_catalog pg_index 4 660 145 149 0 0 0 pg_catalog pg_namespace 4 100 49 49 0 0 0 sys edb$snap 1 9 0 0 1 0 0 pg_catalog pg_authid 1 1 25 25 0 0 0 sys edb$session_wait_history 0 0 0 0 50 0 0 sys edb$session_waits 0 0 0 0 2 0 0 sys edb$stat_all_indexes 0 0 0 0 165 0 0
The information displayed in the DATA from pg_stat_all_tables ordered by seq scan section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the table resides. |
RELATION | The name of the table. |
SEQ SCAN | The number of sequential scans initiated on this table. |
REL TUP READ | The number of tuples read in the table. |
IDX SCAN | The number of index scans initiated on the table. |
IDX TUP READ | The number of index tuples read. |
INS | The number of rows inserted. |
UPD | The number of rows updated. |
DEL | The number of rows deleted. |
DATA from pg_stat_all_tables ordered by rel tup read SCHEMA RELATION SEQ SCAN REL TUP READ IDX SCAN IDX TUP READ INS UPD DEL --------------------------------------------------------------------------------------- pg_catalog pg_class 16 7162 546 319 0 1 0 pg_catalog pg_index 4 660 145 149 0 0 0 pg_catalog pg_namespace 4 100 49 49 0 0 0 pg_catalog pg_database 4 16 42 42 0 0 0 pg_catalog pg_am 13 13 0 0 0 0 0 sys edb$snap 1 9 0 0 1 0 0 pg_catalog pg_authid 1 1 25 25 0 0 0 sys edb$session_wait_history 0 0 0 0 50 0 0 sys edb$session_waits 0 0 0 0 2 0 0 sys edb$stat_all_indexes 0 0 0 0 165 0 0
The information displayed in the DATA from pg_stat_all_tables ordered by rel tup read section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the table resides. |
RELATION | The name of the table. |
SEQ SCAN | The number of sequential scans performed on the table. |
REL TUP READ | The number of tuples read from the table. |
IDX SCAN | The number of index scans performed on the table. |
IDX TUP READ | The number of index tuples read. |
INS | The number of rows inserted. |
UPD | The number of rows updated. |
DEL | The number of rows deleted. |
DATA from pg_statio_all_tables
SCHEMA RELATION HEAP HEAP HEAP IDX IDX
READ HIT ICACHE READ HIT
HIT
IDX TOAST TOAST TOAST TIDX TIDX TIDX
ICACHE READ HIT ICACHE READ HIT ICACHE
HIT HIT HIT
-----------------------------------------------------------------------------
public pgbench_accounts 92766 67215 288 59 32126
9 0 0 0 0 0 0
pg_catalog pg_class 0 296 0 3 16
0 0 0 0 0 0 0
sys edb$stat_all_indexes 8 125 0 4 233
0 0 0 0 0 0 0
sys edb$statio_all_index 8 125 0 4 233
0 0 0 0 0 0 0
sys edb$stat_all_tables 6 91 0 2 174
0 0 0 0 0 0 0
sys edb$statio_all_table 6 91 0 2 174
0 0 0 0 0 0 0
pg_catalog pg_namespace 3 72 0 0 0
0 0 0 0 0 0 0
sys edb$session_wait_his 1 24 0 4 47
0 0 0 0 0 0 0
pg_catalog pg_opclass 3 13 0 2 0
0 0 0 0 0 0 0
pg_catalog pg_trigger 0 12 0 1 15
0 0 0 0 0 0 0The information displayed in the DATA from pg_statio_all_tables section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the table resides. |
RELATION | The name of the table. |
HEAP READ | The number of heap blocks read. |
HEAP HIT | The number of heap blocks hit. |
HEAP ICACHE HIT | The number of heap blocks in Infinite Cache. |
IDX READ | The number of index blocks read. |
IDX HIT | The number of index blocks hit. |
IDX ICACHE HIT | The number of index blocks in Infinite Cache. |
TOAST READ | The number of toast blocks read. |
TOAST HIT | The number of toast blocks hit. |
TOAST ICACHE HIT | The number of toast blocks in Infinite Cache. |
TIDX READ | The number of toast index blocks read. |
TIDX HIT | The number of toast index blocks hit. |
TIDX ICACHE HIT | The number of toast index blocks in Infinite Cache. |
DATA from pg_stat_all_indexes SCHEMA RELATION INDEX IDX SCAN IDX TUP READ IDX TUP FETCH ------------------------------------------------------------------------------ ------------------------------------------- pg_catalog pg_attribute pg_attribute_relid_attnum_index 427 907 907 pg_catalog pg_class pg_class_relname_nsp_index 289 62 62 pg_catalog pg_class pg_class_oid_index 257 257 257 pg_catalog pg_statistic pg_statistic_relid_att_inh_index 207 196 196 enterprisedb pgbench_accounts pgbench_accounts_pkey 200 255 200 pg_catalog pg_cast pg_cast_source_target_index 199 50 50 pg_catalog pg_proc pg_proc_oid_index 116 116 116 pg_catalog edb_partition edb_partition_partrelid_index 112 0 0 pg_catalog edb_policy edb_policy_object_name_index 112 0 0 enterprisedb pgbench_branches pgbench_branches_pkey 101 110 0
The information displayed in the DATA from pg_stat_all_indexes section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the index resides. |
RELATION | The name of the table on which the index is defined. |
INDEX | The name of the index. |
IDX SCAN | The number of indexes scans initiated on this index. |
IDX TUP READ | Number of index entries returned by scans on this index |
IDX TUP FETCH | Number of live table rows fetched by simple index scans using this index. |
DATA from pg_statio_all_indexes SCHEMA RELATION INDEX IDX BLKS READ IDX BLKS HIT IDX BLKS ICACHE HIT ---------------------------------------------------------------------------- pg_catalog pg_attribute pg_attribute_relid_attnum_index 0 867 0 enterprisedb pgbench_accounts pgbench_accounts_pkey 1 778 0 pg_catalog pg_class pg_class_relname_nsp_index 0 590 0 pg_catalog pg_class pg_class_oid_index 0 527 0 pg_catalog pg_statistic pg_statistic_relid_att_inh_index 0 441 0 sys edb$stat_all_indexes edb$stat_idx_pk 1 332 0 sys edb$statio_all_indexes edb$statio_idx_pk 1 332 0 pg_catalog pg_proc pg_proc_oid_index 0 244 0 sys edb$stat_all_tables edb$stat_tab_pk 0 241 0 sys edb$statio_all_tables edb$statio_tab_pk 0 241 0
The information displayed in the DATA from pg_statio_all_indexes section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the index resides. |
RELATION | The name of the table on which the index is defined. |
INDEX | The name of the index. |
IDX BLKS READ | The number of index blocks read. |
IDX BLKS HIT | The number of index blocks hit. |
IDX BLKS ICACHE HIT | The number of index blocks in Infinite Cache that were hit. |
System Wait Information WAIT NAME COUNT WAIT TIME % WAIT ---------------------------------------------------------- query plan 0 0.000407 100.00 db file read 0 0.000000 0.00
The information displayed in the System Wait Information section includes:
| Column Name | Description |
|---|---|
WAIT NAME | The name of the wait. |
COUNT | The number of times that the wait event occurred. |
WAIT TIME | The length of the wait time in milliseconds. |
% WAIT | The percentage of the total wait time used by this wait for this session. |
Database Parameters from postgresql.conf PARAMETER SETTING CONTEXT MINVAL MAXVAL ---------------------------------------------------------------------- --------------------------------------------------- allow_system_table_mods off postmaster application_name psql user archive_command (disabled) sighup archive_mode off postmaster archive_timeout 0 sighup 0 2147483647 array_nulls on user authentication_timeout 60 sighup 1 600 autovacuum on sighup autovacuum_analyze_scale_factor 0.1 sighup 0 100 autovacuum_analyze_threshold 50 sighup 0 2147483647 autovacuum_freeze_max_age 200000000 postmaster 100000000 2000000000 autovacuum_max_workers 3 postmaster 1 8388607 autovacuum_naptime 60 sighup 1 2147483 autovacuum_vacuum_cost_delay 20 ...
The information displayed in the Database Parameters from postgresql.conf section includes:
| Column Name | Description |
|---|---|
PARAMETER | The name of the parameter. |
SETTING | The current value assigned to the parameter. |
CONTEXT | The context required to set the parameter value. |
MINVAL | The minimum value allowed for the parameter. |
MAXVAL | The maximum value allowed for the parameter. |
stat_db_rpt()
The signature is:
stat_db_rpt(<beginning_id>, <ending_id>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
The following example demonstrates the stat_db_rpt() function:
SELECT * FROM stat_db_rpt(9, 10);
stat_db_rpt
-------------------------------------------------------------------------------------
DATA from pg_stat_database
DATABASE NUMBACKENDS XACT COMMIT XACT ROLLBACK BLKS READ BLKS HIT
BLKS ICACHE HIT HIT RATIO ICACHE HIT RATIO
-----------------------------------------------------------------------------
edb 1 21 0 92928 101217
301 52.05 0.15The information displayed in the DATA from pg_stat_database section of the report includes:
| Column Name | Description |
|---|---|
DATABASE | The name of the database. |
NUMBACKENDS | 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 | The number of transactions in this database that have been committed. |
XACT ROLLBACK | The number of transactions in this database that have been rolled back. |
BLKS READ | The number of blocks read. |
BLKS HIT | The number of blocks hit. |
BLKS ICACHE HIT | The number of blocks in Infinite Cache that were hit. |
HIT RATIO | The percentage of times that a block was found in the shared buffer cache. |
ICACHE HIT RATIO | The percentage of times that a block was found in Infinite Cache. |
stat_tables_rpt()
The signature is:
function_name(<beginning_id>, <ending_id>, <top_n>, <scope>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
top_n
top_n represents the number of rows to return
scope
scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:
SYSindicates that the function should return information about system defined tables. A table is considered a system table if it is stored in one of the following schemas:pg_catalog,information_schema,sysordbo.USERindicates that the function should return information about user-defined tables.ALLspecifies that the function should return information about all tables.
The stat_tables_rpt() function returns a two-part report. The first portion of the report contains:
SELECT * FROM stat_tables_rpt(18, 19, 10, 'ALL');
stat_tables_rpt
----------------------------------------------------------------------------
DATA from pg_stat_all_tables ordered by seq scan
SCHEMA RELATION
SEQ SCAN REL TUP READ IDX SCAN IDX TUP READ INS UPD DEL
-----------------------------------------------------------------------------
pg_catalog pg_class
8 2952 78 65 0 0 0
pg_catalog pg_index
4 448 23 28 0 0 0
pg_catalog pg_namespace
4 76 1 1 0 0 0
pg_catalog pg_database
3 6 0 0 0 0 0
pg_catalog pg_authid
2 1 0 0 0 0 0
sys edb$snap
1 15 0 0 1 0 0
public accounts
0 0 0 0 0 0 0
public branches
0 0 0 0 0 0 0
sys edb$session_wait_history
0 0 0 0 25 0 0
sys edb$session_waits
0 0 0 0 10 0 0The information displayed in the DATA from pg_stat_all_tables ordered by seq scan section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the table resides. |
RELATION | The name of the table. |
SEQ SCAN | The number of sequential scans on the table. |
REL TUP READ | The number of tuples read from the table. |
IDX SCAN | The number of index scans performed on the table. |
IDX TUP READ | The number of index tuples read from the table. |
INS | The number of rows inserted. |
UPD | The number of rows updated. |
DEL | The number of rows deleted. |
The second portion of the report contains:
DATA from pg_stat_all_tables ordered by rel tup read
SCHEMA RELATION
SEQ SCAN REL TUP READ IDX SCAN IDX TUP READ INS UPD DEL
-----------------------------------------------------------------------------
pg_catalog pg_class
8 2952 78 65 0 0 0
pg_catalog pg_index
4 448 23 28 0 0 0
pg_catalog pg_namespace
4 76 1 1 0 0 0
sys edb$snap
1 15 0 0 1 0 0
pg_catalog pg_database
3 6 0 0 0 0 0
pg_catalog pg_authid
2 1 0 0 0 0 0
public accounts
0 0 0 0 0 0 0
public branches
0 0 0 0 0 0 0
sys edb$session_wait_history
0 0 0 0 25 0 0
sys edb$session_waits
0 0 0 0 10 0 0
(29 rows)The information displayed in the DATA from pg_stat_all_tables ordered by rel tup read section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the table resides. |
RELATION | The name of the table. |
SEQ SCAN | The number of sequential scans performed on the table. |
REL TUP READ | The number of tuples read from the table. |
IDX SCAN | The number of index scans performed on the table. |
IDX TUP READ | The number of live rows fetched by index scans. |
INS | The number of rows inserted. |
UPD | The number of rows updated. |
DEL | The number of rows deleted. |
statio_tables_rpt()
The signature is:
statio_tables_rpt(<beginning_id>, <ending_id>, <top_n>, <scope>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
top_n
top_n represents the number of rows to return
scope
scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:
SYSindicates that the function should return information about system defined tables. A table is considered a system table if it is stored in one of the following schemas:pg_catalog,information_schema,sysordbo.USERindicates that the function should return information about user-defined tables.ALLspecifies that the function should return information about all tables.
The statio_tables_rpt() function returns a report that contains:
SELECT * FROM statio_tables_rpt(9, 10, 10, 'SYS');
statio_tables_rpt
---------------------------------------------------------------------------------------
-------------------------------
DATA from pg_statio_all_tables
SCHEMA RELATION HEAP HEAP HEAP IDX IDX
READ HIT ICACHE READ HIT
HIT
IDX TOAST TOAST TOAST TIDX TIDX TIDX
ICACHE READ HIT ICACHE READ HIT ICACHE
HIT HIT HIT
-----------------------------------------------------------------------------------------
public pgbench_accounts 92766 67215 288 59 32126
9 0 0 0 0 0 0
pg_catalog pg_class 0 296 0 3 16
0 0 0 0 0 0 0
sys edb$stat_all_indexes 8 125 0 4 233
0 0 0 0 0 0 0
sys edb$statio_all_index 8 125 0 4 233
0 0 0 0 0 0 0
sys edb$stat_all_tables 6 91 0 2 174
0 0 0 0 0 0 0
sys edb$statio_all_table 6 91 0 2 174
0 0 0 0 0 0 0
pg_catalog pg_namespace 3 72 0 0 0
0 0 0 0 0 0 0
sys edb$session_wait_his 1 24 0 4 47
0 0 0 0 0 0 0
pg_catalog pg_opclass 3 13 0 2 0
0 0 0 0 0 0 0
pg_catalog pg_trigger 0 12 0 1 15
0 0 0 0 0 0 0
(16 rows)The information displayed in the DATA from pg_statio_all_tables section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the relation resides. |
RELATION | The name of the relation. |
HEAP READ | The number of heap blocks read. |
HEAP HIT | The number of heap blocks hit. |
HEAP ICACHE HIT | The number of heap blocks in Infinite Cache. |
IDX READ | The number of index blocks read. |
IDX HIT | The number of index blocks hit. |
IDX ICACHE HIT | The number of index blocks in Infinite Cache. |
TOAST READ | The number of toast blocks read. |
TOAST HIT | The number of toast blocks hit. |
TOAST ICACHE HIT | The number of toast blocks in Infinite Cache. |
TIDX READ | The number of toast index blocks read. |
TIDX HIT | The number of toast index blocks hit. |
TIDX ICACHE HIT | The number of toast index blocks in Infinite Cache. |
stat_indexes_rpt()
The signature is:
stat_indexes_rpt(<beginning_id>, <ending_id>, <top_n>, <scope>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
top_n
top_n represents the number of rows to return
scope
scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:
SYSindicates that the function should return information about system defined tables. A table is considered a system table if it is stored in one of the following schemas:pg_catalog, information_schema,sysordbo.USERindicates that the function should return information about user-defined tables.ALLspecifies that the function should return information about all tables.
The stat_indexes_rpt() function returns a report that contains:
edb=# SELECT * FROM stat_indexes_rpt(9, 10, 10, 'ALL');
stat_indexes_rpt
-------------------------------------------------------------------------------
---------------------------------------------
DATA from pg_stat_all_indexes
SCHEMA RELATION INDEX
IDX SCAN IDX TUP READ IDX TUP FETCH
-------------------------------------------------------------------------------
pg_catalog pg_cast pg_cast_source_target_index
30 7 7
pg_catalog pg_class pg_class_oid_index
15 15 15
pg_catalog pg_trigger pg_trigger_tgrelid_tgname_index
12 12 12
pg_catalog pg_attribute pg_attribute_relid_attnum_index
7 31 31
pg_catalog pg_statistic pg_statistic_relid_att_index
7 0 0
pg_catalog pg_database pg_database_oid_index
5 5 5
pg_catalog pg_proc pg_proc_oid_index
5 5 5
pg_catalog pg_operator pg_operator_oprname_l_r_n_index
3 1 1
pg_catalog pg_type pg_type_typname_nsp_index
3 1 1
pg_catalog pg_amop pg_amop_opr_fam_index
2 3 3
(14 rows)The information displayed in the DATA from pg_stat_all_indexes section includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the relation resides. |
RELATION | The name of the relation. |
INDEX | The name of the index. |
IDX SCAN | The number of indexes scanned. |
IDX TUP READ | The number of index tuples read. |
IDX TUP FETCH | The number of index tuples fetched. |
statio_indexes_rpt()
The signature is:
statio_indexes_rpt(<beginning_id>, <ending_id>, <top_n>, <scope>)
Parameters
beginning_id
beginning_id is an integer value that represents the beginning session identifier.
ending_id
ending_id is an integer value that represents the ending session identifier.
top_n
top_n represents the number of rows to return
scope
scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:
SYSindicates that the function should return information about system defined tables. A table is considered a system table if it is stored in one of the following schemas:pg_catalog,information_schema,sysordbo.USERindicates that the function should return information about user-defined tables.ALLspecifies that the function should return information about all tables.
The statio_indexes_rpt() function returns a report that contains:
edb=# SELECT * FROM statio_indexes_rpt(9, 10, 10, 'SYS');
statio_indexes_rpt
-----------------------------------------------------------------------------------------
------------------------------------
DATA from pg_statio_all_indexes
SCHEMA RELATION INDEX
IDX BLKS READ IDX BLKS HIT IDX BLKS ICACHE HIT
-----------------------------------------------------------------------------------------
public pgbench_accounts pgbench_accounts_pkey
59 32126 9
sys edb$stat_all_indexes edb$stat_idx_pk
4 233 0
sys edb$statio_all_indexes edb$statio_idx_pk
4 233 0
sys edb$stat_all_tables edb$stat_tab_pk
2 174 0
sys edb$statio_all_tables edb$statio_tab_pk
2 174 0
sys edb$session_wait_history session_waits_hist_pk
4 47 0
pg_catalog pg_cast pg_cast_source_target_index
1 29 0
pg_catalog pg_trigger pg_trig_tgrelid_tgname_index
1 15 0
pg_catalog pg_class pg_class_oid_index
1 14 0
pg_catalog pg_statistic pg_statistic_relid_att_index
2 12 0
(14 rows)The information displayed in the DATA from pg_statio_all_indexes report includes:
| Column Name | Description |
|---|---|
SCHEMA | The name of the schema in which the relation resides. |
RELATION | The name of the table on which the index is defined. |
INDEX | The name of the index. |
IDX BLKS READ | The number of index blocks read. |
IDX BLKS HIT | The number of index blocks hit. |
IDX BLKS ICACHE HIT | The number of index blocks in Infinite Cache that were hit. |
Performance Tuning Recommendations
To use DRITA reports for performance tuning, review the top five events in a given report, looking for any event that takes a disproportionately large percentage of resources. In a streamlined system, user I/O will probably make up the largest number of waits. Waits should be evaluated in the context of CPU usage and total time; an event may not be significant if it takes 2 minutes out of a total measurement interval of 2 hours, if the rest of the time is consumed by CPU time. The component of response time (CPU "work" time or other "wait" time) that consumes the highest percentage of overall time should be evaluated.
When evaluating events, watch for:
| Event type | Description |
|---|---|
| Checkpoint waits | Checkpoint waits may indicate that checkpoint parameters need to be adjusted, (checkpoint_segments and checkpoint_timeout). |
| WAL-related waits | WAL-related waits may indicate wal_buffers are under-sized. |
| SQL Parse waits | If the number of waits is high, try to use prepared statements. |
| db file random reads | If high, check that appropriate indexes and statistics exist. |
| db file random writes | If high, may need to decrease bgwriter_delay. |
| btree random lock acquires | May indicate indexes are being rebuilt. Schedule index builds during less active time. |
Performance reviews should also include careful scrutiny of the hardware, the operating system, the network and the application SQL statements.
Event Descriptions
The following table lists the basic wait events that are displayed by DRITA.
| Event Name | Description |
|---|---|
add in shmem lock acquire | Obsolete/unused |
bgwriter communication lock acquire | The bgwriter (background writer) process has waited for the short-term lock that synchronizes messages between the bgwriter and a backend process. |
btree vacuum lock acquire | The server has waited for the short-term lock that synchronizes access to the next available vacuum cycle ID. |
buffer free list lock acquire | The server has waited for the short-term lock that synchronizes access to the list of free buffers (in shared memory). |
checkpoint lock acquire | A server process has waited for the short-term lock that prevents simultaneous checkpoints. |
checkpoint start lock acquire | The server has waited for the short-term lock that synchronizes access to the bgwriter checkpoint schedule. |
clog control lock acquire | The server has waited for the short-term lock that synchronizes access to the commit log. |
control file lock acquire | The server has waited for the short-term lock that synchronizes write access to the control file (this should usually be a low number). |
db file extend | A server process has waited for the operating system while adding a new page to the end of a file. |
db file read | A server process has waited for the completion of a read (from disk). |
db file write | A server process has waited for the completion of a write (to disk). |
db file sync | A server process has waited for the operating system to flush all changes to disk. |
first buf mapping lock acquire | The server has waited for a short-term lock that synchronizes access to the shared-buffer mapping table. |
freespace lock acquire | The server has waited for the short-term lock that synchronizes access to the freespace map. |
Infinite Cache read | The server has waited for an Infinite Cache read request. |
Infinite Cache write | The server has waited for an Infinite Cache write request. |
lwlock acquire | The server has waited for a short-term lock that has not been described elsewhere in this section. |
multi xact gen lock acquire | The server has waited for the short-term lock that synchronizes access to the next available multi-transaction ID (when a SELECT...FOR SHARE statement executes). |
multi xact member lock acquire | The server has waited for the short-term lock that synchronizes access to the multi-transaction member file (when a SELECT...FOR SHARE statement executes). |
multi xact offset lock acquire | The server has waited for the short-term lock that synchronizes access to the multi-transaction offset file (when a SELECT...FOR SHARE statement executes). |
oid gen lock acquire | The server has waited for the short-term lock that synchronizes access to the next available OID (object ID). |
query plan | The server has computed the execution plan for a SQL statement. |
rel cache init lock acquire | The server has waited for the short-term lock that prevents simultaneous relation-cache loads/unloads. |
shmem index lock acquire | The server has waited for the short-term lock that synchronizes access to the shared-memory map. |
sinval lock acquire | The server has waited for the short-term lock that synchronizes access to the cache invalidation state. |
sql parse | The server has parsed a SQL statement. |
subtrans control lock acquire | The server has waited for the short-term lock that synchronizes access to the subtransaction log. |
tablespace create lock acquire | The server has waited for the short-term lock that prevents simultaneous CREATE TABLESPACE or DROP TABLESPACE commands. |
two phase state lock acquire | The server has waited for the short-term lock that synchronizes access to the list of prepared transactions. |
wal insert lock acquire | The server has waited for the short-term lock that synchronizes write access to the write-ahead log. A high number may indicate that WAL buffers are sized too small. |
wal write lock acquire | The server has waited for the short-term lock that synchronizes write-ahead log flushes. |
wal file sync | The server has waited for the write-ahead log to sync to disk (related to the wal_sync_method parameter which, by default, is 'fsync' - better performance can be gained by changing this parameter to open_sync). |
wal flush | The server has waited for the write-ahead log to flush to disk. |
wal write | The server has waited for a write to the write-ahead log buffer (expect this value to be high). |
xid gen lock acquire | The server has waited for the short-term lock that synchronizes access to the next available transaction ID. |
When wait events occur for lightweight locks, they are displayed by DRITA as well. A lightweight lock is used to protect a particular data structure in shared memory.
Certain wait events can be due to the server process waiting for one of a group of related lightweight locks, which is referred to as a lightweight lock tranche. Individual lightweight lock tranches are not displayed by DRITA, but their summation is displayed by a single event named other lwlock acquire.
For a list and description of lightweight locks displayed by DRITA, see Section 28.2, The Statistics Collector in the PostgreSQL core documentation available at:
https://www.postgresql.org/docs/10/static/monitoring-stats.html
Under Section 28.2.2. Viewing Statistics, the lightweight locks are listed in Table 28-4 wait_event Description where the Wait Event Type column designates LWLockNamed.
Note that the table entries where Wait Event Type designates LWLockTranche are the lightweight lock tranches.
The following example displays lightweight locks ProcArrayLock, CLogControlLock, WALBufMappingLock, and XidGenLock.
postgres=# select * from sys_rpt(40,70,20);
sys_rpt
----------------------------------------------------------------------------
WAIT NAME COUNT WAIT TIME % WAIT
----------------------------------------------------------------------------
wal flush 56107 44.456494 47.65
db file read 66123 19.543968 20.95
wal write 32886 12.780866 13.70
wal file sync 32933 11.792972 12.64
query plan 223576 4.539186 4.87
db file extend 2339 0.087038 0.09
other lwlock acquire 402 0.066591 0.07
ProcArrayLock 135 0.012942 0.01
CLogControlLock 212 0.010333 0.01
WALBufMappingLock 47 0.006068 0.01
XidGenLock 53 0.005296 0.01
(13 rows)DRITA also displays wait events that occur that are related to certain Advanced Server product features.
These Advanced Server feature specific wait events and the other lwlock acquire event are listed in the following table.
| Event Name | Description |
|---|---|
ICacheLock | The server has waited for access related to Infinite Cache. |
BulkLoadLock | The server has waited for access related to EDB*Loader. |
EDBResoureManagerLock | The server has waited for access related to EDB Resource Manager. |
other lwlock acquire | Summation of waits for lightweight lock tranches. |