pg_qualstats是PostgreSQL的一個extension,用於統計WHERE/JOIN語句中出現的謂詞。

pg_qualstats is a PostgreSQL extension keeping statistics on predicates found in WHERE statements and JOIN clauses.This is useful if you want to be able to analyze what are the most-often executed quals (predicates) on your database. The powa project makes use of this to provide advances index suggestions.

安裝
原始碼位於github上,clone後可直接編譯安裝.


[pg12@localhost extensions]$ git clone https://github.com/powa-team/pg_qualstats.git
fatal: could not create work tree dir 'pg_qualstats'.: Permission denied
[pg12@localhost extensions]$ git clone https://github.com/powa-team/pg_qualstats.git
Cloning into 'pg_qualstats'...
remote: Enumerating objects: 5, done.
remote: Counting objects: 100% (5/5), done.
remote: Compressing objects: 100% (5/5), done.
remote: Total 717 (delta 0), reused 1 (delta 0), pack-reused 712
Receiving objects: 100% (717/717), 299.35 KiB | 300.00 KiB/s, done.
Resolving deltas: 100% (445/445), done.
[pg12@localhost extensions]$ ls
pg_qualstats  rum
[pg12@localhost extensions]$ cd pg_qualstats/
[pg12@localhost pg_qualstats]$ ls
CHANGELOG        debian  expected  Makefile   pg_qualstats--1.0.8.sql  pg_qualstats.control  test
CONTRIBUTORS.md  doc     LICENSE   META.json  pg_qualstats.c           README.md
[pg12@localhost pg_qualstats]$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/xdb/pg12beta1/include/postgresql/server -I/appdb/xdb/pg12beta1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_qualstats.o pg_qualstats.c -MMD -MP -MF .deps/pg_qualstats.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC pg_qualstats.o -L/appdb/xdb/pg12beta1/lib   -Wl,--as-needed -Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags  -shared -o pg_qualstats.so
[pg12@localhost pg_qualstats]$ make install
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/lib/postgresql'
/usr/bin/install -c -m 644 .//pg_qualstats.control '/appdb/xdb/pg12beta1/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//pg_qualstats--1.0.8.sql  '/appdb/xdb/pg12beta1/share/postgresql/extension/'
/usr/bin/install -c -m 755  pg_qualstats.so '/appdb/xdb/pg12beta1/lib/postgresql/'
[pg12@localhost pg_qualstats]$

pg_qualstats與pg_stat_statements類似,需要修改初始化引數shared_preload_libraries


[pg12@localhost pg12db1]$ grep 'shared_preload' postgresql.conf 
shared_preload_libraries = 'pg_stat_statements,pg_qualstats'    # (change requires restart)
[pg12@localhost pg12db1]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-07-26 15:47:01.241 CST [1862] LOG:  starting PostgreSQL 12beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
2019-07-26 15:47:01.242 CST [1862] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-07-26 15:47:01.242 CST [1862] LOG:  listening on IPv6 address "::", port 5432
2019-07-26 15:47:01.244 CST [1862] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-07-26 15:47:01.282 CST [1862] LOG:  redirecting log output to logging collector process
2019-07-26 15:47:01.282 CST [1862] HINT:  Future log output will appear in directory "pg_log".
 done
server started

建立extension


testdb=# create extension pg_qualstats;
CREATE EXTENSION
testdb=# show shared_preload_libraries ;
    shared_preload_libraries     
---------------------------------
 pg_stat_statements,pg_qualstats
(1 row)
testdb=# \dx
                                          List of installed extensions
        Name        | Version |    Schema    |                            Description                            
--------------------+---------+--------------+----------------------------------------------------
 blackhole_am       | 1.0     | public       | template table AM eating all data
 bloom              | 1.0     | public       | bloom access method - signature file based index
 btree_gin          | 1.3     | public       | support for indexing common datatypes in GIN
 dblink             | 1.2     | public       | connect to other PostgreSQL databases from within a database
 hypopg             | 1.1.3   | pgextensions | Hypothetical indexes for PostgreSQL
 pageinspect        | 1.7     | public       | inspect the contents of database pages at a low level
 pg_qualstats       | 1.0.8   | public       | An extension collecting statistics about quals
 pg_stat_statements | 1.7     | public       | track execution statistics of all SQL statements executed
 pg_trgm            | 1.4     | public       | text similarity measurement and index searching based on trigrams
 pgcrypto           | 1.3     | public       | cryptographic functions
 plpgsql            | 1.0     | pg_catalog   | PL/pgSQL procedural language
 rum                | 1.3     | public       | RUM index access method
