Greenplum或DeepGreen数据库查看表倾斜的方法总结

http://blog.csdn.net/jiangshouzhuang/article/details/51792580

上次有个朋友咨询我一个GP数据倾斜的问题，他说查看gp_toolkit.gp_skew_coefficients表时花费了20-30分钟左右才出来结果，后来指导他分析原因并给出其他方案来查看数据倾斜。

 

目前他使用的版本是最新的版本为：

Greenplum Version: 'postgres (Greenplum Database) 4.3.8.2 build 1'

 

其实很多朋友经常使用如下的方式来检查数据分布：

[sql]  view plain  copy

 

1. my_db_safe=# select gp_segment_id,count(1) from person_info group by 1;  
2.   
3.  gp_segment_id | count    
4.   
5. ---------------+--------  
6.   
7.             24 | 470585  
8.   
9.              0 | 470323  
10.   
11.             17 | 469175  
12.   
13.             18 | 468792  
14.   
15.             34 | 470394  
16.   
17.              3 | 469311  
18.   
19.             16 | 469157  
20.   
21.             32 | 470151  
22.   
23.             14 | 470724  
24.   
25.             29 | 470236  
26.   
27.              5 | 469697  
28.   
29.             25 | 470528  
30.   
31.              1 | 468895  
32.   
33.             11 | 470279  
34.   
35.             37 | 469776  
36.   
37.             21 | 470287  
38.   
39.              2 | 469164  
40.   
41.             26 | 470661  
42.   
43.              7 | 470072  
44.   
45.             31 | 469813  
46.   
47.             12 | 470014  
48.   
49.             22 | 470453  
50.   
51.             27 | 470984  
52.   
53.             33 | 470499  
54.   
55.             38 | 470528  
56.   
57.             13 | 470828  
58.   
59.             28 | 470931  
60.   
61.              4 | 469175  
62.   
63.              9 | 470253  
64.   
65.             10 | 470922  
66.   
67.              8 | 470305  
68.   
69.             23 | 470118  
70.   
71.             39 | 470619  
72.   
73.             20 | 470565  
74.   
75.             36 | 470086  
76.   
77.             15 | 471373  
78.   
79.             35 | 469284  
80.   
81.             19 | 468486  
82.   
83.              6 | 471063  
84.   
85.             30 | 468198  
86.   
87. (40 rows)  

 

但是这种方法太简单，只有判断存储是否倾斜，不能够去对数据处理是否会出现倾斜做出判断。而且判断的维度很少，不直观。

 

后来Greenplum提供了gp_toolkit.gp_skew_coefficients等工具来进行检查判断。

 

首先我们来看一下gp_toolkit.gp_skew_coefficients这个视图的逻辑：

[sql]  view plain  copy

 

1. my_db_safe=# \d+ gp_toolkit.gp_skew_coefficients  
2.   
3.            View "gp_toolkit.gp_skew_coefficients"  
4.   
5.     Column    |  Type   | Modifiers | Storage | Description   
6.   
7. --------------+---------+-----------+---------+-------------  
8.   
9.  skcoid       | oid     |           | plain   |   
10.   
11.  skcnamespace | name    |           | plain   |   
12.   
13.  skcrelname   | name    |           | plain   |   
14.   
15.  skccoeff     | numeric |           | main    |   
16.   
17. View definition:  
18.   
19.  SELECT skew.skewoid AS skcoid, pgn.nspname AS skcnamespace, pgc.relname AS skcrelname, skew.skewval AS skccoeff  
20.   
21.    FROM gp_toolkit.__gp_skew_coefficients() skew(skewoid, skewval)  
22.   
23.    JOIN pg_class pgc ON skew.skewoid = pgc.oid  
24.   
25.    JOIN pg_namespace pgn ON pgc.relnamespace = pgn.oid;  

 

当我们使用视图gp_toolkit.gp_skew_coefficients来检查表数据倾斜时，该视图会基于表的行数据量来检查，如果表数据量越大，检查时间就会越长。

[sql]  view plain  copy

 

