PostgreSQL10.0preview功能增強-序列隔離級別預加鎖閾值可控

德哥發表於2017-04-22
SQLView

標籤

PostgreSQL , 10.0 , 序列隔離級別 , max_pred_locks_per_relation , 預加鎖

背景

PostgreSQL 序列隔離級別中一個重要的概念是預加鎖，在事務結束時，檢測預加鎖是否衝突。

10.0增加了三個控制引數，可以控制預加鎖的上限。避免記憶體過度使用。

1. 控制每個事務的物件預加鎖數量。

max_pred_locks_per_transaction (integer)

The shared predicate lock table tracks locks on max_pred_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables);

hence, no more than this many distinct objects can be locked at any one time.

This parameter controls the average number of object locks allocated for each transaction;

individual transactions can lock more objects as long as the locks of all transactions fit in the lock table.

This is not the number of rows that can be locked; that value is unlimited.

The default, 64, has generally been sufficient in testing, but you might need to raise this value if you have clients that touch many different tables in a single serializable transaction.
This parameter can only be set at server start.

2. 當單個物件的行或者頁預加鎖數量達到閾值時，升級為物件預加鎖。減少記憶體開銷。

max_pred_locks_per_relation (integer)

This controls how many pages or tuples of a single relation can be predicate-locked before the lock is promoted to covering the whole relation.

Values greater than or equal to zero mean an absolute limit, while negative values mean max_pred_locks_per_transaction divided by the absolute value of this setting.

The default is -2, which keeps the behaviour from previous versions of PostgreSQL.

This parameter can only be set in the postgresql.conf file or on the server command line.

3. 當單個頁內多少條記錄被加預加鎖時，升級為頁預加鎖。減少記憶體開銷。

max_pred_locks_per_page (integer)

This controls how many rows on a single page can be predicate-locked before the lock is promoted to covering the whole page.

The default is 2. This parameter can only be set in the postgresql.conf file or on the server command line.

關於序列隔離級別的概念，您可以參考

《PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce》

patch 資訊如下

Add GUCs for predicate lock promotion thresholds.  
  
author	Kevin Grittner <kgrittn@postgresql.org>	  
Sat, 8 Apr 2017 10:38:05 +0800 (21:38 -0500)  
committer	Kevin Grittner <kgrittn@postgresql.org>	  
Sat, 8 Apr 2017 10:38:05 +0800 (21:38 -0500)  
commit	c63172d60f242ad3581c83723a5b315bbe547a0e  
tree	0a98686f027aacecb01869bfb269ebd486ea3ba7	tree | snapshot  
parent	9c7f5229ad68d7e0e4dd149e3f80257893e404d4	commit | diff  
Add GUCs for predicate lock promotion thresholds.  
  
Defaults match the fixed behavior of prior releases, but now DBAs  
have better options to tune serializable workloads.  
  
It might be nice to be able to set this per relation, but that part  
will need to wait for another release.  
  
Author: Dagfinn Ilmari Mannsåker

這個patch的討論，詳見郵件組，本文末尾URL。

PostgreSQL社群的作風非常嚴謹，一個patch可能在郵件組中討論幾個月甚至幾年，根據大家的意見反覆的修正，patch合併到master已經非常成熟，所以PostgreSQL的穩定性也是遠近聞名的。

參考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c63172d60f242ad3581c83723a5b315bbe547a0e


相關文章