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

This article mainly introduces "how to use the pg_prewarm plug-in in PostgreSQL". In the daily operation, I believe many people have doubts about how to use the pg_prewarm plug-in in PostgreSQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to use the pg_prewarm plug-in in PostgreSQL". Next, please follow the editor to study!

Use the pg_prewarm plug-in to warm up the data.

Install pg_prewarm

[pg12@localhost pg_prewarm] $makemake-C. /.. / src/backend generated-headersmake [1]: Entering directory `/ home/pg12/source/postgresql-12beta3/src/backend'make-C catalog distprep generated-header-symlinksmake [2]: Entering directory` / home/pg12/source/postgresql-12beta3/src/backend/catalog'make [2]: Nothing to be done for `distprep'.make [2]: Nothing to be done for `generated-header-symlinks'.make [2]: Leaving directory `/ home / pg12/source/postgresql-12beta3/src/backend/catalog'make-C utils distprep generated-header-symlinksmake [2]: Entering directory `/ home/pg12/source/postgresql-12beta3/src/backend/utils'make [2]: Nothing to be done for `distprep'.make [2]: Nothing to be done for `generated-header-symlinks'.make [2]: Leaving directory` / home/pg12/source/postgresql-12beta3/src/backend/utils'make [1]: Leaving directory `/ home/pg12/source/postgresql -12beta3/src/backend'gcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wendif-labels-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-G3-gdwarf-2-fPIC-I. -I. -I../../src/include-D_GNU_SOURCE-I/usr/include/libxml2-c-o pg_prewarm.o pg_prewarm.c-MMD-MP-MF. Deps / pg_prewarm.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-I. -I. -I../../src/include-D_GNU_SOURCE-I/usr/include/libxml2-c-o autoprewarm.o autoprewarm.c-MMD-MP-MF .deps / autoprewarm.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-shared-o pg_prewarm.so Pg_prewarm.o autoprewarm.o-L../../src/port-L../../src/common-Wl -- as-needed-Wl,-rpath,'/appdb/pg12/pg12beta3/lib' -- enable-new-dtags [pg12@localhost pg_prewarm] $sudo make install [sudo] password for pg12: make-C.. / src/backend generated-headersmake [1]: Entering directory `/ home/pg12/source/postgresql-12beta3/src/backend'make-C catalog distprep generated-header-symlinksmake [2]: Entering directory` / home/pg12/source/postgresql-12beta3/src/backend/catalog'make [2]: Nothing to be done for `distprep'.make [2]: Nothing to be done For `generated-header-symlinks'.make [2]: Leaving directory `/ home/pg12/source/postgresql-12beta3/src/backend/catalog'make-C utils distprep generated-header-symlinksmake [2]: Entering directory` / home/pg12/source/postgresql-12beta3/src/backend/utils'make [2]: Nothing to be done for `distprep'.make [2]: Nothing to be done for `generated-header-symlinks'.make [2]: Leaving directory `/ home/pg12/source/postgresql-12beta3/src/backend/ Utils'make [1]: Leaving directory `/ home/pg12/source/postgresql-12beta3/src/backend'/bin/mkdir-p'/ appdb/pg12/pg12beta3/lib/postgresql'/bin/mkdir-p'/ appdb/pg12/pg12beta3/share/postgresql/extension'/bin/mkdir-p'/ appdb/pg12/pg12beta3/share/postgresql/extension'/bin/install-c-m 755 pg_prewarm.so'/ appdb/pg12/pg12beta3/lib/postgresql/pg_prewarm.so'/bin/install- C-m 644. / pg_prewarm.control'/ appdb/pg12/pg12beta3/share/postgresql/extension/'/bin/install-c-m 644. / pg_prewarm--1.1--1.2.sql. / pg_prewarm--1.1.sql. / pg_prewarm--1.0--1.1.sql'/ appdb/pg12/pg12beta3/share/postgresql/extension/' [pg12@localhost pg_prewarm] $

Easy to use

