PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL优化内容) - 珍藏级

11 minute read

背景

数据库是较大型的应用,对于繁忙的数据库,需要消耗大量的内存、CPU、IO、网络资源。

SQL优化是数据库优化的手段之一,优化什么SQL效果最佳呢?首先要了解最耗费资源的SQL,即TOP SQL。

从哪里可以了解数据库的资源都被哪些SQL消耗掉了呢?

资源分为多个维度,CPU,内存,IO等。如何能了解各个维度层面的TOP SQL呢?

pg_stat_statements插件可以用于统计数据库的资源开销,分析TOP SQL。

一、安装pg_stat_statements

pg_stat_statements是PostgreSQL的核心插件之一。可以在编译PostgreSQL时安装,也可以单独安装。

编译时安装

make world    
make install-world    

单独安装

cd src/contrib/pg_stat_statements/    
make; make install    

二、加载pg_stat_statements模块

vi $PGDATA/postgresql.conf    
    
shared_preload_libraries='pg_stat_statements'    

如果要跟踪IO消耗的时间,还需要打开如下参数

track_io_timing = on    

设置单条SQL的最长长度,超过被截断显示(可选)

track_activity_query_size = 2048    

三、配置pg_stat_statements采样参数

vi $PGDATA/postgresql.conf    
    
pg_stat_statements.max = 10000           # 在pg_stat_statements中最多保留多少条统计信息,通过LRU算法,覆盖老的记录。    
pg_stat_statements.track = all           # all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)    
pg_stat_statements.track_utility = off   # 是否跟踪非DML语句 (例如DDL,DCL), on表示跟踪, off表示不跟踪    
pg_stat_statements.save = on             # 重启后是否保留统计信息    

重启数据库

pg_ctl restart -m fast    

四、创建pg_stat_statements extension

在需要查询TOP SQL的数据库中,创建extension

create extension pg_stat_statements;    

五、分析TOP SQL

pg_stat_statements输出内容介绍

查询pg_stat_statements视图,可以得到统计信息

SQL语句中的一些过滤条件在pg_stat_statements中会被替换成变量,减少重复显示的问题。

pg_stat_statements视图包含了一些重要的信息,例如:

1. SQL的调用次数,总的耗时,最快执行时间,最慢执行时间,平均执行时间,执行时间的方差(看出抖动),总共扫描或返回或处理了多少行;

2. shared buffer的使用情况,命中,未命中,产生脏块,驱逐脏块。

3. local buffer的使用情况,命中,未命中,产生脏块,驱逐脏块。

4. temp buffer的使用情况,读了多少脏块,驱逐脏块。

5. 数据块的读写时间。

Name Type References Description
userid oid pg_authid.oid OID of user who executed the statement
dbid oid pg_database.oid OID of database in which the statement was executed
queryid bigint - Internal hash code, computed from the statement’s parse tree
query text - Text of a representative statement
calls bigint - Number of times executed
total_time double precision - Total time spent in the statement, in milliseconds
min_time double precision - Minimum time spent in the statement, in milliseconds
max_time double precision - Maximum time spent in the statement, in milliseconds
mean_time double precision - Mean time spent in the statement, in milliseconds
stddev_time double precision - Population standard deviation of time spent in the statement, in milliseconds
rows bigint - Total number of rows retrieved or affected by the statement
shared_blks_hit bigint - Total number of shared block cache hits by the statement
shared_blks_read bigint - Total number of shared blocks read by the statement
shared_blks_dirtied bigint - Total number of shared blocks dirtied by the statement
shared_blks_written bigint - Total number of shared blocks written by the statement
local_blks_hit bigint - Total number of local block cache hits by the statement
local_blks_read bigint - Total number of local blocks read by the statement
local_blks_dirtied bigint - Total number of local blocks dirtied by the statement
local_blks_written bigint - Total number of local blocks written by the statement
temp_blks_read bigint - Total number of temp blocks read by the statement
temp_blks_written bigint - Total number of temp blocks written by the statement
blk_read_time double precision - Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_time double precision - Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

最耗IO SQL

单次调用最耗IO SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;    

