PostgreSQL的pg_qualstats有什么作用

这篇文章主要讲解了"PostgreSQL的pg_qualstats有什么作用"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"PostgreSQL的pg_qualstats有什么作用"吧！

pg_qualstats是PostgreSQL的一个extension,用于统计WHERE/JOIN语句中出现的谓词。

安装
源代码位于github上,clone后可直接编译安装.

[pg12@localhost extensions]$ git clone https://github.com/powa-team/pg_qualstats.gitfatal: could not create work tree dir 'pg_qualstats'.: Permission denied[pg12@localhost extensions]$ git clone https://github.com/powa-team/pg_qualstats.gitCloning 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 712Receiving objects: 100% (717/717), 299.35 KiB | 300.00 KiB/s, done.Resolving deltas: 100% (445/445), done.[pg12@localhost extensions]$ lspg_qualstats  rum[pg12@localhost extensions]$ cd pg_qualstats/[pg12@localhost pg_qualstats]$ lsCHANGELOG        debian  expected  Makefile   pg_qualstats--1.0.8.sql  pg_qualstats.control  testCONTRIBUTORS.md  doc     LICENSE   META.json  pg_qualstats.c           README.md[pg12@localhost pg_qualstats]$ makegcc -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.Pogcc -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 restartwaiting for server to shut down.... doneserver stoppedwaiting 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-bit2019-07-26 15:47:01.242 CST [1862] LOG:  listening on IPv4 address "0.0.0.0", port 54322019-07-26 15:47:01.242 CST [1862] LOG:  listening on IPv6 address "::", port 54322019-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 process2019-07-26 15:47:01.282 CST [1862] HINT:  Future log output will appear in directory "pg_log". doneserver started

创建extension

testdb=# create extension pg_qualstats;CREATE EXTENSIONtestdb=# 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 SYSTEMtestdb=# 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/libmakemake install

创建用于测试的db

testdb=# create database benchdb;CREATE DATABASE

使用tpcc进行测试

[pg12@localhost test]$ git clone https://github.com/Percona-Lab/sysbench-tpcc.gitCloning into 'sysbench-tpcc'...remote: Enumerating objects: 163, done.remote: Total 163 (delta 0), reused 0 (delta 0), pack-reused 163Receiving objects: 100% (163/163), 52.38 KiB | 0 bytes/s, done.Resolving deltas: 100% (85/85), done.[pg12@localhost test]$ lssysbench-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 preparesysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta3)Initializing worker threads...Creating tables: 2Creating tables: 1Adding indexes 1 ... Waiting on tables 30 secAdding indexes 2 ... Waiting on tables 30 secloading tables: 1 for warehouse: 1loading tables: 1 for warehouse: 2loading tables: 2 for warehouse: 1loading 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 runsysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta3)Running the test with following options:Number of threads: 2Report intermediate results every 1 second(s)Initializing random number generator from current timeInitializing 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.00SQL 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:              906Latency (ms):         min:                                    2.64         avg:                                   22.15         max:                                   80.51         95th percentile:                       53.85         sum:                                20070.80Threads 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 benchdbYou are now connected to database "benchdb" as user "pg12".benchdb=# select * from pg_qualstats_indexes;psql: ERROR:  relation "pg_qualstats_indexes" does not existLINE 1: select * from pg_qualstats_indexes;                      ^benchdb=# CREATE EXTENSION hypopg;CREATE EXTENSIONbenchdb=# CREATE EXTENSION pg_stat_statements ;CREATE EXTENSIONbenchdb=# CREATE EXTENSION pg_qualstats;CREATE EXTENSIONbenchdb=# 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)

感谢各位的阅读，以上就是"PostgreSQL的pg_qualstats有什么作用"的内容了，经过本文的学习后，相信大家对PostgreSQL的pg_qualstats有什么作用这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！