(12 rows)

引數
pg_qualstats.enabled: true or false (to enable or to disable pg_qualstats). ON by default.

pg_qualstats.track_constants: true or false (to enable tracking of each constant. False would reduce the number of entries to track predicates.)
pg_qualstats.max: The number of queries tracked. Defaults to 1000.

pg_qualstats.resolve_oids: Just store the oids or resolve them and store at query time. This takes additional space.

pg_qualstats.track_pg_catalog: Defaults to false. Whether or not the predicates of the objects in pg_catalog schema should be computed.

pg_qualstats.sample_rate: Default is -1. The fraction of queries to be sampled. -1 defaults to (1/max_connections). When set to 1, everything is sampled. Similarly when set to 0.1, one out of 10 queries are sampled.

使用
配置引數:收集所有的謂詞


testdb=# ALTER SYSTEM SET pg_qualstats.sample_rate TO 1;
ALTER SYSTEM
testdb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

安裝sysbench


./autogen.sh
./configure --with-pgsql --with-pgsql-includes=/appdb/atlasdb/pg12beta1/include  --with-pgsql-libs=/appdb/atlasdb/pg12beta1/lib
make
make install

建立用於測試的db


testdb=# create database benchdb;
CREATE DATABASE

使用tpcc進行測試


[pg12@localhost test]$ git clone https://github.com/Percona-Lab/sysbench-tpcc.git
Cloning into 'sysbench-tpcc'...
remote: Enumerating objects: 163, done.
remote: Total 163 (delta 0), reused 0 (delta 0), pack-reused 163
Receiving objects: 100% (163/163), 52.38 KiB | 0 bytes/s, done.
Resolving deltas: 100% (85/85), done.
[pg12@localhost test]$ ls
sysbench-tpcc
[pg12@localhost test]$ cd sysbench-tpcc/
[pg12@localhost sysbench-tpcc]$ pwd
/data/test/sysbench-tpcc
[pg12@localhost sysbench-tpcc]$ ls
[pg12@localhost sysbench-tpcc]$ ./tpcc.lua --pgsql-user=pg12 --pgsql-port=5432 --pgsql-db=benchdb --time=10 --threads=2 --report-interval=1 --tables=2 --scale=2 --use_fk=0  --trx_level=RC --db-driver=pgsql prepare
sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating tables: 2
Creating tables: 1
Adding indexes 1 ... 
Waiting on tables 30 sec
Adding indexes 2 ... 
Waiting on tables 30 sec
loading tables: 1 for warehouse: 1
loading tables: 1 for warehouse: 2
loading tables: 2 for warehouse: 1
loading tables: 2 for warehouse: 2
[pg12@localhost sysbench-tpcc]$ ./tpcc.lua --pgsql-user=pg12 --pgsql-port=5432 --pgsql-db=benchdb --time=10 --threads=2 --report-interval=1 --tables=2 --scale=2 --use_fk=0  --trx_level=RC --db-driver=pgsql run
sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 2
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 2 tps: 90.83 qps: 2492.29 (r/w/o: 1135.85/1170.79/185.65) lat (ms,95%): 59.99 err/s 0.00 reconn/s: 0.00
[ 2s ] thds: 2 tps: 87.00 qps: 2462.93 (r/w/o: 1103.97/1184.97/173.99) lat (ms,95%): 64.47 err/s 0.00 reconn/s: 0.00
[ 3s ] thds: 2 tps: 92.03 qps: 2465.76 (r/w/o: 1139.35/1142.35/184.06) lat (ms,95%): 50.11 err/s 1.00 reconn/s: 0.00
[ 4s ] thds: 2 tps: 81.98 qps: 2491.38 (r/w/o: 1126.72/1200.70/163.96) lat (ms,95%): 61.08 err/s 0.00 reconn/s: 0.00
[ 5s ] thds: 2 tps: 88.99 qps: 2519.78 (r/w/o: 1143.90/1197.89/177.98) lat (ms,95%): 51.02 err/s 0.00 reconn/s: 0.00
[ 6s ] thds: 2 tps: 82.01 qps: 2465.44 (r/w/o: 1122.20/1179.21/164.03) lat (ms,95%): 63.32 err/s 1.00 reconn/s: 0.00
[ 7s ] thds: 2 tps: 86.01 qps: 2515.42 (r/w/o: 1154.19/1189.20/172.03) lat (ms,95%): 61.08 err/s 1.00 reconn/s: 0.00
[ 8s ] thds: 2 tps: 89.99 qps: 2420.80 (r/w/o: 1114.91/1125.91/179.99) lat (ms,95%): 50.11 err/s 0.00 reconn/s: 0.00
[ 9s ] thds: 2 tps: 100.01 qps: 2632.33 (r/w/o: 1208.15/1224.15/200.02) lat (ms,95%): 50.11 err/s 1.00 reconn/s: 0.00
[ 10s ] thds: 2 tps: 104.76 qps: 2593.05 (r/w/o: 1188.27/1195.26/209.52) lat (ms,95%): 44.98 err/s 1.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            11477
        write:                           11851
        other:                           1814
        total:                           25142
    transactions:                        906    (90.23 per sec.)
    queries:                             25142  (2503.86 per sec.)
    ignored errors:                      5      (0.50 per sec.)
    reconnects:                          0      (0.00 per sec.)