总最耗IO SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;    

最耗时 SQL

单次调用最耗时 SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;    

总最耗时 SQL TOP 5(最需要关注的是这个)

select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;    

响应时间抖动最严重 SQL

select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;    

最耗共享内存 SQL

select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;    

最耗临时空间 SQL

select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;    

六、重置统计信息

pg_stat_statements是累积的统计,如果要查看某个时间段的统计,需要打快照,建议参考

《PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)》

用户也可以定期清理历史的统计信息,通过调用如下SQL

select pg_stat_statements_reset();    

慢SQL到底慢在哪里?

如果要分析慢SQL到底慢在哪里,使用数据库命令explain (analyze,verbose,timing,costs,buffers,timing) SQL;就可以,再加上一些开关,可以看到更加详细的信息。

开关, 当前会话生效,打印更加详细的信息

set client_min_messages=debug5;
set log_checkpoints = on;
set log_error_verbosity = verbose ;
set log_lock_waits = on;                  
set log_replication_commands = off;
set log_temp_files = 0;
set track_activities = on;
set track_counts = on;
set track_io_timing = on;
set track_functions = 'all';
set trace_sort=on;
set log_statement_stats = off;
set log_parser_stats = on;
set log_planner_stats = on;
set log_executor_stats = on;
set log_autovacuum_min_duration=0;
set deadlock_timeout = '1s';
set debug_print_parse = off;
set debug_print_rewritten = off;
set debug_print_plan = off;
set debug_pretty_print = on;
 
如
explain (analyze,verbose,timing,costs,buffers) select count(*),relkind from pg_class group by relkind order by count(*) desc limit 1;

七、慢SQL、TOP SQL优化示例

1、查看真实的执行计划

begin;  
set local lock_timeout='1s';  
set local statement_timeout=0;  
explain (analyze,verbose,timing,costs,buffers,timing) SQL;  -- SQL代替为要分析的SQL  
rollback;  

2、从explain结果中,找到最耗时的NODE

postgres=#  explain (analyze,verbose,timing,costs,buffers) select count(*),c34 from test where c33<3 group by c34;   
                      QUERY PLAN             
---------------------------------------------------------------------------------------------------------------  
 HashAggregate  (cost=18042933.67..18042933.78 rows=11 width=16) (actual time=79898.384..79898.386 rows=11 loops=1)  
   Output: count(*), c34  
   Group Key: test.c34  
   Buffers: shared hit=3296 read=16663371  
   ->  Seq Scan on public.test  (cost=0.00..17916667.00 rows=25253334 width=8) (actual time=0.065..74406.748 rows=24997473 loops=1)  大量耗费  
         Output: id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64  
         Filter: (test.c33 < 3)  
         Rows Removed by Filter: 75002527  过滤了大量的行,但是还有很多行需要被查询  
         Buffers: shared hit=3296 read=16663371  
 Planning Time: 0.096 ms  
 Execution Time: 79898.435 ms  
(11 rows)  

3、针对NODE进行优化

3.1、以上例子,实际上就是扫描花费了很多时间,并且扫描后过滤的结果占比比较低,可以使用索引解决。

postgres=# create index idx on test (c33,c34);  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*),c34 from test where c33<3 group by c34;   
                                                                    QUERY PLAN                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------  
 HashAggregate  (cost=685855.26..685855.37 rows=11 width=16) (actual time=8056.793..8056.795 rows=11 loops=1)  
   Output: count(*), c34  
   Group Key: test.c34  
   Buffers: shared hit=112642  
   ->  Index Only Scan using idx on public.test  (cost=0.57..557588.60 rows=25653333 width=8) (actual time=0.031..3691.071 rows=24997473 loops=1)  
         Output: c33, c34  
         Index Cond: (test.c33 < 3)  
         Heap Fetches: 0  
         Buffers: shared hit=112642   扫描了多少 index 数据块   
 Planning Time: 0.166 ms  
 Execution Time: 8056.842 ms  
(11 rows)  

3.2、加索引后的优化,聚集。

如果未使用index only scan,那么需要回表,回表可能导致扫描更多的数据块。  
  
