postgressql數(shù)據(jù)源怎么配置

PostgresSQL提供了許多數(shù)據(jù)庫(kù)配置參數(shù)，本章將介紹每個(gè)參數(shù)的作用和如何配置每一個(gè)參數(shù)。

目前創(chuàng)新互聯(lián)建站已為上千家的企業(yè)提供了網(wǎng)站建設(shè)、域名、雅安服務(wù)器托管、網(wǎng)站托管、服務(wù)器托管、企業(yè)網(wǎng)站設(shè)計(jì)、大連網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(fā)展。

10.1 如何設(shè)置數(shù)據(jù)庫(kù)參數(shù)

所有的參數(shù)的名稱都是不區(qū)分大小寫的。每個(gè)參數(shù)的取值是布爾型、整型、浮點(diǎn)型和字符串型這四種類型中的一個(gè)，分別用boolean

、integer、 floating point和string表示。布爾型的值可以寫成ON、OFF、 TRUE、 FALSE、 YES、 NO、 1和 0，而且不區(qū)分大小

寫。

有些參數(shù)用來(lái)配置內(nèi)存大小和時(shí)間值。內(nèi)存大小的單位可以是KB、MB和GB。時(shí)間的單位可以是毫秒、秒、分鐘、小時(shí)和天。用ms表示

毫秒，用s表示秒，用 min表示分鐘，用h表示小時(shí)，用d表示天。表示內(nèi)存大小和時(shí)間值的參數(shù)參數(shù)都有一個(gè)默認(rèn)的單位，如果用戶

在設(shè)置參數(shù)的值時(shí)沒(méi)有指定單位，則以參數(shù)默認(rèn)的 單位為準(zhǔn)。例如，參數(shù)shared_buffers表示數(shù)據(jù)緩沖區(qū)的大小，它的默認(rèn)單位是

數(shù)據(jù)塊的個(gè)數(shù)，如果把它的值設(shè)成8，因?yàn)槊總€(gè)數(shù)據(jù)塊的大小是 8KB，則數(shù)據(jù)緩沖區(qū)的大小是8*8=64KB，如果將它的值設(shè)成128MB，

則數(shù)據(jù)緩沖區(qū)的大小是128MB。參數(shù)vacuum_cost_delay 的默認(rèn)單位是毫秒，如果把它的值設(shè)成10，則它的值是10毫秒，如果把它的

值設(shè)成100s，則它的值是100秒。

所有的參數(shù)都放在文件 postgresql.conf中，下面是一個(gè)文件實(shí)例:

#這是注釋

log_connections = yes

log_destination = 'syslog'

search_path = '"$user", public'

每一行只能指定一個(gè)參數(shù)，空格和空白行都會(huì)被忽略。“ #”表示注釋，注釋信息不用單獨(dú)占一行，可以出現(xiàn)在配置文件的任何地方

。如果參數(shù)的值不是簡(jiǎn)單的標(biāo)識(shí)符和數(shù)字，應(yīng)該用單引號(hào)引起來(lái)。如果參數(shù)的值中有單引號(hào)，應(yīng)該寫兩個(gè)單引號(hào)，或者在單引號(hào)前面

加一個(gè)反斜杠。

一個(gè)配置文件也可以包含其它配置文件，使用include指令能夠達(dá)到這個(gè)目的，例如，假設(shè)postgresql.conf文件中有下面一行：

include ‘my.confg’

文件my.config中的配置信息也會(huì)被數(shù)據(jù)庫(kù)讀入。include指令指定的配置文件也可以用include指令再包含其它配置文件。如果

include指令中指定的文件名不是絕對(duì)路徑，數(shù)據(jù)庫(kù)會(huì)在postgresql.conf文件所在的目錄下查找這個(gè)文件。

用戶也可以在數(shù)據(jù)庫(kù)啟動(dòng)以后修改postgresql.conf配置文件，使用命令pg_ctl reload來(lái)通知數(shù)據(jù)庫(kù)重新讀取配置文件。注意，有些

參數(shù)在數(shù)據(jù)庫(kù)啟動(dòng)以后，不能被修改，只有重新啟動(dòng)數(shù)據(jù)庫(kù)以后，新的參數(shù)值才能生效。另外一些參數(shù)可 以在數(shù)據(jù)庫(kù)運(yùn)行過(guò)程中被

修改而且新的值可以立即生效。所以數(shù)據(jù)庫(kù)在運(yùn)行過(guò)程中重新讀取參數(shù)配置文件以后，不是所有的參數(shù)都會(huì)被賦給新的值。

用戶可以在自己建立的會(huì)話中執(zhí)行命令SET修改某些配置參數(shù)的值（注意不是全部參數(shù)），例如：

SET ENABLE_SEQSCAN TO OFF;

另外，有些參數(shù)只有數(shù)據(jù)庫(kù)超級(jí)用戶才能使用SET命令修改它們。用戶可以在psql中執(zhí)行命令show來(lái)查看所有的數(shù)據(jù)庫(kù)參數(shù)的當(dāng)前值

。例如：

（1）show all; --查看所有數(shù)據(jù)庫(kù)參數(shù)的值

（2）show search_path; --查看參數(shù)search_path的值

10.2 連接與認(rèn)證

10.2.1 連接設(shè)置

listen_addresses (string)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。它指定數(shù)據(jù)庫(kù)用來(lái)監(jiān)聽(tīng)客戶端連接的TCP/IP地址。默認(rèn)是值是* ，表示數(shù)據(jù)庫(kù)在啟動(dòng)以

后將在運(yùn)行數(shù)據(jù)的機(jī)器上的所有的IP地址上監(jiān)聽(tīng)用戶請(qǐng)求（如果機(jī)器只有一個(gè)網(wǎng)卡，只有一個(gè)IP地址，有多個(gè)網(wǎng)卡的機(jī)器有多個(gè) IP

地址）。可以寫成機(jī)器的名字，也可以寫成IP地址，不同的值用逗號(hào)分開(kāi)，例如，’server01’, ’140.87.171.49, 140.87.171.21

’。如果被設(shè)成localhost，表示數(shù)據(jù)庫(kù)只能接受本地的客戶端連接請(qǐng)求，不能接受遠(yuǎn)程的客戶端連接請(qǐng)求。

port (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。它指定數(shù)據(jù)庫(kù)監(jiān)聽(tīng)?wèi)舳诉B接的TCP端口。默認(rèn)值是5432。

