共计 7200 个字符，预计需要花费 18 分钟才能阅读完成。
行业资讯
数据库
PostgreSQL 数据库性能调优的注意点及 pg 数据库性能优化方法是什么
本篇内容主要讲解“PostgreSQL 数据库性能调优的注意点及 pg 数据库性能优化方法是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“PostgreSQL 数据库性能调优的注意点及 pg 数据库性能优化方法是什么”吧!
PostgreSQL 优化思路
优化思路：
0、为每个表执行 ANALYZE
然后分析 EXPLAIN (ANALYZE，BUFFERS) sql。
1、对于多表查询，查看每张表数据，然后改进连接顺序。
2、先查找那部分是重点语句，比如上面 SQL，外面的嵌套层对于优化来说没有意义，可以去掉。
3、查看语句中，where 等条件子句，每个字段能过滤的效率。找出可优化处。
比如 oc.order_id = oo.order_id 是关联条件，需要加索引
oc.op_type = 3 能过滤出 1 /20 的数据，
oo.event_type IN (hellip;) 能过滤出 1 /10 的数据，
这两个是优化的重点，也就是实现确保 op_type 与 event_type 已经加了索引，其次确保索引用到了。
一、排序
尽量避免
排序的数据量尽量少，并保证在内存里完成排序。
（至于具体什么数据量能在内存中完成排序，不同数据库有不同的配置：oracle 是 sort_area_size；postgresql 是 work_mem (integer)，单位是 KB，默认值是 4MB。mysql 是 sort_buffer_size 注意：该参数对应的分配内存是每连接独占！）
二、索引
过滤的数据量比较少，一般来说 20%, 应该走索引。20%-40% 可能走索引也可能不走索引。40%，基本不走索引 (会全表扫描)
保证值的数据类型和字段数据类型要一直。
对索引的字段进行计算时，必须在运算符右侧进行计算。也就是 to_char(oc.create_date, lsquo;yyyyMMdd rsquo;) 是没用的
表字段之间关联，尽量给相关字段上添加索引。
复合索引，遵从最左前缀的原则, 即最左优先。（单独右侧字段查询没有索引的）
三、连接查询方式
1、hash join
放内存里进行关联。
适用于结果集比较大的情况。
比如都是 200000 数据
2、nest loop
从结果 1 逐行取出，然后与结果集 2 进行匹配。
适用于两个结果集，其中一个数据量远大于另外一个时。
结果集一：1000
结果集二：1000000
四、多表联查时
在多表联查时，需要考虑连接顺序问题。
1、当 postgresql 中进行查询时，如果多表是通过逗号，而不是 join 连接，那么连接顺序是多表的笛卡尔积中取最优的。如果有太多输入的表，PostgreSQL 规划器将从穷举搜索切换为基因概率搜索，以减少可能性数目 (样本空间)。基因搜索花的时间少，但是并不一定能找到最好的规划。
2、对于 JOIN
LEFT JOIN / RIGHT JOIN 会一定程度上指定连接顺序，但是还是会在某种程度上重新排列：
FULL JOIN 完全强制连接顺序。
如果要强制规划器遵循准确的 JOIN 连接顺序，我们可以把运行时参数 join_collapse_limit 设置为 1
PostgreSQL 提供了一些性能调优的功能
主要有如下几个方面。
1. 使用 EXPLAIN
EXPLAIN 命令可以查看执行计划，这个方法是我们最主要的调试工具。
2. 及时更新执行计划中使用的统计信息
由于统计信息不是每次操作数据库都进行更新的，一般是在 VACUUM、ANALYZE、CREATE INDEX 等 DDL 执行的时候会更新统计信息，
因此执行计划所用的统计信息很有可能比较旧。这样执行计划的分析结果可能误差会变大。
以下是表 tenk1 的相关的一部分统计信息。
SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE tenk1%relnamerelkindreltuplesrelpagestenk1r10000358tenk1_hundredi1000030tenk1_thous_tenthousi1000030tenk1_unique1i1000030tenk1_unique2i1000030
(5 rows)
其中 relkind 是类型，r 是自身表，i 是索引 index；reltuples 是项目数；relpages 是所占硬盘的块数。
3. 明确用 join 来关联表
一般写法：
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;如果明确用 join 的话，执行时候执行计划相对容易控制一些。
例子：
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);4. 关闭自动提交
（autocommit=false）
5. 多次插入数据用 copy 命令更高效
我们有的处理中要对同一张表执行很多次 insert 操作。这个时候我们用 copy 命令更有效率。因为 insert 一次，其相关的 index 都要做一次，比较花费时间。
6. 临时删除 index
有时候我们在备份和重新导入数据的时候，如果数据量很大的话，要很几个小时才能完成。这个时候可以先把 index 删除掉。导入在建 index。
7. 外键关联的删除
如果表的有外键的话，每次操作都没去 check 外键整合性。因此比较慢。数据导入后在建立外键也是一种选择。
8. 增加 maintenance_work_mem 参数大小
增加这个参数可以提升 CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY 的执行效率。
9. 增加 checkpoint_segments 参数的大小
增加这个参数可以提升大量数据导入时候的速度。
10. 设置 archive_mode 无效
这个参数设置为无效的时候，能够提升以下的操作的速度
CREATE TABLE AS SELECT
CREATE INDEX
ALTER TABLE SET TABLESPACE
CLUSTER 等。
11. 最后执行 VACUUM ANALYZE
表中数据大量变化的时候建议执行 VACUUM ANALYZE。
对生产运行的数据库要用定时任务 crontb 执行如下操作：
psql -U username -d databasename -c vacuum verbose analyze tablename;PostgreSQL 参数设置 autovacuum 相关参数
autovacuum：默认为 on，表示是否开起 autovacuum。默认开起。特别的，当需要冻结 xid 时，尽管此值为 off，PG 也会进行 vacuum。
autovacuum_naptime：下一次 vacuum 的时间，默认 1min。 这个 naptime 会被 vacuum launcher 分配到每个 DB 上。autovacuum_naptime/num of db。
log_autovacuum_min_duration：记录 autovacuum 动作到日志文件，当 vacuum 动作超过此值时。 “-1”表示不记录。“0”表示每次都记录。
autovacuum_max_workers：最大同时运行的 worker 数量，不包含 launcher 本身。
autovacuum_work_mem：每个 worker 可使用的最大内存数。autovacuum_vacuum_threshold：默认 50。与 autovacuum_vacuum_scale_factor 配合使用，autovacuum_vacuum_scale_factor 默认值为 20%。当 update,delete 的 tuples 数量超过 autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold 时，进行 vacuum。如果要使 vacuum 工作勤奋点，则将此值改小。
autovacuum_analyze_threshold：默认 50。与 autovacuum_analyze_scale_factor 配合使用。autovacuum_analyze_scale_factor：默认 10%。当 update,insert,delete 的 tuples 数量超过 autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold 时，进行 analyze。
autovacuum_freeze_max_age：200 million。离下一次进行 xid 冻结的最大事务数。
autovacuum_multixact_freeze_max_age：400 million。离下一次进行 xid 冻结的最大事务数。
autovacuum_vacuum_cost_delay：如果为 -1，取 vacuum_cost_delay 值。
autovacuum_vacuum_cost_limit：如果为 -1，到 vacuum_cost_limit 的值，这个值是所有 worker 的累加值。选项默认值说明是否优化原因 max_connections100 允许客户端连接的最大数目否因为在测试的过程中，100 个连接已经足够 fsyncon 强制把数据同步更新到磁盘是因为系统的 IO 压力很大，为了更好的测试其他配置的影响，把改参数改为 offshared_buffers24MB 决定有多少内存可以被 PostgreSQL 用于缓存数据（推荐内存的 1 /4) 是在 IO 压力很大的情况下，提高该值可以减少 IOwork_mem1MB 使内部排序和一些复杂的查询都在这个 buffer 中完成是有助提高排序等操作的速度，并且减低 IOeffective_cache_size128MB 优化器假设一个查询可以用的最大内存，和 shared_buffers 无关（推荐内存的 1 /2) 是设置稍大，优化器更倾向使用索引扫描而不是顺序扫描 maintenance_work_mem16MB 这里定义的内存只是被 VACUUM 等耗费资源较多的命令调用时使用是把该值调大，能加快命令的执行 wal_buffer768kB 日志缓存区的大小是可以降低 IO，如果遇上比较多的并发短事务，应该和 commit_delay 一起用 checkpoint_segments3 设置 wal log 的最大数量数（一个 log 的大小为 16M）是默认的 48M 的缓存是一个严重的瓶颈，基本上都要设置为 10 以上 checkpoint_completion_target0.5 表示 checkpoint 的完成时间要在两个 checkpoint 间隔时间的 N% 内完成是能降低平均写入的开销 commit_delay0 事务提交后，日志写到 wal log 上到 wal_buffer 写入到磁盘的时间间隔。需要配合 commit_sibling 是能够一次写入多个事务，减少 IO，提高性能 commit_siblings5 设置触发 commit_delay 的并发事务数，根据并发事务多少来配置是减少 IO，提高性能 autovacuum_naptime1min 下一次 vacuum 任务的时间是提高这个间隔时间，使他不是太频繁 autovacuum_analyze_threshold50 与 autovacuum_analyze_scale_factor 配合使用，来决定是否 analyze 是使 analyze 的频率符合实际 autovacuum_analyze_scale_factor0.1 当 update,insert,delete 的 tuples 数量超过 autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold 时，进行 analyze。是使 analyze 的频率符合实际
pg 中性能相关常调参数参数名称参数意义优化思路 shared_buffers 数据库服务器将使用的共享内存缓冲区大小，该缓冲区为所有连接共用。从磁盘读入的数据（主要包括表和索引）都缓存在这里。提高该值可以减少数据库的磁盘 IO。work_mem 声明内部排序和哈希操作可使用的工作内存大小。该内存是在开始使用临时磁盘文件之前使用的内存数目。数值以 kB 为单位的，缺省是 1024 (1MB)。请注意对于复杂的查询，可能会同时并发运行好几个排序或者哈希操作，每个都会使用这个参数声明的这么多内存，然后才会开始求助于临时文件。同样，好几个正在运行的会话可能会同时进行排序操作。因此使用的总内存可能是 work_mem 的好几倍。ORDER BY, DISTINCT 和 mergejoin 都要用到排序操作，而哈希操作在哈希连接、哈希聚集和以哈希为基础的 IN 子查询处理中都会用到。该参数是会话级参数。执行排序操作时，会根据 work_mem 的大小决定是否将一个大的结果集拆分为几个小的和 work_mem 差不多大小的临时文件写入外存。显然拆分的结果是导致了 IO，降低了排序的速度。因此增加 work_mem 有助于提高排序的速度。通常设置时可以逐渐调大，知道数据库在排序的操作时不会有大量的写文件操作即可。该内存每个连接一份，当并发连接较多时候，该值不宜过大。effective_cache_size 优化器假设一个查询可以使用的最大内存（包括 pg 使用的和操作系统缓存），和 shared_buffer 等内存无关，只是给优化器生成计划使用的一个假设值。设置稍大，优化器更倾向使用索引扫描而不是顺序扫描，建议的设置为可用空闲内存的 25%，这里的可用空闲内存指的是主机物理内存在运行 pg 时得空闲值。maintenance_work_mem 这里定义的内存只是在 CREATE INDEX, VACUUM 等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕。在数据库导入数据后，执行建索引等操作时，可以调大，比如 512M。wal_buffers 日志缓冲区，日志缓冲区的大小。两种情况下要酌情调大：1. 单事务的数据修改量很大，产生的日志大于 wal_buffers，为了避免多次 IO，调大该值。
2. 系统中并发小数据量修改的短事务较多，并且设置了 commit_delay，此时 wal_buffers 需要容纳多个事务（commit_siblings 个）的日志，调大该值避免多次 IO。
commit_delay 事务提交后，日志写到 wal_buffer 上到 wal_buffer 写到磁盘的时间间隔。如果并发的非只读事务数目较多，可以适当增加该值，使日志缓冲区一次刷盘可以刷出较多的事务，减少 IO 次数，提高性能。需要和 commit_sibling 配合使用。commit_siblings 触发 commit_delay 等待的并发事务数，也就是系统的并发活跃事务数达到了该值事务才会等待 commit_delay 的时间才将日志刷盘，如果系统中并发活跃事务达不到该值，commit_delay 将不起作用，防止在系统并发压力较小的情况下事务提交后空等其他事务。应根据系统并发写的负载配置。例如统计出系统并发执行增删改操作的平均连接数，设置该值为该平均连接数。fsync 设置为 on 时，日志缓冲区刷盘时，需要确认已经将其写入了磁盘，设置为 off 时，由操作系统调度磁盘写的操作，能更好利用缓存机制，提高 IO 性能。该性能的提高是伴随了数据丢失的风险，当操作系统或主机崩溃时，不保证刷出的日志是否真正写入了磁盘。应依据操作系统和主机的稳定性来配置。autovacuum 是否开启自动清理进程（如开启需要同时设置参数 stats_start_collector = on，stats_row_level = on，），整理数据文件碎片，更新统计信息。如果系统中有大量的增删改操作，建议打开自动清理进程，这样一方面可以增加数据文件的物理连续性，减少磁盘的随机 IO，一方面可以随时更新数据库的统计信息，使优化器可以选择最优的查询计划得到最好的查询性能。如果系统中只有只读的事务，那么关闭自动清理进程。autovacuum_naptime 自动清理进程执行清理分析的时间间隔应该根据数据库的单位时间更新量来决定该值，一般来说单位时间的更新量越大该时间间隔应该设置越短。由于自动清理对系统的开销较大，该值应该谨慎配置（不要过小）。bgwriter_delay 后台写进程的自动执行时间后台写进程的作用是将 shared_buffer 里的脏页面写回到磁盘，减少 checkpoint 的压力，如果系统数据修改的压力一直很大，建议将该时间间隔设置小一些，以免积累的大量的脏页面到 checkpoint，使 checkpoint 时间过长（checkpoint 期间系统响应速度较慢）。bgwriter_lru_maxpages 后台写进程一次写出的脏页面数依据系统单位时间数据的增删改量来修改 bgwriter_lru_multiplier 后台写进程根据最近服务进程需要的 buffer 数量乘上这个比率估算出下次服务进程需要的 buffer 数量，在使用后台写进程写回脏页面，使缓冲区能使用的干净页面达到这个估计值。依据系统单位时间数据的增删改量来修改。
到此，相信大家对“PostgreSQL 数据库性能调优的注意点及 pg 数据库性能优化方法是什么”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