当数据分散存储是,使用聚集可以优化,本例使用了idx only scan,不需要优化  
  
cluster test using idx;  

3.3、聚集后的优化,并行。

postgres=# set max_parallel_workers_per_gather =32;  
SET  
postgres=#  explain (analyze,verbose,timing,costs,buffers) select count(*),c34 from test where c33<3 group by c34;   
                                                                                QUERY PLAN                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Finalize GroupAggregate  (cost=318089.48..318125.45 rows=11 width=16) (actual time=999.918..1000.053 rows=11 loops=1)  
   Output: count(*), c34  
   Group Key: test.c34  
   Buffers: shared hit=9623  
   ->  Gather Merge  (cost=318089.48..318124.02 rows=264 width=16) (actual time=999.768..999.973 rows=275 loops=1)  
         Output: c34, (PARTIAL count(*))  
         Workers Planned: 24  
         Workers Launched: 24  
         Buffers: shared hit=9623  
         ->  Sort  (cost=317088.90..317088.93 rows=11 width=16) (actual time=926.196..926.198 rows=11 loops=25)  
               Output: c34, (PARTIAL count(*))  
               Sort Key: test.c34  
               Sort Method: quicksort  Memory: 25kB  
               Worker 0:  Sort Method: quicksort  Memory: 25kB  
               Worker 1:  Sort Method: quicksort  Memory: 25kB  
               Worker 2:  Sort Method: quicksort  Memory: 25kB  
               Worker 3:  Sort Method: quicksort  Memory: 25kB  
               Worker 4:  Sort Method: quicksort  Memory: 25kB  
               Worker 5:  Sort Method: quicksort  Memory: 25kB  
               Worker 6:  Sort Method: quicksort  Memory: 25kB  
               Worker 7:  Sort Method: quicksort  Memory: 25kB  
               Worker 8:  Sort Method: quicksort  Memory: 25kB  
               Worker 9:  Sort Method: quicksort  Memory: 25kB  
               Worker 10:  Sort Method: quicksort  Memory: 25kB  
               Worker 11:  Sort Method: quicksort  Memory: 25kB  
               Worker 12:  Sort Method: quicksort  Memory: 25kB  
               Worker 13:  Sort Method: quicksort  Memory: 25kB  
               Worker 14:  Sort Method: quicksort  Memory: 25kB  
               Worker 15:  Sort Method: quicksort  Memory: 25kB  
               Worker 16:  Sort Method: quicksort  Memory: 25kB  
               Worker 17:  Sort Method: quicksort  Memory: 25kB  
               Worker 18:  Sort Method: quicksort  Memory: 25kB  
               Worker 19:  Sort Method: quicksort  Memory: 25kB  
               Worker 20:  Sort Method: quicksort  Memory: 25kB  
               Worker 21:  Sort Method: quicksort  Memory: 25kB  
               Worker 22:  Sort Method: quicksort  Memory: 25kB  
               Worker 23:  Sort Method: quicksort  Memory: 25kB  
               Buffers: shared hit=207494  
               Worker 0: actual time=923.125..923.126 rows=11 loops=1  
                 Buffers: shared hit=8571  
               Worker 1: actual time=922.567..922.568 rows=11 loops=1  
                 Buffers: shared hit=7575  
               Worker 2: actual time=923.209..923.210 rows=11 loops=1  
                 Buffers: shared hit=8448  
               Worker 3: actual time=922.613..922.615 rows=11 loops=1  
                 Buffers: shared hit=7958  
               Worker 4: actual time=923.265..923.266 rows=11 loops=1  
                 Buffers: shared hit=8706  
               Worker 5: actual time=923.329..923.330 rows=11 loops=1  
                 Buffers: shared hit=8800  
               Worker 6: actual time=923.298..923.299 rows=11 loops=1  
                 Buffers: shared hit=8637  
               Worker 7: actual time=922.778..922.780 rows=11 loops=1  
                 Buffers: shared hit=7168  
               Worker 8: actual time=923.348..923.349 rows=11 loops=1  
                 Buffers: shared hit=8804  
               Worker 9: actual time=923.303..923.304 rows=11 loops=1  
                 Buffers: shared hit=8576  
               Worker 10: actual time=923.270..923.272 rows=11 loops=1  
                 Buffers: shared hit=8848  
               Worker 11: actual time=923.308..923.309 rows=11 loops=1  
                 Buffers: shared hit=8500  
               Worker 12: actual time=923.415..923.417 rows=11 loops=1  
                 Buffers: shared hit=8606  
               Worker 13: actual time=922.827..922.828 rows=11 loops=1  
                 Buffers: shared hit=7402  
               Worker 14: actual time=923.307..923.309 rows=11 loops=1  
                 Buffers: shared hit=8415  
               Worker 15: actual time=922.994..922.996 rows=11 loops=1  
                 Buffers: shared hit=7467  
               Worker 16: actual time=923.456..923.457 rows=11 loops=1  
                 Buffers: shared hit=8460  
               Worker 17: actual time=923.364..923.366 rows=11 loops=1  
                 Buffers: shared hit=8647  
               Worker 18: actual time=923.287..923.289 rows=11 loops=1  
                 Buffers: shared hit=8549  
               Worker 19: actual time=922.968..922.969 rows=11 loops=1  
                 Buffers: shared hit=7211  
               Worker 20: actual time=923.361..923.363 rows=11 loops=1  
                 Buffers: shared hit=8650  
               Worker 21: actual time=923.178..923.179 rows=11 loops=1  
                 Buffers: shared hit=7691  
               Worker 22: actual time=923.129..923.131 rows=11 loops=1  
                 Buffers: shared hit=7609  
               Worker 23: actual time=923.427..923.428 rows=11 loops=1  
                 Buffers: shared hit=8573  
               ->  Partial HashAggregate  (cost=317088.60..317088.71 rows=11 width=16) (actual time=926.136..926.138 rows=11 loops=25)  
                     Output: c34, PARTIAL count(*)  
                     Group Key: test.c34  
                     Buffers: shared hit=207326  
                     Worker 0: actual time=923.055..923.058 rows=11 loops=1  
                       Buffers: shared hit=8564  
                     Worker 1: actual time=922.506..922.509 rows=11 loops=1  
                       Buffers: shared hit=7568  
                     Worker 2: actual time=923.159..923.162 rows=11 loops=1  
                       Buffers: shared hit=8441  
                     Worker 3: actual time=922.551..922.553 rows=11 loops=1  
                       Buffers: shared hit=7951  
                     Worker 4: actual time=923.220..923.223 rows=11 loops=1  
                       Buffers: shared hit=8699  
                     Worker 5: actual time=923.285..923.288 rows=11 loops=1  
                       Buffers: shared hit=8793  
                     Worker 6: actual time=923.254..923.257 rows=11 loops=1  
                       Buffers: shared hit=8630  
                     Worker 7: actual time=922.695..922.698 rows=11 loops=1  
                       Buffers: shared hit=7161  
                     Worker 8: actual time=923.290..923.293 rows=11 loops=1  
                       Buffers: shared hit=8797  
                     Worker 9: actual time=923.254..923.256 rows=11 loops=1  
                       Buffers: shared hit=8569  
                     Worker 10: actual time=923.223..923.226 rows=11 loops=1  
                       Buffers: shared hit=8841  
                     Worker 11: actual time=923.224..923.226 rows=11 loops=1  
                       Buffers: shared hit=8493  
                     Worker 12: actual time=923.373..923.376 rows=11 loops=1  
                       Buffers: shared hit=8599  
                     Worker 13: actual time=922.766..922.769 rows=11 loops=1  
                       Buffers: shared hit=7395  
                     Worker 14: actual time=923.232..923.235 rows=11 loops=1  
                       Buffers: shared hit=8408  
                     Worker 15: actual time=922.935..922.938 rows=11 loops=1  
                       Buffers: shared hit=7460  
                     Worker 16: actual time=923.406..923.409 rows=11 loops=1  
                       Buffers: shared hit=8453  
                     Worker 17: actual time=923.317..923.319 rows=11 loops=1  
                       Buffers: shared hit=8640  
                     Worker 18: actual time=923.204..923.206 rows=11 loops=1  
                       Buffers: shared hit=8542  
                     Worker 19: actual time=922.893..922.895 rows=11 loops=1  
                       Buffers: shared hit=7204  
                     Worker 20: actual time=923.283..923.286 rows=11 loops=1  
                       Buffers: shared hit=8643  
                     Worker 21: actual time=923.089..923.092 rows=11 loops=1  
                       Buffers: shared hit=7684  
                     Worker 22: actual time=923.049..923.051 rows=11 loops=1  
                       Buffers: shared hit=7602  
                     Worker 23: actual time=923.379..923.381 rows=11 loops=1  
                       Buffers: shared hit=8566  
                     ->  Parallel Index Only Scan using idx on public.test  (cost=0.57..311744.15 rows=1068889 width=8) (actual time=0.294..726.243 rows=999899 loops=25)  
                           Output: c33, c34  
                           Index Cond: (test.c33 < 3)  
                           Heap Fetches: 0  
                           Buffers: shared hit=207326  
                           Worker 0: actual time=0.249..739.989 rows=1028079 loops=1  
                             Buffers: shared hit=8564  
                           Worker 1: actual time=0.500..698.527 rows=912456 loops=1  
                             Buffers: shared hit=7568  
                           Worker 2: actual time=0.449..733.146 rows=1010592 loops=1  
                             Buffers: shared hit=8441  
                           Worker 3: actual time=0.554..712.277 rows=953955 loops=1  
                             Buffers: shared hit=7951  
                           Worker 4: actual time=0.088..736.872 rows=1047915 loops=1  
                             Buffers: shared hit=8699  
                           Worker 5: actual time=0.172..734.815 rows=1056267 loops=1  
                             Buffers: shared hit=8793  
                           Worker 6: actual time=0.052..737.294 rows=1040346 loops=1  
                             Buffers: shared hit=8630  
                           Worker 7: actual time=0.086..696.398 rows=862866 loops=1  
                             Buffers: shared hit=7161  
                           Worker 8: actual time=0.051..735.082 rows=1053918 loops=1  
                             Buffers: shared hit=8797  
                           Worker 9: actual time=0.336..740.511 rows=1031994 loops=1  
                             Buffers: shared hit=8569  
                           Worker 10: actual time=0.496..735.275 rows=1063836 loops=1  
                             Buffers: shared hit=8841  
                           Worker 11: actual time=0.238..728.468 rows=1016595 loops=1  
                             Buffers: shared hit=8493  
                           Worker 12: actual time=0.049..737.655 rows=1035648 loops=1  
                             Buffers: shared hit=8599  
                           Worker 13: actual time=0.302..699.745 rows=888966 loops=1  
                             Buffers: shared hit=7395  
                           Worker 14: actual time=0.200..729.542 rows=1011114 loops=1  
                             Buffers: shared hit=8408  
                           Worker 15: actual time=0.296..695.864 rows=898623 loops=1  
                             Buffers: shared hit=7460  
                           Worker 16: actual time=0.070..734.046 rows=1015812 loops=1  
                             Buffers: shared hit=8453  
                           Worker 17: actual time=0.053..737.755 rows=1040868 loops=1  
                             Buffers: shared hit=8640  
                           Worker 18: actual time=0.081..737.488 rows=1030689 loops=1  
                             Buffers: shared hit=8542  
                           Worker 19: actual time=0.092..694.639 rows=870957 loops=1  
                             Buffers: shared hit=7204  
                           Worker 20: actual time=0.523..737.503 rows=1040607 loops=1  
                             Buffers: shared hit=8643  
                           Worker 21: actual time=1.978..709.165 rows=925182 loops=1  
                             Buffers: shared hit=7684  
                           Worker 22: actual time=0.294..699.942 rows=907497 loops=1  
                             Buffers: shared hit=7602  
                           Worker 23: actual time=0.120..739.781 rows=1030689 loops=1  
                             Buffers: shared hit=8566  
 Planning Time: 0.311 ms  
 Execution Time: 1007.876 ms  
