性能優化和體系化運維 ——唐成

内容簡要：

一、作業系統優化

二、資料庫配置優化

三、日常操作

四、運維方案

作業系統優化主要從共享記憶體、參數設定、使用大頁、信号量和Limit參數等方面闡述。

（一）共享記憶體

當使用者使用PostgreSQL 9.3或GreenPlum5及以前的版本，為防止資料庫使用Swap，需先設定下面兩個參數：

Ø  kernel.shmmax = 16724692992

Ø  kernel.shmall = 4083177

從PostgreSQL 9.3開始，資料庫不再大量使用Sysv類型的共享記憶體，主要使用MMAP類型的共享記憶體，是以上面兩個參數設定一個較小的值，資料庫也可以啟動。

（二）參數設定

1.   vm.swappiness=0

在資料庫中需盡量避免使用Swap，是以将該參數值設定為0。

2.   Overcommit參數

通常情況下設定vm.overcommit_memory=2，即不讓系統超申請。通常情況下該值為0，表示申請的記憶體可以超過實體機記憶體。當大家都開始使用的時候，則會發生OOM，将一些程序給Cut掉，這在資料庫中是比較危險的情況，是以建議将vm.overcommit_memory設為2。

除此之外，需要設定vm.overcommit_ratio= 90，需要根據實際情況設定。

當設定完這兩個值後，可以申請的記憶體不超過：

swap的大小 + 實體記憶體* vm.overcommit_ratio

例如：一個256G記憶體的機器，16G Swap，應該把vm.overcommit_ratio= 93，這樣256*95%+16=254G，記憶體申請不可超過254G，如果超過的話則申請失敗。

（三）大頁

1.為什麼要使用大頁

使用大頁是因為頁表問題的存在，使用小頁會存在頁表占用過多記憶體的問題。

假設一台256G的機器，我們配置設定了共享記憶體為128G。如果是小頁，大小為4K，則有33554432頁表項，每項至少占用4位元組，則頁表大小32M*4=128M，如果有1024個連接配接，則頁表占用128M*1024=128G記憶體，占據機器總記憶體的一半。

如果使用2M大小的大頁表，則：則有128G/2M=65536項，65536*4=256K，1024個連接配接：1024*256k=256M記憶體，記憶體占用率大幅降低。

通常在 Linux作業系統裡面，建議使用大頁。

2.大頁配置

大頁參數設定：vm.nr_hugepages；

這個參數設定的值為多少，則有對應數量的2M大頁。大頁的大小需要與資料庫的Shared_buffer相一緻，如果比Shared_buffer大很多則會浪費資源。

大頁不會被Swap，預設Lock，即類似Oracle的lock_sga，且配置設定大頁記憶體後，及時不使用大頁，也不可做其他用途。

（四）信号量

PostgreSQL資料庫是多程序資料庫，程序和程序之間通路同一個共享記憶體時，需要各種各樣的“鎖”機制，通常信号量指的就是程序之間的“鎖”。需要設定kernel.sem=20 13000 20 650，參數的4個資料對應：SEMMSL、SEMMNS、SEMOPM、SEMMNI。

l  SEMMSL：信号集的最大信号量數，PostgreSQL要求大于17，取整數20， Oracle要求是250。

l  SEMMNS：整個系統範圍内的最大信号量數，是以SEMMNS = SEMMSL *SEMMNI。

l  SEMOPM：Semop函數在一次調用中所能操作一個信号量集中最大的信号量數，是以能常與SEMMSL相同。

l  SEMMNI：信号量集的最大數目，PostgreSQL資料庫中要求是資料庫程序數/16，假設允許10000個連接配接，即需要至少625，取一個整數650。這個程序數不隻是使用者服務程序，還需要包括一些管理的服務程序，如Autovacuum的Work程序。

（五）Limit參數

1./etc/security/limits.conf（軟/硬限制一樣）:

