Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the function of pg_qualstats of PostgreSQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the role of pg_qualstats of PostgreSQL"?

Pg_qualstats is an extension of PostgreSQL that is used to count predicates that appear in WHERE/JOIN statements.

Installation

The source code is located on github and can be compiled and installed directly after 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% (5max 5), done.remote: Compressing objects: 100% (5max 5), done.remote: Total 717 (delta 0) Reused 1 (delta 0), pack-reused 712Receiving objects: 100% (717 KiB/s 717), 299.35 KiB | 300.00 KiB/s, done.Resolving deltas: 100% (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 is similar to pg_stat_statements in that the initialization parameter shared_preload_libraries needs to be modified

[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 starting PostgreSQL 12beta1 on x86_64-pc-linux-gnu 47 LOG 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 15 49 47 LOG 01.242 CST [1862] LOG: listening on IPv4 address "0.0.0.0", port 54322019-07-26 15 Swiss 47A 01.242 CST [1862] LOG: listening on IPv6 address ":" Port 54322019-07-26 15 LOG 47V 01.244 CST [1862] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-07-26 1515 LOG 47V 01.282 CST [1862] LOG: redirecting log output to logging collector process2019-07-26 15GV 47A 01.282 CST [1862] HINT: Future log output will appear in directory "pg_log". Doneserver started

Create 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 ata 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)

Parameters.

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.

Use

Configuration parameters: collect all predicates

Testdb=# ALTER SYSTEM SET pg_qualstats.sample_rate TO 1 makes alter SYSTEMtestdb=# select pg_reload_conf (); pg_reload_conf-t (1 row)

Install sysbench

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

Create a db for testing

Testdb=# create database benchdb;CREATE DATABASE

Use tpcc for testing

[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 KiB), 52.38 KiB | 0 bytes/s, done.Resolving deltas: 100% (85 KiB) 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 qps) 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.97x 1184.97) 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 tps 184.06) lat (ms: 1139.35) 95%): 50.11 err/s 1.00 reconn/s: 0.00 [4s] thds: 2 tps: 81.98 qps: 2491.38 (r/w/o: 1126.72max 1200.70 qps 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) lat (ms: 1143.90) 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 reconn/s 1179.21) 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) lat (ms: 1189.20) 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 prime 1125.91 qps 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 impulse 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 r/w/o 1195.26 reconn/s) lat (ms 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.0000and9.00 execution time (avg/stddev): 10.0354Comp0.00 [pg12@localhost sysbench-tpcc] $

Query pg_qualstats_indexes to see which columns do not have index, but there are predicates on those columns

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) | | c_balance | numeric (12heli2) | c_ytd_payment | numeric (12recover2) | 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) Thank you for reading The above is the content of "what is the role of PostgreSQL's pg_qualstats". After the study of this article, I believe you have a deeper understanding of the role of PostgreSQL's pg_qualstats, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.