(193 rows)  

3.4、并行后的优化,列存储。

当前未有内置列存,可以使用VOPS插件,或者CSTORE插件  

3.5、列存后的优化,动态编译、向量计算。

set jit=on  

3.6、向量计算后的优化,。。。

3.7、参数优化

最常见的是COST成本因子设置不当,导致没有正确的使用索引。

《PostgreSQL 10 on ECS 实施 流复制备库镜像+自动快照备份+自动备份验证+自动清理备份与归档 - 珍藏级》

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户) - 珍藏级》

《PostgreSQL on Linux 最佳部署手册 - 珍藏级》

《EDB PPAS (PostgreSQL plus advanced server) 10 参数模板 - 珍藏级》

《PostgreSQL 10 参数模板 - 珍藏级》

《优化器成本因子校对 - PostgreSQL explain cost constants alignment to timestamp》

《优化器成本因子校对(disk,ssd,memory IO开销精算) - PostgreSQL real seq_page_cost & random_page_cost in disks,ssd,memory》

3.8、收集统计信息

通常数据库会自动的收集统计信息,但是如果你不小心关了AUTOVACUUM或者关闭了表级的AUTOVACUUM,那么可能因为统计信息不准确导致执行计划不准确。

analyze tbl;

3.9、垃圾回收

