起源自天文學的PostgreSQL 優化器成本因子校對

標簽

PostgreSQL , 優化器 , 成本因子 , 校對

背景

最近在寫一個PostgreSQL數據庫培訓PPT, 其中有explain的講解需求, 剛剛接觸PostgreSQL的童鞋對PostgreSQL的explain輸出中cost的部分可能會一頭霧水, 看不懂cost的值和SQL實際運行時間有什麼聯係.

為了讓大家能更加深刻的了解explain, 我打算對explain的cost常量做一次校準, 這個校準的靈感來自天文望遠鏡赤道儀的校準. PostgreSQL cost常量在校準後, explain 的輸出cost就會非常接近真實執行的時間.

接下來我們就來看一看如何校準PostgreSQL COST常量.

https://www.postgresql.org/docs/9.3/static/runtime-config-query.html

在赤道儀的校準中, 要用到北極星以及至少一顆已知赤經赤緯的亮星.

同樣, 在PostgreSQL COST的校準中, 要用到的是已知算法以及真實的數據. 利用真實的數據和公式, 求出未知數, 達到校準的目的.

已知的數據可以來自硬件廠商或者自行測試得到, 已知的cost值算法則可以參考PostgreSQL手冊或源代碼.

src/backend/optimizer/path/costsize.c

PostgreSQL的cost常量如下 :

seq_page_cost  
-- 連續塊掃描操作的單個塊的cost. 例如全表掃描  
  
random_page_cost  
-- 隨機塊掃描操作的單個塊的cost. 例如索引掃描  
  
cpu_tuple_cost  
-- 處理每條記錄的CPU開銷  
  
cpu_index_tuple_cost  
-- 掃描每個索引條目帶來的CPU開銷  
  
cpu_operator_cost  
-- 操作符或函數帶來的CPU開銷.(需要注意函數以及操作符對應的函數的三態, 執行計劃會根據三態做優化, 關係到多條記錄時三態對應的調用次數是需要關心的)

接下來舉例說明如何校對這幾個常量.

1. 推算seq_page_cost 以及 cpu_tuple_cost

創建測試表

digoal=# create table tbl_cost_align (id int, info text, crt_time timestamp);  
CREATE TABLE

插入測試數據, 這裏插入的ID為隨機數, 這樣的話可以使得我們後麵要做的離散IO請求測試更準確一些.

digoal=# insert into tbl_cost_align select (random()*2000000000)::int, md5(random()::text), clock_timestamp() from generate_series(1,100000);  
INSERT 0 100000  
digoal=# insert into tbl_cost_align select (random()*2000000000)::int, md5(random()::text), clock_timestamp() from generate_series(1,10000000);  
INSERT 0 10000000

分析表, 獲得統計信息

digoal=# analyze tbl_cost_align;  
ANALYZE

可以查看到占用的數據塊個數

digoal=# select relpages from pg_class where relname='tbl_cost_align';  
 relpages   
----------  
    94393  
(1 row)

執行checkpoint後關閉數據庫, 為了得到一個純粹的物理磁盤的連續io請求的cost常量, 不能有shared buffer的幹擾.

digoal=# checkpoint;  
CHECKPOINT  
pg93@db-172-16-3-150-> pg_ctl stop -m fast  
waiting for server to shut down.... done  
server stopped

同時還不能有OS Cache的幹擾, 所以要清理操作係統cache.

[root@db-172-16-3-150 ssd1]# sync; echo 3 > /proc/sys/vm/drop_caches

前麵我們說了, 有些指標可以通過硬件廠商得到或者自行測試得到, 那麼這裏我們就要自己測試得到.

測試方法比較多, 本文將通過systemtap來得到每次IO請求的時間.

為了降低systemtap帶來的額外開銷, 請參考 :

《USE blockdev --setra 0 and systemtap test real BLOCKDEV iops》

《設置進程親和 - numactl 或 taskset - retrieve or set a process's CPU affinity (affect SYSTEMTAP TIME)》

指定親和1, 啟動數據庫 :

pg93@db-172-16-3-150-> taskset -c 1 /home/pg93/pgsql9.3.1/bin/postgres >/dev/null 2>&1

開啟psql

pg93@db-172-16-3-150-> psql  
psql (9.3.1)  
Type "help" for help.  
digoal=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
           5727  
(1 row)

指定親和7, 開啟stap, 收集postgres進程相關的io信息.

