偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

PostgreSQL中數(shù)據(jù)批量導入優(yōu)化方法

數(shù)據(jù)庫 其他數(shù)據(jù)庫 PostgreSQL
在數(shù)據(jù)遷移過程中,PostgreSQL數(shù)據(jù)庫導入大量數(shù)據(jù)時候非常緩慢,本文我們就來說說PostgreSQL數(shù)據(jù)庫批量導入數(shù)據(jù)時的優(yōu)化方法和策略。

現(xiàn)在很多企業(yè)都將數(shù)據(jù)庫逐漸由Mysql轉(zhuǎn)向了更加強大而且開源的PostgreSQL數(shù)據(jù)庫。在數(shù)據(jù)遷移過程中,PostgreSQL數(shù)據(jù)庫導入大量數(shù)據(jù)時候非常緩慢,本文我們就來說說PostgreSQL數(shù)據(jù)庫批量導入數(shù)據(jù)時的優(yōu)化方法和策略。

概述

考慮PostgreSQL數(shù)據(jù)庫批量導入數(shù)據(jù)時性能緩慢的原因,無非有幾個因素:索引,觸發(fā)器,外鍵,GUID主鍵,還有可能是預(yù)寫日志(WAL)。我們就從這幾個影響因素著手優(yōu)化。當然有可能,本文說的這些技巧都不能有效問題,遇到這樣的問題時候,就需要我們具體問題具體分析,并針對性的解決。

關(guān)閉日志記錄

對于PostgreSQL 9.5及更高版本,可以先將目標表更改為UNLOGGED,然后在加載數(shù)據(jù)后將其更改回LOGGED:

  1. ALTER TABLE <target table> SET UNLOGGED 
  2. <批量導入數(shù)據(jù)…> 
  3. ALTER TABLE <target table> LOGGED 

UNLOGGED模式可以確保PostgreSQL不會在變量導入數(shù)據(jù)時將表寫操作記錄到預(yù)寫日志(WAL),從而極大的優(yōu)化導入過程。但是,由于未記錄操作,因此如果在加載過程中發(fā)生崩潰或服務(wù)器關(guān)機等故障,則無法恢復數(shù)據(jù)。PostgreSQL重新啟動后將自動截斷任何未記錄的表。

另外,未記錄的表不會復制到備用服務(wù)器。在這種情況下,必須在加載之前刪除現(xiàn)有的復制,并在加載之后重新創(chuàng)建。根據(jù)主節(jié)點中的數(shù)據(jù)量和備用數(shù)據(jù)庫的數(shù)量,重建復制的時間可能會很長,對于高可用性要求來說這是不可接受的。

建議采用以下方法,將數(shù)據(jù)批量插入未記錄的表中:

  • 在將表和數(shù)據(jù)更改為未記錄模式之前對其進行備份;
  • 數(shù)據(jù)加載完成后,重新創(chuàng)建對備用服務(wù)器的任何復制;

對可以輕松重新填充的表使用UNLOGGED的批量插入(例如,大型查找表或維度表)。

刪除索引

數(shù)據(jù)庫索引可能在批量數(shù)據(jù)插入期間導致嚴重的延遲。因為添加數(shù)據(jù)過程,對應(yīng)的索引條目需要實時更新。

建議在開始批量插入之前盡可能刪除目標表中的索引,并在導入完成后重新創(chuàng)建索引。同樣,在大型表上創(chuàng)建索引可能很耗時,但是比在加載過程中更新索引要快。

  1. DROP INDEX <index_name1><index_name2> … <index_name_n> 
  2. <批量導入數(shù)據(jù)…> 
  3. CREATE INDEX <index_name> ON <target_table>(column1, …,column n) 

創(chuàng)建索引之前,臨時提高maintenance_work_mem配置參數(shù)可能會有幫助。增加的工作內(nèi)存可以幫助更快地創(chuàng)建索引。

為了安全起見的另一種選擇是使用現(xiàn)有數(shù)據(jù)和索引在同一數(shù)據(jù)庫中復制目標表。然后,測試有索引和刪除索兩種情況下批量導入數(shù)據(jù)的性能對比,然后根據(jù)測試結(jié)果選擇更好的方法。

刪除外鍵

和索引一樣,外鍵約束也會影響大批量導入的性能。因為導入過程中必須檢查插入的每個行數(shù)據(jù)的每個外鍵是否存在相應(yīng)的主鍵。當批量導入時,必須為每一行觸發(fā)該觸發(fā)器檢查外鍵,從而增加了開銷。

