PostgreSQL的pg_qualstats有什么作用-创新互联
这篇文章主要讲解了“PostgreSQL的pg_qualstats有什么作用”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL的pg_qualstats有什么作用”吧！
pg_qualstats是PostgreSQL的一个extension,用于统计WHERE/JOIN语句中出现的谓词。
安装
源代码位于github上,clone后可直接编译安装.
[pg12@localhost extensions]$ git clone /tupian/20230521/pg_qualstats fatal: could not create work tree dir 'pg_qualstats'.: Permission denied [pg12@localhost extensions]$ git clone /tupian/20230521/pg_qualstats 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 /tupian/20230521/sysbench-tpcc 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)感谢各位的阅读，以上就是“PostgreSQL的pg_qualstats有什么作用”的内容了，经过本文的学习后，相信大家对PostgreSQL的pg_qualstats有什么作用这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
分享文章：PostgreSQL的pg_qualstats有什么作用-创新互联
本文网址：http://mswzjz.cn/article/dpccsc.html