Skip to main content

Integrate pg_stat_statements

Database performance issues ne figureout karva mate, one of the most effective way 6e pg_stat_statements module ne enable karvu. aani madad thi kai query ketlo time and device resourses ne utilitzation kare 6e aeni details mali rahe 6e.

aa pg_stat_statements by default installed nathi hotu. aa module ne darek database j aapde use karie 6ie aema separately install karvu pade 6e.

Chalo janie aapde aa pg_stat_statements ne kai rite install kari sakie.

  1. sauthi pehla aapde ek database cluster banavsu using initdb command.
initdb -D /tmp/db
  1. aapde database ne start karsu.
pg_ctl -D /tmp/db start
  1. Extention ne add karva mate aapde sauthi pehla PostgreSQL file ne update karvi padse. kem k aapde /tmp/db ma aapda cluster ne enable karyu hatu to sauthi pehla aa folder ma jao.
cd /tmp/db
  1. postgresql.conf file ne vi ma open karo.
vi postgresql.conf
  1. search karo 'shared_preload_libraries' and jo aa line commented hoi to uncomment karo. tyar pa6i aema value set karo.
shared_preload_libraries = 'pg_stat_statements'
  1. Server ne restart karo.
pg_cli -D /tmp/db restart
  1. Database ne connect karo.
psql -p 5432 -d postgres
  1. Extention create karo.
create extention pg_stat_statements;
  1. records ne check karo.
select * from pg_stat_statements;
  1. Jo data read proper na thai sake to extended mode on karo.
\x
  1. fari thi data ne proper extended mode on karya pa6i proper rite joi sako 6o.
select * from pg_stat_statements;

-[ RECORD 1 ]---------------------
userid              : 10
dbid                : 14021
toplevel            : t
queryid             : -1739183385080879393
query               : create extension pg_stat_statements
plans               : 0
total_plan_time     : 0
min_plan_time       : 0
max_plan_time       : 0
mean_plan_time      : 0
stddev_plan_time    : 0
calls               : 1
total_exec_time     : 51.135042
min_exec_time       : 51.135042
max_exec_time       : 51.135042
mean_exec_time      : 51.135042
stddev_exec_time    : 0
rows                : 0
shared_biks_hit     : 2707
shared_blks_read    : 161
shared_blks_dirtied : 78
shared_blks_written : 7
local_blks_hit      : 0
local_blks_read     : 0
local_blks_dirtied  : 0
local_blks_written  : 0
temp_blks_read      : 0
temp_blks_written   : 0
blk_read_time       : 0
blk_write_time      : 0
wal_records         : 530
wal_fpi             : 48
wal_bytes           : 290621
  1. Jo aapde koi dummy load joito joi to aena mate aapde pgbench no use kari sakie 6ie. pgbench ek khub popular module 6e, jeno upyog PostgreSQL ne benchmarking mate thai 6e. aa module PostgreSQL sathe j install thai 6e, jethi aapde aene extra install karva ni jarur nathi padti. Yaad rahe aa badhu matra practice mate 6e. aa practice ni madad thi aapde jani sakie k aapda database ma, aapda system ma read heavy vadhare operations thai 6e k write heavy. ek vakhat pg_stat_statements no upyog karya pa6i aapne
pgbench -i postgres
  1. pgbench ni madad thi aapde postgres database par 8 concurrent users thi total 25000 transactions perform karavsu jema read and write banne prakar na transactions hoi 6e.
pgbench -c 8 -t 25000 postgres;
  1. have aapde pg_stat_statements ni madad thi total load based data fetch karsu.
SELECT 
 round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) percent,
 round(total_exec_time::numeric, 2) AS total,
 calls,
 round(mean_exec_time:: numeric, 2) AS mean,
 substring(query, 1, 200)
FROM Pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
  1. aa query kaink aa rite result aape 6e.
percent | total     | calls  | mean   | substring
59.85   | 126959.20 | 200000 | 0.63   | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
38.07   | 80752.66  | 200000 | 0.40   | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
1.26    | 2668.59   | 200000 | 0.01   | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
0.31    | 659.81    | 200000 | 0.00   | INSERT INTO bench_history (tid, bid, aid, delta, mtime) VALUES (S1, 52, 53, S4, CURRENT_TIMESTAMP)
0.31    | 652.90    | 200000 | 0.00   | SELECT abalance FROM pgbench_accounts WHERE aid = $1
0.08    | 172.72    | 1      | 172.72 | copy pgbench_accounts from stdin
8.03    | 58.53     | 200000 | 0.00   | BEGIN
0.02    | 51.14     | 1      | 51.14  | create extension pg_stat_statements
0.02    | 49.59     | 200000 | 0.00   | END
0.02    | 32.28     | 1      | 32.28  | alter table pgbench_accounts add primary key (aid)
(10 rows)

Aapde joi sakie k 1st record vadi update query badho load na 60% part ne represent kare 6e. Jo aapde aa query ne improve karie to possible 6e sayad aapde scale up bhi karva ni jarur na pade haal ma.