[root@db-172-16-3-150 ~]# taskset -c 7 stap -e '  
global a  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start") {  
  delete a  
  println("query__start ", user_string($arg1), "pid:", pid())  
}  
probe vfs.read.return {  
  t = gettimeofday_ns() - @entry(gettimeofday_ns())  
  # if (execname() == "postgres" && devname != "N/A")  
    a[pid()] <<< t  
}  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__done") {  
  if (@count(a[pid()]))   
    printdln("**", pid(), @count(a[pid()]), @avg(a[pid()]))  
  println("query__done ", user_string($arg1), "pid:", pid())  
  if (@count(a[pid()])) {  
    println(@hist_log(a[pid()]))  
    #println(@hist_linear(a[pid()],1024,4096,100))  
  }  
  delete a  
}' -x 5727

接下來在psql中執行explain analyze, 在explain的結果中可以得到一個值, 實際的執行時間(3260.695 -0.839).

並且可以得到原始的cost(195393.00), 這個原始的cost有助於驗證公式是否正確.

digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;  
                                                               QUERY PLAN                                                             
      
------------------------------------------------------------------------------------------------------------------------------------  
----  
 Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.839..3260.695 rows=10100000 loops  
=1)  
   Output: id, info, crt_time  
   Buffers: shared read=94393    
   -- 注意這個read指的是未命中shared buffer, 如果是命中的話會有hit=?  
  
 Total runtime: 4325.885 ms  
(4 rows)

執行完explain之後, 在stap輸出中得到了我們想要的平均IO響應時間信息(14329).

query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:5727  
5727**94417**14329  
query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:5727  
  value |-------------------------------------------------- count  
   1024 |                                                       0  
   2048 |                                                       0  
   4096 |                                                     153  
   8192 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  86293  
  16384 |@                                                   1864  
  32768 |                                                     116  
  65536 |@@@                                                 5918  
 131072 |                                                      59  
 262144 |                                                       7  
 524288 |                                                       3  
1048576 |                                                       2  
2097152 |                                                       2  
4194304 |                                                       0  
8388608 |                                                       0

收集到以上數據後, 首先驗證公式的正確性. 驗證公式前, 需要解讀explain的輸出. 以及現有的2個常量.

digoal=# show seq_page_cost;  
 seq_page_cost   
---------------  
 1  
(1 row)  
digoal=# show cpu_tuple_cost;  
 cpu_tuple_cost   
----------------  
 0.01  
(1 row)

公式正確 :

195393 = (shared read=)94393*1(seq_page_cost) + (rows=)10100000*0.01(cpu_tuple_cost)  
digoal=# select 94393+10100000*0.01;  
 ?column?    
-----------  
 195393.00  
(1 row)

那麼從stap中我們得到io的平均響應時間是14329納秒(0.014329毫秒). 真實的執行時間是(3260.695 -0.839).

套用到公式中.

3260.695 -0.839 = 94393*0.014329 + 10100000*x  
x = 0.00018884145574257426

接下來要做的是調整seq_page_cost和cpu_tuple_cost, 重新執行SQL.

digoal=# set seq_page_cost=0.014329;  
SET  
digoal=# set cpu_tuple_cost=0.00018884145574257426;  
SET

重新執行SQL後, 我們看到評估出來的COST是3259.86, 實際的執行時間是1599.507ms.

不一致是因為現在用到了shared buffer, 已經沒有直接讀硬盤或者OS CACHE了.

digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;  
                                                              QUERY PLAN                                                              
    
------------------------------------------------------------------------------------------------------------------------------------  
--  
 Seq Scan on postgres.tbl_cost_align  (cost=0.00..3259.86 rows=10100000 width=45) (actual time=0.011..1599.507 rows=10100000 loops=1  
)  
   Output: id, info, crt_time  
   Buffers: shared hit=94393  
 Total runtime: 2617.152 ms  
(4 rows)

可以重啟數據庫並清除CACHE來測試, 一定會得到滿意的答案.

pg93@db-172-16-3-150-> taskset -c 1 /home/pg93/pgsql9.3.1/bin/postgres >/dev/null 2>&1  
[root@db-172-16-3-150 ~]# sync; echo 3 > /proc/sys/vm/drop_caches  
digoal=# set seq_page_cost=0.014329;  
SET  
digoal=# set cpu_tuple_cost=0.00018884145574257426;  
SET  
digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;  
                                                              QUERY PLAN                                                              
    
------------------------------------------------------------------------------------------------------------------------------------  
--  
 Seq Scan on postgres.tbl_cost_align  (cost=0.00..3259.86 rows=10100000 width=45) (actual time=0.915..3318.443 rows=10100000 loops=1  
)  
   Output: id, info, crt_time  
   Buffers: shared read=94393  
 Total runtime: 4380.828 ms  
