欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

PostgreSQL數(shù)據(jù)庫性能調(diào)優(yōu)的注意點以及pg數(shù)據(jù)庫性能優(yōu)化方式

 更新時間:2023年03月15日 09:47:02   作者:ac.char  
這篇文章主要介紹了PostgreSQL數(shù)據(jù)庫性能調(diào)優(yōu)的注意點以及pg數(shù)據(jù)庫性能優(yōu)化方式,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教

PostgreSQL 優(yōu)化思路

優(yōu)化思路:

0、為每個表執(zhí)行 ANALYZE

然后分析 EXPLAIN (ANALYZE,BUFFERS) sql。

1、對于多表查詢,查看每張表數(shù)據(jù),然后改進連接順序。

2、先查找那部分是重點語句,比如上面SQL,外面的嵌套層對于優(yōu)化來說沒有意義,可以去掉。

3、查看語句中,where等條件子句,每個字段能過濾的效率。找出可優(yōu)化處。

比如oc.order_id = oo.order_id是關(guān)聯(lián)條件,需要加索引

  • oc.op_type = 3 能過濾出1/20的數(shù)據(jù),
  • oo.event_type IN (…) 能過濾出1/10的數(shù)據(jù),

這兩個是優(yōu)化的重點,也就是實現(xiàn)確保op_type與event_type已經(jīng)加了索引,其次確保索引用到了。

一、排序

  • 盡量避免
  • 排序的數(shù)據(jù)量盡量少,并保證在內(nèi)存里完成排序。

