使用pg_prewarm插件可"预热"数据. **安装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]$ ``` 简单使用 ``` [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 ``` **使用样例** 通过预热,在执行查询前已把数据读取到cache中,可以提升查询性能 测试数据 ``` [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) ``` 没有预热的情况 ``` [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 ``` 先行预热的情况 ``` [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 ``` 因为读取索引的block的数不多,因此性能差别不大. **参考资料** [Pre-warming the buffer cache in PostgreSQL](https://blog.dbi-services.com/pre-warming-the-buffer-cache-in-postgresql/) [pg_prewarm](https://www.postgresql.org/docs/11/pgprewarm.html)