通常数据库会自动垃圾回收,但是如果你不小心关了AUTOVACUUM或者关闭了表级的AUTOVACUUM,或者你有长事务一直存在,可能导致垃圾无法及时回收,导致性能问题。

例如在大量的更新数据后,未垃圾回收,导致索引扫描时可能扫描大量的垃圾版本。

《PostgreSQL 垃圾版本引入的索引扫描性能下降诊断》

vacuum analyze tbl;

八、常见优化点汇总

1、JOIN方法:nestloop, mergejoin, hashjoin. work_mem的SIZE影响hashjoin的性能。 你必须搞清楚这几个JOIN的原理才能了解应该使用什么方法,当然数据库会帮你做出选择,但如果成本因子不准确可能导致不准确。

https://www.postgresql.org/docs/10/static/planner-optimizer.html

2、分组聚合、HASH聚合的选择。work_mem的SIZE。

https://www.postgresql.org/docs/10/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE

3、扫描方法:bitmap scan, index scan, index only scan ,seq scan, index skip scan等的概念,以及选择。

《PostgreSQL 数据离散性 与 索引扫描性能(btree & bitmap index scan)》

《PostgreSQL Oracle 兼容性之 - INDEX SKIP SCAN (递归查询变态优化) 非驱动列索引扫描优化》