max_connections (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。它決定數(shù)據(jù)庫(kù)可以同時(shí)建立的最大的客戶端連接的數(shù)目。默認(rèn)值是100。

superuser_reserved_connections (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。它表示預(yù)留給超級(jí)用戶的數(shù)據(jù)庫(kù)連接數(shù)目。它的值必須小于max_connections。 普通用

戶可以在數(shù)據(jù)庫(kù)中建立的最大的并發(fā)連接的數(shù)目是max_connections- superuser_reserved_connections， 默認(rèn)值是3。

unix_socket_group (string)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。設(shè)置Unix-domain socket所在的操作系統(tǒng)用戶組。默認(rèn)值是空串，用啟動(dòng)數(shù)據(jù)庫(kù)的操作

系統(tǒng)用戶所在的組作為Unix-domain socket的用戶組。

unix_socket_permissions (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。它設(shè)置Unix-domain socket的訪問(wèn)權(quán)限，格式與操作系統(tǒng)的文件訪問(wèn)權(quán)限是一樣的。默

認(rèn)值是0770，表示任何操作系統(tǒng)用戶都能訪問(wèn)Unix-domain socket。可以設(shè)為0770（所有Unix-domain socket文件的所有者所在的組

包含的用戶都能訪問(wèn)）和0700(只有Unix-domain socket文件的所有者才能訪問(wèn))。對(duì)于Unix-domain socket，只有寫權(quán)限才有意義，

讀和執(zhí)行權(quán)限是沒(méi)有意義的。

tcp_keepalives_idle (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。默認(rèn)值是0，意思是使用操作系統(tǒng)的默認(rèn)值。它設(shè)置TCP套接字的TCP_KEEPIDLE屬性。這個(gè)參數(shù)對(duì)于

通過(guò)Unix-domain socket建立的數(shù)據(jù)庫(kù)連接沒(méi)有任何影響。

tcp_keepalives_interval (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。默認(rèn)值是0，意思是使用操作系統(tǒng)的默認(rèn)值。它設(shè)置TCP套接字的TCP_KEEPINTVL屬性。這個(gè)參數(shù)對(duì)

于通過(guò)Unix-domain socket建立的數(shù)據(jù)庫(kù)連接沒(méi)有任何影響。

tcp_keepalives_count (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。默認(rèn)值是0，意思是使用操作系統(tǒng)的默認(rèn)值。它設(shè)置TCP套接字的TCP_KEEPCNT屬性。這個(gè)參數(shù)對(duì)于

通過(guò)Unix-domain socket建立的數(shù)據(jù)庫(kù)連接沒(méi)有任何影響。

10.2.2. 安全與認(rèn)證

authentication_timeout (integer)

這個(gè)參數(shù)只能在postgresql.conf文件中被設(shè)置，它指定一個(gè)時(shí)間長(zhǎng)度，在這個(gè)時(shí)間長(zhǎng)度內(nèi)，必須完成客戶端認(rèn)證操作，否則客戶端

連接請(qǐng)求將被拒絕。它可以阻止某些客戶端進(jìn)行認(rèn)證時(shí)長(zhǎng)時(shí)間占用數(shù)據(jù)庫(kù)連接。單位是秒，默認(rèn)值是60。

ssl (boolean)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。決定數(shù)據(jù)庫(kù)是否接受SSL連接。默認(rèn)值是off。

ssl_ciphers (string)

指定可以使用的SSL加密算法。查看操作系統(tǒng)關(guān)于openssl的用戶手冊(cè)可以得到完整的加密算法列表（執(zhí)行命令openssl ciphers –v

也可以得到）。

10.3 資源消耗

10.3.1 內(nèi)存

shared_buffers (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。它表示數(shù)據(jù)緩沖區(qū)中的數(shù)據(jù)塊的個(gè)數(shù)，每個(gè)數(shù)據(jù)塊的大小是8KB。數(shù)據(jù)緩沖區(qū)位于數(shù)據(jù)

庫(kù)的共享內(nèi)存中，它越大越好，不能小于128KB。默認(rèn)值是1024。

temp_buffers (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。默認(rèn)值是8MB。它決定存放臨時(shí)表的數(shù)據(jù)緩沖區(qū)中的數(shù)據(jù)塊的個(gè)數(shù)，每個(gè)數(shù)據(jù)塊的大小是8KB。臨時(shí)

表緩沖區(qū)存放在每個(gè)數(shù)據(jù)庫(kù)進(jìn)程的私有內(nèi)存中，而不是存放在數(shù)據(jù)庫(kù)的共享內(nèi)存中。默認(rèn)值是1024。

max_prepared_transactions (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。它決定能夠同時(shí)處于prepared狀態(tài)的事務(wù)的最大數(shù)目（參考PREPARE TRANSACTION命令

）。如果它的值被設(shè)為0。則將數(shù)據(jù)庫(kù)將關(guān)閉prepared事務(wù)的特性。它的值通常應(yīng)該和max_connections的值 一樣大。默認(rèn)值是5。

work_mem (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。它決定數(shù)據(jù)庫(kù)的排序操作和哈希表使用的內(nèi)存緩沖區(qū)的大小。如何work_mem指定的內(nèi)存被耗盡，數(shù)

據(jù)庫(kù)將使用磁盤文件進(jìn) 行完成操作，速度會(huì)慢很多。ORDER BY、DISTINCT和merge連接會(huì)使用排序操作。哈希表在Hash連接、hash聚

集函數(shù)和用哈希表來(lái)處理IN謂詞中的子查詢中被使用。單位是 KB，默認(rèn)值是1024。

maintenance_work_mem (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。它決定數(shù)據(jù)庫(kù)的維護(hù)操作使用的內(nèi)存空間的大小。數(shù)據(jù)庫(kù)的維護(hù)操作包括VACUUM、CREATE INDEX和

ALTER TABLE ADD FOREIGN KEY等操作。 maintenance_work_mem的值如果比較大，通常可以縮短VACUUM數(shù)據(jù)庫(kù)和從dump文件中恢復(fù)數(shù)

據(jù)庫(kù)需要的時(shí)間。 maintenance_work_mem存放在每個(gè)數(shù)據(jù)庫(kù)進(jìn)程的私有內(nèi)存中，而不是存放在數(shù)據(jù)庫(kù)的共享內(nèi)存中。單位是KB，默

認(rèn)值是16384。

max_stack_depth (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置，但只有數(shù)據(jù)庫(kù)超級(jí)用戶才能修改它。它決定一個(gè)數(shù)據(jù)庫(kù)進(jìn)程在運(yùn)行時(shí)的STACK所占的空間的最大值

。數(shù)據(jù)庫(kù)進(jìn)程在運(yùn)行時(shí)，會(huì) 自動(dòng)檢查自己的STACK大小是否超過(guò)max_stack_depth，如果超過(guò)，會(huì)自動(dòng)終止當(dāng)前事務(wù)。這個(gè)值應(yīng)該比

操作系統(tǒng)設(shè)置的進(jìn)程STACK的大小 的上限小1MB。使用操作系統(tǒng)命令“ulimit –s“可以得到操作系統(tǒng)設(shè)置的進(jìn)程STACK的最大值。單

位是KB，默認(rèn)值是100。

10.3.2 Free Space Map

數(shù)據(jù)庫(kù)的所有可用空間信息都存放在一個(gè)叫free space map (FSM)的結(jié)構(gòu)中，它記載數(shù)據(jù)文件中每個(gè)數(shù)據(jù)塊的可用空間的大小。FSM

中沒(méi)有記錄的數(shù)據(jù)塊，即使有可用空間，也不會(huì)系統(tǒng)使用。系統(tǒng)如果需要新的物理存 儲(chǔ)空間，會(huì)首先在FSM中查找，如果FSM中沒(méi)有

一個(gè)數(shù)據(jù)頁(yè)有足夠的可用空間，系統(tǒng)就會(huì)自動(dòng)擴(kuò)展數(shù)據(jù)文件。所以，F(xiàn)SM如果太小，會(huì)導(dǎo)致系統(tǒng)頻繁地?cái)U(kuò)展數(shù) 據(jù)文件，浪費(fèi)物理存儲(chǔ)

空間。命令VACUUM VERBOSE在執(zhí)行結(jié)束以后，會(huì)提示當(dāng)前的FSM設(shè)置是否滿足需要，如果FSM的參數(shù)值太小，它會(huì)提示增大參數(shù)。

FSM存放在數(shù)據(jù)庫(kù)的共享內(nèi)存中，由于物理內(nèi)存的限制，F(xiàn)SM不可能跟蹤數(shù)據(jù)庫(kù)的所有的數(shù)據(jù)文件的所有數(shù)據(jù)塊的可用空間信息，只能

跟蹤一部分?jǐn)?shù)據(jù)塊的可用空間信息。

max_fsm_relations (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。默認(rèn)值是1000。它決定FSM跟蹤的表和索引的個(gè)數(shù)的上限。每個(gè)表和索引在FSM中占7個(gè)

字節(jié)的存儲(chǔ)空間。

max_fsm_pages (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。它決定FSM中跟蹤的數(shù)據(jù)塊的個(gè)數(shù)的上限。initdb在創(chuàng)建數(shù)據(jù)庫(kù)集群時(shí)會(huì)根據(jù)物理內(nèi)存

的大小決定它的值。每 個(gè)數(shù)據(jù)塊在fsm中占6個(gè)字節(jié)的存儲(chǔ)空間。它的大小不能小于16 * max_fsm_relations。默認(rèn)值是20000。

10.3.3 內(nèi)核資源

max_files_per_process (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。他設(shè)定每個(gè)數(shù)據(jù)庫(kù)進(jìn)程能夠打開(kāi)的文件的數(shù)目。默認(rèn)值是1000。

shared_preload_libraries (string)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。它設(shè)置數(shù)據(jù)庫(kù)在啟動(dòng)時(shí)要加載的操作系統(tǒng)共享庫(kù)文件。如果有多個(gè)庫(kù)文件，名字用逗號(hào)

分開(kāi)。如果數(shù)據(jù)庫(kù)在啟動(dòng)時(shí)未找到shared_preload_libraries指定的某個(gè)庫(kù)文件，數(shù)據(jù)庫(kù)將無(wú)法啟動(dòng)。默認(rèn)值為空串。

10.3.4 垃圾收集

執(zhí)行VACUUM 和ANALYZE命令時(shí)，因?yàn)樗鼈儠?huì)消耗大量的CPU與IO資源，而且執(zhí)行一次要花很長(zhǎng)時(shí)間，這樣會(huì)干擾系統(tǒng)執(zhí)行應(yīng)用程序發(fā)

出的SQL命令。為了解決這個(gè) 問(wèn)題，VACUUM 和ANALYZE命令執(zhí)行一段時(shí)間后，系統(tǒng)會(huì)暫時(shí)終止它們的運(yùn)行，過(guò)一段時(shí)間后再繼續(xù)執(zhí)行

這兩個(gè)命令。這個(gè)特性在默認(rèn)的情況下是關(guān)閉的。將參數(shù) vacuum_cost_delay設(shè)為一個(gè)非零的正整數(shù)就可以打開(kāi)這個(gè)特性。

用戶通常只需要設(shè)置參數(shù)vacuum_cost_delay和vacuum_cost_limit，其它的參數(shù)使用默認(rèn)值即可。VACUUM 和ANALYZE命令在執(zhí)行過(guò)程

中，系統(tǒng)會(huì)計(jì)算它們執(zhí)行消耗的資源，資源的數(shù)量用一個(gè)正整數(shù)表示，如果資源的數(shù)量超過(guò) vacuum_cost_limit，則執(zhí)行命令的進(jìn)程

會(huì)進(jìn)入睡眠狀態(tài)，睡眠的時(shí)間長(zhǎng)度是是vacuum_cost_delay。 vacuum_cost_limit的值越大，VACUUM 和ANALYZE命令在執(zhí)行的過(guò)程中

，睡眠的次數(shù)就越少，反之，vacuum_cost_limit的值越小，VACUUM 和ANALYZE命令在執(zhí)行的過(guò)程中，睡眠的次數(shù)就越多。

vacuum_cost_delay (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。默認(rèn)值是0。它決定執(zhí)行VACUUM 和ANALYZE命令的進(jìn)程的睡眠時(shí)間。單位是微秒。它的值最好是10

的整數(shù)，如果不是10的整數(shù)，系統(tǒng)會(huì)自動(dòng)將它設(shè)為比該值大的并且最接近該值的是10 的倍數(shù)的整數(shù)。如果值是0，VACUUM 和ANALYZE

命令在執(zhí)行過(guò)程中不會(huì)主動(dòng)進(jìn)入睡眠狀態(tài)，會(huì)一直執(zhí)行下去直到結(jié)束。

vacuum_cost_page_hit (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。默認(rèn)值是1。

vacuum_cost_page_miss (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。默認(rèn)值是10。

vacuum_cost_page_dirty (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。默認(rèn)值是20。

vacuum_cost_limit (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。默認(rèn)值是200。

10.3.5 后臺(tái)寫數(shù)據(jù)庫(kù)進(jìn)程

后臺(tái)寫數(shù)據(jù)庫(kù)進(jìn)程負(fù)責(zé)將數(shù)據(jù)緩沖區(qū)中的被修改的數(shù)據(jù)塊（又叫臟數(shù)據(jù)塊）寫回到數(shù)據(jù)庫(kù)物理文件中。

bgwriter_delay (integer)

這個(gè)參數(shù)只能在文件postgresql.conf中設(shè)置。它決定后臺(tái)寫數(shù)據(jù)庫(kù)進(jìn)程的睡眠時(shí)間。后臺(tái)寫數(shù)據(jù)庫(kù)進(jìn)程每次完成寫數(shù)據(jù)到物理文件

中的任務(wù)以后， 就會(huì)睡眠bgwriter_delay指定的時(shí)間。 bgwriter_delay的值應(yīng)該是10的倍數(shù)，如果用戶設(shè)定的值不是10的倍數(shù)，數(shù)

據(jù)庫(kù)會(huì)自動(dòng)將參數(shù)的值設(shè)為比用戶指定的值大的最接近用戶指定的值 的同時(shí)是10的倍數(shù)的值。單位是毫秒，默認(rèn)值是200。

bgwriter_lru_maxpages (integer)

這個(gè)參數(shù)只能在文件postgresql.conf中設(shè)置。默認(rèn)值是100。后臺(tái)寫數(shù)據(jù)庫(kù)進(jìn)程每次寫臟數(shù)據(jù)塊時(shí)，寫到外部文件中的臟數(shù)據(jù)塊的個(gè)

數(shù)不能超過(guò) bgwriter_lru_maxpages指定的值。例如，如果它的值是500，則后臺(tái)寫數(shù)據(jù)庫(kù)進(jìn)程每次寫到物理文件的數(shù)據(jù)頁(yè)的個(gè)數(shù)不

能超過(guò)500，若 超過(guò)，進(jìn)程將進(jìn)入睡眠狀態(tài)，等下次醒來(lái)再執(zhí)行寫物理文件的任務(wù)。如果它的值被設(shè)為0, 后臺(tái)寫數(shù)據(jù)庫(kù)進(jìn)程將不會(huì)

寫任何物理文件（但還會(huì)執(zhí)行檢查點(diǎn)操作）。

bgwriter_lru_multiplier (floating point)

這個(gè)參數(shù)只能在文件postgresql.conf中設(shè)置。默認(rèn)值是2.0。它決定后臺(tái)寫數(shù)據(jù)庫(kù)進(jìn)程每次寫物理文件時(shí)，寫到外部文件中的臟數(shù)據(jù)

塊的個(gè)數(shù) （不能超過(guò)bgwriter_lru_maxpages指定的值）。一般使用默認(rèn)值即可，不需要修改這個(gè)參數(shù)。這個(gè)參數(shù)的值越大，后臺(tái)寫

數(shù)據(jù)庫(kù)進(jìn)程每次寫 的臟數(shù)據(jù)塊的個(gè)數(shù)就越多。

10.4 事務(wù)日志

full_page_writes (boolean)

這個(gè)參數(shù)只能在postgresql.conf文件中被設(shè)置。默認(rèn)值是on。打開(kāi)這個(gè)參數(shù)，可以提高數(shù)據(jù)庫(kù)的可靠性，減少數(shù)據(jù)丟失的概率，但

是會(huì)產(chǎn)生過(guò)多的事務(wù)日志，降低數(shù)據(jù)庫(kù)的性能。

wal_buffers (integer)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。默認(rèn)值是8。它指定事務(wù)日志緩沖區(qū)中包含的數(shù)據(jù)塊的個(gè)數(shù)，每個(gè)數(shù)據(jù)塊的大小是8KB，

所以默認(rèn)的事務(wù)日志緩沖區(qū)的大小是8*8=64KB。事務(wù)日志緩沖區(qū)位于數(shù)據(jù)庫(kù)的共享內(nèi)存中。

wal_writer_delay (integer)

這個(gè)參數(shù)只能在postgresql.conf文件中被設(shè)置。它決定寫事務(wù)日志進(jìn)程的睡眠時(shí)間。WAL進(jìn)程每次在完成寫事務(wù)日志的任務(wù)后，就會(huì)

睡眠 wal_writer_delay指定的時(shí)間，然后醒來(lái)，繼續(xù)將新產(chǎn)生的事務(wù)日志從緩沖區(qū)寫到WAL文件中。單位是毫秒（millisecond），

默認(rèn) 值是200。

commit_delay (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。它設(shè)定事務(wù)在發(fā)出提交命令以后的睡眠時(shí)間，只有在睡眠了commit_delay指定的時(shí)間以后，事務(wù)產(chǎn)

生的事務(wù)日志才會(huì) 被寫到事務(wù)日志文件中，事務(wù)才能真正地提交。增大這個(gè)參數(shù)會(huì)增加用戶的等待時(shí)間，但是可以讓多個(gè)事務(wù)被同

時(shí)提交，提高系統(tǒng)的性能。如果數(shù)據(jù)庫(kù)中的負(fù)載比較 高，而且大部分事務(wù)都是更新類型的事務(wù)，可以考慮增大這個(gè)參數(shù)的值。下面

的參數(shù)commit_siblings會(huì)影響commit_delay是否生效。 默認(rèn)值是0，單位是微秒（microsecond）。

commit_siblings (integer)

這個(gè)參數(shù)可以在任何時(shí)候被設(shè)置。這個(gè)參數(shù)的值決定參數(shù)commit_delay是否生效。假設(shè)commit_siblings的值是5，如果一個(gè)事務(wù)發(fā)出

一個(gè)提交請(qǐng)求，此時(shí)，如果數(shù)據(jù)庫(kù)中正在執(zhí)行的事務(wù)的個(gè)數(shù)大于或等于5，那么該事務(wù)將睡眠commit_delay指定的時(shí)間。如果數(shù)據(jù)庫(kù)

中正在執(zhí)行的事務(wù) 的個(gè)數(shù)小于5，這個(gè)事務(wù)將直接提交。默認(rèn)值是5。

10.5 檢查點(diǎn)

checkpoint_segments (integer)

這個(gè)參數(shù)只能在postgresql.conf文件中被設(shè)置。默認(rèn)值是3。它影響系統(tǒng)何時(shí)啟動(dòng)一個(gè)檢查點(diǎn)操作。如果上次檢查點(diǎn)操作結(jié)束以后，

系統(tǒng)產(chǎn)生的事 務(wù)日志文件的個(gè)數(shù)超過(guò)checkpoint_segments的值，系統(tǒng)就會(huì)自動(dòng)啟動(dòng)一個(gè)檢查點(diǎn)操作。增大這個(gè)參數(shù)會(huì)增加數(shù)據(jù)庫(kù)崩

潰以后恢復(fù)操作需要的時(shí) 間。

checkpoint_timeout (integer)

這個(gè)參數(shù)只能在postgresql.conf文件中被設(shè)置。單位是秒，默認(rèn)值是300。它影響系統(tǒng)何時(shí)啟動(dòng)一個(gè)檢查點(diǎn)操作。如果現(xiàn)在的時(shí)間減

去上次檢查 點(diǎn)操作結(jié)束的時(shí)間超過(guò)了checkpoint_timeout的值，系統(tǒng)就會(huì)自動(dòng)啟動(dòng)一個(gè)檢查點(diǎn)操作。增大這個(gè)參數(shù)會(huì)增加數(shù)據(jù)庫(kù)崩

潰以后恢復(fù)操作需要的時(shí) 間。

checkpoint_completion_target (floating point)

這個(gè)參數(shù)控制檢查點(diǎn)操作的執(zhí)行時(shí)間。合法的取值在0到1之間，默認(rèn)值是0.5。不要輕易地改變這個(gè)參數(shù)的值，使用默認(rèn)值即可。 這

個(gè)參數(shù)只能在postgresql.conf文件中被設(shè)置。

10.6 歸檔模式

archive_mode (boolean)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。默認(rèn)值是off。它決定數(shù)據(jù)庫(kù)是否打開(kāi)歸檔模式。

archive_dir (string)

這個(gè)參數(shù)只有在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，才能被設(shè)置。默認(rèn)值是空串。它設(shè)定存放歸檔事務(wù)日志文件的目錄。

archive_timeout (integer)

這個(gè)參數(shù)只能在postgresql.conf文件中被設(shè)置。默認(rèn)值是0。單位是秒。如果archive_timeout的值不是0，而且當(dāng)前時(shí)間減去數(shù) 據(jù)

庫(kù)上次進(jìn)行事務(wù)日志文件切換的時(shí)間大于archive_timeout的值，數(shù)據(jù)庫(kù)將進(jìn)行一次事務(wù)日志文件切換。一般情況下，數(shù)據(jù)庫(kù)只有在

一個(gè)事務(wù)日志 文件寫滿以后，才會(huì)切換到下一個(gè)事務(wù)日志文件，設(shè)定這個(gè)參數(shù)可以讓數(shù)據(jù)庫(kù)在一個(gè)事務(wù)日志文件尚未寫滿的情況下

切換到下一個(gè)事務(wù)日志文件。

10.7 優(yōu)化器參數(shù)

10.7.1 存取方法參數(shù)

下列參數(shù)控制查詢優(yōu)化器是否使用特定的存取方法。除非對(duì)優(yōu)化器特別了解，一般情況下，使用它們默認(rèn)值即可。

enable_bitmapscan (boolean)

打開(kāi)或者關(guān)閉bitmap-scan 。默認(rèn)值是 on。

enable_hashagg (boolean)

打開(kāi)或者關(guān)閉hashed aggregation。默認(rèn)值是 on。

enable_hashjoin (boolean)

打開(kāi)或者關(guān)閉hash-join。默認(rèn)值是 on。

enable_indexscan (boolean)

打開(kāi)或者關(guān)閉index-scan。默認(rèn)值是 on。

enable_mergejoin (boolean)

打開(kāi)或者關(guān)閉merge-join。默認(rèn)值是 on。

enable_nestloop (boolean)

打開(kāi)或者關(guān)閉nested-loop join。默認(rèn)值是 on。不可能完全不使用nested-loop join，關(guān)閉這個(gè)參數(shù)會(huì)讓系統(tǒng)在有其它存取方法可

用的情況下，不使用nested-loop join。

enable_seqscan (boolean)

打開(kāi)或者關(guān)閉sequential scan。默認(rèn)值是 on。不可能完全不使用sequential scan，關(guān)閉這個(gè)參數(shù)會(huì)讓系統(tǒng)在有其它存取方法可用

的情況下，不使用sequential scan。

postgresql的連接超時(shí)問(wèn)題，誰(shuí)知道在連接字符串寫什么關(guān)鍵字可以讓連接永不超時(shí)

NpgsqlConneciton, NpgsqlCommand都有CommandTimeout屬性值，單位為毫秒，默認(rèn)值為90秒。如果命令執(zhí)行時(shí)間超過(guò)CommandTimeout值，就會(huì)發(fā)生timeout錯(cuò)誤，即執(zhí)行時(shí)間超出設(shè)定時(shí)間。

所以，解決方式就是將CommandTimeout值設(shè)置足夠大。

如何提高postgresql查詢性能

一、使用EXPLAIN：

PostgreSQL為每個(gè)查詢都生成一個(gè)查詢規(guī)劃，因?yàn)檫x擇正確的查詢路徑對(duì)性能的影響是極為關(guān)鍵的。PostgreSQL本身已經(jīng)包含了一個(gè)規(guī)劃器用于尋找最優(yōu)規(guī)劃，我們可以通過(guò)使用EXPLAIN命令來(lái)查看規(guī)劃器為每個(gè)查詢生成的查詢規(guī)劃。

PostgreSQL中生成的查詢規(guī)劃是由1到n個(gè)規(guī)劃節(jié)點(diǎn)構(gòu)成的規(guī)劃樹(shù)，其中最底層的節(jié)點(diǎn)為表掃描節(jié)點(diǎn)，用于從數(shù)據(jù)表中返回檢索出的數(shù)據(jù)行。然而，不同

的掃描節(jié)點(diǎn)類型代表著不同的表訪問(wèn)模式，如：順序掃描、索引掃描，以及位圖索引掃描等。如果查詢?nèi)匀恍枰B接、聚集、排序，或者是對(duì)原始行的其它操作，那

么就會(huì)在掃描節(jié)點(diǎn)"之上"有其它額外的節(jié)點(diǎn)。并且這些操作通常都有多種方法，因此在這些位置也有可能出現(xiàn)不同的節(jié)點(diǎn)類型。EXPLAIN將為規(guī)劃樹(shù)中的每

個(gè)節(jié)點(diǎn)都輸出一行信息，顯示基本的節(jié)點(diǎn)類型和規(guī)劃器為執(zhí)行這個(gè)規(guī)劃節(jié)點(diǎn)計(jì)算出的預(yù)計(jì)開(kāi)銷值。第一行(最上層的節(jié)點(diǎn))是對(duì)該規(guī)劃的總執(zhí)行開(kāi)銷的預(yù)計(jì)，這個(gè)數(shù)

值就是規(guī)劃器試圖最小化的數(shù)值。

這里有一個(gè)簡(jiǎn)單的例子，如下：

復(fù)制代碼 代碼如下:

EXPLAIN SELECT * FROM tenk1;

QUERY PLAN

-------------------------------------------------------------

Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)

EXPLAIN引用的數(shù)據(jù)是：

1). 預(yù)計(jì)的啟動(dòng)開(kāi)銷(在輸出掃描開(kāi)始之前消耗的時(shí)間，比如在一個(gè)排序節(jié)點(diǎn)里做排續(xù)的時(shí)間)。

2). 預(yù)計(jì)的總開(kāi)銷。

3). 預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)輸出的行數(shù)。

4). 預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)的行平均寬度(單位：字節(jié))。

這里開(kāi)銷(cost)的計(jì)算單位是磁盤頁(yè)面的存取數(shù)量，如1.0將表示一次順序的磁盤頁(yè)面讀取。其中上層節(jié)點(diǎn)的開(kāi)銷將包括其所有子節(jié)點(diǎn)的開(kāi)銷。這里的輸出

行數(shù)(rows)并不是規(guī)劃節(jié)點(diǎn)處理/掃描的行數(shù)，通常會(huì)更少一些。一般而言，頂層的行預(yù)計(jì)數(shù)量會(huì)更接近于查詢實(shí)際返回的行數(shù)。

現(xiàn)在我們執(zhí)行下面基于系統(tǒng)表的查詢：

復(fù)制代碼 代碼如下:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

從查詢結(jié)果中可以看出tenk1表占有358個(gè)磁盤頁(yè)面和10000條記錄，然而為了計(jì)算cost的值，我們?nèi)匀恍枰懒硗庖粋€(gè)系統(tǒng)參數(shù)值。

復(fù)制代碼 代碼如下:

postgres=# show cpu_tuple_cost;

cpu_tuple_cost

----------------

0.01

(1 row)

cost = 358(磁盤頁(yè)面數(shù)) + 10000(行數(shù)) * 0.01(cpu_tuple_cost系統(tǒng)參數(shù)值)

下面我們?cè)賮?lái)看一個(gè)帶有WHERE條件的查詢規(guī)劃。

復(fù)制代碼 代碼如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 7000;

QUERY PLAN

------------------------------------------------------------

Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)

Filter: (unique1 7000)

EXPLAIN的輸出顯示，WHERE子句被當(dāng)作一個(gè)"filter"應(yīng)用，這表示該規(guī)劃節(jié)點(diǎn)將掃描表中的每一行數(shù)據(jù)，之后再判定它們是否符合過(guò)濾的條

件，最后僅輸出通過(guò)過(guò)濾條件的行數(shù)。這里由于WHERE子句的存在，預(yù)計(jì)的輸出行數(shù)減少了。即便如此，掃描仍將訪問(wèn)所有10000行數(shù)據(jù)，因此開(kāi)銷并沒(méi)有

真正降低，實(shí)際上它還增加了一些因數(shù)據(jù)過(guò)濾而產(chǎn)生的額外CPU開(kāi)銷。

上面的數(shù)據(jù)只是一個(gè)預(yù)計(jì)數(shù)字，即使是在每次執(zhí)行ANALYZE命令之后也會(huì)隨之改變，因?yàn)锳NALYZE生成的統(tǒng)計(jì)數(shù)據(jù)是通過(guò)從該表中隨機(jī)抽取的樣本計(jì)算的。

如果我們將上面查詢的條件設(shè)置的更為嚴(yán)格一些的話，將會(huì)得到不同的查詢規(guī)劃，如：

復(fù)制代碼 代碼如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 100;

QUERY PLAN

------------------------------------------------------------------------------

Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

這里，規(guī)劃器決定使用兩步規(guī)劃，最內(nèi)層的規(guī)劃節(jié)點(diǎn)訪問(wèn)一個(gè)索引，找出匹配索引條件的行的位置，然后上層規(guī)劃節(jié)點(diǎn)再?gòu)谋砝镒x取這些行。單獨(dú)地讀取數(shù)據(jù)行比順

序地讀取它們的開(kāi)銷要高很多，但是因?yàn)椴⒎窃L問(wèn)該表的所有磁盤頁(yè)面，因此該方法的開(kāi)銷仍然比一次順序掃描的開(kāi)銷要少。這里使用兩層規(guī)劃的原因是因?yàn)樯蠈右?guī)

