DRITA builds on features in PostgreSQL like the Postgres statistics collector, extensions such as pg_stat_statements, and modules like pg_buffercache. DRITA is especially helpful to Oracle DBAs who are familiar with Oracle Statspack, but new to Postgres. Here is a quick overview. As always, read the docs.
DRITA requires timed_statistics to be enabled.
demo=# \! grep timed $PGDATA/postgresql.conf
timed_statistics = on # record wait timings, defaults to on
demo=# show timed_statistics;
timed_statistics
------------------
on
(1 row)
Some information will be excluded from the reports if pg_buffercache is not installed.
demo=# create extension pg_buffercache;
CREATE EXTENSION
Initialize pgbench, so we can generate some server activity
demo=# \! pgbench -i -F 90 -s 10 demo
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.10 s, remaining 0.92 s)
200000 of 1000000 tuples (20%) done (elapsed 0.24 s, remaining 0.94 s)
300000 of 1000000 tuples (30%) done (elapsed 0.39 s, remaining 0.91 s)
400000 of 1000000 tuples (40%) done (elapsed 0.56 s, remaining 0.84 s)
500000 of 1000000 tuples (50%) done (elapsed 0.73 s, remaining 0.73 s)
600000 of 1000000 tuples (60%) done (elapsed 0.87 s, remaining 0.58 s)
700000 of 1000000 tuples (70%) done (elapsed 1.04 s, remaining 0.45 s)
800000 of 1000000 tuples (80%) done (elapsed 1.38 s, remaining 0.34 s)
900000 of 1000000 tuples (90%) done (elapsed 3.43 s, remaining 0.38 s)
1000000 of 1000000 tuples (100%) done (elapsed 4.36 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
Check database size.
pg_size_pretty
----------------
174 MB
(1 row)
Call the edbsnap function to take a starting snapshot.
demo=# select * from edbsnap();
edbsnap
----------------------
Statement processed.
(1 row)
Generate server activity with pgbench .
demo=# \! pgbench -n -c 2 -T 600 demo
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 2
number of threads: 1
duration: 600 s
number of transactions actually processed: 313975
latency average: 3.822 ms
tps = 523.287709 (including connections establishing)
tps = 523.290766 (excluding connections establishing)
Call the edbsnap function again to take an ending snapshot of the workload.
demo=# select * from edbsnap();
edbsnap
----------------------
Statement processed.
(1 row)
We can check which snapshots are available by calling the get_snaps function.
demo=# select * from get_snaps();
get_snaps
-----------------------------
1 19-APR-16 17:39:53.043944
2 19-APR-16 17:58:34.766845
(2 rows)
There are five available reports for comparing two snapshots. The edbreport function includes data from all five reports. We can view the report in psql, or export the report analyze and compare later.
demo=# \o /tmp/demo_pgbench_20160419
demo=# select * from edbreport(1,2);
demo=# \o
-bash-4.2$ cat demo_pgbench_20160419
edbreport
-------------------------------------------------------------------------------------------------------------------------------------------------------
EnterpriseDB Report for database demo 19-APR-16
Version: EnterpriseDB 9.5.1.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
Begin snapshot: 1 at 19-APR-16 18:23:52.980122
End snapshot: 2 at 19-APR-16 18:35:04.78618
Size of database demo is 191 MB
Tablespace: pg_default Size: 857 MB Owner: enterprisedb
Tablespace: pg_global Size: 920 kB Owner: enterprisedb
Schema: pg_toast_temp_1 Size: 0 bytes Owner: enterprisedb
Schema: public Size: 180 MB Owner: enterprisedb
Top 10 Relations by pages
TABLE RELPAGES
----------------------------------------------------------------------------------
pgbench_accounts 18182
pgbench_history 1976
pg_proc 140
pg_attribute 83
pg_depend 78
pg_description 37
pg_collation 36
pg_class 20
pg_rewrite 20
pgbench_tellers 19
Top 10 Indexes by pages
INDEX RELPAGES
----------------------------------------------------------------------------------
pgbench_accounts_pkey 2745
pg_depend_reference_index 65
pg_depend_depender_index 63
pg_proc_proname_args_nsp_index 51
pg_attribute_relid_attnam_index 22
pg_description_o_c_o_index 21
pg_attribute_relid_attnum_index 16
pg_proc_oid_index 13
pg_shdepend_depender_index 9
pg_shdepend_reference_index 8
Top 10 Relations by DML
SCHEMA RELATION UPDATES DELETES INSERTS
--------------------------------------------------------------------------------------------------
public pgbench_accounts 313975 0 1000000
public pgbench_tellers 313975 0 100
public pgbench_branches 313975 0 10
public pgbench_history 0 0 313975
DATA from pg_stat_database
DATABASE NUMBACKENDS XACT COMMIT XACT ROLLBACK BLKS READ BLKS HIT BLKS ICACHE HIT HIT RATIO ICACHE HIT RATIO
------------------------------------------------------------------------------------------------------------------------------
demo 0 314407 0 4966 5469881 0 99.91 0.00
DATA from pg_buffercache
RELATION BUFFERS
-----------------------------------------------------------------------------
pgbench_accounts 18190
pgbench_accounts_pkey 2745
pgbench_history 2006
pg_toast_2618 110
pg_statistic 31
pg_rewrite 24
pgbench_tellers 23
pg_depend 22
pgbench_branches 17
edb$stat_all_indexes 16
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
-----------------------------------------------------------------------------------------------------------------------
public pgbench_branches 128602 1206750 193301 193301 0 313975 0
public pgbench_tellers 121518 12045800 193517 193517 0 313975 0
pg_catalog pg_extension 268 1240 0 0 0 0 0
pg_catalog pg_database 110 1210 2796 2796 0 0 0
pg_catalog pg_tablespace 107 166 820 820 0 0 0
pg_catalog pg_class 105 42461 3741 3149 0 0 0
pg_catalog pg_am 91 91 0 0 0 0 0
pg_catalog pg_namespace 18 558 431 348 0 0 0
pg_catalog pg_constraint 14 182 0 0 0 0 0
pg_catalog pg_db_role_setting 14 65 3320 830 0 0 0
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
-----------------------------------------------------------------------------------------------------------------------
public pgbench_tellers 121518 12045800 193517 193517 0 313975 0
public pgbench_branches 128602 1206750 193301 193301 0 313975 0
pg_catalog pg_proc 14 44138 3100 4512 0 0 0
pg_catalog pg_class 105 42461 3741 3149 0 0 0
pg_catalog pg_extension 268 1240 0 0 0 0 0
pg_catalog pg_database 110 1210 2796 2796 0 0 0
pg_catalog pg_index 5 815 1161 1461 0 0 0
pg_catalog pg_namespace 18 558 431 348 0 0 0
pg_catalog pg_constraint 14 182 0 0 0 0 0
pg_catalog pg_tablespace 107 166 820 820 0 0 0
DATA from pg_statio_all_tables
SCHEMA RELATION HEAP HEAP HEAP IDX IDX IDX TOAST TOAST TOAST TIDX TIDX TIDX
READ HIT ICACHE READ HIT ICACHE READ HIT ICACHE READ HIT ICACHE
HIT HIT HIT HIT
-----------------------------------------------------------------------------------------------------------------------------------------------------
public pgbench_accounts 0 996476 0 2745 1884521 0 0 0 0 0 0 0
public pgbench_branches 17 913444 0 2 193513 0 0 0 0 0 0 0
public pgbench_tellers 21 682650 0 7 397685 0 0 0 0 0 0 0
public pgbench_history 2006 328733 0 0 0 0 0 0 0 0 0 0
pg_catalog pg_proc 0 5334 0 1 8606 0 0 0 0 0 0 0
pg_catalog pg_class 0 5089 0 0 7724 0 0 0 0 0 0 0
pg_catalog pg_attribute 24 4381 0 2 8702 0 0 0 0 0 0 0
pg_catalog pg_database 0 2912 0 0 4456 0 0 0 0 0 0 0
pg_catalog pg_operator 0 1876 0 0 1973 0 0 0 0 0 0 0
pg_catalog pg_type 0 1787 0 0 4756 0 0 0 0 0 0 0
DATA from pg_stat_all_indexes
SCHEMA RELATION INDEX IDX SCAN IDX TUP READ IDX TUP FETCH
-------------------------------------------------------------------------------------------------------------------------
public pgbench_accounts pgbench_accounts_pkey 627950 627950 627950
public pgbench_tellers pgbench_tellers_pkey 193517 226804 193517
public pgbench_branches pgbench_branches_pkey 193301 413723 129226
pg_catalog pg_attribute pg_attribute_relid_attnum_index 4287 10820 10820
pg_catalog pg_db_role_setting pg_db_role_setting_databaseid_rol_i 3320 830 830
pg_catalog pg_class pg_class_oid_index 2819 3318 2822
pg_catalog pg_proc pg_proc_oid_index 2480 2480 2480
pg_catalog pg_cast pg_cast_source_target_index 2038 355 355
pg_catalog pg_type pg_type_oid_index 1741 1741 1741
pg_catalog pg_database pg_database_datname_index 1559 1559 1559
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 2745 1884521 0
public pgbench_tellers pgbench_tellers_pkey 7 397685 0
public pgbench_branches pgbench_branches_pkey 2 193513 0
pg_catalog pg_attribute pg_attribute_relid_attnum_index 2 8702 0
pg_catalog pg_class pg_class_oid_index 0 5735 0
pg_catalog pg_proc pg_proc_oid_index 1 5050 0
pg_catalog pg_db_role_setting pg_db_role_setting_databaseid_rol_i 0 4150 0
pg_catalog pg_type pg_type_oid_index 0 3573 0
pg_catalog pg_proc pg_proc_proname_args_nsp_index 0 3556 0
pg_catalog pg_statistic pg_statistic_relid_att_inh_index 0 2548 0
System Wait Information
WAIT NAME COUNT WAIT TIME % WAIT
---------------------------------------------------------------------------
wal flush 2111 7.738697 37.57
query plan 7189 5.251493 25.49
wal write 1806 3.828541 18.59
wal file sync 1806 3.704491 17.98
db file read 34 0.072955 0.35
db file extend 109 0.003226 0.02
xid gen lock acquire 1 0.000190 0.00
clog control lock acquire 3 0.000016 0.00
Database Parameters from postgresql.conf
PARAMETER SETTING CONTEXT MINVAL MAXVAL
-------------------------------------------------------------------------------------------------------------------------
allow_system_table_mods off postmaster
application_name psql.bin user
archive_command rsync -aq %p /mnt/pgbackups/archivedir/% sighup
...
We can also get individual session wait information with the following functions.
demo=# \df sys.sess*
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------+------------------+---------------------------+--------
sys | sess_rpt | SETOF text | integer, integer, integer | normal
sys | sesshist_rpt | SETOF text | integer, integer | normal
sys | sessid_rpt | SETOF text | integer, integer, integer | normal
(3 rows)
Here are the 5 individual report functions plus sys_rpt for viewing wait informaiton.
SELECT
n.nspname ||'.'|| p.proname as edbreports
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.protype = '0'
AND p.proname ~ '^(.*_rpt)$'
AND p.proname NOT LIKE 'sess%'
AND n.nspname ~ '^(sys)$';
"psql.edit.107272.sql" 8L, 259C written
edbreports
------------------------
sys.stat_db_rpt
sys.stat_indexes_rpt
sys.stat_tables_rpt
sys.statio_indexes_rpt
sys.statio_tables_rpt
sys.sys_rpt
(6 rows)
An example of viewing just the top 10 wait events.
demo=# select * from sys_rpt(1,2,10);
sys_rpt
-----------------------------------------------------------------------------
WAIT NAME COUNT WAIT TIME % WAIT
---------------------------------------------------------------------------
wal flush 2111 7.738697 37.57
query plan 7189 5.251493 25.49
wal write 1806 3.828541 18.59
wal file sync 1806 3.704491 17.98
db file read 34 0.072955 0.35
db file extend 109 0.003226 0.02
xid gen lock acquire 1 0.000190 0.00
clog control lock acquire 3 0.000016 0.00
(10 rows))
DRITA catalog views are available as well.
demo=# \dt edb$*
List of relations
Schema | Name | Type | Owner
--------+--------------------------+-------+--------------
sys | edb$session_wait_history | table | enterprisedb
sys | edb$session_waits | table | enterprisedb
sys | edb$snap | table | enterprisedb
sys | edb$stat_all_indexes | table | enterprisedb
sys | edb$stat_all_tables | table | enterprisedb
sys | edb$stat_database | table | enterprisedb
sys | edb$statio_all_indexes | table | enterprisedb
sys | edb$statio_all_tables | table | enterprisedb
sys | edb$system_waits | table | enterprisedb
(9 rows)
demo=# select edb_id,dbname,backend_id,wait_name,elapsed,p1 from edb$session_wait_history where wait_name = 'query plan' limit 1;
edb_id | dbname | backend_id | wait_name | elapsed | p1
--------+--------+------------+------------+---------+----
1 | demo | 98079 | query plan | 15 | 0
(1 row)
edb_id | dbname | backend_id | wait_name | elapsed | p1
--------+--------+------------+------------+---------+----
1 | demo | 98079 | query plan | 15 | 0
(1 row)
No comments:
Post a Comment