(至于具體什么數(shù)據(jù)量能在內(nèi)存中完成排序,不同數(shù)據(jù)庫有不同的配置:oracle是sort_area_size;postgresql是work_mem (integer),單位是KB,默認值是4MB。mysql是sort_buffer_size 注意:該參數(shù)對應的分配內(nèi)存是每連接獨占?。?/p>

二、索引

  • 過濾的數(shù)據(jù)量比較少,一般來說<20%,應該走索引。20%-40% 可能走索引也可能不走索引。> 40% ,基本不走索引(會全表掃描)
  • 保證值的數(shù)據(jù)類型和字段數(shù)據(jù)類型要一直。
  • 對索引的字段進行計算時,必須在運算符右側(cè)進行計算。也就是 to_char(oc.create_date, ‘yyyyMMdd’)是沒用的
  • 表字段之間關(guān)聯(lián),盡量給相關(guān)字段上添加索引。
  • 復合索引,遵從最左前綴的原則,即最左優(yōu)先。(單獨右側(cè)字段查詢沒有索引的)

三、連接查詢方式

1、hash join

  • 放內(nèi)存里進行關(guān)聯(lián)。
  • 適用于結(jié)果集比較大的情況。
  • 比如都是200000數(shù)據(jù)

2、nest loop

  • 從結(jié)果1 逐行取出,然后與結(jié)果集2進行匹配。
  • 適用于兩個結(jié)果集,其中一個數(shù)據(jù)量遠大于另外一個時。
  • 結(jié)果集一:1000
  • 結(jié)果集二:1000000

四、多表聯(lián)查時

在多表聯(lián)查時,需要考慮連接順序問題。

1、當postgresql中進行查詢時,如果多表是通過逗號,而不是join連接,那么連接順序是多表的笛卡爾積中取最優(yōu)的。如果有太多輸入的表, PostgreSQL規(guī)劃器將從窮舉搜索切換為基因概率搜索,以減少可能性數(shù)目(樣本空間)?;蛩阉骰ǖ臅r間少, 但是并不一定能找到最好的規(guī)劃。

2、對于JOIN

  • LEFT JOIN / RIGHT JOIN 會一定程度上指定連接順序,但是還是會在某種程度上重新排列:
  • FULL JOIN 完全強制連接順序。

如果要強制規(guī)劃器遵循準確的JOIN連接順序,我們可以把運行時參數(shù)join_collapse_limit設置為 1

PostgreSQL提供了一些性能調(diào)優(yōu)的功能

主要有如下幾個方面。

1.使用EXPLAIN

EXPLAIN命令可以查看執(zhí)行計劃,這個方法是我們最主要的調(diào)試工具。

2.及時更新執(zhí)行計劃中使用的統(tǒng)計信息

由于統(tǒng)計信息不是每次操作數(shù)據(jù)庫都進行更新的,一般是在 VACUUM 、 ANALYZE 、 CREATE INDEX等DDL執(zhí)行的時候會更新統(tǒng)計信息,

因此執(zhí)行計劃所用的統(tǒng)計信息很有可能比較舊。 這樣執(zhí)行計劃的分析結(jié)果可能誤差會變大。

以下是表tenk1的相關(guān)的一部分統(tǒng)計信息。

SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'tenk1%';
relnamerelkindreltuplesrelpages
tenk1r10000358
tenk1_hundredi1000030
tenk1_thous_tenthousi1000030
tenk1_unique1i1000030
tenk1_unique2i1000030

(5 rows)

其中 relkind是類型,r是自身表,i是索引index;reltuples是項目數(shù);relpages是所占硬盤的塊數(shù)。

3.明確用join來關(guān)聯(lián)表

一般寫法:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

如果明確用join的話,執(zhí)行時候執(zhí)行計劃相對容易控制一些。

例子:

SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

4.關(guān)閉自動提交

(autocommit=false)

5.多次插入數(shù)據(jù)用copy命令更高效

我們有的處理中要對同一張表執(zhí)行很多次insert操作。這個時候我們用copy命令更有效率。因為insert一次,其相關(guān)的index都要做一次,比較花費時間。

6.臨時刪除index

有時候我們在備份和重新導入數(shù)據(jù)的時候,如果數(shù)據(jù)量很大的話,要很幾個小時才能完成。這個時候可以先把index刪除掉。導入在建index。

7.外鍵關(guān)聯(lián)的刪除

如果表的有外鍵的話,每次操作都沒去check外鍵整合性。因此比較慢。數(shù)據(jù)導入后在建立外鍵也是一種選擇。

8.增加maintenance_work_mem參數(shù)大小

增加這個參數(shù)可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的執(zhí)行效率。

9.增加checkpoint_segments參數(shù)的大小

增加這個參數(shù)可以提升大量數(shù)據(jù)導入時候的速度。

10.設置archive_mode無效

這個參數(shù)設置為無效的時候,能夠提升以下的操作的速度

  • CREATE TABLE AS SELECT
  • CREATE INDEX
  • ALTER TABLE SET TABLESPACE
  • CLUSTER等。

11.最后執(zhí)行VACUUM ANALYZE

表中數(shù)據(jù)大量變化的時候建議執(zhí)行VACUUM ANALYZE。

對生產(chǎn)運行的數(shù)據(jù)庫要用定時任務crontb執(zhí)行如下操作:

psql -U username -d databasename -c "vacuum verbose analyze tablename;"

PostgreSQL 參數(shù)設置

autovacuum 相關(guān)參數(shù)

autovacuum:
默認為on,表示是否開起autovacuum。默認開起。特別的,當需要凍結(jié)xid時,盡管此值為off,PG也會進行vacuum。 

autovacuum_naptime:
下一次vacuum的時間,默認1min。 這個naptime會被vacuum launcher分配到每個DB上。autovacuum_naptime/num of db。 

log_autovacuum_min_duration:
記錄autovacuum動作到日志文件,當vacuum動作超過此值時。 “-1”表示不記錄?!?”表示每次都記錄。 

autovacuum_max_workers:
最大同時運行的worker數(shù)量,不包含launcher本身。 

autovacuum_work_mem:
每個worker可使用的最大內(nèi)存數(shù)。

autovacuum_vacuum_threshold:
默認50。與autovacuum_vacuum_scale_factor配合使用,autovacuum_vacuum_scale_factor默認值為20%。當update,delete的tuples數(shù)量超過autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold時,進行vacuum。如果要使vacuum工作勤奮點,則將此值改小。 

autovacuum_analyze_threshold:
默認50。與autovacuum_analyze_scale_factor配合使用。

autovacuum_analyze_scale_factor:
默認10%。當update,insert,delete的tuples數(shù)量超過autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold時,進行analyze。 

autovacuum_freeze_max_age:200 million。離下一次進行xid凍結(jié)的最大事務數(shù)。 

autovacuum_multixact_freeze_max_age:
400 million。離下一次進行xid凍結(jié)的最大事務數(shù)。 

autovacuum_vacuum_cost_delay:
如果為-1,取vacuum_cost_delay值。 

autovacuum_vacuum_cost_limit:
如果為-1,到vacuum_cost_limit的值,這個值是所有worker的累加值。
選項默認值說明是否優(yōu)化原因
max_connections100允許客戶端連接的最大數(shù)目因為在測試的過程中,100個連接已經(jīng)足夠
fsyncon強制把數(shù)據(jù)同步更新到磁盤因為系統(tǒng)的IO壓力很大,為了更好的測試其他配置的影響,把改參數(shù)改為off
shared_buffers24MB決定有多少內(nèi)存可以被PostgreSQL用于緩存數(shù)據(jù)(推薦內(nèi)存的1/4)在IO壓力很大的情況下,提高該值可以減少IO
work_mem1MB使內(nèi)部排序和一些復雜的查詢都在這個buffer中完成有助提高排序等操作的速度,并且減低IO
effective_cache_size128MB優(yōu)化器假設一個查詢可以用的最大內(nèi)存,和shared_buffers無關(guān)(推薦內(nèi)存的1/2)設置稍大,優(yōu)化器更傾向使用索引掃描而不是順序掃描
maintenance_work_mem16MB這里定義的內(nèi)存只是被VACUUM等耗費資源較多的命令調(diào)用時使用把該值調(diào)大,能加快命令的執(zhí)行
wal_buffer768kB日志緩存區(qū)的大小可以降低IO,如果遇上比較多的并發(fā)短事務,應該和commit_delay一起用
checkpoint_segments3設置wal log的最大數(shù)量數(shù)(一個log的大小為16M)默認的48M的緩存是一個嚴重的瓶頸,基本上都要設置為10以上
checkpoint_completion_target0.5表示checkpoint的完成時間要在兩個checkpoint間隔時間的N%內(nèi)完成能降低平均寫入的開銷
commit_delay0事務提交后,日志寫到wal log上到wal_buffer寫入到磁盤的時間間隔。需要配合commit_sibling能夠一次寫入多個事務,減少IO,提高性能
commit_siblings5設置觸發(fā)commit_delay的并發(fā)事務數(shù),根據(jù)并發(fā)事務多少來配置減少IO,提高性能
autovacuum_naptime1min下一次vacuum任務的時間提高這個間隔時間,使他不是太頻繁
autovacuum_analyze_threshold50與autovacuum_analyze_scale_factor配合使用,來決定是否analyze使analyze的頻率符合實際
autovacuum_analyze_scale_factor0.1當update,insert,delete的tuples數(shù)量超過autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold時,進行analyze。使analyze的頻率符合實際

pg中性能相關(guān)常調(diào)參數(shù)

參數(shù)名稱參數(shù)意義優(yōu)化思路
shared_buffers數(shù)據(jù)庫服務器將使用的共享內(nèi)存緩沖區(qū)大小,該緩沖區(qū)為所有連接共用。從磁盤讀入的數(shù)據(jù)(主要包括表和索引)都緩存在這里。提高該值可以減少數(shù)據(jù)庫的磁盤IO。
work_mem聲明內(nèi)部排序和哈希操作可使用的工作內(nèi)存大小。該內(nèi)存是在開始使用臨時磁盤文件之前使用的內(nèi)存數(shù)目。數(shù)值以kB為單位的,缺省是 1024 (1MB)。請注意對于復雜的查詢,可能會同時并發(fā)運行好幾個排序或者哈希操作,每個都會使用這個參數(shù)聲明的這么多內(nèi)存,然后才會開始求助于臨時文件。同樣,好幾個正在運行的會話可能會同時進行排序操作。因此使用的總內(nèi)存可能是 work_mem 的好幾倍。ORDER BY, DISTINCT 和mergejoin都要用到排序操作,而哈希操作在哈希連接、哈希聚集和以哈希為基礎的 IN 子查詢處理中都會用到。該參數(shù)是會話級參數(shù)。執(zhí)行排序操作時,會根據(jù)work_mem的大小決定是否將一個大的結(jié)果集拆分為幾個小的和 work_mem差不多大小的臨時文件寫入外存。顯然拆分的結(jié)果是導致了IO,降低了排序的速度。因此增加work_mem有助于提高排序的速度。通常設置時可以逐漸調(diào)大,知道數(shù)據(jù)庫在排序的操作時不會有大量的寫文件操作即可。該內(nèi)存每個連接一份,當并發(fā)連接較多時候,該值不宜過大。
effective_cache_size優(yōu)化器假設一個查詢可以使用的最大內(nèi)存(包括pg使用的和操作系統(tǒng)緩存),和shared_buffer等內(nèi)存無關(guān),只是給優(yōu)化器生成計劃使用的一個假設值。設置稍大,優(yōu)化器更傾向使用索引掃描而不是順序掃描,建議的設置為可用空閑內(nèi)存的25%,這里的可用空閑內(nèi)存指的是主機物理內(nèi)存在運行pg時得空閑值。
maintenance_work_mem這里定義的內(nèi)存只是在CREATE INDEX, VACUUM等時用到,因此用到的頻率不高,但是往往這些指令消耗比較多的資源,因此應該盡快讓這些指令快速執(zhí)行完畢。在數(shù)據(jù)庫導入數(shù)據(jù)后,執(zhí)行建索引等操作時,可以調(diào)大,比如512M。
wal_buffers日志緩沖區(qū),日志緩沖區(qū)的大小。兩種情況下要酌情調(diào)大:1.單事務的數(shù)據(jù)修改量很大,產(chǎn)生的日志大于wal_buffers,為了避免多次IO,調(diào)大該值。
2.系統(tǒng)中并發(fā)小數(shù)據(jù)量修改的短事務較多,并且設置了commit_delay,此時wal_buffers需要容納多個事務(commit_siblings個)的日志,調(diào)大該值避免多次IO。
commit_delay事務提交后,日志寫到wal_buffer上到wal_buffer寫到磁盤的時間間隔。如果并發(fā)的非只讀事務數(shù)目較多,可以適當增加該值,使日志緩沖區(qū)一次刷盤可以刷出較多的事務,減少IO次數(shù),提高性能。需要和commit_sibling配合使用。
commit_siblings觸發(fā)commit_delay等待的并發(fā)事務數(shù),也就是系統(tǒng)的并發(fā)活躍事務數(shù)達到了該值事務才會等待commit_delay的時間才將日志刷盤,如果系統(tǒng)中并發(fā)活躍事務達不到該值,commit_delay將不起作用,防止在系統(tǒng)并發(fā)壓力較小的情況下事務提交后空等其他事務。應根據(jù)系統(tǒng)并發(fā)寫的負載配置。例如統(tǒng)計出系統(tǒng)并發(fā)執(zhí)行增刪改操作的平均連接數(shù),設置該值為該平均連接數(shù)。
fsync設置為on時,日志緩沖區(qū)刷盤時,需要確認已經(jīng)將其寫入了磁盤,設置為off時,由操作系統(tǒng)調(diào)度磁盤寫的操作,能更好利用緩存機制,提高IO性能。該性能的提高是伴隨了數(shù)據(jù)丟失的風險,當操作系統(tǒng)或主機崩潰時,不保證刷出的日志是否真正寫入了磁盤。應依據(jù)操作系統(tǒng)和主機的穩(wěn)定性來配置。
autovacuum是否開啟自動清理進程(如開啟需要同時設置參數(shù)stats_start_collector = on,stats_row_level = on,),整理數(shù)據(jù)文件碎片,更新統(tǒng)計信息。如果系統(tǒng)中有大量的增刪改操作,建議打開自動清理進程,這樣一方面可以增加數(shù)據(jù)文件的物理連續(xù)性,減少磁盤的隨機IO,一方面可以隨時更新數(shù)據(jù)庫的統(tǒng)計信息,使優(yōu)化器可以選擇最優(yōu)的查詢計劃得到最好的查詢性能。如果系統(tǒng)中只有只讀的事務,那么關(guān)閉自動清理進程。
autovacuum_naptime自動清理進程執(zhí)行清理分析的時間間隔應該根據(jù)數(shù)據(jù)庫的單位時間更新量來決定該值,一般來說單位時間的更新量越大該時間間隔應該設置越短。由于自動清理對系統(tǒng)的開銷較大,該值應該謹慎配置(不要過?。?。
bgwriter_delay后臺寫進程的自動執(zhí)行時間后臺寫進程的作用是將shared_buffer里的臟頁面寫回到磁盤,減少checkpoint的壓力,如果系統(tǒng)數(shù)據(jù)修改的壓力一直很大,建議將該時間間隔設置小一些,以免積累的大量的臟頁面到checkpoint,使checkpoint時間過長(checkpoint期間系統(tǒng)響應速度較慢)。
bgwriter_lru_maxpages后臺寫進程一次寫出的臟頁面數(shù)依據(jù)系統(tǒng)單位時間數(shù)據(jù)的增刪改量來修改
bgwriter_lru_multiplier后臺寫進程根據(jù)最近服務進程需要的buffer數(shù)量乘上這個比率估算出下次服務進程需要的buffer數(shù)量,在使用后臺寫進程寫回臟頁面,使緩沖區(qū)能使用的干凈頁面達到這個估計值。依據(jù)系統(tǒng)單位時間數(shù)據(jù)的增刪改量來修改。

總結(jié)

以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。

相關(guān)文章

  • postgresql重置序列起始值的操作

    postgresql重置序列起始值的操作

    這篇文章主要介紹了postgresql重置序列起始值,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • PostgreSQL完成按月累加的操作

    PostgreSQL完成按月累加的操作

    這篇文章主要介紹了PostgreSQL完成按月累加的操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • postgresql合并string_agg函數(shù)的實例

    postgresql合并string_agg函數(shù)的實例

    這篇文章主要介紹了postgresql合并string_agg函數(shù)的實例,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • navicat無法連接postgreSQL-11的解決方案

    navicat無法連接postgreSQL-11的解決方案

    這篇文章主要介紹了navicat無法連接postgreSQL-11的解決方案,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2020-12-12
  • PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案

    PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案

    這篇文章主要介紹了PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • postgresql修改完端口后直接psql連接數(shù)據(jù)庫報錯的解決

    postgresql修改完端口后直接psql連接數(shù)據(jù)庫報錯的解決

    這篇文章主要介紹了postgresql修改完端口后直接psql連接數(shù)據(jù)庫報錯的解決,具有很好的參考價值,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • postgresql 刪除重復數(shù)據(jù)案例詳解

    postgresql 刪除重復數(shù)據(jù)案例詳解

    這篇文章主要介紹了postgresql 刪除重復數(shù)據(jù)案例詳解,本篇文章通過簡要的案例,講解了該項技術(shù)的了解與使用,以下就是詳細內(nèi)容,需要的朋友可以參考下
    2021-08-08
  • 使用PostgreSQL的JSONB數(shù)據(jù)類型進行高效查詢的示例代碼

    使用PostgreSQL的JSONB數(shù)據(jù)類型進行高效查詢的示例代碼

    PostgreSQL的JSONB數(shù)據(jù)類型提供了一種靈活的方式來存儲和查詢JSON格式的數(shù)據(jù),下面我們將詳細討論如何使用JSONB數(shù)據(jù)類型進行高效查詢,并提供相應的解決方案和示例代碼,需要的朋友可以參考下
    2024-04-04
  • PgSQL條件語句與循環(huán)語句示例代碼詳解

    PgSQL條件語句與循環(huán)語句示例代碼詳解

    這篇文章主要介紹了PgSQL條件語句與循環(huán)語句,pgSQL中有兩種條件語句分別為if與case語句,每種語句通過示例代碼給大家介紹的非常詳細,需要的朋友可以參考下
    2022-07-07
  • PostgreSQL教程(十九):SQL語言函數(shù)

    PostgreSQL教程(十九):SQL語言函數(shù)

    這篇文章主要介紹了PostgreSQL教程(十九):SQL語言函數(shù),本文講解了SQL語言函數(shù)基本概念、基本類型、復合類型、帶輸出參數(shù)的函數(shù)、返回結(jié)果作為表數(shù)據(jù)源等內(nèi)容,需要的朋友可以參考下
    2015-05-05

最新評論