溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

PostgreSQL中怎么使用pg_prewarm插件

發(fā)布時(shí)間：2021-11-08 16:37:57 來源：億速云 閱讀：449 作者：iii 欄目：關(guān)系型數(shù)據(jù)庫

這篇文章主要介紹“PostgreSQL中怎么使用pg_prewarm插件”，在日常操作中，相信很多人在PostgreSQL中怎么使用pg_prewarm插件問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”PostgreSQL中怎么使用pg_prewarm插件”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

使用pg_prewarm插件可”預(yù)熱”數(shù)據(jù).

安裝pg_prewarm

[pg12@localhost pg_prewarm]$ make
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend'
make -C catalog distprep generated-header-symlinks
make[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-symlinks
make[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 -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 pg_prewarm.o pg_prewarm.c -MMD -MP -MF .deps/pg_prewarm.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 -I. -I. -I../../src/include  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o autoprewarm.o autoprewarm.c -MMD -MP -MF .deps/autoprewarm.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 -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-headers
make[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend'
make -C catalog distprep generated-header-symlinks
make[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-symlinks
make[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]$

簡(jiǎn)單使用

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

查看buffer cache

[local]:5432 pg12@testdb=# select * from vw_buffercache_hogs;
           key            | buffers | dirty_buffers | hog_factor 
--------------------------+---------+---------------+------------
                          |   64640 |             0 |     0.9863
 t_prewarm                |     543 |           541 |     0.0083
 - system catalogues      |     176 |            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

使用樣例
通過預(yù)熱,在執(zhí)行查詢前已把數(shù)據(jù)讀取到cache中,可以提升查詢性能
測(cè)試數(shù)據(jù)

[local]:5432 pg12@testdb=# drop table if exists t_prewarm;
DROP TABLE
Time: 9.680 ms
[local]:5432 pg12@testdb=# create table t_prewarm(id int,c1 varchar(20));
CREATE TABLE
Time: 4.736 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_prewarm(id,c1) select x,'c1-'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 3783.073 ms (00:03.783)

沒有預(yù)熱的情況

[pg12@localhost ~]$ sudo sh -c 'echo 3 >/proc/sys/vm/drop_caches'
[sudo] password for pg12: 
[pg12@localhost ~]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-08-20 15:26: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-bit
2019-08-20 15:26:06.697 CST [2519] LOG:  listening on IPv6 address "::1", port 5432
2019-08-20 15:26:06.697 CST [2519] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-08-20 15:26:06.701 CST [2519] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-20 15:26:06.739 CST [2519] LOG:  redirecting log output to logging collector process
2019-08-20 15:26:06.739 CST [2519] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[pg12@localhost ~]$ time psql  -d testdb -c "select count(*) from t_prewarm;" -d testdb
Timing is on.
Expanded display is used automatically.
  count  
---------
 1000000
(1 row)
Time: 187.754 ms
real  0m0.261s
user  0m0.003s
sys 0m0.009s

先行預(yù)熱的情況

[pg12@localhost ~]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-08-20 15:26: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-bit
2019-08-20 15:26:45.445 CST [2537] LOG:  listening on IPv6 address "::1", port 5432
2019-08-20 15:26:45.445 CST [2537] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-08-20 15:26:45.448 CST [2537] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-20 15:26:45.484 CST [2537] LOG:  redirecting log output to logging collector process
2019-08-20 15:26:45.484 CST [2537] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[pg12@localhost ~]$ sudo sh -c 'echo 3 >/proc/sys/vm/drop_caches'
[pg12@localhost ~]$ psql  -d testdb -c "select pg_prewarm('t_prewarm');"
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 t_prewarm;"
Timing is on.
Expanded display is used automatically.
  count  
---------
 1000000
(1 row)
Time: 88.713 ms
real  0m0.103s
user  0m0.003s
sys 0m0.006s

187.754 ms vs 88.713 ms

下面是使用索引的情況

[pg12@localhost ~]$ sudo sh -c 'echo 3 >/proc/sys/vm/drop_caches'
[pg12@localhost ~]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-08-20 15:30: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-bit
2019-08-20 15:30:54.228 CST [2567] LOG:  listening on IPv6 address "::1", port 5432
2019-08-20 15:30:54.228 CST [2567] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-08-20 15:30:54.229 CST [2567] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-20 15:30:54.249 CST [2567] LOG:  redirecting log output to logging collector process
2019-08-20 15:30:54.249 CST [2567] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[pg12@localhost ~]$ time psql -d testdb -c "select * from t_prewarm where id in (1,500000);"Timing is on.
Expanded display is used automatically.
   id   |    c1     
--------+-----------
      1 | c1-1
 500000 | c1-500000
(2 rows)
Time: 8.219 ms
real  0m0.041s
user  0m0.004s
sys 0m0.021s
[pg12@localhost ~]$ 
[pg12@localhost ~]$ sudo sh -c 'echo 3 >/proc/sys/vm/drop_caches'
[pg12@localhost ~]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-08-20 15:31: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-bit
2019-08-20 15:31:44.409 CST [2584] LOG:  listening on IPv6 address "::1", port 5432
2019-08-20 15:31:44.409 CST [2584] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-08-20 15:31:44.412 CST [2584] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-20 15:31:44.446 CST [2584] LOG:  redirecting log output to logging collector process
2019-08-20 15:31:44.446 CST [2584] HINT:  Future log output will appear in directory "pg_log".
 done
server 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 ms
real  0m0.061s
user  0m0.000s
sys 0m0.007s
[pg12@localhost ~]$ time psql -d testdb -c "select * from t_prewarm where id in (1,500000);"Timing is on.
Expanded display is used automatically.
   id   |    c1     
--------+-----------
      1 | c1-1
 500000 | c1-500000
(2 rows)
Time: 5.431 ms
real  0m0.010s
user  0m0.001s
sys 0m0.001s

因?yàn)樽x取索引的block的數(shù)不多,因此性能差別不大.

到此，關(guān)于“PostgreSQL中怎么使用pg_prewarm插件”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手