(4 rows)

現在是完全從硬盤讀取, 所以得出的cost就和實際執行時間相當接近了3259.86 VS (3318.443 - 0.915).

2. 推算random_page_cost 以及 cpu_index_tuple_cost , cpu_operator_cost

random_page_cost 本文還是通過stap跟蹤來獲得.

cpu_index_tuple_cost 和 cpu_operator_cost 兩個未知數不是很好推算, 基本上出現cpu_index_tuple_cost 的場景, 另一個cpu_operator_cost 也出現了, 所以2個未知數都是同時出現.

那麼我們隻能給他們來個比例. 或者能夠直接跟蹤到其中的一個未知數, 才能得出另一個未知數.

本文利用cpu_index_tuple_cost 和 cpu_operator_cost的默認占比來求得這兩個值.

首先我們還是要確定公式, 為了方便公式驗證, 把所有的常量都設置為1.

digoal=# set random_page_cost=1;  
SET  
digoal=# set cpu_tuple_cost=1;  
SET  
digoal=# set cpu_index_tuple_cost=1;  
SET  
digoal=# set cpu_operator_cost=1;  
SET  
digoal=# set enable_seqscan=off; set enable_bitmapscan=off; explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;  
SET  
SET  
                                                                         QUERY PLAN                                                   
                           
------------------------------------------------------------------------------------------------------------------------------------  
-------------------------  
 Index Scan using idx_tbl_cost_align_id on postgres.tbl_cost_align  (cost=174.00..20181.67 rows=5031 width=45) (actual time=0.029..1  
7.773 rows=5037 loops=1)  
   Output: id, info, crt_time  
   Index Cond: (tbl_cost_align.id > 1998999963)  
   Buffers: shared hit=5054  
 Total runtime: 18.477 ms  
(5 rows)

執行計劃表明這是個索引掃描, 至於掃了多少個數據塊是未知的, 索引的tuples也是未知的, 已知的是cost和rows.

20181.67 = blocks*random_page_cost + cpu_tuple_cost*5031 + cpu_index_tuple_cost*5031 + cpu_operator_cost*?

求這個問號, 可以通過更改cpu_operator_cost來得到.

digoal=# set cpu_operator_cost=2;  
SET  
digoal=# set enable_seqscan=off; set enable_bitmapscan=off; explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;  
SET  
SET  
                                                                         QUERY PLAN                                                   
                          
------------------------------------------------------------------------------------------------------------------------------------  
------------------------  
 Index Scan using idx_tbl_cost_align_id on postgres.tbl_cost_align  (cost=348.00..25386.67 rows=5031 width=45) (actual time=0.013..5  
.785 rows=5037 loops=1)  
   Output: id, info, crt_time  
   Index Cond: (tbl_cost_align.id > 1998999963)  
   Buffers: shared hit=5054  
 Total runtime: 6.336 ms  
(5 rows)

25386.67-20181.67 = 5205 得到本例通過索引掃描的條數. 等式就變成了  
20181.67 = blocks*random_page_cost + cpu_tuple_cost*5031 + cpu_index_tuple_cost*5031 + cpu_operator_cost*5205

接下來要求blocks, 也就是掃描的隨機頁數.

通過調整random_page_cost得到.

digoal=# set random_page_cost = 2;  
SET  
digoal=# set enable_seqscan=off; set enable_bitmapscan=off; explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;  
SET  
SET  
                                                                         QUERY PLAN                                                   
                          
------------------------------------------------------------------------------------------------------------------------------------  
------------------------  
 Index Scan using idx_tbl_cost_align_id on postgres.tbl_cost_align  (cost=348.00..30301.33 rows=5031 width=45) (actual time=0.013..5  
.778 rows=5037 loops=1)  
   Output: id, info, crt_time  
   Index Cond: (tbl_cost_align.id > 1998999963)  
   Buffers: shared hit=5054  
 Total runtime: 6.331 ms  
(5 rows)

30301.33-25386.67 = 4914.66

得到blocks = 4914.66.

更新等式 :

20181.67 = 4914.66*random_page_cost + cpu_tuple_cost*5031 + cpu_index_tuple_cost*5031 + cpu_operator_cost*5205

接下來要做的是通過stap統計出random_page_cost.

pg93@db-172-16-3-150-> taskset -c 1 /home/pg93/pgsql9.3.1/bin/postgres >/dev/null 2>&1  
[root@db-172-16-3-150 ~]# sync; echo 3 > /proc/sys/vm/drop_caches  
digoal=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          10009  
(1 row)  
  
