起源自天文學的PostgreSQL優化器成本因子校對
標籤
PostgreSQL , 優化器 , 成本因子 , 校對
背景
最近在寫一個PostgreSQL資料庫培訓PPT, 其中有explain的講解需求, 剛剛接觸PostgreSQL的童鞋對PostgreSQL的explain輸出中cost的部分可能會一頭霧水, 看不懂cost的值和SQL實際執行時間有什麼聯絡.
為了讓大家能更加深刻的瞭解explain, 我打算對explain的cost常量做一次校準, 這個校準的靈感來自天文望遠鏡赤道儀的校準. PostgreSQL cost常量在校準後, explain 的輸出cost就會非常接近真實執行的時間.
接下來我們就來看一看如何校準PostgreSQL COST常量.
http://www.postgresql.org/docs/9.3/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS
在赤道儀的校準中, 要用到北極星以及至少一顆已知赤經赤緯的亮星.
同樣, 在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)
參考
1. http://www.postgresql.org/docs/9.3/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS
2. http://www.postgresql.org/docs/9.3/static/sql-createfunction.html
3. http://www.postgresql.org/docs/9.3/static/sql-explain.html
4. http://www.postgresql.org/docs/9.3/static/sql-altertable.html
5. http://www.postgresql.org/docs/9.3/static/using-explain.html
6. http://www.postgresql.org/docs/9.3/static/sql-alterdatabase.html
7. https://sourceware.org/systemtap/tapsets
8. http://www.postgresql.org/docs/9.3/static/dynamic-trace.html
9. src/backend/optimizer/path/costsize.c
相關文章
- 【PostgreSQL 】PostgreSQL 15對distinct的優化SQL優化
- ORACLE 聚簇因子優化Oracle優化
- HttpRunner 的結果校驗器優化HTTP優化
- 基於因子圖優化的鐳射IMU融合SLAM學習優化SLAM
- CBO Cost Formulas基於成本優化器的成本計算公式大全ORM優化公式
- 基於成本的Spark SQL優化器框架 [session]SparkSQL優化框架Session
- PostgreSQL 優化器程式碼概覽SQL優化
- 【CBO】基於成本優化器的基本原則（二）優化
- 【CBO】基於成本優化器的基本原則（一）優化
- PostgreSQL11preview-優化器增強彙總SQLView優化
- Mysql優化系列之——優化器對子查詢的處理MySql優化
- 【效能優化】Oracle 效能優化：降低列值聚簇因子 提高查詢效率優化Oracle
- 基於成本的Oracle優化法則Oracle優化
- 基於Barra多因子模型的組合權重優化模型優化
- postgresql 優化與維護SQL優化
- PostgreSQL 查詢成本模型SQL模型
- PostgreSQL10.0preview效能增強-hash,nestloopjoin優化(聰明的優化器是這樣的)SQLViewOOP優化
- 基於成本的優化器 — 一般錯誤概念和問題(轉)優化
- TiDB與MySQL優化器對照TiDBMySql優化
- PostgreSQL秒殺場景優化SQL優化
- 早餐｜第十七期 · 模型優化器對模型做了哪些優化模型優化
- 記一起由 Clang 編譯器優化觸發的 Crash編譯優化
- 球幕投影在天文館中使用的優勢分析
- 《圖解天文學》：一本美妙的書圖解
- PostgreSQL 優化需要掌握的知識類別SQL優化
- 對IN & EXISTS NOT IN & NOT EXISTS的優化優化
- 【PostgreSQL】 字首模糊查詢級優化SQL優化
- PostgreSQL10GIN索引鎖優化SQL索引優化
- 關於索引聚簇因子（CLUSTERING_FACTOR）引起的sql優化過程索引SQL優化
- oracle 的優化器Oracle優化
- Oracle的優化器Oracle優化
- 華瑞IT學校：選擇IT行業的優勢行業
- 小米開源自研智慧SQL優化與改寫工具SOAR使用指南SQL優化
- 優化器優化
- 深度學習的優化器（各類 optimizer 的原理、優缺點及數學推導）深度學習優化
- 自己優化的對拍程式優化
- Oracle對索引分析的優化Oracle索引優化
- MySQL 5.6，5.7的優化器對於count(*）的處理方式MySql優化