卓刀_个人页

登录数据库后，可执行：
ALTER ROLE role_name CONNECTION LIMIT 500;
类似的SQL 语句来限制对应role的最大连接限制，例如这里是限制role_name 用户最大的连接数为500。当该role 的连接数超过500后，会报如下错误：
FATAL: too many connections for role "dyned"。

HybridDB for PostgreSQL 支持用户使用 PL/Java 语言，编写并上传 jar 软件包，并利用这些 jar 包创建用户自定义函数（UDF）。该功能支持的 PL/Java 语言版本为社区版 PL/Java 1.5.0，使用的 JVM 版本为 1.8。

其他详情可参见阿里云帮助文档 https://help.aliyun.com/document_detail/50594.html?spm=a2c4g.11186623.6.567.6Tklv5

如果出现了这种情况，可以根据提示：
1.停止数据库
2.postgres --single -D /usr/local/pgsql/data 单用户模式重启数据库
3.超级用户执行vacuum freeze mydb命令。

1.查询当前所有表的年龄，SQL 语句如下：

SELECT c.oid::regclass as table_name,
     greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');

2.查询所有数据库的年龄，SQL 语句如下：

SELECT datname, age(datfrozenxid) FROM pg_database;

3.设置vacuum_cost_delay为一个比较高的数值（例如50ms），这样可以减少普通vacuum对正常数据查询的影响
4.设置vacuum_freeze_table_age=0.5 * autovacuum_freeze_max_age，vacuum_freeze_min_age为原来值的0.1倍
5.对上面查询的表依次执行vacuum freeze，注意要预估好时间。

目前已经有很多实现好的开源PostgreSQL vacuum freeze监控管理工具，比如说flexible-freeze），它能够：
1.确定数据库的高峰和低峰期
2.在数据库低峰期创建一个cron job 去执行flexible_freeze.py
3.flexible_freeze.py 会自动对具有最老XID的表进行vacuum freeze

与vacuum freeze相关的参数主要有三个：

• vacuum_freeze_min_age
• vacuum_freeze_table_age
• autovacuum_freeze_max_age

vacuum_freeze_min_age 表示表中每个元组需要freeze的最小年龄。这里值得一提的是每次表被freeze 之后，会更新pg_class 中的relfrozenxid 列为本次freeze的XID。表年龄就是当前的最新的XID 与relfrozenxid的差值，而元组年龄可以理解为每个元组的t_xmin与relfrozenxid的差值。所以，这个参数也可以被简单理解为每个元组两次被freeze之间的XID 差值的一个最小值。增大该参数可以避免一些无用的freeze 操作，减小该参数可以使得在表必须被强制清理之前保留更多的XID 空间。该参数最大值为20亿，最小值为2亿。

普通的vacuum 使用visibility map来快速定位哪些数据页需要被扫描，只会扫描那些脏页，其他的数据页即使其中元组对应的xmin非常旧也不会被扫描。而在freeze的过程中，我们是需要对所有可见且未被all-frozen的数据页进行扫描，这个扫描过程PostgreSQL 称为aggressive vacuum。每次vacuum都去扫描每个表所有符合条件的数据页显然是不现实的，所以我们要选择合理的aggressive vacuum周期。PostgreSQL 引入了参数vacuum_freeze_table_age来决定这个周期。

vacuum_freeze_table_age 表示表的年龄大于该值时，会进行aggressive vacuum，即扫描表中可见且未被all-frozen的数据页。该参数最大值为20亿，最小值为1.5亿。如果该值为0，则每次扫描表都进行aggressive vacuum。

直到这里，我们可以看出：

• 当表的年龄超过vacuum_freeze_table_age则会aggressive vacuum
• 当元组的年龄超过vacuum_freeze_min_age后可以进行freeze

