如何優(yōu)化postgreSQL中的刪除語句，好慢

The usual advice when you complain about slow bulk deletions in postgresql is "make sure the foreign keys (pointing to the table you are deleting from) are indexed". This is because postgresql doesn't create the indexes automatically for all the foreign keys (FK) which can be considered a degree of freedom or a nuisance, depends how you look at it. Anyway, the indexing usually solves the performance issue. Unless you stumble upon a FK field that is not indexable. Like I did.

成都創(chuàng)新互聯(lián)公司是一家專業(yè)提供鳳慶企業(yè)網(wǎng)站建設(shè),專注與成都網(wǎng)站建設(shè)、成都網(wǎng)站制作、H5網(wǎng)站設(shè)計(jì)、小程序制作等業(yè)務(wù)。10年已為鳳慶眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站制作公司優(yōu)惠進(jìn)行中。

Postgresql 建索引性能優(yōu)化

客戶新上線了一套監(jiān)控系統(tǒng)，可以監(jiān)控到所有的執(zhí)行慢的SQL，監(jiān)控到有個批量任務(wù)導(dǎo)入大量數(shù)據(jù)后，進(jìn)行索引創(chuàng)建。耗時需要幾分鐘，雖然不影響業(yè)務(wù)，但是需要整改。

這種問題的處理思路，都是大拆小，搞并發(fā)。

在測試環(huán)境，創(chuàng)建一個大表進(jìn)行測試，創(chuàng)建大量的假數(shù)據(jù)。

創(chuàng)建表

隨機(jī)字符串生成函數(shù)

生成大量的數(shù)據(jù)

經(jīng)測試發(fā)現(xiàn)這種方法創(chuàng)建數(shù)據(jù)太慢了，改成使用COPY的方式創(chuàng)建數(shù)據(jù)。

排查發(fā)現(xiàn)random_string效率太低，生成一條數(shù)據(jù)接近1ms

重新創(chuàng)建表

寫程序創(chuàng)建8600萬條數(shù)據(jù)放在test.csv中

導(dǎo)入大量數(shù)據(jù)

測試基準(zhǔn)數(shù)據(jù)

\timing 開啟計(jì)時

耗時532824.434 ms

耗時385838.893 ms，提升 38%的性能，非常不錯，但是遠(yuǎn)遠(yuǎn)不夠。

仍然會出發(fā)告警。

重新創(chuàng)建表

創(chuàng)建分區(qū)

并發(fā)創(chuàng)建INDEX，并記錄每個分區(qū)索引創(chuàng)建的開始時間和結(jié)束時間；

耗時 = 最大結(jié)束時間 - 最小開始時間 = 137 s，速度提升接近4倍。

順序創(chuàng)建INDEX，并記錄每個分區(qū)索引創(chuàng)建的開始時間和結(jié)束時間；

耗時 = 每個索引的耗時相加 = 457358.14 ms，速度提升 16.5%

順序創(chuàng)建INDEX，優(yōu)化并發(fā)

耗時 = 每個索引的耗時相加 = 292027.642 ms, 速度提升接近兩倍。

在開啟了并發(fā)參數(shù)的情況下，如果再疊加并發(fā)分區(qū)INDEX創(chuàng)建，會不會有驚喜呢？

并發(fā)創(chuàng)建INDEX，并記錄每個分區(qū)索引創(chuàng)建的開始時間和結(jié)束時間；

耗時 = 最大結(jié)束時間 - 最小開始時間 = 141 s，速度還不如默認(rèn)并發(fā)參數(shù)下的表現(xiàn)。應(yīng)該是資源發(fā)生爭搶導(dǎo)致的，通過系統(tǒng)監(jiān)控發(fā)現(xiàn)CPU已經(jīng)打滿了。

分區(qū)并發(fā)是目前能想到的最優(yōu)化手段了。

還需要結(jié)合查詢的情況進(jìn)行分析，分區(qū)會帶來一點(diǎn)點(diǎn)的性能下降是否影響也需要考慮一下。

分區(qū)時目前能避開監(jiān)控報(bào)警的唯一手段了，另外還鉆了監(jiān)控報(bào)警的空子。

客戶的監(jiān)控是基于單條語句的，單個分區(qū)的最大創(chuàng)建時間為47s，控制在分鐘以內(nèi)了。

技術(shù)文檔 - PostgreSQL 性能優(yōu)化之 fsync 參數(shù)

目 錄

總 結(jié)

