PostgreSQL成本因子校准（二）

最新推荐文章于 2022-10-09 14:01:53 发布

weixin_33913377

最新推荐文章于 2022-10-09 14:01:53 发布

阅读量380

点赞数

文章标签：数据库操作系统

原文链接：http://www.cnblogs.com/pg-libs/p/6305622.html

版权

校准参数：

　　random_page_cost(stap), cpu_index_cost（公式）, cpu_operator_cost（公式）

步骤：

1.为了方便计算，其它参数常量设为1：

　　set random_page_cost=1;

　　set cpu_tuple_cost=1;

　　set cpu_index_tuple_cost=1;

　　set cpu_operator_cost=1;

　　set enable_seqscan=off; set enable_bitmapscan=off;

　　explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;

                                                                          QUERY PLAN                              
                                            
------------------------------------------------------------------------------------------------------------------
--------------------------------------------
 Index Scan using tbl_cost_align_id_idx on public.tbl_cost_align  (cost=174.00..20870.00 rows=5205 width=45) (actu
al time=0.076..32827.810 rows=4908 loops=1)
   Output: id, info, crt_time
   Index Cond: (tbl_cost_align.id > 1998999963)
   Buffers: shared hit=113 read=4811
 Planning time: 9.621 ms
 Execution time: 32835.450 ms
(6 rows)

Explain输出

分析：执行计划表明这是个索引扫描, 至于扫了多少个数据块是未知的, 索引的tuples也是未知的, 已知的是cost和rows

20870.00 = blocks*random_page_cost + cpu_tuple_cost*5205+ cpu_index_tuple_cost*? + cpu_operator_cost(1)*?

2.更改cpu_operator_cost

　　set cpu_operator_cost=2;

　　set enable_seqscan=off; set enable_bitmapscan=off; explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;

                                                                        QUERY PLAN                                
                                         
------------------------------------------------------------------------------------------------------------------
-----------------------------------------
 Index Scan using tbl_cost_align_id_idx on public.tbl_cost_align  (cost=348.00..26249.00 rows=5205 width=45) (actu
al time=0.032..13.791 rows=4908 loops=1)
   Output: id, info, crt_time
   Index Cond: (tbl_cost_align.id > 1998999963)
   Buffers: shared hit=4924
 Planning time: 0.165 ms
 Execution time: 20.082 ms
(6 rows)

Explain输出

分析：26249.00 = blocks*random_page_cost + cpu_tuple_cost*5205+ cpu_index_tuple_cost*? + cpu_operator_cost(2)*?，得到本例通过索引扫描的条数：26249.00-20870.00=5379

等式变为20870.00 = blocks*random_page_cost + cpu_tuple_cost*5205+ cpu_index_tuple_cost*5205 + cpu_operator_cost(1)*5379

3.同样的方法求blocks

　　set random_page_cost = 2;

set enable_seqscan=off; set enable_bitmapscan=off; explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;

                                                                        QUERY PLAN                                
                                         
------------------------------------------------------------------------------------------------------------------
-----------------------------------------
 Index Scan using tbl_cost_align_id_idx on public.tbl_cost_align  (cost=348.00..31330.00 rows=5205 width=45) (actu
al time=0.032..13.154 rows=4908 loops=1)
   Output: id, info, crt_time
   Index Cond: (tbl_cost_align.id > 1998999963)
   Buffers: shared hit=4924
 Planning time: 0.165 ms
 Execution time: 19.309 ms
(6 rows)

Explain

blocks=31330.00-26249.00=5081.00

更新公式：20870.00 = 5081*random_page_cost + cpu_tuple_cost*5205+ cpu_index_tuple_cost*5205 + cpu_operator_cost*5379

4.通过stap统计random_page_cost

　　CHECKPOINT关闭数据库；

　　将操作系统的缓存刷入硬盘 sync; echo 3 > /proc/sys/vm/drop_caches

　　以非0号cpu亲和启动数据库 taskset -c 1 /usr/local/pgsql/bin/postgres >/dev/null 2>&1

　　连接数据库，查看pid：select pg_backend_pid();

　　启动监控代码：同上

　　执行：set enable_seqscan=off; set enable_bitmapscan=off; explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;

　　查看stap及explain输出：

13094**4854**8752355
query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where
id>1998999963;pid:13094
     value |-------------------------------------------------- count
      2048 |                                                      0
      4096 |                                                      0
      8192 |                                                      6
     16384 |                                                     27
     32768 |                                                      7
     65536 |                                                      3
    131072 |                                                      0
    262144 |                                                     21
    524288 |                                                      2
   1048576 |                                                      9
   2097152 |@@@@@@@@@                                           486
   4194304 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@                1782
   8388608 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  2495
  16777216 |                                                      8
  33554432 |                                                      4
  67108864 |                                                      2
 134217728 |                                                      1
 268435456 |                                                      1
 536870912 |                                                      0
1073741824 |                                                      0

stap输出

                                                                         QUERY PLAN                               
                                          
------------------------------------------------------------------------------------------------------------------
------------------------------------------
 Index Scan using tbl_cost_align_id_idx on public.tbl_cost_align  (cost=0.43..20415.52 rows=5205 width=45) (actual
 time=6.920..42273.537 rows=4908 loops=1)
   Output: id, info, crt_time
   Index Cond: (tbl_cost_align.id > 1998999963)
   Buffers: shared hit=110 read=4814
 Planning time: 148.140 ms
 Execution time: 42282.361 ms
(6 rows)