《PostgreSQL 空间切割(st_split, ST_Subdivide)功能扩展 - 空间对象网格化 (多边形GiST优化)》

《PostgreSQL 空间st_contains,st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》

有些时候SQL不当也可能引起扫描放大

《PostgreSQL 11 preview - 分页内核层优化 - 索引扫描offset优化(使用vm文件skip heap scan)》

《PostgreSQL 范围过滤 + 其他字段排序OFFSET LIMIT(多字段区间过滤)的优化与加速》

《PostgreSQL Oracle 兼容性之 - TZ_OFFSET》

《PostgreSQL 索引扫描offset内核优化 - case》

《PostgreSQL 数据访问 offset 的质变 case》

《论count与offset使用不当的罪名 和 分页的优化》

《PostgreSQL offset 原理,及使用注意事项》

《分页优化 - order by limit x offset y performance tuning》

3.1、索引的选择

《PostgreSQL 9种索引的原理和应用场景》

4、递归:改写SQL,完成一些极限优化。

《PostgrSQL 递归SQL的几个应用 - 极客与正常人的思维》

5、exists, in, = any等:改写SQL

《PostgreSQL in 与 = any 的SQL语法异同与性能优化》

6、膨胀、统计信息不准确,导致SQL不正确。

《[未完待续] PostgreSQL 扩展存储引擎介绍 - zheap - 1》

《PostgreSQL snapshot too old补丁, 防止数据库膨胀》

《乱序写入导致的索引膨胀(B-tree, GIN, GiST皆如此)》