除非受到業(yè)務(wù)規(guī)則的限制,否則建議先從目標表中刪除所有外鍵,在單個事務(wù)中加載數(shù)據(jù),然后在提交事務(wù)后重新創(chuàng)建外鍵。

  1. ALTER TABLE <target_table> 
  2. DROP CONSTRAINT <foreign_key_constraint> 
  3.  
  4.  
  5. BEGIN TRANSACTION 
  6. <批量導入數(shù)據(jù)…> 
  7. COMMIT 
  8.  
  9.  
  10. ALTER TABLE <target_table> 
  11. ADD CONSTRAINT <foreign key constraint> 
  12. FOREIGN KEY (<foreign_key_field>
  13. REFERENCES <parent_table>(<primary key field>)... 

同樣增加maintenance_work_mem配置參數(shù)也能提高重新創(chuàng)建外鍵約束的性能。

暫停觸發(fā)器

INSERT或DELETE觸發(fā)器(如果導入過程還涉及從目標表中刪除記錄)可能會導致批量數(shù)據(jù)導入延遲。這是因為每個觸發(fā)器將具有需要檢查的邏輯,并且需要在每行被插入或刪除后立即完成操作。

建議在批量導入數(shù)據(jù)之前禁用目標表中的所有觸發(fā)器,并在導入完成后再啟用它們。禁用所有觸發(fā)器也會強制執(zhí)行外鍵約束檢查的系統(tǒng)觸發(fā)器。

  1. ALTER TABLE <target table> DISABLE TRIGGER ALL 
  2. <批量導入數(shù)據(jù)…> 
  3. ALTER TABLE <target table> ENABLE TRIGGER ALL 

使用多值INSERT

對于成批數(shù)據(jù)加載,運行數(shù)千個或數(shù)十萬個INSERT語句可能是個糟糕的選擇。因為查詢優(yōu)化器必須解析和準備每個單獨的INSERT命令,然后進行所有約束檢查,作為單獨的事務(wù)運行并記錄日志。而使用多值單個INSERT語句可以節(jié)省這些不必要的開支。

  1. INSERT INTO <target_table> (<column1><column2>, …, <column_n>
  2. VALUES 
  3. (<value a><value b>, …, <value x>), 
  4. (<value 1><value 2>, …, <value n>), 
  5. (<value A><value B>, …, <value Z>), 
  6. (<value i><value ii>, …, <value L>), 
  7. ... 

多值INSERT性能受現(xiàn)有索引的影響。建議在運行命令之前先刪除索引,然后再創(chuàng)建索引。

另一個需要注意的地方是PostgreSQL可用于運行多值INSERT的內(nèi)存量。運行多值INSERT時,RAM中必須容納大量輸入值,并且除非有足夠的可用內(nèi)存,否則該過程可能會失敗。

建議將設(shè)置effective_cache_size參數(shù)到50%,并將shared_buffer設(shè)為機器的總內(nèi)存的參數(shù)設(shè)為25%。為了安全起見,將導入劃分為多條的多值INSERT,每個語句的值不要超過1000行。

使用COPY命令

建議使用PostgreSQL COPY命令從一個或多個文件導入數(shù)據(jù)。COPY針對批量數(shù)據(jù)導入會進行額外的優(yōu)化,比運行大量INSERT語句甚至多值INSERTS的都要快。

  1. COPY <target table> [( column1>, … , <column_n>)] 
  2. FROM '<文件路徑>
  3. WITH (<option1><option2>, … , <option_n>

使用COPY的還有很多的優(yōu)勢:

  • 它支持文本和二進制文件導入;
  • 本質(zhì)上是事務(wù)性的;
  • 它允許指定輸入文件的結(jié)構(gòu);
  • 它可以使用WHERE子句有條件地導入數(shù)據(jù)。

運行ANALYZ

這與提高批量數(shù)據(jù)導入性能無關(guān),但是強烈建議在批量導入之后立即在目標表上運行ANALYZE命令。大量的新導入的行將大大改變數(shù)據(jù)表中列中的數(shù)據(jù)分布,并且會使表的統(tǒng)計信息都過時。當用查詢優(yōu)化器使用過時的統(tǒng)計信息時,查詢性能可能會非常慢。運行ANALYZE命令將確保更新統(tǒng)計信息。

總結(jié)

對于數(shù)據(jù)庫應(yīng)用程序來說,可能并非每天都會進行批量數(shù)據(jù)導入,但是在運行時會對查詢性能產(chǎn)生影響。這就是為什么有必要盡可能縮短導入時間。DBA可以最大程度地減少意外的事情之一就是在具有類似服務(wù)器規(guī)格和PostgreSQL配置的開發(fā)或準線上環(huán)境中進行性能測試并進行優(yōu)化。每種數(shù)據(jù)加載方案都是不同的,最好嘗試每種方法并找到最好最快的方法。

 

責任編輯:趙寧寧 來源: 蟲蟲搜奇
相關(guān)推薦

2010-11-24 11:13:07

MySQL批量導入

2020-12-18 10:40:00

ExcelJava代碼

2013-12-26 13:19:26

PostgreSQL優(yōu)化

2023-11-30 16:29:16

PostgreSQL數(shù)據(jù)庫Kubernetes

2011-08-25 16:13:31

SQL Server批量替換數(shù)據(jù)

2023-02-07 08:15:45

PostgreSQLIO技巧

2010-10-20 14:56:18

2011-08-15 16:58:34

SQL Server遠程查詢批量導入數(shù)據(jù)

2022-12-28 20:11:25

圖數(shù)據(jù)庫

2018-05-23 13:47:28

數(shù)據(jù)庫PostgreSQL查詢優(yōu)化

2018-05-25 15:04:57

數(shù)據(jù)庫PostgreSQL查詢優(yōu)化器

2010-04-14 09:24:29

在Oracle數(shù)據(jù)庫

2023-03-07 08:17:19

Postgresql數(shù)據(jù)庫優(yōu)化

2010-09-09 15:23:16

SQL更新數(shù)據(jù)

2011-03-10 10:50:01

excelsql數(shù)據(jù)庫

2010-10-26 11:04:48

oracle數(shù)據(jù)導入

2015-10-14 09:32:36

Amazon谷歌批量遷移

2023-12-30 20:04:51

MyBatis框架數(shù)據(jù)

2010-06-02 16:14:14

導入MySQL

2022-09-01 08:42:36

SQL數(shù)據(jù)項目
點贊
收藏

51CTO技術(shù)棧公眾號