pg_stat_statements · PHP知識總結

> pg_stat_statements視圖提供一種方法追蹤一個服務器所執行的所有 SQL 語句的執行統計信息，包括SQL語句的執行時間、執行次數等 **前置條件：** 1. 需要安裝`pg_stat_statements`擴展 2. 需要在`postgresql.conf`的shared_preload_libraries配置中增加`pg_stat_statements`來載入，因為它需要額外的共享內存，由于修改了配置文件，因此需要重啟數據庫服務才會生效 ``` // 安裝擴展 [root@izwz91quxhnlkan8kjak5hz postgresql-13.0]# cd contrib/ [root@izwz91quxhnlkan8kjak5hz contrib]# ls adminpack btree_gist earthdistance intarray Makefile pg_prewarm postgres_fdw tablefunc vacuumlo amcheck citext file_fdw isn oid2name pgrowlocks README tcn xml2 auth_delay contrib-global.mk fuzzystrmatch jsonb_plperl pageinspect pg_standby seg test_decoding auto_explain cube hstore jsonb_plpython passwordcheck pg_stat_statements sepgsql tsm_system_rows bloom dblink hstore_plperl lo pg_buffercache pgstattuple spi tsm_system_time bool_plperl dict_int hstore_plpython ltree pgcrypto pg_trgm sslinfo unaccent btree_gin dict_xsyn intagg ltree_plpython pg_freespacemap pg_visibility start-scripts uuid-ossp [root@izwz91quxhnlkan8kjak5hz contrib]# cd pg_stat_statements/ [root@izwz91quxhnlkan8kjak5hz pg_stat_statements]# make && make install make -C ../../src/backend generated-headers ... /usr/bin/install -c -m 755 pg_stat_statements.so '/www/server/postgresql/lib/pg_stat_statements.so' /usr/bin/install -c -m 644 ./pg_stat_statements.control '/www/server/postgresql/share/extension/' /usr/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.7--1.8.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql '/www/server/postgresql/share/extension/' [postgres@izwz91quxhnlkan8kjak5hz postgres]$ psql psql (13.0) Type "help" for help. postgres=# create extension pg_stat_statements; CREATE EXTENSION // 修改配置文件postgresql.conf [postgres@izwz91quxhnlkan8kjak5hz postgres]# vim postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all // 重啟服務 [postgres@izwz91quxhnlkan8kjak5hz postgres]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2021-05-11 19:57:21.920 CST [11604] LOG: starting PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit 2021-05-11 19:57:21.920 CST [11604] LOG: listening on IPv4 address "127.0.0.1", port 5432 2021-05-11 19:57:21.920 CST [11604] LOG: could not bind IPv6 address "::1": 無法指定被請求的地址 2021-05-11 19:57:21.920 CST [11604] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2021-05-11 19:57:21.925 CST [11604] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2021-05-11 19:57:21.940 CST [11606] LOG: database system was shut down at 2021-05-11 19:57:21 CST 2021-05-11 19:57:22.013 CST [11604] LOG: database system is ready to accept connections done server started ``` **表字段說明：** | 列名 | 示例值 | 說明 | | --- | --- | --- | | userid | 10 | 用戶ID | | dbida | 12917 | 數據庫OID | | queryid | 4390283800491518311 | SQL進行歸一化后的HASH值 | | query | select version() | SQL歸一化后的內容 | |plans|24|SQL被解析生成執行計劃的次數| | calls | 1 | 執行次數 | | total_plan_time | 0.208 | SQL總共的執行時間 | | min_plan_time | 0.208 | SQL最小的執行時間 | | max_plan_time | 0.208 | SQL最大的執行時間 | | mean_plan_time | 0.208 | SQL平均的執行時間 | | stddev_plan_time | 0 | 在該語句中花費時間的總體標準偏差，以毫秒計 | | rows | 1 | SQL返回或者影響的行數 | | shared\_blks\_hit | 0 | SQL在shared\_buffer中命中的塊數 | | shared\_blks\_read | 0 | 從磁盤中讀取的塊數 | | shared\_blks\_dirtied | 0 | SQL語句弄臟的shared\_buffer的塊數 | | shared\_blks\_written | 0 | SQL語句寫入的塊數 | | 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 | 從磁盤寫入花費的時間 | **函數：** 1. **pg_stat_statements(showtext boolean)**：`pg_stat_statements`視圖按照一個也叫`pg_stat_statements`的函數來定義。客戶端可以直接調用`pg_stat_statements`函數，并且通過指定`showtext := false`來忽略查詢文本（即，對應于視圖的`query`列的`OUT`參數將返回空值） 2. **pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint)**：該函數可用于清除指定的`userid`，`dbid`和`queryid`相對應的`pg_stat_statements`收集的統計信息。如果未指定任何參數，或者所有指定的參數均為`0`（無效），則它將刪除所有統計信息。默認情況下，此功能只能由超級用戶執行。可以使用`GRANT`授予其他人訪問權限 **配置參數：** * pg_stat_statements.max(integer)：由該模塊跟蹤的語句的最大數目（即`pg_stat_statements`視圖中行的最大數量）。如果觀測到的可區分的語句超過這個數量，最少被執行的語句的信息將會被丟棄。默認值為 5000。這個參數只能在服務器啟動時設置 * pg_stat_statements.track(enum)：該配置可控制哪些語句會被該模塊計數。指定`top`可以跟蹤頂層語句（那些直接由客戶端發出的語句），指定`all`還可以跟蹤嵌套的語句（例如在函數中調用的語句），指定`none`可以禁用語句統計信息收集。默認值是`top`。只有超級用戶能夠改變這個設置 * pg_stat_statements.track_utility(boolean)：該配置可用于控制該模塊是否會跟蹤工具命令。工具命令是除了`SELECT`、`INSERT`、`UPDATE`和`DELETE`之外所有的其他命令。默認值是`on`。只有超級用戶能夠改變這個設置。 * pg_stat_statements.save(boolean)：指定是否在服務器關閉之后還保存語句統計信息。如果被設置為`off`，那么關閉后不保存統計信息并且在服務器啟動時也不會重新載入統計信息。默認值為`on`。這個參數只能在`postgresql.conf`文件中或者在服務器命令行上設置 ``` // 示例配置 # postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all // 應用示例 // 最耗時 SQL，單次調用最耗時 SQL TOP 5 select userid::regrole, dbid, query from pg_stat_statements order by mean_exec_time desc limit 5; // 最耗IO SQL，單次調用最耗IO SQL TOP 5 select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5; ? // 總最耗IO SQL TOP 5 select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5; ? // 響應時間抖動最嚴重 SQL select userid::regrole, dbid, query from pg\stat_statements order by stddev_exec_time desc limit 5; // 最耗共享內存 SQL select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5; // 最耗臨時空間 SQL select userid::regrole, dbid, query from pg\_stat\_statements order by temp\_blks\_written desc limit 5; ```