为了保证整个数据库的最老最新事务差不能超过20亿的原则，两次aggressive vacuum之间的新老事务差不能超过20亿，即两次aggressive vacuum之间表的年龄增长（vacuum_freeze_table_age）不能超过20亿减去vacuum_freeze_min_age（只有元组年龄超过vacuum_freeze_min_age才会被freeze）。但是看上面的参数，很明显不能绝对保证这个约束，为了解决这个问题，PostgreSQL 引入了autovacuum_freeze_max_age 参数。

autovacuum_freeze_max_age 表示如果当前最新的XID 减去元组的t_xmin 大于等于autovacuum_freeze_max_age，则元组对应的表会强制进行autovacuum，即使PostgreSQL已经关闭了autovacuum。该参数最小值为2亿，最大值为20亿。

也就是说，在经过autovacuum_freeze_max_age-vacuum_freeze_min_age的XID 增长之后，这个表肯定会被强制地进行 一次freeze。因为autovacuum_freeze_max_age最大值为20亿，所以说在两次freeze之间，XID 的增长肯定不会超过20亿，这就保证了上文中整个数据库的最老最新事务差不能超过20亿的原则。

值得一提的是，vacuum_freeze_table_age设置的值如果比autovacuum_freeze_max_age要高，则每次vacuum_freeze_table_age生效地时候，autovacuum_freeze_max_age已经生效，起不到过滤减少数据页扫描的作用。所以默认的规则，vacuum_freeze_table_age要设置的比autovacuum_freeze_max_age小。但是也不能太小，太小的话会造成频繁的aggressive vacuum。

另外我们通过分析源码可知，vacuum_freeze_table_age在最后应用时，会去取min(vacuum_freeze_table_age,0.95 autovacuum_freeze_max_age)。所以官方文档推荐vacuum_freeze_table_age=0.95 autovacuum_freeze_max_age。

freeze 操作会消耗大量的IO，对于不经常更新的表，可以合理地增大autovacuum_freeze_max_age和vacuum_freeze_min_age的差值。

但是如果设置autovacuum_freeze_max_age 和vacuum_freeze_table_age过大，因为需要存储更多的事务提交信息，会造成pg_xact 和 pg_commit 目录占用更多的空间。例如，我们把autovacuum_freeze_max_age设置为最大值20亿，pg_xact大约占500MB，pg_commit_ts大约是20GB（一个事务的提交状态占2位）。如果是对存储比较敏感的用户，也要考虑这点影响。

而减小vacuum_freeze_min_age则会造成vacuum 做很多无用的工作，因为当数据库freeze 了符合条件的row后，这个row很可能接着会被改变。理想的状态就是，当该行不会被改变，才去freeze 这行。

vacuum freeze 命令表示强制对表或数据库进行freeze 操作。freeze 操作是为了保证整个数据库的最老最新事务差不能超过20亿，从而防止事务ID 发生回卷。

在PostgreSQL 中，XID 是用32位无符号数来表示的，很容易发生溢出的情况，造成事务可见性混乱。为了解决这个问题，PostgreSQL:

• PostgreSQL中是使用2^31取模的方法来进行事务的比较
• 同一个数据库中，存在的最旧和最新两个事务之间的年龄最多是2^31，即20亿

freeze 操作会将表中过老的元组的xmin 赋值为特殊的XID FrozenTransactionId（3）。FrozenTransactionId为t_xmin的元组将会被其他所有的事务可见，这样该元组原来对应的XID 相当于被回收了，经过不断的处理，就可以控制一个数据库的最老的事务和最新的事务的年龄不超过20亿。

如果有笛卡尔乘积或者大表的排序或者hash表的操作，会使用临时文件来存储中间过程的数据。如果频繁的进行上述操作，临时文件会快速增长。重启PostgreSQL可以解决这个问题。另外建议用户设置temp_file_limit 为磁盘空间的10 %。

temp_file_limit的详细解释如下：
Enter temp_file_limit, a parameter added by Mark Kirkwood to PostgreSQL 9.2. This is a limit on per-session usage of temporary files for sorts, hashes, and similar operations.
If a user goes over the limit, their query gets cancelled and they see an error. This is an excellent way to prevent a single user, or a bad application change, from DOSing your database server. Set it to something high; I'm using 10GB or 20GB, or 10% of available disks space, whichever is less. But even a high limit like that will save you from some unexpected downtime.