劃節(jié)點(diǎn)把通過(guò)索引檢索出來(lái)的行的物理位置先進(jìn)行排序，這樣可以最小化單獨(dú)讀取磁盤頁(yè)面的開(kāi)銷。節(jié)點(diǎn)名稱里面提到的"位圖(bitmap)"是進(jìn)行排序的機(jī)

制。

現(xiàn)在我們還可以將WHERE的條件設(shè)置的更加嚴(yán)格，如：

復(fù)制代碼 代碼如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 3;

QUERY PLAN

------------------------------------------------------------------------------

Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)

Index Cond: (unique1 3)

在該SQL中，表的數(shù)據(jù)行是以索引的順序來(lái)讀取的，這樣就會(huì)令讀取它們的開(kāi)銷變得更大，然而事實(shí)上這里將要獲取的行數(shù)卻少得可憐，因此沒(méi)有必要在基于行的物理位置進(jìn)行排序了。

現(xiàn)在我們需要向WHERE子句增加另外一個(gè)條件，如：

復(fù)制代碼 代碼如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 3 AND stringu1 = 'xxx';

QUERY PLAN

------------------------------------------------------------------------------

Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)

Index Cond: (unique1 3)

Filter: (stringu1 = 'xxx'::name)

新增的過(guò)濾條件stringu1 = 'xxx'只是減少了預(yù)計(jì)輸出的行數(shù)，但是并沒(méi)有減少實(shí)際開(kāi)銷，因?yàn)槲覀內(nèi)匀恍枰L問(wèn)相同數(shù)量的數(shù)據(jù)行。而該條件并沒(méi)有作為一個(gè)索引條件，而是被當(dāng)成對(duì)索引結(jié)果的過(guò)濾條件來(lái)看待。

