这篇文章主要介绍“PostgreSQL中怎么使用pg_prewarm插件”，在日常操作中，相信很多人在PostgreSQL中怎么使用pg_prewarm插件问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中怎么使用pg_prewarm插件”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

使用pg_prewarm插件可”预热”数据.

安装pg_prewarm

[pg12@localhostpg_prewarm]$makemake-C../../src/backendgenerated-headersmake[1]:Enteringdirectory`/home/pg12/source/postgresql-12beta3/src/backend'make-Ccatalogdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/home/pg12/source/postgresql-12beta3/src/backend/catalog'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/home/pg12/source/postgresql-12beta3/src/backend/catalog'make-Cutilsdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/home/pg12/source/postgresql-12beta3/src/backend/utils'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/home/pg12/source/postgresql-12beta3/src/backend/utils'make[1]:Leavingdirectory`/home/pg12/source/postgresql-12beta3/src/backend'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../../src/include-D_GNU_SOURCE-I/usr/include/libxml2-c-opg_prewarm.opg_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-oautoprewarm.oautoprewarm.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-opg_prewarm.sopg_prewarm.oautoprewarm.o-L../../src/port-L../../src/common-Wl,--as-needed-Wl,-rpath,'/appdb/pg12/pg12beta3/lib',--enable-new-dtags[pg12@localhostpg_prewarm]$sudomakeinstall[sudo]passwordforpg12:make-C../../src/backendgenerated-headersmake[1]:Enteringdirectory`/home/pg12/source/postgresql-12beta3/src/backend'make-Ccatalogdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/home/pg12/source/postgresql-12beta3/src/backend/catalog'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/home/pg12/source/postgresql-12beta3/src/backend/catalog'make-Cutilsdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/home/pg12/source/postgresql-12beta3/src/backend/utils'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/home/pg12/source/postgresql-12beta3/src/backend/utils'make[1]:Leavingdirectory`/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-m755pg_prewarm.so'/appdb/pg12/pg12beta3/lib/postgresql/pg_prewarm.so'/bin/install-c-m644./pg_prewarm.control'/appdb/pg12/pg12beta3/share/postgresql/extension/'/bin/install-c-m644./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@localhostpg_prewarm]$

简单使用

[local]:5432pg12@testdb=#createextensionpg_prewarm;CREATEEXTENSIONTime:10.460ms[local]:5432pg12@testdb=#createtablet_prewarm(idint,c1varchar(20));CREATETABLETime:2.796ms[local]:5432pg12@testdb=#insertintot_prewarmselectx,'c1-'||xfromgenerate_series(1,100000);ERROR:column"x"doesnotexistLINE1:insertintot_prewarmselectx,'c1-'||xfromgenerate_series...^Time:1.565ms[local]:5432pg12@testdb=#insertintot_prewarmselectx,'c1-'||xfromgenerate_series(1,100000)asx;INSERT0100000Time:242.437ms[local]:5432pg12@testdb=#selectpg_prewarm('t_prewarm');pg_prewarm------------541(1row)Time:2.399ms

查看buffer cache

[local]:5432pg12@testdb=#select*fromvw_buffercache_hogs;key|buffers|dirty_buffers|hog_factor--------------------------+---------+---------------+------------|64640|0|0.9863t_prewarm|543|541|0.0083-systemcatalogues|176|26|0.0027-unknownfile32856|32|1|0.0005-unknownfile32861|28|2|0.0004-global|19|0|0.0003-unknownfile32869|15|4|0.0002-unknownfile32868|13|3|0.0002-unknownfile32860|8|1|0.0001-unknownfile32867|8|1|0.0001t_copy|8|0|0.0001-unknownfile32873|7|2|0.0001-unknownfile32809|7|1|0.0001-unknownfile32816|6|4|0.0001-unknownfile32872|5|1|0.0001-unknownfile32874|4|2|0.0001pg_rewriteTOAST|4|3|0.0001-unknownfile32815|4|1|0.0001-unknownfile32859|3|1|0.0000pg_rewriteTOASTindex|2|1|0.0000pg_statisticTOASTindex|2|0|0.0000pg_statisticTOAST|1|0|0.0000t_import|1|0|0.0000(23rows)Time:106.757ms

使用样例
通过预热,在执行查询前已把数据读取到cache中,可以提升查询性能
测试数据

[local]:5432pg12@testdb=#droptableifexistst_prewarm;DROPTABLETime:9.680ms[local]:5432pg12@testdb=#createtablet_prewarm(idint,c1varchar(20));CREATETABLETime:4.736ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#insertintot_prewarm(id,c1)selectx,'c1-'||xfromgenerate_series(1,1000000)asx;INSERT01000000Time:3783.073ms(00:03.783)

没有预热的情况

[pg12@localhost~]$sudosh-c'echo3>/proc/sys/vm/drop_caches'[sudo]passwordforpg12:[pg12@localhost~]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2019-08-2015:26:06.692CST[2519]LOG:startingPostgreSQL12beta3onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-08-2015:26:06.697CST[2519]LOG:listeningonIPv6address"::1",port54322019-08-2015:26:06.697CST[2519]LOG:listeningonIPv4address"127.0.0.1",port54322019-08-2015:26:06.701CST[2519]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-08-2015:26:06.739CST[2519]LOG:redirectinglogoutputtologgingcollectorprocess2019-08-2015:26:06.739CST[2519]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted[pg12@localhost~]$timepsql-dtestdb-c"selectcount(*)fromt_prewarm;"-dtestdbTimingison.Expandeddisplayisusedautomatically.count---------1000000(1row)Time:187.754msreal0m0.261suser0m0.003ssys0m0.009s

先行预热的情况

[pg12@localhost~]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2019-08-2015:26:45.444CST[2537]LOG:startingPostgreSQL12beta3onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-08-2015:26:45.445CST[2537]LOG:listeningonIPv6address"::1",port54322019-08-2015:26:45.445CST[2537]LOG:listeningonIPv4address"127.0.0.1",port54322019-08-2015:26:45.448CST[2537]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-08-2015:26:45.484CST[2537]LOG:redirectinglogoutputtologgingcollectorprocess2019-08-2015:26:45.484CST[2537]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted[pg12@localhost~]$sudosh-c'echo3>/proc/sys/vm/drop_caches'[pg12@localhost~]$psql-dtestdb-c"selectpg_prewarm('t_prewarm');"Timingison.Expandeddisplayisusedautomatically.pg_prewarm------------5406(1row)Time:109.636ms[pg12@localhost~]$timepsql-dtestdb-c"selectcount(*)fromt_prewarm;"Timingison.Expandeddisplayisusedautomatically.count---------1000000(1row)Time:88.713msreal0m0.103suser0m0.003ssys0m0.006s

187.754 ms vs 88.713 ms

下面是使用索引的情况

[pg12@localhost~]$sudosh-c'echo3>/proc/sys/vm/drop_caches'[pg12@localhost~]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2019-08-2015:30:54.227CST[2567]LOG:startingPostgreSQL12beta3onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-08-2015:30:54.228CST[2567]LOG:listeningonIPv6address"::1",port54322019-08-2015:30:54.228CST[2567]LOG:listeningonIPv4address"127.0.0.1",port54322019-08-2015:30:54.229CST[2567]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-08-2015:30:54.249CST[2567]LOG:redirectinglogoutputtologgingcollectorprocess2019-08-2015:30:54.249CST[2567]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted[pg12@localhost~]$timepsql-dtestdb-c"select*fromt_prewarmwhereidin(1,500000);"Timingison.Expandeddisplayisusedautomatically.id|c1--------+-----------1|c1-1500000|c1-500000(2rows)Time:8.219msreal0m0.041suser0m0.004ssys0m0.021s[pg12@localhost~]$[pg12@localhost~]$sudosh-c'echo3>/proc/sys/vm/drop_caches'[pg12@localhost~]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2019-08-2015:31:44.406CST[2584]LOG:startingPostgreSQL12beta3onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-08-2015:31:44.409CST[2584]LOG:listeningonIPv6address"::1",port54322019-08-2015:31:44.409CST[2584]LOG:listeningonIPv4address"127.0.0.1",port54322019-08-2015:31:44.412CST[2584]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-08-2015:31:44.446CST[2584]LOG:redirectinglogoutputtologgingcollectorprocess2019-08-2015:31:44.446CST[2584]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted[pg12@localhost~]$timepsql-dtestdb-c"selectpg_prewarm('idx_t_prewarm_id');"Timingison.Expandeddisplayisusedautomatically.pg_prewarm------------2745(1row)Time:43.962msreal0m0.061suser0m0.000ssys0m0.007s[pg12@localhost~]$timepsql-dtestdb-c"select*fromt_prewarmwhereidin(1,500000);"Timingison.Expandeddisplayisusedautomatically.id|c1--------+-----------1|c1-1500000|c1-500000(2rows)Time:5.431msreal0m0.010suser0m0.001ssys0m0.001s

因为读取索引的block的数不多,因此性能差别不大.

到此，关于“PostgreSQL中怎么使用pg_prewarm插件”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！