PostgreSQL小記

 當前環境: 
PostgreSQL 9.5.1
CentOS 6.5
 

1.檢視當前客戶端連線數

#查詢當前的連線的pid
select pg_backend_pid();

#檢視錶結構，pg_stat_activity是一個系統檢視
\d+ pg_stat_activity  

#檢視當前連線總數
ps -ef|grep -v grep |grep postgres |grep "idle" | wc -l
select count(1) from pg_stat_activity WHERE NOT pid=pg_backend_pid();

#linux連線數程式pid,使用者名稱,應用名稱,client登入時間,執行時間,後臺是否等待一個lock,當前狀態,sql語句
select pid,usename ,application_name,backend_start,current_timestamp - least(query_start,xact_start) as runtime,waiting,state,query from pg_stat_activity;

#等待事件,根據阻塞的語句的會話PID做相應處理

SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement
FROM pg_locks bl
JOIN pg_stat_activity a ON a.pid = bl.pid
JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_stat_activity ka ON ka.pid = kl.pid WHERE NOT bl.granted;

#撤銷(需要時間)
SELECT pg_cancel_backend(30036); 

#殺掉程式(刪除postgresql的程式使用kill或者pg_terminate_backend()命令，不要使用kill -9)
kill pid
select pg_terminate_backend();

【參考】:
http://blog.csdn.net/luojinbai/article/details/44586917
https://my.oschina.net/Kenyon/blog/187143

2.常用命令
\h：檢視SQL命令的解釋，比如\h select。
\?：檢視psql命令列表。
\l：列出所有資料庫。 select oid,datname from pg_database;
\c [database_name]：連線其他資料庫。
\d：列出當前資料庫的所有表格。
\d [table_name]：列出某一張表格的結構。
\du：列出所有使用者。
\e：開啟文字編輯器。
\conninfo：列出當前資料庫和連線的資訊。

\dn 或者 \dnS 檢視當前database下的schema
\dt 檢視當前database的當前搜尋路徑下schema的表

配置search_path路徑,為了能夠找到schema的表等等
denali=# \dt region_longlive
No matching relations found.
denali=# SHOW search_path
denali-# ;
   search_path  
-----------------
 "$user", public
(1 row)

denali=# set search_path to region_longlive,public;
SET
denali=# SHOW search_path;
       search_path      
-------------------------
 region_longlive, public
(1 row)

denali=# \dt
                   List of relations
     Schema      |      Name      | Type  |    Owner   
-----------------+----------------+-------+-------------
 region_longlive | entity         | table | denaliadmin
 region_longlive | entity_result  | table | denaliadmin
 region_longlive | result         | table | denaliadmin
 region_longlive | result_history | table | denaliadmin
(4 rows)

denali=#

3.檢視size
#database size:
select pg_size_pretty(pg_database_size('postgres'));
\l+
\l+ <database_name>
select t1.datname AS db_name, pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1 order by pg_database_size(t1.datname) desc;

#shcema size:
SELECT schema_name,
    pg_size_pretty(sum(table_size)::bigint) as "disk space",
    (sum(table_size) / pg_database_size(current_database())) * 100
        as "percent"