1. my_db_safe=# select * from  gp_toolkit.gp_skew_coefficients;  
2.   
3.  skcoid | skcnamespace |      skcrelname      |          skccoeff             
4.   
5. --------+--------------+----------------------+-----------------------------  
6.   
7.   46126 | my_schema   | test_bigtable        | 0.0004627596152847200000000  
8.   
9.   54316 | os           | ao_schedule          |   323.443016025055973672000  
10.   
11.   54436 | os           | ao_queue             |       165.29109905152748000  
12.   
13.   54480 | os           | ao_ext_connection    |   632.455532033675866400000  
14.   
15.   21225 | public        | test_backup          | 0.0016942981301548301720000  
16.   
17.   46073 | my_schema   | hellogp              |                           0  
18.   
19.   54830 | os           | ao_custom_sql        |                           0  
20.   
21.   63391 | os           | ao_variables         |       557.00665189601665000  
22.   
23.   54357 | os           | ao_job               |       289.53638837796634000  

  

其中skccoeff 通过存储记录均值计算出的标准差，这个值越低说明数据存放约均匀，反之说明数据存储分布不均匀，要考虑分布键选择是否合理。

 

另外一个视图gp_toolkit.gp_skew_idle_fractions 通过计算表扫描过程中，系统闲置的百分比，帮助用户快速判断，是否存在分布键选择不合理，导致数据处理倾斜的问题。

[sql]  view plain  copy

 

1. my_db_safe=# select * from gp_toolkit.gp_skew_idle_fractions;  
2.   
3.  sifoid | sifnamespace |      sifrelname      |        siffraction           
4.   
5. --------+--------------+----------------------+----------------------------  
6.   
7.   46126 | my_schema   | test_bigtable        | 0.000007679941018052981353  
8.   
9.   54316 | os           | ao_schedule          |     0.93750000000000000000  
10.   
11.   54436 | os           | ao_queue             |     0.88472222222222222222  
12.   
13.   54480 | os           | ao_ext_connection    |     0.97500000000000000000  
14.   
15.   21225 | public        | test_backup          | 0.000033598871077931781492  
16.   
17.   46073 | my_schema   | hellogp              |                          0  
18.   
19.   54830 | os           | ao_custom_sql        |                          0  
20.   
21.   63391 | os           | ao_variables         |     0.97142857142857142857  
22.   
23.   54357 | os           | ao_job               |     0.93125000000000000000  

 

siffraction字段表示表扫描过程中系统闲置的百分比，比如0.1表示10%的倾斜。

 

结合上面两个视图的结果，我们可以看到某些表的结论是数据倾斜很厉害，比如ao_schedule表，但是实际上这些表是因为数据量太少，只有几条，那只能分布在某几个segment节点上，其他segment节点都没有数据，比如：

[sql]  view plain  copy

 

1. my_db_safe=# select gp_segment_id,count(1) from os.ao_schedule group by 1;  
2.   
3.  gp_segment_id | count   
4.   
5. ---------------+-------  
6.   
7.             21 |     1  
8.   
9.             30 |     1  
10.   
11.              8 |     1  
12.   
13.             36 |     2  
14.   
15. (4 rows)  

 

可以看出，os.ao_schedule表只有5条数据，所有判断数据倾斜时要结合多方面来判断。

 

本文章会介绍一种替代上面两个视图低效查询数据倾斜的方式。

 

解决方案的原理：

这次方案也是使用视图来观察每个segment上的每个表的文件大小。它将仅仅输出那些表至少一个segment大小比预期的大20%以上。

 

下面一个工具，一个能够快速给出表倾斜的信息。

执行如下的创建函数的SQL：

[sql]  view plain  copy

 