如果WHERE條件里有多個(gè)字段存在索引，那么規(guī)劃器可能會(huì)使用索引的AND或OR的組合，如：

復(fù)制代碼 代碼如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 100 AND unique2 9000;

QUERY PLAN

-------------------------------------------------------------------------------------

Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)

Recheck Cond: ((unique1 100) AND (unique2 9000))

- BitmapAnd (cost=11.27..11.27 rows=11 width=0)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)

Index Cond: (unique2 9000)

這樣的結(jié)果將會(huì)導(dǎo)致訪問(wèn)兩個(gè)索引，與只使用一個(gè)索引，而把另外一個(gè)條件只當(dāng)作過(guò)濾器相比，這個(gè)方法未必是更優(yōu)。

現(xiàn)在讓我們來(lái)看一下基于索引字段進(jìn)行表連接的查詢規(guī)劃，如：

復(fù)制代碼 代碼如下:

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;

QUERY PLAN

--------------------------------------------------------------------------------------

Nested Loop (cost=2.37..553.11 rows=106 width=488)

- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

- Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)

Index Cond: ("outer".unique2 = t2.unique2)

從查詢規(guī)劃中可以看出(Nested

Loop)該查詢語(yǔ)句使用了嵌套循環(huán)。外層的掃描是一個(gè)位圖索引，因此其開(kāi)銷與行計(jì)數(shù)和之前查詢的開(kāi)銷是相同的，這是因?yàn)闂l件unique1