[local]: 5432 pg12@testdb=# create extension pg_prewarm;CREATE EXTENSIONTime: 10.460 ms [local]: 5432 pg12@testdb=# create table t_prewarm (id int,c1 varchar (20)); CREATE TABLETime: 2.796 ms [local]: 5432 pg12@testdb=# insert into t_prewarm select x from generate_series; ERROR: column "x" does not existLINE 1: insert into t_prewarm select x from generate_series... ^ Time: 1.565 ms [local]: 5432 pg12@testdb=# insert into t_prewarm select x row c1muri | | x from generate_series (1meme 100000) as x terse insert 0 100000Time: 242.437 ms [local]: 5432 pg12@testdb=# select pg_prewarm ('tasking precursors'); pg_prewarm-541 (1 row) Time: 2.399 ms

View buffer cache

[local]: 5432 pg12@testdb=# select * from vw_buffercache_hogs Key | buffers | dirty_buffers | hog_factor-- +-| 64640 | 0 | 0.9863 t_prewarm | | 0.0083 | 0.0083-system catalogues | 26 | 0.0027-unknown file 32856 | 32 | 1 | 0.0005-unknown file 32861 | 28 | 2 | 0.0004-global | 19 | 0 | 0. | 0003-unknown file 32869 | 15 | 4 | 0.0002-unknown file 32868 | 13 | 3 | 0.0002-unknown file 32860 | 8 | 1 | 0.0001-unknown file 32867 | 8 | 1 | 0.0001 t_copy | 8 | 0 | 0.0001-unknown file 32873 | 7 | 2 | 0.0001-unknown file 32809 | 7 | 1 | 0.0001-unknown file 32816 | 6 | 4 | 0.0001-unknown file 32872 | 5 | 1 | 0.0001-unknown file 32874 | 4 | 2 | 0.0001 pg_rewrite TOAST | 4 | 3 | 0.0001-unknown file 32815 | 4 | 1 | 0.0001-unknown file 32859 | 3 | 1 | 0.0000 pg_rewrite TOAST index | 2 | 1 | 0.0000 pg_statistic TOAST index | 2 | 0 | 0.0000 pg_statistic TOAST | 1 | 0 | 0.0000 t_import | 1 | 0 | 0.0000 (23 rows) Time: 106.757 ms

Use exampl

By preheating, the data is read into the cache before the query is executed, which can improve the query performance.

test data

[local]: 5432 pg12@testdb=# drop table if exists tactile precursors; drop TABLETime: 9.680 ms [local]: 5432 pg12@testdb=# create table t_prewarm (id int,c1 varchar (20)); CREATE TABLETime: 4.736 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# insert into t_prewarm (id,c1) select x copyright'| | x from generate_series (1m 1000000) as x insert 0 1000000Time: 3783.073 ms (001R 03.783)

There is no preheating.

[pg12@localhost ~] $sudo sh-c 'echo 3 > / proc/sys/vm/drop_caches' [sudo] password for pg12: [pg12@localhost ~] $pg_ctl restartwaiting for server to shut down.... Doneserver stoppedwaiting for server to start....2019-08-2015: 26 starting PostgreSQL 12beta3 on x86_64-pc-linux-gnu 06.692 CST [2519] LOG: starting PostgreSQL 12beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit2019-08-2015: 26 LOG 06.697 CST [2519] LOG: listening on IPv6 address ":: 1", port 54322019-08-2015: 26 Vane 06.697 CST [2519] LOG: listening on IPv4 address "127.0.0.1" Port 54322019-08-20 15 listening on Unix socket 26 listening on Unix socket [2519] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-08-20 15 26 LOG 06.739 CST [2519] LOG: redirecting log output to logging collector process2019-08-20 15 Swiss 26 listening on Unix socket [2519] HINT: Future log output will appear in directory "pg_log". Doneserver started [pg12@localhost] $time psql-d testdb-c "select count (*) from tasking precursors;"-d testdbTiming is on.Expanded display is used automatically. Count-1000000 (1 row) Time: 187.754 msreal 0m0.261suser 0m0.003ssys 0m0.009s

The condition of warming up in advance.