[root@db-172-16-3-150 ~]# taskset -c 2 stap -e '  
global a  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start") {  
  delete a  
  println("query__start ", user_string($arg1), "pid:", pid())  
}  
probe vfs.read.return {  
  t = gettimeofday_ns() - @entry(gettimeofday_ns())  
  # if (execname() == "postgres" && devname != "N/A")  
    a[pid()] <<< t  
}  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__done") {  
  if (@count(a[pid()]))   
    printdln("**", pid(), @count(a[pid()]), @avg(a[pid()]))  
  println("query__done ", user_string($arg1), "pid:", pid())  
  if (@count(a[pid()])) {  
    println(@hist_log(a[pid()]))  
    #println(@hist_linear(a[pid()],1024,4096,100))  
  }  
  delete a  
}' -x 10009  
  
digoal=# set enable_seqscan=off; set enable_bitmapscan=off; explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;  
SET  
SET  
                                                                         QUERY PLAN                                                   
                          
------------------------------------------------------------------------------------------------------------------------------------  
------------------------  
 Index Scan using idx_tbl_cost_align_id on postgres.tbl_cost_align  (cost=0.43..5003.15 rows=5031 width=45) (actual time=0.609..1844  
.415 rows=5037 loops=1)  
   Output: id, info, crt_time  
   Index Cond: (tbl_cost_align.id > 1998999963)  
   Buffers: shared hit=152 read=4902  
 Total runtime: 1846.683 ms  
(5 rows)  
  
query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;pid:10009  
10009**4946**368362  
query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;pid:10009  
   value |-------------------------------------------------- count  
    2048 |                                                      0  
    4096 |                                                      0  
    8192 |                                                     33  
   16384 |                                                      2  
   32768 |                                                      6  
   65536 |                                                      4  
  131072 |@@@@@@@@@@@@@@@@@@@                                1193  
  262144 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  2971  
  524288 |@@@@@@@@@@@@                                        729  
 1048576 |                                                      2  
 2097152 |                                                      5  
 4194304 |                                                      0  
 8388608 |                                                      1  
16777216 |                                                      0  
33554432 |                                                      0

更新等式, 使用時間等式 :

等式1 :

1844.415 = 4914.66*0.368362 + 0.00018884145574257426*5031 + cpu_index_tuple_cost*5031 + cpu_operator_cost*5205

cpu_tuple_cost用例子1中計算得到的0.00018884145574257426

cpu_index_tuple_cost和cpu_operator_cost的比例用係統默認的2 : 1.

等式2 :

cpu_index_tuple_cost/cpu_operator_cost = 2

最終得到 :

cpu_index_tuple_cost = 0.00433497085216479990  
cpu_operator_cost = 0.00216748542608239995

結合例子1 得到的兩個常量, 所有的5個常量值就調整好了.

digoal=# set cpu_tuple_cost=0.00018884145574257426;  
SET  
digoal=# set cpu_index_tuple_cost = 0.00433497085216479990;  
SET  
digoal=# set cpu_operator_cost = 0.00216748542608239995;  
SET  
digoal=# set seq_page_cost=0.014329;  
SET  
digoal=# set random_page_cost = 0.368362;  
SET  
digoal=# set enable_seqscan=off; set enable_bitmapscan=off; explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;  
SET  
SET  
                                                                         QUERY PLAN                                                   
                          
------------------------------------------------------------------------------------------------------------------------------------  
------------------------  
 Index Scan using idx_tbl_cost_align_id on postgres.tbl_cost_align  (cost=0.38..1844.42 rows=5031 width=45) (actual time=0.553..1346  
.468 rows=5037 loops=1)  
   Output: id, info, crt_time  
   Index Cond: (tbl_cost_align.id > 1998999963)  
   Buffers: shared hit=152 read=4902  
 Total runtime: 1348.428 ms  
(5 rows)

以後使用調整後的cost常量, 就可以估算出SQL的真實執行時間, 真實執行時間會因為shared buffer hit以及os cache比explain得到的值略短, 但是已經非常接近了.

digoal=# set enable_seqscan=off; set enable_bitmapscan=off; explain select * from tbl_cost_align where id>1998999963;  
SET  
SET  
                                            QUERY PLAN                                               
---------------------------------------------------------------------------------------------------  
 Index Scan using idx_tbl_cost_align_id on tbl_cost_align  (cost=0.38..1844.42 rows=5031 width=45)  
   Index Cond: (id > 1998999963)  
(2 rows)