100發(fā)揮了作用。 這個(gè)時(shí)候t1.unique2 =

t2.unique2條件子句還沒(méi)有產(chǎn)生什么作用，因此它不會(huì)影響外層掃描的行計(jì)數(shù)。然而對(duì)于內(nèi)層掃描而言，當(dāng)前外層掃描的數(shù)據(jù)行將被插入到內(nèi)層索引掃描

中，并生成類似的條件t2.unique2 = constant。所以，內(nèi)層掃描將得到和EXPLAIN SELECT * FROM tenk2

WHERE unique2 = 42一樣的計(jì)劃和開(kāi)銷。最后，以外層掃描的開(kāi)銷為基礎(chǔ)設(shè)置循環(huán)節(jié)點(diǎn)的開(kāi)銷，再加上每個(gè)外層行的一個(gè)迭代(這里是 106

* 3.01)，以及連接處理需要的一點(diǎn)點(diǎn)CPU時(shí)間。

如果不想使用嵌套循環(huán)的方式來(lái)規(guī)劃上面的查詢，那么我們可以通過(guò)執(zhí)行以下系統(tǒng)設(shè)置，以關(guān)閉嵌套循環(huán)，如：

復(fù)制代碼 代碼如下:

SET enable_nestloop = off;

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;

QUERY PLAN

