調節effective_io_concurrenc優化PostgreSQL bitmap index scan性能 環境 測試 少量堆page掃描的場景 總結 補充 參考1：bitmap heap scan的代價估算 參考2 參考資料

測試發現PostgreSQL在bitmap index scan時，如果要讀入大量堆page，讀IO的速度會遠低于正常的順序讀，影響性能。

下面用一個例子說明這個問題。

桌上型電腦上的CentOS7.1虛機

消費級SSD

blockdev --setra設定為2048

建立測試表

每個page大概有107條記錄，c1的範圍是0~199，也就是大概每2個page中就有一個c1相同的記錄，即按page算，c1的選擇性差不多是50%。

資料是2GB，shared_buffers是256MB。

現在用c1作為條件，使用不同的執行計劃進行查詢，并且每次查詢前都清一次OS緩存并重起PostgreSQL。

Bitmap index掃描145秒。

每個IO大小是16個扇區(8K),沒有看到大的IO合并，IO隊列深度也小于1，判斷磁盤預讀沒有生效。

順序掃描74秒。

index掃描76秒

通過上面的資料可以看出，順序掃描和索引掃描都可以利用磁盤預讀，但bitmap索引掃描不行。

微觀上通過strace -p跟蹤postgres後端程序看看索引掃描和bitmap索引掃描各自調用的API有什麼差別。

以下是索引掃描調用的API片段

bitmap索引掃描調用的API片段

從上面的調用看，應該是fadvise64()使得磁盤預讀失效。fadvise64()調用是由effective_io_concurrency參數控制的預讀功能，effective_io_concurrency的預設值為1，它隻對bitmap index scan有效。

下面将effective_io_concurrency禁用，發現性能有所提高，執行時間102秒，磁盤預讀也生效了。

除了禁用，還有一個辦法，就是将effective_io_concurrency設得更大，完全代替磁盤預讀。這次效果更好，46秒就出結果了。

采用這個辦法，IO請求大小還是16個扇區，但是隊列深度也就是IO的并行度提高了。

再看看調用的API，fadvise64()和read()仍然是交替的，但fadvise64()會提前好幾個周期就将相應的預讀請求發給IO裝置。

上面的場景需要掃描50%的堆page，下面看看隻需掃描少量堆page的情況。

構造掃描1%堆page的查詢，下面在不同effective_io_concurrency值的情況下bitmap index scan的執行結果。

從這個結果看，effective_io_concurrency的值為0還是為1,性能差别不大。

下面看看其它掃描方式的執行結果。

從這兒看好像Index Scan總比bitmap index Scan快，其實這和測試資料有關。測試資料的導入的方式決定了從index裡取出的堆元組已經是按page順序排列好的，是以沒有發揮出bitmap調整元組順序的效果。

PostgreSQL的預讀依賴OS的磁盤預讀和posix_fadvise()調用。(MySQL利用的是libaio，機制不同)

posix_fadvise()也就是effective_io_concurrency生效時，磁盤預讀會失效，對于bitmap heap scan需要掃描大量位置相鄰page的場景，性能不佳。

為優化bitmap heap scan的大量讀IO，根據情況可以将effective_io_concurrency設為0或者設定為一個比較大的值。

上面這個例子中性能優化效果看上去并不是很大，但在另一個環境中用相同的方法優化tpch Q6查詢效果就更加明顯了。優化前執行時間1050秒，effective_io_concurrency設為0，250秒。effective_io_concurrency設為100，116秒。

如果不考慮effective_io_concurrency使磁盤預讀失效的性能下降，bitmap heap scan的代價估算還是蠻合理的。 但是考慮這個因素的話，對于需要讀取大量堆page的時候，比如20%以上随機分布的page，順序讀會更好。

根據相關文章的解釋，如果read不連續，将會使磁盤預讀失效。

http://os.51cto.com/art/200910/159067.htm

但是，實際檢驗的結果，通過特殊的索引和查詢條件，構造一個完全跳躍式的read()序列，read()和lseek()交替出現，沒有2個連續的read()，結果預讀仍然有效。可見Linux對順序讀的判斷并沒有字面上那麼簡單。

http://blog.chinaunix.net/uid-20726500-id-5747918.html

http://blog.163.com/digoal@126/blog/static/163877040201421392811622/

http://mysql.taobao.org/monthly/2015/05/04/