Ø  soft nofile 65536

Ø  hard nofile 65536（打開檔案的值）

Ø  soft nproc 131072

Ø  hard nproc 131072（程序數）

Ø  soft memlock -1

Ø  hard memlock -1 （記憶體）

2./etc/security/limits.d/20-nproc.conf

當設定了“/etc/security/limits.d/20-nproc.conf”時，參數有時候不一定生效，因為在不同的機器中可能還有個Limits.d，下面有個配置檔案優先級比limits.d/20-nproc.conf高，有些機器不一定是20，可能是其他的值。此時要将值設高一些，然後檢查底下limits.d下面這篇檔案中是否設定，如果沒有則要把這個值設高，如果設低的話，limits.d.conf裡設高也沒有用。

資料庫主要包含以下幾個參數：

l  Shared_buffer

1）小記憶體(32G)的機器上配置4GB~8GB即可；

2）小記憶體的機器（>32G)，配置8GB即可。

通常Shared_buffer配置4GB~8GB即可。PostgreSQL是使用這個檔案緩存做的，如果Shared_buffer設大，緩存有兩份。

l  Work_mem

1）通常保持預設的4MB即可；

2）如果機器記憶體很多，可以設定為64MB，通常不要太大，防止發生OOM。

l  Maintenance_work_mem:

可以在Session級别設定，當手工建索引或Vacuum慢時，可以把這個參數在Session級别調大。

l  Wal_buffers

通常保持預設值-1即可，-1表示會自動根據shared_buffer的大小而自動設定一個合适的大小，最大不要超過WAL檔案的大小，如16MB。

l  Max_connections

可以設定的大一些，如5000，因為修改這個參數需要重新開機機器。

時間上還有很多的其他參數，如一些逾時參數，防止長時間發呆的連接配接，防止長時間發呆的事務等，具體詳情可關注PostgreSQL中文社群的教育訓練認證考試PCA、PCP、PCM。

資料庫日常操作及對應語句：

l  檢視資料庫版本

select version();

l  檢視資料庫的啟動時間

select pg_postmaster_start_time();

l  檢視最後load配置檔案的時間

select pg_conf_load_time();

l  顯示資料庫時區

show timezone;

l  檢視有哪些資料庫

psql –l

l  檢視目前使用者

select user; select current_user, session_user;

current_user, session_user指不帶括号的函數。

l  檢視目前連接配接的資料庫名稱

select current_catalog, current_database ();

l  檢視目前用戶端的IP及端口

select inet_client_addr(), inet_client_port();

l  檢視目前資料庫伺服器的IP及端口

select inet_server_addr(), inet_server_port();

l  查詢目前session的背景服務程序的pid

select pg_backend_pid();

l  檢視參數配置

1）show shared_buffers

2）select current_setting('shared_buffers');

l  檢視目前正在寫的WAL檔案

1）9.X: select pg_xlogfile_name(pg_current_xlog_location());

2）>=10版本： select pg_walfile_name(pg_current_wal_lsn ());

l  檢視目前WAL的buffer還有多少位元組沒有刷到磁盤中

9.X:selectpg_xlog_location_diff(pg_current_xlog_insert_location(), pg_current_xlog_location());

>=10版本: : select pg_wal_lsn_diff(pg_current_wal_insert_lsn (), pg_current_wal_lsn ());

l  檢視資料庫執行個體是否正在做基礎備份

select pg_is_in_backup(), pg_backup_start_time() ;

l  目前資料庫執行個體是Hot Standby狀态還是正常資料庫狀态

1）select pg_is_in_recovery();

如果是備庫顯示true，否則是主庫。

2）pg_controldata |grep state

指控制檔案，在生産情況“in production”情況下是主庫，在恢複狀态下是備庫。

l  檢視資料庫的大小

select pg_database_size('osdba')；

l  檢視表的大小

1）select pg_size_pretty(pg_relation_size('ipdb2')) ;