1. CREATE OR REPLACE FUNCTION my_func_for_files_skew()  
2.   
3. RETURNS void AS   
4.   
5. $$  
6.   
7. DECLARE   
8.   
9.     v_function_name text := 'my_create_func_for_files_skew';  
10.   
11.     v_location_id int;  
12.   
13.     v_sql text;  
14.   
15.     v_db_oid text;  
16.   
17.     v_number_segments numeric;  
18.   
19.     v_skew_amount numeric;  
20.   
21. BEGIN  
22.   
23.     --定义代码的位置，方便用来定位问题--                 
24.   
25.     v_location_id := 1000;  
26.   
27.       
28.   
29.     --获取当前数据库的oid--          
30.   
31.     SELECT oid INTO v_db_oid   
32.   
33.     FROM pg_database   
34.   
35.     WHERE datname = current_database();  
36.   
37.    
38.   
39.     --文件倾斜的视图并创建该视图--        
40.   
41.     v_location_id := 2000;  
42.   
43.     v_sql := 'DROP VIEW IF EXISTS my_file_skew_view';  
44.   
45.         
46.   
47.     v_location_id := 2100;  
48.   
49.     EXECUTE v_sql;  
50.   
51.       
52.   
53.     --保存db文件的外部表并创建该外部表--       
54.   
55.     v_location_id := 2200;  
56.   
57.     v_sql := 'DROP EXTERNAL TABLE IF EXISTS my_db_files_web_tbl';  
58.   
59.    
60.   
61.     v_location_id := 2300;  
62.   
63.     EXECUTE v_sql;  
64.   
65.    
66.   
67.     --获取 segment_id,relfilenode,filename,size 信息--       
68.   
69.     v_location_id := 3000;  
70.   
71.     v_sql := 'CREATE EXTERNAL WEB TABLE my_db_files_web_tbl ' ||  
72.   
73.             '(segment_id int, relfilenode text, filename text, size numeric) ' ||  
74.   
75.             'execute E''ls -l $GP_SEG_DATADIR/base/' || v_db_oid ||   
76.   
77.             ' | grep gpadmin | ' ||  
78.   
79.             E'awk {''''print ENVIRON["GP_SEGMENT_ID"] "\\t" $9 "\\t" ' ||  
80.   
81.             'ENVIRON["GP_SEG_DATADIR"] "/' || v_db_oid ||   
82.   
83.             E'/" $9 "\\t" $5''''}'' on all ' || 'format ''text''';  
84.   
85.    
86.   
87.     v_location_id := 3100;  
88.   
89.     EXECUTE v_sql;  
90.   
91.    
92.   
93.     --获取所有primary segment的个数--       
94.   
95.     v_location_id := 4000;  
96.   
97.     SELECT count(*) INTO v_number_segments   
98.   
99.     FROM gp_segment_configuration   
100.   
101.     WHERE preferred_role = 'p'   
102.   
103.     AND content >= 0;  
104.   
105.    
106.   
107.     --如果primary segment总数为40个，那么此处v_skew_amount=1.2*0.025=0.03--      
108.   
109.     v_location_id := 4100;  
110.   
111.     v_skew_amount := 1.2*(1/v_number_segments);  
112.   
113.       
114.   
115.     --创建记录文件倾斜的视图--      
116.   
117.     v_location_id := 4200;  
118.   
119.     v_sql := 'CREATE OR REPLACE VIEW my_file_skew_view AS ' ||  
120.   
121.              'SELECT schema_name, ' ||  
122.   
123.              'table_name, ' ||  
124.   
125.              'max(size)/sum(size) as largest_segment_percentage, ' ||  
126.   
127.              'sum(size) as total_size ' ||  
128.   
129.              'FROM    ( ' ||  
130.   
131.              'SELECT n.nspname AS schema_name, ' ||  
132.   
133.              '      c.relname AS table_name, ' ||  
134.   
135.              '      sum(db.size) as size ' ||  
136.   
137.              '      FROM my_db_files_web_tbl db ' ||  
138.   
139.              '      JOIN pg_class c ON ' ||  
140.   
141.              '      split_part(db.relfilenode, ''.'', 1) = c.relfilenode ' ||  
142.   
143.              '      JOIN pg_namespace n ON c.relnamespace = n.oid ' ||  
144.   
145.              '      WHERE c.relkind = ''r'' ' ||  
146.   
147.              '      GROUP BY n.nspname, c.relname, db.segment_id ' ||  
148.   
149.              ') as sub ' ||  
150.   
151.              'GROUP BY schema_name, table_name ' ||  
152.   
153.              'HAVING sum(size) > 0 and max(size)/sum(size) > ' ||  --只记录大于合适的才输出---  
154.   
155.              v_skew_amount::text || ' ' ||   
156.   
157.              'ORDER BY largest_segment_percentage DESC, schema_name, ' ||  
158.   
159.              'table_name';  
160.   
161.    
162.   
163.     v_location_id := 4300;  
164.   
165.     EXECUTE v_sql;   
166.   
167.    
168.   
169.     EXCEPTION  
170.   
171.         WHEN OTHERS THEN  
172.   
173.             RAISE EXCEPTION '(%:%:%)', v_function_name, v_location_id, sqlerrm;  
174.   
175. END;  
176.   
177. $$ language plpgsql;  

 