------------------------------------------------------------------------------------------

Hash Join (cost=232.61..741.67 rows=106 width=488)

Hash Cond: ("outer".unique2 = "inner".unique2)

- Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)

- Hash (cost=232.35..232.35 rows=106 width=244)

- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

這個(gè)規(guī)劃仍然試圖用同樣的索引掃描從tenk1里面取出符合要求的100行，并把它們存儲(chǔ)在內(nèi)存中的散列(哈希)表里，然后對(duì)tenk2做一次全表順序掃

描，并為每一條tenk2中的記錄查詢散列(哈希)表，尋找可能匹配t1.unique2 =

t2.unique2的行。讀取tenk1和建立散列表是此散列聯(lián)接的全部啟動(dòng)開(kāi)銷，因?yàn)槲覀冊(cè)陂_(kāi)始讀取tenk2之前不可能獲得任何輸出行。

此外，我們還可以用EXPLAIN ANALYZE命令檢查規(guī)劃器預(yù)估值的準(zhǔn)確性。這個(gè)命令將先執(zhí)行該查詢，然后顯示每個(gè)規(guī)劃節(jié)點(diǎn)內(nèi)實(shí)際運(yùn)行時(shí)間，以及單純EXPLAIN命令顯示的預(yù)計(jì)開(kāi)銷，如：

