greenplum 5.7 + gp_toolkit

gp_toolkit 是 greenplum的一个功能schema。包含了大量实用的函数。

[gpadmin@node1 gpseg-1]$ psql -d peiybdb
psql (8.3.23)
Type "help" for help.

peiybdb=# \dnS+
                                                 List of schemas
        Name        |  Owner  | Access privileges  |                         Description                         
--------------------+---------+--------------------+-------------------------------------------------------------
 gp_toolkit         | gpadmin | gpadmin=UC/gpadmin | 
                              : =U/gpadmin           
 information_schema | gpadmin | gpadmin=UC/gpadmin | 
                              : =U/gpadmin           
 pg_aoseg           | gpadmin |                    | Reserved schema for Append Only segment list and eof tables
 pg_bitmapindex     | gpadmin |                    | Reserved schema for internal relations of bitmap indexes
 pg_catalog         | gpadmin | gpadmin=UC/gpadmin | system catalog schema
                              : =U/gpadmin           
 pg_toast           | gpadmin |                    | reserved schema for TOAST tables
 public             | gpadmin | gpadmin=UC/gpadmin | standard public schema
                              : =UC/gpadmin          
(7 rows)

gp_toolkit.gp_log_system

所有日志,实际上是一个外部表，列出了所有segment, master的csvlog的内容

peiybdb=# select * from gp_toolkit.gp_log_system;

gp_toolkit.gp_log_database

当前数据库日志

peiybdb=# select * from gp_toolkit.gp_log_database;

gp_toolkit.gp_disk_free

segment节点的磁盘情况

peiybdb=# select * from gp_toolkit.gp_disk_free;
 dfsegment | dfhostname |         dfdevice         | dfspace  
-----------+------------+--------------------------+----------
         0 |  node2     |  /dev/mapper/centos-root | 10183304
         1 |  node3     |  /dev/mapper/centos-root | 10183736
(2 rows)

gp_toolkit.gp_stats_missing

未收集统计信息的表

peiybdb=# select * from gp_toolkit.gp_stats_missing;
 smischema | smitable | smisize | smicols | smirecs 
-----------+----------+---------+---------+---------
(0 rows)

gp_toolkit.gp_bloat_diag

找出膨胀较厉害的表

peiybdb=# select * from gp_toolkit.gp_bloat_diag;
 bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages |              bdidiag               
----------+------------+------------+-------------+-------------+------------------------------------
     2618 | pg_catalog | pg_rewrite |          19 |           5 | moderate amount of bloat suspected
(1 row)

gp_toolkit.gp_bloat_expected_pages

找出所有对象的膨胀明细

peiybdb=# select * from gp_toolkit.gp_bloat_expected_pages;
 btdrelid | btdrelpages | btdexppages 
----------+-------------+-------------
     2620 |           1 |           2
     1260 |           1 |           2
     1255 |          20 |          17
    11840 |           1 |           2

参考:
https://greenplum.org/docs/570/common/gpdb-features.html
https://gpdb.docs.pivotal.io/570/main/index.html