2）select pg_size_pretty(pg_total_relation_size('ipdb2')) ;

l  檢視某個表上索引的大小

1）select pg_size_pretty(pg_indexes_size('ipdb2'));

2）“ipdb2”指表名。

l  檢視表空間的大小

1）select pg_size_pretty(pg_tablespace_size('pg_global'));

2）select pg_size_pretty(pg_tablespace_size('pg_default'));

l  檢視表對應的資料檔案

select pg_relation_filepath('test01');

l  讓配置生效

1）pg_ctl reload

2）select pg_reload_conf();

l  切換Log日志檔案

select pg_rotate_logfile();

l  切換WAL日志檔案

1）9.x: select pg_switch_xlog();

2）>=10版本： select pg_switch_wal();

l  手工産生checkpoint

checkpoint;

l  查詢正在運作的SQL（也能看到等待事件）

select * from pg_stat_activity;

l  取消一個長時間運作的查詢SQL（非DML）

select pg_cancel_backend(pid);

l  終止一個進行運作的SQL（包括DML)

select pg_terminate_backend(pid);

l  殺掉除自己之外的連接配接（危險）

Select usename,datname, client_addr, pg_terminate_backend(pid) from pg_stat_activity where pid<> pg_backend_pid();

l  檢視備庫

select * from pg_stat_replication;

l  暫停備庫的wal日志應用

select pg_xlog_replay_pause();

l  繼續備庫的wal日志應用

pg_xlog_replay_resume();

l  檢查備庫的wal日志應用是否暫停了

pg_is_xlog_replay_paused();

（一）制定運維整體方案

制定完善運維整體方案，包括運維環境監控、日常資料庫管理、資料庫備份與恢複、性能監控、性能調優。

運維環境監控:包括 CPU是否過高、IO是否過忙、網絡監控（網絡流量是否過大）、磁盤空間監控、資料庫年齡監控（如果資料年齡超了，資料庫會停止工作）、表和物化視圖上索引的數量、資料庫級的統計資訊。

日常資料庫管理：包括執行個體狀态檢查、PG監聽是否正常、WAL日志檢查（是否出現爆增還爆減）、表空間檢查、日志檢查（是否報錯）、備份有效性檢查的方法。

資料庫備份與恢複：包括備份政策設定、實體備份、邏輯備份（庫表小做邏輯備份）、備份腳本、恢複腳本或恢複操作過程、如何防止誤删除（是否架建延持備庫）。

性能監控：包括檢查等待事件、磁盤IO監控、TOP 10 SQL、資料庫的每秒查詢的行、插入的行、删除的行、更新的行。

性能調優：包括OS層面優化、PG參數優化、SQL優化、IO優化、架構優化：如讀寫分離、分庫分表。

上述工作都需要提前做好，以保證後續正常運維。

（二）運維的工作

日常運維工作包括:

• 表、索引、物化視圖、資料庫、表空間的大小，表空間剩餘可用空間；

• 資料庫年齡、表的年齡；

• 表，物化視圖的索引數量；

• 索引掃描次數；

• 表、物化視圖、索引膨脹位元組數，膨脹比例；

• Deadtuple；

• 序列剩餘次數；

• HA，備份，歸檔，備庫延遲狀态；

• 錯誤日志統計；

• 事件觸發器、觸發器的情況；

• Unlogged table的情況，如果是9.X版本之前，了解Hash Index情況；

• 鎖等待；

• 活躍度，Active, Idle, Idle in transaction狀态會話數，剩餘可用連接配接數；

• 帶事務号的長事務，2PC事務；

• 網卡使用率，CPU使用率，IO使用率，記憶體使用率；

• 慢SQL及當時的Analyze執行計劃；

• TOP SQL；

• 資料庫級别統計資訊：復原數，送出數，命中率，死鎖次數，IO TIME，Tuple DML次數。