Throughput:
    events/s (eps):                      90.2272
    time elapsed:                        10.0413s
    total number of events:              906
Latency (ms):
         min:                                    2.64
         avg:                                   22.15
         max:                                   80.51
         95th percentile:                       53.85
         sum:                                20070.80
Threads fairness:
    events (avg/stddev):           453.0000/9.00
    execution time (avg/stddev):   10.0354/0.00
[pg12@localhost sysbench-tpcc]$

查詢pg_qualstats_indexes,可檢視哪些列上沒有index,但在這些列上存在謂詞


testdb=# \c benchdb
You are now connected to database "benchdb" as user "pg12".
benchdb=# select * from pg_qualstats_indexes;
psql: ERROR:  relation "pg_qualstats_indexes" does not exist
LINE 1: select * from pg_qualstats_indexes;
                      ^
benchdb=# CREATE EXTENSION hypopg;
CREATE EXTENSION
benchdb=# CREATE EXTENSION pg_stat_statements ;
CREATE EXTENSION
benchdb=# CREATE EXTENSION pg_qualstats;
CREATE EXTENSION
benchdb=# show shared_preload_libraries ;
    shared_preload_libraries     
---------------------------------
 pg_stat_statements,pg_qualstats
(1 row)
benchdb=# select * from pg_qualstats_indexes;
    relid    |   attnames   |      possible_types      | execution_count 
-------------+--------------+--------------------------+-----------------
 customer2   | {c_id}       | {brin,btree,hash}        |               5
 customer2   | {c_last}     | {brin,btree,hash,spgist} |              12
 customer1   | {c_id}       | {brin,btree,hash}        |               8
 customer1   | {c_last}     | {brin,btree,hash,spgist} |              34
 orders2     | {o_c_id}     | {brin,btree,hash}        |               1
 order_line2 | {ol_o_id}    | {brin,btree,hash}        |               8
 order_line1 | {ol_o_id}    | {brin,btree}             |             213
 stock1      | {s_quantity} | {brin,btree}             |             213
(8 rows)
benchdb=# \d customer2
                           Table "public.customer2"
     Column     |            Type             | Collation | Nullable | Default 
----------------+-----------------------------+-----------+----------+---------
 c_id           | integer                     |           | not null | 
 c_d_id         | smallint                    |           | not null | 
 c_w_id         | smallint                    |           | not null | 
 c_first        | character varying(16)       |           |          | 
 c_middle       | character(2)                |           |          | 
 c_last         | character varying(16)       |           |          | 
 c_street_1     | character varying(20)       |           |          | 
 c_street_2     | character varying(20)       |           |          | 
 c_city         | character varying(20)       |           |          | 
 c_state        | character(2)                |           |          | 
 c_zip          | character(9)                |           |          | 
 c_phone        | character(16)               |           |          | 
 c_since        | timestamp without time zone |           |          | 
 c_credit       | character(2)                |           |          | 
 c_credit_lim   | bigint                      |           |          | 
 c_discount     | numeric(4,2)                |           |          | 
 c_balance      | numeric(12,2)               |           |          | 
 c_ytd_payment  | numeric(12,2)               |           |          | 
 c_payment_cnt  | smallint                    |           |          | 
 c_delivery_cnt | smallint                    |           |          | 
 c_data         | text                        |           |          | 
Indexes:
    "customer2_pkey" PRIMARY KEY, btree (c_w_id, c_d_id, c_id)
    "idx_customer2" btree (c_w_id, c_d_id, c_last, c_first)

參考資料
pg_qualstats
Automatic Index Recommendations in PostgreSQL using pg_qualstats and hypopg

PostgreSQL DBA(63) - Extension(pg_qualstats)

相關文章