with
t_wait as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.transactionid,a.mppsessionid,a.mppiswriter,a.gp_segment_id,
b.procpid,b.sess_id,b.waiting_reason,b.current_query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name

from pg_locks a,pg_stat_activity b where a.mppsessionid=b.sess_id and not a.granted   

),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.transactionid,a.mppsessionid,a.mppiswriter,a.gp_segment_id,
b.procpid,b.sess_id,b.waiting_reason,b.current_query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name

from pg_locks a,pg_stat_activity b where a.mppsessionid=b.sess_id and a.granted   

),
t_overlap as
(
select r.* from t_wait w join t_run r on
(

r.locktype is not distinct from w.locktype and   
r.database is not distinct from w.database and   
r.relation is not distinct from w.relation and   
r.page is not distinct from w.page and   
r.tuple is not distinct from w.tuple and   
r.transactionid is not distinct from w.transactionid and   
r.classid is not distinct from w.classid and   
r.objid is not distinct from w.objid and   
r.objsubid is not distinct from w.objsubid and   
r.mppsessionid <> w.mppsessionid   

)
),
t_unionall as
(
select r.* from t_overlap r
union all
select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,textin(xidout(transactionid)),classid::regclass,objid,objsubid,
string_agg(
'Gp_Segment_Id: '||case when gp_segment_id is null then 'NULL' else gp_segment_id::text end||chr(10)||
'MppIsWriter: '||case when mppiswriter is null then 'NULL' when mppiswriter is true then 'TRUE' else 'FALSE' end||chr(10)||
'MppSessionId: '||case when mppsessionid is null then 'NULL' else mppsessionid::text end||chr(10)||
'ProcPid: '||case when procpid is null then 'NULL' else procpid::text end||chr(10)||
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||
'Lock_Granted: '||case when granted is null then 'NULL' when granted is true then 'TRUE' else 'FALSE' end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , Waiting_Reason: '||case when waiting_reason is null then 'NULL' else waiting_reason::text end||chr(10)||
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||
'SQL (Current SQL in Transaction): '||chr(10)||
case when current_query is null then 'NULL' else current_query::text end,
chr(10)||'--------'||chr(10)
order by
( case mode

when 'INVALID' then 0   
when 'AccessShareLock' then 1   
when 'RowShareLock' then 2   
when 'RowExclusiveLock' then 3   
when 'ShareUpdateExclusiveLock' then 4   
when 'ShareLock' then 5   
when 'ShareRowExclusiveLock' then 6   
when 'ExclusiveLock' then 7   
when 'AccessExclusiveLock' then 8   
else 0   

end ) desc,
(case when granted then 0 else 1 end)
) as lock_conflict
from t_unionall
group by
locktype,datname,relation::regclass,page,tuple,textin(xidout(transactionid)),classid::regclass,objid,objsubid ;

SQL 语句如下：
SELECT

schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio

FROM

pg_stat_all_tables

WHERE

n_dead_tup >= 1000

ORDER BY dead_tup_ratio DESC;

建议使用RDS for PostgreSQL，您可以根据您的业务压力选择相应的规格。

这里涉及到了一个PostgreSQL存在的问题xid 回卷。在PostgreSQL中XID 是使用32位无符号数表示的，如果数据库不断的消耗XID，不引入这种freeze vacuum的机制，终有一天会造成xid 溢出，因为是无符号数，溢出的xid 反而比之前的xid 小了。而PostgreSQL 是使用tuple 的xmin 和xmax 来判断每个事务的可见性的，这种情况下可见性是非常混乱的。
为了解决这个问题，PostgreSQL 引入了freeze vacuum的机制。当表的最大xid大于等于autovacuum_freeze_max_age时，PostgreSQL会将表中的对应row的xmax的值设置为2，被认为是最老的事务，这样新的事务都能看到该row，完成freeze vacuum的目的。