然后我们执行函数，创建相关的对象：

[sql]  view plain  copy

 

1. my_db_safe=# select my_func_for_files_skew();  
2.   
3. NOTICE:  view "my_file_skew_view" does not exist, skipping  
4.   
5. CONTEXT:  SQL statement "DROP VIEW IF EXISTS my_file_skew_view"  
6.   
7. PL/pgSQL function "my_func_for_files_skew" line 22 at execute statement  
8.   
9. NOTICE:  table "my_db_files_web_tbl" does not exist, skipping  
10.   
11. CONTEXT:  SQL statement "DROP EXTERNAL TABLE IF EXISTS my_db_files_web_tbl"  
12.   
13. PL/pgSQL function "my_func_for_files_skew" line 29 at execute statement  
14.   
15.  my_func_for_files_skew   
16.   
17. ------------------------  
18.   
19.    
20.   
21. (1 row)  

 

 

这时我们就可以查看我们计划的倾斜表：

[sql]  view plain  copy

 

1. my_db_safe=# select * from my_file_skew_view ;  
2.   
3.  schema_name  |      table_name       | largest_segment_percentage | total_size   
4.   
5. --------------+-----------------------+----------------------------+------------  
6.   
7.  os           | ao_variables          |     0.87500000000000000000 |        448  
8.   
9. my_schema   | test                  |     0.50000000000000000000 |        192  
10.   
11.  os           | ao_queue              |     0.22579365079365079365 |      20160  
12.   
13.  os           | ao_schedule           |     0.39534883720930232558 |        344  
14.   
15.  os           | ao_job                |     0.35305343511450381679 |       8384  
16.   
17.  pg_catalog    | pg_attribute_encoding |     0.03067484662576687117 |    5341184  
18.   
19.  os           | ao_ext_connection     |     1.00000000000000000000 |        120  
20.   
21. (8 rows)  
22.   
23.    
24.   
25. my_db_safe=#  

 

我们也可以选择按照倾斜度的大小进行排序：

[sql]  view plain  copy

 

1. my_db_safe=# select * from my_file_skew_view order by largest_segment_percentage desc;  
2.   
3.  schema_name  |      table_name       | largest_segment_percentage | total_size   
4.   
5. --------------+-----------------------+----------------------------+------------  
6.   
7.  os           | ao_ext_connection     |     1.00000000000000000000 |        120  
8.   
9.  os           | ao_variables          |     0.87500000000000000000 |        448  
10.   
11.  my_schema   | test                  |     0.50000000000000000000 |        192  
12.   
13.  os           | ao_schedule           |     0.39534883720930232558 |        344  
14.   
15.  os           | ao_job                |     0.35305343511450381679 |       8384  
16.   
17.  os           | ao_queue              |     0.22579365079365079365 |      20160  
18.   
19.  pg_catalog    | pg_attribute_encoding |     0.03067484662576687117 |    5341184  

 

根据查看结果，需要我们关注的是largest_segment_percentage这个字段的值，越靠近1说明一个segment上面的数据比集群的其他节点更多，比如os.ao_variables表的largest_segment_percentage为0.875，说明87.5%的数据在一个segment上面。

 

我们可以验证一下：

[sql]  view plain  copy

 

1. my_db_safe=# select gp_segment_id,count(1) from os.ao_variables group by 1;  
2.   
3.  gp_segment_id | count   
4.   
5. ---------------+-------  
6.   
7.             32 |     1  
8.   
9.             35 |     7  
10.   
11. (2 rows)  

 

很显然，共有7条数据(总共8条数据)都在gp_segment_id为35的segment上面，占87.5%。

 

 

如果大家对Greenplum数据库熟悉的话，就会发现上面工具的一个问题，即表膨胀。

当我们对表执行DML操作时，对于删除的空间并没有立马释放给操作系统，所以我们的计算结果可能会包含这部分大小。

 

个人建议在执行这个查看表文件倾斜之前，对需要统计的表进行Vacuum回收空间，或使用CTAS方式进行表重建。

 

另外补充一点，如果你想对单个表进行统计倾斜度时，可以修改函数，添加一个参数，用来传入表名或表的oid即可。