PostgreSQL中怎么使用pg_prewarm插件
发表于:2024-10-19 作者:千家信息网编辑
千家信息网最后更新 2024年10月19日,这篇文章主要介绍"PostgreSQL中怎么使用pg_prewarm插件",在日常操作中,相信很多人在PostgreSQL中怎么使用pg_prewarm插件问题上存在疑惑,小编查阅了各式资料,整理出简
千家信息网最后更新 2024年10月19日PostgreSQL中怎么使用pg_prewarm插件
这篇文章主要介绍"PostgreSQL中怎么使用pg_prewarm插件",在日常操作中,相信很多人在PostgreSQL中怎么使用pg_prewarm插件问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答"PostgreSQL中怎么使用pg_prewarm插件"的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
使用pg_prewarm插件可"预热"数据.
安装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 -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.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]$
简单使用
[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,'c1-'||x from generate_series(1,100000);ERROR: column "x" does not existLINE 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 100000Time: 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 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,'c1-'||x from generate_series(1,1000000) as x;INSERT 0 1000000Time: 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 restartwaiting for server to shut down.... doneserver stoppedwaiting 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-bit2019-08-20 15:26:06.697 CST [2519] LOG: listening on IPv6 address "::1", port 54322019-08-20 15:26:06.697 CST [2519] LOG: listening on IPv4 address "127.0.0.1", port 54322019-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 process2019-08-20 15:26:06.739 CST [2519] HINT: Future log output will appear in directory "pg_log". doneserver started[pg12@localhost ~]$ time psql -d testdb -c "select count(*) from t_prewarm;" -d testdbTiming is on.Expanded display is used automatically. count --------- 1000000(1 row)Time: 187.754 msreal 0m0.261suser 0m0.003ssys 0m0.009s
先行预热的情况
[pg12@localhost ~]$ pg_ctl restartwaiting for server to shut down.... doneserver stoppedwaiting 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-bit2019-08-20 15:26:45.445 CST [2537] LOG: listening on IPv6 address "::1", port 54322019-08-20 15:26:45.445 CST [2537] LOG: listening on IPv4 address "127.0.0.1", port 54322019-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 process2019-08-20 15:26:45.484 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('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 msreal 0m0.103suser 0m0.003ssys 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 restartwaiting for server to shut down.... doneserver stoppedwaiting 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-bit2019-08-20 15:30:54.228 CST [2567] LOG: listening on IPv6 address "::1", port 54322019-08-20 15:30:54.228 CST [2567] LOG: listening on IPv4 address "127.0.0.1", port 54322019-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 process2019-08-20 15:30: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 (1,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-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-bit2019-08-20 15:31:44.409 CST [2584] LOG: listening on IPv6 address "::1", port 54322019-08-20 15:31:44.409 CST [2584] LOG: listening on IPv4 address "127.0.0.1", port 54322019-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 process2019-08-20 15:31: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 (1,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
因为读取索引的block的数不多,因此性能差别不大.
到此,关于"PostgreSQL中怎么使用pg_prewarm插件"的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注网站,小编会继续努力为大家带来更多实用的文章!
插件
学习
情况
数据
性能
更多
索引
帮助
查询
实用
接下来
不大
差别
文章
方法
理论
知识
篇文章
网站
资料
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
数据库统计优化
国际最大的信息数据库
微信公众平台连数据库
网络安全法学习目标
东南大学网络安全导师放养
刀片机服务器搭建
lol服务器多大
mc自己开服务器地址
索尼软件开发工资
网络技术按表示方法分为
软件开发淘汰机制
数据库列级约束怎么设置
涪陵软件开发公司
为什么服务器没有gpu
db2数据库安全
国际服刺激战场服务器资源
标准网络技术服务报价行情
腾网网络技术有限公司
网络安全等级保护验收要求
辽宁推荐服务器租用虚拟主机
安卓软件开发价格
惠易行互联网科技有限公司
重庆系统软件开发哪家实惠
保险数据库设计
搭建测试服务器地址
山东中信互联网科技有限公司
戴尔服务器t140面板
网络安全保护长效机制
计算机网络技术老师需要懂
邮箱服务器地址