《Greenplum 列存表(AO表)的膨胀、垃圾检查与空间收缩(含修改分布键)》

《如何检测、清理Greenplum膨胀、垃圾(含修改分布键) - 阿里云HybridDB for PG最佳实践》

《PostgreSQL 收缩膨胀表或索引 - pg_squeeze or pg_repack》

《PostgreSQL 垃圾回收原理以及如何预防膨胀 - How to prevent object bloat in PostgreSQL》

《PostgreSQL垃圾回收代码分析 - why postgresql cann’t reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》

《Greenplum 统计信息收集参数 - 暨统计信息不准引入的broadcast motion一例》

《PostgreSQL merge join 评估成本时可能会查询索引 - 硬解析务必引起注意 - 批量删除数据后, 未释放empty索引页导致mergejoin执行计划变慢 case》

6.1、COST成本因子与硬件不匹配,导致COST计算不准确。

《优化器成本因子校对 - PostgreSQL explain cost constants alignment to timestamp》

《优化器成本因子校对(disk,ssd,memory IO开销精算) - PostgreSQL real seq_page_cost & random_page_cost in disks,ssd,memory》

7、数据聚集的优化,例如数据分布比较分散,扫描数据时会导致IO浪费。

《PostgreSQL 黑科技 - 空间聚集存储, 内窥GIN, GiST, SP-GiST索引》

《数据寻龙点穴(空间聚集分析) - 阿里云RDS PostgreSQL最佳实践》

《PostgreSQL 聚集存储 与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》

《索引扫描优化之 - GIN数据重组优化(按元素聚合) 想象在玩多阶魔方》

8、确实需要大量计算时,可以:向量计算、列存储、并行计算、GPU加速,这些方法属于激发硬件最大潜能的方法。

《PostgreSQL VOPS 向量计算 + DBLINK异步并行 - 单实例 10亿 聚合计算跑进2秒》

《PostgreSQL GPU 加速(HeteroDB pg_strom) (GPU计算, GPU-DIO-Nvme SSD, 列存, GPU内存缓存)》

《PostgreSQL 11 preview - 并行计算 增强 汇总》

9、防死机优化,比如雪崩等场景,这种情况下,则应该让任务排队,尽量的减少同时执行的任务,避免出现大家一起死的情况。也可以使用资源队列、连接池、内置的线程池等。

《PostgreSQL 设置单条SQL的执行超时 - 防雪崩》

《PostgresPro buildin pool(内置连接池)版本 原理与测试》

《阿里云 RDS PostgreSQL 高并发特性 vs 社区版本 (1.6万并发: 3倍吞吐,240倍响应速度)》

《PostgreSQL数据库开源连接池pgbouncer的使用》

10、防锁死或死锁的优化,这种情况,可能出现在一些比较恶心的业务长时间持锁,不释放。另一方面,在等待中的事务,可能想持有大锁(例如DDL语句),从而导致锁放大,发生冲突的概率会大增。从而导致锁等待引起的雪崩。

lock_timeout  

《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》

《PostgreSQL 实时健康监控 大屏 - 高频指标 - 珍藏级》

11、资源隔离,当存在混合负责的请,或者单个实例有多个业务线在使用,并且有分级机制时。 你肯定不想让不同的业务之间相互干扰,或者因为低级业务的大量资源占用,影响高级业务。

《Linux cgroup资源隔离各个击破之 - io隔离》

《Linux cgroup - cpu与cpuset子系统讲解》

《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) HTAP功能之资源隔离管理 - CPU与刷脏资源组管理》

12、操作系统、文件系统、存储、网络链路等优化。

《PostgreSQL 源码性能诊断(perf profiling)指南 - 珍藏级》

《PostgreSQL 代码性能诊断之 - OProfile & Systemtap》

总之,1、优化的目标就是让数据库少走弯路,少计算,少扫描,越直接越好。 2、激发潜能的目的则是让一个QUERY能使用越多的资源越好,以最短的时间完成任务。

参考

https://www.postgresql.org/docs/9.6/static/pgstatstatements.html

Flag Counter

digoal’s 大量PostgreSQL文章入口