磐维2.0 之pg_stat_statements插件
一、概念描述
pg_stat_statements是pg的一个扩展插件，通常用于统计数据库的资源开销，分析TOP SQL，找出慢查询。
二、安装插件
testdb=#
testdb=# create extension pg_stat_statements;
CREATE EXTENSION
testdb=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------
security_plugin
(1 row)
[omm@pw_1 ~]$
[omm@pw_1 ~]$
[omm@pw_1 ~]$ gs_guc set -N all -I all -c "shared_preload_libraries='security_plugin,pg_stat_statements'"
The pw_guc run with the following arguments: [gs_guc -N all -I all -c shared_preload_libraries='security_plugin,pg_stat_statements' set ].
Begin to perform the total nodes: 3.
Popen count is 3, Popen success count is 3, Popen failure count is 0.
Begin to perform gs_guc for datanodes.
Command count is 3, Command success count is 3, Command failure count is 0.
Total instances: 3. Failed instances: 0.
ALL: Success to perform gs_guc!
[omm@pw_1 ~]$
[omm@pw_1 ~]$
[omm@pw_1 ~]$ ptk cluster stop -n pw2
INFO[2024-02-18T14:17:34.110] operation: stop
INFO[2024-02-18T14:17:34.110] ========================================
INFO[2024-02-18T14:17:34.110] stop cluster by cm_ctl ...
INFO[2024-02-18T14:17:44.652] ========================================
INFO[2024-02-18T14:17:44.652] stop successfully
[omm@pw_1 ~]$ ptk cluster start -n pw2
INFO[2024-02-18T14:17:51.298] checking cluster state before start
INFO[2024-02-18T14:17:51.428] operation: start
INFO[2024-02-18T14:17:51.428] ========================================
INFO[2024-02-18T14:17:51.428] start cluster by cm_ctl ...
INFO[2024-02-18T14:18:06.041] ========================================
INFO[2024-02-18T14:18:06.041] start cluster successfully
[omm@pw_1 ~]$
[omm@pw_1 ~]$
[omm@pw_1 ~]$ psql -r
psql ((PanWeiDB 2.0.0 (Build0)) compiled at 2023-11-30 09:02:43 commit 03b85d1 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
testdb=# show shared_preload_libraries;
shared_preload_libraries
------------------------------------
security_plugin,pg_stat_statements
(1 row)
三、pg_stat_statements视图
testdb=# \d pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Modifiers
---------------------+------------------+-----------
userid | oid | //用户id
dbid | oid | //数据库oid
query | text | //查询SQL
calls | bigint | //调用次数
total_time | double precision | //SQL总共执行时间
rows | bigint | //SQL返回或者影响的行数
shared_blks_hit | bigint | //SQL在在shared_buffer中命中的块数
shared_blks_read | bigint | //SQL从page cache或者磁盘中读取的块数
shared_blks_dirtied | bigint | //SQL语句弄脏的shared_buffer的块数
shared_blks_written | bigint | //SQL语句写入的块数
local_blks_hit | bigint | //临时表中命中的块数
local_blks_read | bigint | //临时表需要读的块数
local_blks_dirtied | bigint | //临时表弄脏的块数
local_blks_written | bigint | //临时表写入的块数
temp_blks_read | bigint | //从临时文件读取的块数
temp_blks_written | bigint | //从临时文件写入的数据块数
blk_read_time | double precision | //从磁盘或者读取花费的时间
blk_write_time | double precision | //从磁盘写入花费的时间
四、pg_stat_statements相关参数
testdb=# select name, setting,context from pg_settings where upper(name) like upper('%pg_stat_statements%');
name | setting | context
----------------------------------+---------+------------
pg_stat_statements.max | 1000 | //保留记录数（表示监控的语句最多为1000句）
pg_stat_statements.save | on | //重启后保留记录（表示当postgresql停止时，把信息存入磁盘文件以备下次启动时再使用）
pg_stat_statements.track | top | //all：所有sql包括函数内嵌套的sql；top：直接执行的sql （表示不监控嵌套的sql语句）
pg_stat_statements.track_utility | on | //是否跟踪非DML语句，如DDL，DCL（表示对 INSERT/UPDATE/DELETE/SELECT 之外的sql动作也作监控。）
五、测试验证
注：安装benchsql 参考 https://support.enmotech.com/article/publish/8448
| TOP SQL查找功能 | SQL语句 |
|---|---|
| 单次调用最消耗IO SQL TOP | select userid,dbid,query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 10; |
| IO开销总量最大的 SQL TOP | select userid,dbid,query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 10; |
| 最耗共享内存TOP SQL | select userid,dbid,query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10; |
| 最耗临时空间TOP SQL | select userid,dbid,query from pg_stat_statements order by temp_blks_written desc limit 10; |
| 执行次数最多的TOP SQL | select query from pg_stat_statements order by calls desc limit 10; |
| 共享池命中率最低的TOP SQL | select query from pg_stat_statements order by (1-shared_blks_hit/(shared_blks_hit+shared_blks_read )) desc limit 10; |
例子L:
testdb=# select userid,dbid,query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 10;
userid | dbid | query
--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
18261 | 18297 | UPDATE bmsql_district SET d_ytd = d_ytd + $1 WHERE d_w_id = $2 AND d_id = $3
10 | 18201 | SET connection_info = '{"driver_name":"libpq","driver_version":"(PanWeiDB 2.0.0 (Build0)) compiled at 2023-11-30 09:02:43 commit 03b85d1 last mr "}'
10 | 18202 | SELECT sender_pid,local_role,peer_role,peer_state, state,sender_sent_location,sender_write_location, sender_flush_location,sender_replay_location, receiver_received_location,receiver_write_location, receiver_flush_l
ocation,receiver_replay_location, sync_percent,sync_state,sync_priority, sync_most_available,channel FROM pg_stat_get_wal_senders();
10 | 18202 | select * from pg_catalog.disable_conn(?, ?, ?);
18261 | 18297 | INSERT INTO bmsql_history ( h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
18261 | 18297 | create table bmsql_customer (
+
| | c_w_id integer not null,
+
| | c_d_id integer not null,
+
| | c_id integer not null,
+
| | c_discount decimal(4,4),
+
| | c_credit char(2),
+
| | c_last varchar(16),
+
| | c_first varchar(16),
+
| | c_credit_lim decimal(12,2),
+
| | c_balance decimal(12,2),
+
| | c_ytd_payment decimal(12,2),
+
| | c_payment_cnt integer,
+
| | c_delivery_cnt integer,
+
| | c_street_1 varchar(20),
+
| | c_street_2 varchar(20),
+
| | c_city varchar(20),
+
| | c_state char(2),
+
| | c_zip char(9),
+
| | c_phone char(16),
+
| | c_since timestamp,
+
| | c_middle char(2),
+
| | c_data varchar(500)
+
| | )
10 | 18297 | SELECT n.nspname as "Schema",
+
| | c.relname as "Name",
+
| | CASE c.relkind WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END as "Type",
+
| | pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
+
| | c.reloptions as "Storage"
+
| | FROM pg_catalog.pg_class c
+
| | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
+
| | LEFT JOIN pg_catalog.gs_recyclebin rcy ON rcy.rcyrelid = c.oid and rcy.rcyoperation=?
+
| | WHERE c.relkind IN (?,?,?,?,?,?,?,?,?)
+
| | AND rcy.rcyrelid is null
+
| | AND n.nspname <> ?
+
| | AND n.nspname <> ?
+
| | AND n.nspname <> ?
+
| | AND n.nspname !~ ?
+
| | AND c.relname not like ?
+
| | AND c.relname not like ?
+
| | AND pg_catalog.pg_table_is_visible(c.oid)
+
| | ORDER BY 1,2;
18261 | 18297 | SELECT c_first, c_middle, c_last, c_balance FROM bmsql_customer WHERE c_w_id = $1 AND c_d_id = $2 AND c_id = $3
10 | 18202 | select sender_pid,local_role,peer_role,peer_state,state,sender_sent_location,sender_write_location,sender_flush_location,sender_replay_location,receiver_received_location,receiver_write_location,receiver_flush_locat
ion,receiver_replay_location,sync_percent,sync_state,sync_priority,sync_most_available,channel from pg_stat_get_wal_senders() where peer_role=?;
18261 | 18297 | SELECT w_name, w_street_1, w_street_2, w_city, w_state, w_zip FROM bmsql_warehouse WHERE w_id = $1
(10 rows)
testdb=#