PostgreSQL 通過調(diào)用系統(tǒng) fsync() 或者其他使得事務(wù)內(nèi)容寫入到物理磁盤，這樣可以保證操作系統(tǒng)或者數(shù)據(jù)庫出現(xiàn)宕機(jī)后，仍然可以恢復(fù)到某一個一致性的狀態(tài)。理論上講 PostgreSQL 的 fsync 功能關(guān)閉，可以實(shí)現(xiàn)性能的提升，但是帶來的影響就是需要承擔(dān)數(shù)據(jù)的丟失，因?yàn)槌霈F(xiàn)系統(tǒng)宕機(jī)或者數(shù)據(jù)庫崩潰的時候有一些數(shù)據(jù)是沒有落盤的。

本文將驗(yàn)證 fsync 參數(shù)的性能影響，以及參數(shù)關(guān)閉時數(shù)據(jù)庫宕機(jī)后的影響。

數(shù)據(jù)量：1000W

fsync 參數(shù)：on

初始化表：user_info

pgbench 壓測

pgbench 結(jié)果

pgbench 壓測

pgbench 結(jié)果

數(shù)據(jù)量：1000W

fsync 參數(shù)：off

初始化表：user_info

pgbench 壓測

pgbench 結(jié)果

pgbench 壓測

pgbench 結(jié)果

通過對比發(fā)現(xiàn)，將 fsync 改為 off，對于讀 TPS，參數(shù) fsync 的影響不大，對于寫 TPS，性能有一定提升。

現(xiàn)在驗(yàn)證參數(shù)關(guān)閉時數(shù)據(jù)庫宕機(jī)后的影響

首先，使用將數(shù)據(jù)庫性能跑起來

然后，模擬服務(wù)器斷電

之后，啟動數(shù)據(jù)庫

提示信息：比致命錯誤還過分的錯誤。

結(jié)果：數(shù)據(jù)庫無法啟動，原因就是因?yàn)闊o法找到一個有效的 checkpoint 記錄，這就是因?yàn)?fsync 設(shè)置為 off，由于數(shù)據(jù)庫異常宕機(jī)導(dǎo)致?？梢酝ㄟ^使用 pg_resetxlog 恢復(fù)數(shù)據(jù)庫，但是會造成部分?jǐn)?shù)據(jù)無法找回，數(shù)據(jù)丟失；也可以通過備份恢復(fù)，同樣也會丟失部分?jǐn)?shù)據(jù)。

fsync 參數(shù)對于讀 TPS 的性能影響不大，對于寫 TPS 的性能有一些影響，設(shè)置為 off，寫 TPS 性能有一定提升，但是存在數(shù)據(jù)庫宕機(jī)后無法正常啟動，即使恢復(fù)后啟動數(shù)據(jù)庫，也會有數(shù)據(jù)丟失的很大風(fēng)險(xiǎn)。因此生產(chǎn)環(huán)境非必要時，不要將此參數(shù)設(shè)置為 off，還是使用默認(rèn)的 on 比較穩(wěn)妥。

SQL語句優(yōu)化，使用postgresql數(shù)據(jù)庫，查詢下面sql,需要20多分鐘：

postgresql（8.2）的配置文件中有一個參數(shù)log_min_duration_statement，意思是只log執(zhí)行時間大于設(shè)定值的語句，如果設(shè)為0，表示log所有語句；如果設(shè)為-1，表示不log任何語句。

看起來，這個配置選項(xiàng)對性能的調(diào)整是很有用的，比如可以設(shè)置：

log_min_duration_statement = 1000

則只log執(zhí)行時間大于1s的語句，重點(diǎn)優(yōu)化這些sql語句就好了。

然而，奇怪的，這個選項(xiàng)不太容易生效！經(jīng)過反復(fù)試驗(yàn)，原來需要如下配置：

#debug_print_parse = off

#debug_print_rewritten = off

#debug_print_plan = off

#debug_pretty_print = off

log_connections = off

#log_disconnections = off

log_duration = off

log_line_prefix = '%t [%p]: [%l-1] ' # Special values:

# %u = user name

# %d = database name

# %r = remote host and port

# %h = remote host

# %p = PID

# %t = timestamp (no milliseconds)

# %m = timestamp with milliseconds

# %i = command tag

# %c = session id

# %l = session line number

# %s = session start timestamp

# %x = transaction id

# %q = stop here in non-session

# processes

# %% = '%'

# e.g. '%u%%%d '

log_statement = 'none' # none, mod, ddl, all

#log_statement = 'all' # none, mod, ddl, all

#log_hostname = off

注意看上面的其中兩個選項(xiàng)的設(shè)置：

log_duration = off

log_statement = 'none'

這兩個選項(xiàng)的意思是不log任何sql語句和執(zhí)行時間，但是恰恰是關(guān)閉了這兩個，log_min_duration_statement才會生效！可能postgresql內(nèi)部 對這兩個選項(xiàng)做了“互斥”處理吧。