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

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

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

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

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

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

創(chuàng)建表

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

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

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

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

重新創(chuàng)建表

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

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

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

\timing 開(kāi)啟計(jì)時(shí)

耗時(shí)532824.434 ms

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

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

重新創(chuàng)建表

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

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

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

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

耗時(shí) = 每個(gè)索引的耗時(shí)相加 = 457358.14 ms，速度提升 16.5%

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

耗時(shí) = 每個(gè)索引的耗時(shí)相加 = 292027.642 ms, 速度提升接近兩倍。

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

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

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

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

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

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

客戶(hù)的監(jiān)控是基于單條語(yǔ)句的，單個(gè)分區(qū)的最大創(chuàng)建時(shí)間為47s，控制在分鐘以?xún)?nèi)了。

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

目 錄

總 結(jié)

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

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

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

fsync 參數(shù)：on

初始化表：user_info

pgbench 壓測(cè)

pgbench 結(jié)果

pgbench 壓測(cè)

pgbench 結(jié)果

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

fsync 參數(shù)：off

初始化表：user_info

pgbench 壓測(cè)

pgbench 結(jié)果

pgbench 壓測(cè)

pgbench 結(jié)果

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

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

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

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

之后，啟動(dòng)數(shù)據(jù)庫(kù)

提示信息：比致命錯(cuò)誤還過(guò)分的錯(cuò)誤。

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

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

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

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

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

log_min_duration_statement = 1000

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

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

#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

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

log_duration = off

log_statement = 'none'

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

分享名稱(chēng)：postgresql有話(huà)的簡(jiǎn)單介紹
分享地址：http://www.rwnh.cn/article32/dscohsc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)網(wǎng)站制作、關(guān)鍵詞優(yōu)化、域名注冊(cè)、全網(wǎng)營(yíng)銷(xiāo)推廣、標(biāo)簽優(yōu)化、外貿(mào)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)