FROM (
     SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
     FROM   pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace
             ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;

#table size:
\dt+
SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

【參考】:

4.修改配置引數
語法:
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
備註: 命令很簡單, 命令設定的引數值會寫入到一個名為 postgresql.auto.conf 的檔案,
這個檔案和配置檔案 postgresql.conf 不同, 它是二進位制檔案,不能手工編輯;
如果將引數值設定成 DEFAULT , 將在動態檔案 postgresql.auto.conf 刪除引數設定. 和之前一樣,引數設定後需要 reload 或者 重啟資料庫生效,這和之前版本是一樣的.

 

#查詢配置名稱和簡短說明
SELECT * FROM pg_settings; 
show all;

SELECT name, short_desc FROM pg_settings; 

#檢視系統中哪些選項被修改過。
select name, source, setting from pg_settings  where source != 'default'  and source != 'override'

#a.設定引數，當前session有效和檢視引數的值
postgres=# set work_mem='16MB';
SET
postgres=# show work_mem; 
 work_mem
----------
 16MB
(1 row)

 

#b.設定引數，永久有效
postgres=# show work_mem;
 work_mem
----------
 16MB
(1 row)

postgres=# alter system set  work_mem to '32MB';
ALTER SYSTEM
postgres=# show work_mem;
 work_mem
----------
 16MB
(1 row)

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show work_mem;
 work_mem
----------
 32MB
(1 row)

postgres=#

 

【參考】:
http://francs3.blog.163.com/blog/static/4057672720144194492582/
http://blog.csdn.net/dyx1024/article/details/6629776

 

5.linux的tcp配置
[root@sht-sgmhadoopcm-01 ~]# sysctl -a|grep tcp_keepalive
net.ipv4.tcp_keepalive_time = 7200
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_intvl = 75
[root@sht-sgmhadoopcm-01 ~]#
tcp_keepalive_time:    一個連線需要TCP開始傳送keepalive探測資料包之前的空閒時間。以秒為單位
tcp_keepalive_probes: 傳送TCP keepalive探測資料包的最大數量，預設是9.如果傳送9個keepalive探測包後對端仍然沒有響應，就關掉這個連線
tcp_keepalive_intvl:  傳送兩個TCP keepalive探測資料包的間隔時間，預設是75秒

 

【案例】:
系統核心引數配置

tcp_keepalive_time，在TCP保活開啟的情況下，最後一次資料交換到TCP傳送第一個保活探測包的間隔，即允許的持續空閒時長，或者說每次正常傳送心跳的週期，預設值為7200s（2h）。
tcp_keepalive_probes 在tcp_keepalive_time之後，沒有接收到對方確認，繼續傳送保活探測包次數，預設值為9（次）
tcp_keepalive_intvl，在tcp_keepalive_time之後，沒有接收到對方確認，繼續傳送保活探測包的傳送頻率，預設值為75s。
傳送頻率tcp_keepalive_intvl乘以傳送次數tcp_keepalive_probes，就得到了從開始探測到放棄探測確定連線斷開的時間

若設定，伺服器在客戶端連線空閒的時候，每90秒傳送一次保活探測包到客戶端，若沒有及時收到客戶端的TCP Keepalive ACK確認，將繼續等待15秒*2=30秒。
總之可以在90s+30s=120秒（兩分鐘）時間內可檢測到連線失效與否。

以下改動，需要寫入到/etc/sysctl.conf檔案：

net.ipv4.tcp_keepalive_time=90
net.ipv4.tcp_keepalive_intvl=15
net.ipv4.tcp_keepalive_probes=2
儲存退出，然後執行sysctl -p生效。可透過 sysctl -a | grep keepalive 命令檢測一下是否已經生效。

針對已經設定SO_KEEPALIVE的套接字，應用程式不用重啟，核心直接生效。

 

6.postgresql tcp配置
在網路上連線遠端伺服器postgresql時，不活動時間稍長就會自動斷開連線，不利於操作。

[postgres@sht-sgmhadoopcm-01 ~]$ cat /usr/local/pgsql/data/postgresql.conf |grep keepalives
#tcp_keepalives_idle = 0                # TCP_KEEPIDLE, in seconds;
#tcp_keepalives_interval = 0            # TCP_KEEPINTVL, in seconds;
#tcp_keepalives_count = 0               # TCP_KEEPCNT;
[postgres@sht-sgmhadoopcm-01 data]$

因為postgresql支援TCP_KEEPLIVE機制。
有三個系統變數tcp_keepalives_idle，tcp_keepalives_interval ，tcp_keepalives_count 來設定postgresql如何處理死連線。
在postgresql, 這三個引數都設為0將使用作業系統的預設值，在linux下，tcp_keepalives_idle一般是2個小時，也就是2個小時後，伺服器才可以自動關掉死連線。

在實際應運中，可以自行調整以上引數。
tcp_keepalives_idle = 30                # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10            # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10               # TCP_KEEPCNT;

引數對應:

 linux-tcp                                                     postgresql              
-------------------------------------------------------------------
tcp_keepalive_time       TCP_KEEPIDLE        tcp_keepalives_idle
tcp_keepalive_probes    TCP_KEEPCNT         tcp_keepalives_count
tcp_keepalive_intvl        TCP_KEEPINTVL      tcp_keepalives_interval

【參考】:
http://blog.csdn.net/yohoph/article/details/41512935


http://www.blogjava.net/yongboy/archive/2015/04/14/424413.html

7.postgresql引數配置(調優--記憶體)

share_buffers
該引數主要是跟作業系統的共享記憶體有關，簡單的說，就是不要超過實體記憶體的總量。
相關作業系統的(linux)下，使用 cat /etc/sysctl.conf 命令後，檢視兩個屬性kernel.shmall和kernel.shmmax

temp_buffers
這個引數很簡單，就是設定連線資料庫的session大小。

work_mem
這個引數主要用於查詢條件帶排序的，或者是hash表等相關的操作。work_mem是一個連線session獨享的，不是多個連線session共享的。

effective_cache_size
是postgresql能夠使用的最大快取，這個數字對於獨立的pgsql伺服器而言應該足夠大，比如4G的記憶體，可以設定為3.5G(437500)

maintenance_work_mem
該引數主要是一個database session在執行相關操作時所需要的空間容量。

max_connections
通常，max_connections的目的是防止max_connections * work_mem超出了實際記憶體大小。
比如，如果將work_mem設定為實際記憶體的2%大小，則在極端情況下，如果有50個查詢都有排序要求，而且都使 用2％的記憶體，則會導致swap的產生，系統效能就會大大降低。
當然，如果有4G的記憶體，同時出現50個如此大的查詢的機率應該是很小的。不過，要清楚 max_connections和work_mem的關係
假如有幾千個連線，則需要考慮使用連線池管理軟體，來減少併發連線的開銷。

wal_buffers
 WAL共享資料存 儲 器使用的記憶體量。 這個引數要求足夠大，如果太小的話， log關 聯的磁碟操作過頻繁。

 

【記憶體使用相關】：

   shared_buffers   wal_buffers   work_mem   effective_cache_size

   計算公式：記憶體使用量 = work_mem * max_connections + shared_buffers

   (不過，對此我並不是非常認可，實際上會有很大誤差，試想如果shared_buffers好work_mem都很小，而你卻成功讀取一個非常大的表的情景)

【磁碟I/O 相關】：

   checkpoint_segments   checkpoint_timeout   checkpoint_completion_target   shared_buffers

 

【參考】:
http://blog.csdn.net/found2008/article/details/6825576
http://blog.csdn.net/kyle__shaw/article/details/17576259

http://blog.csdn.net/chenyi8888/article/details/7019348

http://www.cnblogs.com/gaojian/p/topindex.html

http://blog.csdn.net/xing1989/article/details/8919280