復(fù)制代碼 代碼如下:

EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------

Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)

- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37

rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)

Index Cond: (unique1 100)

- Index Scan using tenk2_unique2 on tenk2 t2

(cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1

loops=100)

Index Cond: ("outer".unique2 = t2.unique2)

Total runtime: 14.452 ms

注意"actual time"數(shù)值是以真實(shí)時(shí)間的毫秒來(lái)計(jì)算的，而"cost"預(yù)估值是以磁盤頁(yè)面讀取數(shù)量來(lái)計(jì)算的，所以它們很可能是不一致的。然而我們需要關(guān)注的只是兩組數(shù)據(jù)的比值是否一致。

在一些查詢規(guī)劃里，一個(gè)子規(guī)劃節(jié)點(diǎn)很可能會(huì)運(yùn)行多次，如之前的嵌套循環(huán)規(guī)劃，內(nèi)層的索引掃描會(huì)為每個(gè)外層行執(zhí)行一次。在這種情況下，"loops"將報(bào)告

該節(jié)點(diǎn)執(zhí)行的總次數(shù)，而顯示的實(shí)際時(shí)間和行數(shù)目則是每次執(zhí)行的平均值。這么做的原因是令這些真實(shí)數(shù)值與開(kāi)銷預(yù)計(jì)顯示的數(shù)值更具可比性。如果想獲得該節(jié)點(diǎn)所

花費(fèi)的時(shí)間總數(shù)，計(jì)算方式是用該值乘以"loops"值。

EXPLAIN ANALYZE顯示的"Total runtime"包括執(zhí)行器啟動(dòng)和關(guān)閉的時(shí)間，以及結(jié)果行處理的時(shí)間，但是它并不包括分析、重寫或者規(guī)劃的時(shí)間。

如果EXPLAIN命令僅能用于測(cè)試環(huán)境，而不能用于真實(shí)環(huán)境，那它就什么用都沒(méi)有。比如，在一個(gè)數(shù)據(jù)較少的表上執(zhí)行EXPLAIN，它不能適用于數(shù)量很

多的大表，因?yàn)橐?guī)劃器的開(kāi)銷計(jì)算不是線性的，因此它很可能對(duì)大些或者小些的表選擇不同的規(guī)劃。一個(gè)極端的例子是一個(gè)只占據(jù)一個(gè)磁盤頁(yè)面的表，在這樣的表

上，不管它有沒(méi)有索引可以使用，你幾乎都總是得到順序掃描規(guī)劃。規(guī)劃器知道不管在任何情況下它都要進(jìn)行一個(gè)磁盤頁(yè)面的讀取，所以再增加幾個(gè)磁盤頁(yè)面讀取用

以查找索引是毫無(wú)意義的。

二、批量數(shù)據(jù)插入：

