postgresql 读书会 一期 系统管理的一些script 3

表的状态例如当前有多少live rows , index scans   等等都能帮助定义空和无用的表,根据一些基本的查询语句就能得到有效的验证,  pg_stat_user_tables 提供了这方面的功能和信息.

对于找到无用的index的问题,有两个点可以被用到, 个技术是找到索引重复的, 第二个是基于索引被访问到的状态.

WITH index_info AS

(SELECT pg_get_indexdef(indexrelid) AS index_def, indexrelid::regclass

index_name , indrelid::regclass table_name, array_agg(attname) AS index_att 

FROM  pg_index i 

JOIN pg_attribute a ON i.indexrelid = a.attrelid

GROUP BY pg_get_indexdef(indexrelid), indrelid, indexrelid

)

SELECT DISTINCT

CASE WHEN a.index_name > b.index_name THEN a.index_def

ELSE b.index_def END AS index_def,

CASE WHEN a.index_name > b.index_name THEN

a.index_name ELSE b.index_name END AS index_name,

CASE WHEN a.index_name > b.index_name THEN b.index_def

ELSE a.index_def END AS overlap_index_def,

CASE WHEN a.index_name > b.index_name THEN b.index_def

ELSE a.index_def END AS overlap_index_name,

a.table_name

FROM

index_info a INNER 

JOIN index_info b ON a.index_name != b.index_name

AND a.table_name = b.table_name AND a.index_att && b.index_att ;

上面这段寻找INDEX 重复的主要是基于INDEX  NAME 和创建INDEX语句的比对生成的. 所以在给出结果后,还需要人工来进行更细致的比对,不能进行直接的重复索引的清理.

另外在日常工作中经常有由于程序或相关人员误操作导致的一张表中的重复数据的问题.下图的表中name 是不应该重复的,现在我们要在已经有错误数据的表上加索引,由于有重复的数据则是不能进行的,如果这是在别的数据库上处理上要比PG麻烦,PG 本身具有ctid的物理属性, 所以借由这个属性就可以简单的清理重复的数据.

如果保留次输入的重复数据的天就使用min 如果是保留左后一次的输入,就选择max, 这里处理这个功能PG还是比较方便的.

对于当前的PG中语句之间的锁定,主要在于独占式的访问,access exclusive , 例如改动表的物理结构, alter ,drop , truncate ,vacuum full, cluster ,reindex 等等. 另一种语句之间的等待,有的时候一个语句会等待另一个语句很长时间,主要是locked 语句没有commited.

SELECT
lock1.pid as locked_pid,
stat1.usename as locked_user,
stat1.query as locked_statement,
stat1.state as state,
stat2.query as locking_statement,
stat2.state as state,
now() - stat1.query_start as locking_duration,
lock2.pid as locking_pid,
stat2.usename as locking_user
FROM pg_catalog.pg_locks lock1
JOIN pg_catalog.pg_stat_activity stat1 on lock1.pid = stat1.pid
JOIN pg_catalog.pg_locks lock2 on
(lock1.locktype,lock1.database,lock1.relation,
lock1.page,lock1.tuple,lock1.virtualxid,
lock1.transactionid,lock1.classid,lock1.objid,
lock1.objsubid) IS NOT DISTINCT FROM
(lock2.locktype,lock2.DATABASE,
lock2.relation,lock2.page,
lock2.tuple,lock2.virtualxid,
lock2.transactionid,lock2.classid,
lock2.objid,lock2.objsubid)
JOIN pg_catalog.pg_stat_activity stat2 on lock2.pid
= stat2.pid
WHERE NOT lock1.granted AND lock2.granted;

上面的语句可以查出类似的情况,帮助相关人员分析当前的PG的语句锁的情况.

下面就是查出两个语句15423 阻挡了 15226的执行

然后你就可以选择到底要删除那个语句是被锁的还是引起锁的.  通过 select pg_terminate_backend(pid) 将连接切断. 

后还有一个问题可能会被问及, 关于VIEW  和那些表有关系的问题, 例如问及现在的VIEW 都和那些表之间有关系,这并不是一个很容易回答的问题,尤其一个VIEW中有多个表参与合成.  下面的语句就会将VIEW 和表之间的关系打印出来.

SELECT view_schema,view_name parent, table_schema, table_name FROM
information_schema.view_table_usage WHERE view_name LIKE '_' order by
view_name;