目录
- PostgreSQL 优化思路
- 一、排序
- 二、索引
- 三、连接查询方式
- 四、多表联查时
- PostgreSQL提供了一些性能调优的功能
- 1.使用EXPLAIN
- 2.及时更新执行计划中使用的统计信息
- 3.明确用join来关联表
- 4.关闭自动提交
- 5.多次插入数据用copy命令更高效
- 6.临时删除index
- 7.外键关联的删除
- 8.增加maintenance_work_mem参数大小
- 9.增加checkpoint_segments参数的大小
- 10.设置archive_mode无效
- 11.最后执行VACUUM ANALYZE
- PostgreSQL 参数设置
- autovacuum 相关参数
- 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 (…) 能过滤出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, ‘yyyyMMdd’)是没用的
- 表字段之间关联,尽量给相关字段上添加索引。
- 复合索引,遵从最左前缀的原则,即最左优先。(单独右侧字段查询没有索引的)
三、连接查询方式
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%\’;
(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的累加值。
pg中性能相关常调参数
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持悠久资源。