有以下幾種方法用于優(yōu)化數(shù)據(jù)的批量插入。

1. 關(guān)閉自動(dòng)提交：

在批量插入數(shù)據(jù)時(shí)，如果每條數(shù)據(jù)都被自動(dòng)提交，當(dāng)中途出現(xiàn)系統(tǒng)故障時(shí)，不僅不能保障本次批量插入的數(shù)據(jù)一致性，而且由于有多次提交操作的發(fā)生，整個(gè)插入效

率也會(huì)受到很大的打擊。解決方法是，關(guān)閉系統(tǒng)的自動(dòng)提交，并且在插入開(kāi)始之前，顯示的執(zhí)行begin

transaction命令，在全部插入操作完成之后再執(zhí)行commit命令提交所有的插入操作。

2. 使用COPY：

使用COPY在一條命令里裝載所有記錄，而不是一系列的INSERT命令。COPY命令是為裝載數(shù)量巨大的數(shù)據(jù)行優(yōu)化過(guò)的，它不像INSERT命令那樣靈

活，但是在裝載大量數(shù)據(jù)時(shí)，系統(tǒng)開(kāi)銷也要少很多。因?yàn)镃OPY是單條命令，因此在填充表的時(shí)就沒(méi)有必要關(guān)閉自動(dòng)提交了。

3. 刪除索引：

如果你正在裝載一個(gè)新創(chuàng)建的表，最快的方法是創(chuàng)建表，用COPY批量裝載，然后創(chuàng)建表需要的任何索引。因?yàn)樵谝汛嬖跀?shù)據(jù)的表上創(chuàng)建索引比維護(hù)逐行增加要快。當(dāng)然在缺少索引期間，其它有關(guān)該表的查詢操作的性能將會(huì)受到一定的影響，唯一性約束也有可能遭到破壞。

4. 刪除外鍵約束：

和索引一樣，"批量地"檢查外鍵約束比一行行檢查更加高效。因此，我們可以先刪除外鍵約束，裝載數(shù)據(jù)，然后在重建約束。

5. 增大maintenance_work_mem：

在裝載大量數(shù)據(jù)時(shí)，臨時(shí)增大maintenance_work_mem系統(tǒng)變量的值可以改進(jìn)性能。這個(gè)系統(tǒng)參數(shù)可以提高CREATE

INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的執(zhí)行效率，但是它不會(huì)對(duì)COPY操作本身產(chǎn)生多大的影響。

6. 增大checkpoint_segments：

臨時(shí)增大checkpoint_segments系統(tǒng)變量的值也可以提高大量數(shù)據(jù)裝載的效率。這是因?yàn)樵谙騊ostgreSQL裝載大量數(shù)據(jù)時(shí)，將會(huì)導(dǎo)致

檢查點(diǎn)操作(由系統(tǒng)變量checkpoint_timeout聲明)比平時(shí)更加頻繁的發(fā)生。在每次檢查點(diǎn)發(fā)生時(shí)，所有的臟數(shù)據(jù)都必須flush到磁盤上。

通過(guò)提高checkpoint_segments變量的值，可以有效的減少檢查點(diǎn)的數(shù)目。

7. 事后運(yùn)行ANALYZE：

在增加或者更新了大量數(shù)據(jù)之后，應(yīng)該立即運(yùn)行ANALYZE命令，這樣可以保證規(guī)劃器得到基于該表的最新數(shù)據(jù)統(tǒng)計(jì)。換句話說(shuō)，如果沒(méi)有統(tǒng)計(jì)數(shù)據(jù)或者統(tǒng)計(jì)數(shù)據(jù)太過(guò)陳舊，那么規(guī)劃器很可能會(huì)選擇一個(gè)較差的查詢規(guī)劃，從而導(dǎo)致查詢效率過(guò)于低下。

postgresql 毫秒轉(zhuǎn)日期

強(qiáng)轉(zhuǎn)下吧 轉(zhuǎn)成日期

cast(字段名 as date)

或者可以用substr截取

討論P(yáng)ostgreSQL 和其他數(shù)據(jù)庫(kù)的差異在哪里

PostgreSQL 近幾年在全球的人氣不斷攀升，每年發(fā)布的版本都體現(xiàn)了社區(qū)的活力，9.6作為里程碑式的作品，更加有非常多的新特性加入。

例如

1. 多核并行計(jì)算

2. FDW 下推join, sort, where clause.

3. snapshot too old

4. 檢查點(diǎn)平滑fsync

5. vacuum freeze加速

6. sharding base on fdw

7. 分詞增強(qiáng)，支持相鄰phrases搜索，據(jù)說(shuō)比ES用起來(lái)還爽。

8. scale-up 多核增強(qiáng), 72HT的機(jī)器tpc-b select only達(dá)到了180萬(wàn)的tps.

9. 推出等待事件統(tǒng)計(jì)信息

10. 支持多副本同步復(fù)制，滿足金融級(jí)可靠性要求

11. 聚合復(fù)用SFUNC，多個(gè)聚合如果INIT和SFUNC一致的話，可以節(jié)約非常多的運(yùn)算開(kāi)銷。

12. 事務(wù)idle超時(shí)機(jī)制

還有很多，可以在 release notes頁(yè)面查找

除此之外，社區(qū)開(kāi)發(fā)的一些特性也很吸引人，例如

1. rum插件，支持文本相似度查詢，效率嘛10億級(jí)別TOKEN，毫秒級(jí)響應(yīng)，比搜索引擎還好用，具體見(jiàn)云棲社區(qū)的測(cè)試文章。

2. LLVM版本的PostgreSQL，對(duì)大數(shù)據(jù)量的表達(dá)式處理性能提升非常明顯。也是大數(shù)據(jù)處理慣用的手法，例如Impala。雖然PostgreSQL的定位是OLTP，但不代表它不能處理OLAP的請(qǐng)求，而且Gartner去年就提出了HTAP的數(shù)據(jù)庫(kù)概念，指即能處理TP有能處理AP的數(shù)據(jù)庫(kù)產(chǎn)品，PostgreSQL的特性可見(jiàn)一斑。

3. 10.0版本已經(jīng)加入的聚合算子下推，你是不是開(kāi)始浮想聯(lián)翩了呢？

更多的插件可以到github , pgxn.org , pgfoundry ,

分享題目：包含postgresql毫秒的詞條
網(wǎng)頁(yè)路徑：http://vcdvsql.cn/article44/dsdgeee.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設(shè)、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站排名、虛擬主機(jī)、企業(yè)建站、網(wǎng)站改版

廣告

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