[pg12@localhost ~] $pg_ctl restartwaiting for server to shut down.... Doneserver stoppedwaiting for server to start....2019-08-2015: 26 doneserver stoppedwaiting for server to start....2019 45.444 CST [2537] LOG: starting PostgreSQL 12beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit2019-08-2015: 26 starting PostgreSQL 12beta3 on x86_64-pc-linux-gnu 45.445 CST [2537] LOG: listening on IPv6 address ":: 1", port 54322019-08-2015: 26 starting PostgreSQL 12beta3 on x86_64-pc-linux-gnu 45.445 CST [2537] LOG: listening on IPv4 address "127.0.0.1" Port 54322019-08-20 15 listening on Unix socket 26 listening on Unix socket [2537] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-08-20 15 15 26 listening on Unix socket 45.484 CST [2537] LOG: redirecting log output to logging collector process2019-08-20 15 Swiss 26 listening on Unix socket 26 CST [2537] HINT: Future log output will appear in directory "pg_log". Doneserver started [pg12@localhost] $sudo sh-c 'echo 3 > / proc/sys/vm/drop_caches' [pg12@localhost ~] $psql-d testdb-c "select pg_prewarm (' tact precursors');" Timing is on.Expanded display is used automatically. Pg_prewarm-5406 (1 row) Time: 109.636 ms [pg12@localhost] $time psql-d testdb-c "select count (*) from tasking preloading;" Timing is on.Expanded display is used automatically. Count-1000000 (1 row) Time: 88.713 msreal 0m0.103suser 0m0.003ssys 0m0.006s

187.754 ms vs 88.713 ms

Here is how the index is used

[pg12@localhost ~] $sudo sh-c 'echo 3 > / proc/sys/vm/drop_caches' [pg12@localhost ~] $pg_ctl restartwaiting for server to shut down.... Doneserver stoppedwaiting for server to start....2019-08-2015: 30 doneserver stoppedwaiting for server to start....2019 54.227 CST [2567] LOG: starting PostgreSQL 12beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit2019-08-2015: 30 starting PostgreSQL 12beta3 on x86_64-pc-linux-gnu 54.228 CST [2567] LOG: listening on IPv6 address ":: 1", port 54322019-08-2015: 30 starting PostgreSQL 12beta3 on x86_64-pc-linux-gnu 54.228 CST [2567] LOG: listening on IPv4 address "127.0.0.1" Port 54322019-08-20 15 listening on Unix socket 30 listening on Unix socket [2567] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-08-20 15 15 15 listening on Unix socket 54.249 CST [2567] LOG: redirecting log output to logging collector process2019-08-20 15 V 30 listening on Unix socket 54.249 CST [2567] HINT: Future log output will appear in directory "pg_log". Doneserver started [pg12@localhost] $time psql-d testdb-c "select * from t_prewarm where id in (1meme 500000);" Timing is on.Expanded display is used automatically. Id | C1-+-1 | C1-1 500000 | C1-500000 (2 rows) Time: 8.219 msreal 0m0.041suser 0m0.004ssys 0m0.021s [pg12@localhost ~] $[pg12@localhost ~] $sudo sh-c 'echo 3 > / proc/sys/vm/drop_caches' [pg12@localhost ~] $pg_ctl restartwaiting for server to shut down.... Doneserver stoppedwaiting for server to start....2019-08-2015: 31LOG 44.406 CST [2584] LOG: starting PostgreSQL 12beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit2019-08-2015: 31LOG 44.409 CST [2584] LOG: listening on IPv6 address ":: 1", port 54322019-08-2015: 31LOG 44.409 CST [2584] LOG: listening on IPv4 address "127.0.0.1" Port 54322019-08-20 15 listening on Unix socket 31mer 44.412 CST [2584] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-08-20 1515 listening on Unix socket 44.446 CST [2584] LOG: redirecting log output to logging collector process2019-08-20 15V 31V 44.446 CST [2584] HINT: Future log output will appear in directory "pg_log". Doneserver started [pg12@localhost] $time psql-d testdb-c "select pg_prewarm ('idx_t_prewarm_id');" Timing is on.Expanded display is used automatically. Pg_prewarm-2745 (1 row) Time: 43.962 msreal 0m0.061suser 0m0.000ssys 0m0.007s [pg12@localhost] $time psql-d testdb-c "select * from t_prewarm where id in (1Met 500000);" Timing is on.Expanded display is used automatically. Id | C1-+-1 | C1-1 500000 | C1-500000 (2 rows) Time: 5.431 msreal 0m0.010suser 0m0.001ssys 0m0.001s

Because the number of block reading indexes is small, there is little difference in performance.

At this point, the study on "how to use the pg_prewarm plug-in in PostgreSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.