-
Notifications
You must be signed in to change notification settings - Fork 1.9k
/
20161123_01_sql_001.sql
1517 lines (1329 loc) · 92.4 KB
/
20161123_01_sql_001.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-- 初始化
begin;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
create schema IF NOT EXISTS __rds_pg_stats__;
set search_path=__rds_pg_stats__,public,pg_catalog;
-- 全局, pg_db_role_setting
create table snap_pg_db_role_setting as select 1::int8 snap_id, now() snap_ts, * from pg_db_role_setting;
-- 全局, 表空间占用
create table snap_pg_tbs_size as select 1::int8 snap_id, now() snap_ts, spcname, pg_tablespace_location(oid), pg_size_pretty(pg_tablespace_size(oid)) from pg_tablespace where spcname<>'pg_global' order by pg_tablespace_size(oid) desc;
-- 全局, 数据库空间占用
create table snap_pg_db_size as select 1::int8 snap_id, now() snap_ts, datname, pg_size_pretty(pg_database_size(oid)) from pg_database order by pg_database_size(oid) desc;
-- 全局, 当前活跃度
create table snap_pg_stat_activity as select 1::int8 snap_id, now() snap_ts, state, count(*) from pg_stat_activity group by 1,2,3;
-- 全局, 总剩余连接数
create table snap_pg_conn_stats as select 1::int8 snap_id, now() snap_ts, max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from (select count(*) used from pg_stat_activity) t1,(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,(select setting::int max_conn from pg_settings where name=$$max_connections$$) t3;
-- 全局, 用户连接数限制
create table snap_pg_role_conn_limit as select 1::int8 snap_id, now() snap_ts, a.rolname,a.rolconnlimit,b.connects from pg_roles a,(select usename,count(*) connects from pg_stat_activity group by usename) b where a.rolname=b.usename order by b.connects desc;
-- 全局, 数据库连接限制
create table snap_pg_db_conn_limit as select 1::int8 snap_id, now() snap_ts, a.datname, a.datconnlimit, b.connects from pg_database a,(select datname,count(*) connects from pg_stat_activity group by datname) b where a.datname=b.datname order by b.connects desc;
-- 全局, TOP CPU TIME 10 SQL
create table snap_pg_cputime_topsql as select 1::int8 snap_id, now() snap_ts, c.rolname,b.datname,a.total_time/a.calls per_call_time,a.* from pg_stat_statements a,pg_database b,pg_roles c where a.userid=c.oid and a.dbid=b.oid order by a.total_time desc limit 10;
-- 全局, 数据库统计信息, 回滚比例, 命中比例, 数据块读写时间, 死锁, 复制冲突
create table snap_pg_stat_database as select 1::int8 snap_id, now() snap_ts, datname,round(100*(xact_rollback::numeric/(case when xact_commit > 0 then xact_commit else 1 end + xact_rollback)),2)||$$ %$$ rollback_ratio, round(100*(blks_hit::numeric/(case when blks_read>0 then blks_read else 1 end + blks_hit)),2)||$$ %$$ hit_ratio, blk_read_time, blk_write_time, conflicts, deadlocks from pg_stat_database;
-- 全局, 检查点, bgwriter 统计信息
create table snap_pg_stat_bgwriter as select 1::int8 snap_id, now() snap_ts, * from pg_stat_bgwriter;
-- 全局, archiver 统计信息
create table snap_pg_stat_archiver as select 1::int8 snap_id, now() snap_ts,coalesce(pg_xlogfile_name(pg_current_xlog_insert_location()),'-') as now_insert_xlog_file, * from pg_stat_archiver;
-- 全局, 数据库年龄
create table snap_pg_database_age as select 1::int8 snap_id, now() snap_ts, datname,age(datfrozenxid),2^31-age(datfrozenxid) age_remain from pg_database order by age(datfrozenxid) desc;
-- 全局, 长事务, 2PC
create table snap_pg_long_xact as select 1::int8 snap_id, now() snap_ts, datname,usename,query,xact_start,now()-xact_start xact_duration,query_start,now()-query_start query_duration,state from pg_stat_activity where state<>$$idle$$ and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval $$30 min$$ order by xact_start;
create table snap_pg_long_2pc as select 1::int8 snap_id, now() snap_ts, name,statement,prepare_time,now()-prepare_time duration,parameter_types,from_sql from pg_prepared_statements where now()-prepare_time > interval $$30 min$$ order by prepare_time;
-- 全局, 用户密码到期时间
create table snap_pg_user_deadline as select 1::int8 snap_id, now() snap_ts, rolname,rolvaliduntil from pg_roles order by rolvaliduntil;
-- 库级, 快照清单
create table snap_list (id serial8 primary key, snap_ts timestamp, snap_level text);
insert into snap_list (snap_ts, snap_level) values (now(), 'database');
-- 库级, pg_stat_statements
create table snap_pg_stat_statements as select 1::int8 snap_id, now() snap_ts, * from pg_stat_statements;
-- 库级, 对象空间占用柱状图
create table snap_pg_rel_space_bucket as select 1::int8 snap_id, now() snap_ts, current_database(), buk this_buk_no, cnt rels_in_this_buk, pg_size_pretty(min) buk_min, pg_size_pretty(max) buk_max from
(
select row_number() over (partition by buk order by tsize), tsize, buk, min(tsize) over (partition by buk),max(tsize) over (partition by buk), count(*) over (partition by buk) cnt from
(
select pg_relation_size(a.oid) tsize, width_bucket(pg_relation_size(a.oid),tmin-1,tmax+1,10) buk from
(
select min(pg_relation_size(a.oid)) tmin, max(pg_relation_size(a.oid)) tmax from pg_class a, pg_namespace c where a.relnamespace=c.oid and nspname !~ $$^pg_$$ and nspname<>$$information_schema$$
) t, pg_class a, pg_namespace c where a.relnamespace=c.oid and nspname !~ $$^pg_$$ and nspname<>$$information_schema$$
) t
) t where row_number=1;
-- 库级, 数据库对象空间前5000
create table snap_pg_db_rel_size as select 1::int8 snap_id, now() snap_ts, current_database(),b.nspname,c.relname,c.relkind,pg_relation_size(c.oid),a.seq_scan,a.seq_tup_read,a.idx_scan,a.idx_tup_fetch,a.n_tup_ins,a.n_tup_upd,a.n_tup_del,a.n_tup_hot_upd,a.n_live_tup,a.n_dead_tup from pg_stat_all_tables a, pg_class c,pg_namespace b where c.relnamespace=b.oid and c.relkind=$$r$$ and a.relid=c.oid order by pg_relation_size(c.oid) desc limit 5000;
-- 库级, pg_statio_all_tables
create table snap_pg_statio_all_tables as select 1::int8 snap_id, now() snap_ts, current_database(),* from pg_statio_all_tables;
-- 库级, pg_statio_all_indexes
create table snap_pg_statio_all_indexes as select 1::int8 snap_id, now() snap_ts, current_database(),* from pg_statio_all_indexes;
-- 库级, 索引数超过4并且SIZE大于10MB的表
create table snap_pg_many_indexes_rel as select 1::int8 snap_id, now() snap_ts, current_database(), t2.nspname, t1.relname, pg_relation_size(t1.oid), t3.idx_cnt from pg_class t1, pg_namespace t2, (select indrelid,count(*) idx_cnt from pg_index group by 1 having count(*)>4) t3 where t1.oid=t3.indrelid and t1.relnamespace=t2.oid and pg_relation_size(t1.oid)/1024/1024.0>10 order by t3.idx_cnt desc;
-- 库级, 上次快照以来未使用,或者使用较少的索引
create table snap_pg_notused_indexes as select 1::int8 snap_id, now() snap_ts, current_database(),t2.schemaname,t2.relname,t2.indexrelname,t2.idx_scan,t2.idx_tup_read,t2.idx_tup_fetch,pg_relation_size(indexrelid) from pg_stat_all_tables t1,pg_stat_all_indexes t2 where t1.relid=t2.relid and t2.idx_scan<10 and t2.schemaname not in ($$pg_toast$$,$$pg_catalog$$) and indexrelid not in (select conindid from pg_constraint where contype in ($$p$$,$$u$$,$$f$$)) and pg_relation_size(indexrelid)>65536 order by pg_relation_size(indexrelid) desc;
-- 库级, 表膨胀前10
create table snap_pg_table_bloat as select 1::int8 snap_id, now() snap_ts,
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::bigint || $$ bytes$$ END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN $$0 bytes$$ ELSE (bs*(ipages-iotta))::bigint || $$ bytes$$ END AS wastedisize,
CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,$$?$$) AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting($$block_size$$)::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#"[0-9]+.[0-9]+#"%$$ for $$#$$)
IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind=$$r$$
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml order by wastedbytes desc limit 10;
-- 库级, 索引膨胀前10
create table snap_pg_index_bloat as select 1::int8 snap_id, now() snap_ts,
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::bigint || $$ bytes$$ END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN $$0 bytes$$ ELSE (bs*(ipages-iotta))::bigint || $$ bytes$$ END AS wastedisize,
CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,$$?$$) AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting($$block_size$$)::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#"[0-9]+.[0-9]+#"%$$ for $$#$$)
IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind=$$r$$
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml order by wastedibytes desc limit 10;
-- 库级, 垃圾数据前十
create table snap_pg_dead_tup as select 1::int8 snap_id, now() snap_ts, current_database(),schemaname,relname,n_dead_tup from pg_stat_all_tables where n_live_tup>0 and n_dead_tup/n_live_tup>0.2 and schemaname not in ($$pg_toast$$,$$pg_catalog$$) order by n_dead_tup desc limit 10;
-- 库级, 表年龄前100
create table snap_pg_rel_age as select 1::int8 snap_id, now() snap_ts, current_database(),rolname,nspname,relkind,relname,age(relfrozenxid),2^31-age(relfrozenxid) age_remain from pg_roles t1 join pg_class t2 on t1.oid=t2.relowner join pg_namespace t3 on t2.relnamespace=t3.oid where t2.relkind in ($$t$$,$$r$$) order by age(relfrozenxid) desc limit 100;
-- 库级, unlogged table 和 哈希索引
create table snap_pg_unlogged_table as select 1::int8 snap_id, now() snap_ts, current_database(),t3.rolname,t2.nspname,t1.relname from pg_class t1,pg_namespace t2,pg_roles t3 where t1.relnamespace=t2.oid and t1.relowner=t3.oid and t1.relpersistence=$$u$$;
create table snap_pg_hash_idx as select 1::int8 snap_id, now() snap_ts, current_database(),pg_get_indexdef(oid) from pg_class where relkind=$$i$$ and pg_get_indexdef(oid) ~ $$USING hash$$;
-- 库级, 剩余可使用次数不足1000万次的序列检查
create or replace function sequence_stats(OUT v_datname name, OUT v_role name, OUT v_nspname name, OUT v_relname name, OUT v_times_remain int8) returns setof record as $$
declare
begin
v_datname := current_database();
for v_role,v_nspname,v_relname in select rolname,nspname,relname from pg_roles t1 , pg_class t2 , pg_namespace t3 where t1.oid=t2.relowner and t2.relnamespace=t3.oid and t2.relkind='S'
LOOP
execute 'select (max_value-last_value)/increment_by from "'||v_nspname||'"."'||v_relname||'" where not is_cycled' into v_times_remain;
return next;
end loop;
end;
$$ language plpgsql;
create table snap_pg_seq_deadline as select 1::int8 snap_id, now() snap_ts, * from sequence_stats() where v_times_remain is not null and v_times_remain < 10240000 order by v_times_remain limit 10;
-- 库级, 清理未引用的大对象
create table snap_pg_vacuumlo as select 1::int8 snap_id, now() snap_ts, current_database(), 1::int8 as lo_bloat;
create or replace function vacuumlo(i_snapid int8) returns void as $$
declare
los oid[];
lo oid;
v_nspname name;
v_relname name;
v_attname name;
v_bloat int8;
begin
SELECT array_agg(oid) into los FROM pg_largeobject_metadata;
if los is not null and array_length(los,1) > 0 then
for v_nspname,v_relname,v_attname in
SELECT s.nspname, c.relname, a.attname
FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t
WHERE a.attnum > 0 AND NOT a.attisdropped
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND c.relnamespace = s.oid
AND t.typname in ('oid', 'lo')
AND c.relkind in ('r', 'm')
AND s.nspname !~ '^pg_'
loop
for lo in execute format('SELECT %I FROM %I.%I', quote_ident(v_attname), quote_ident(v_nspname), quote_ident(v_relname)) loop
los := array_remove(los, lo);
end loop;
end loop;
end if;
if los is not null and array_length(los,1) > 0 then
select sum(current_setting('block_size')::int8) into v_bloat from pg_largeobject where loid = any(los);
insert into snap_pg_vacuumlo select i_snapid, now() snap_ts, current_database(), v_bloat;
raise notice 'lo bloats: %', pg_size_pretty(v_bloat);
raise notice 'begin vacuumlo. %', clock_timestamp();
perform lo_unlink(o) from unnest(los) t(o);
raise notice 'end vacuumlo. %', clock_timestamp();
end if;
end;
$$ language plpgsql strict;
-- 库级, 锁等待
create table snap_pg_waiting as
with t_wait as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,
transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,
a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where
a.pid=b.pid and a.granted)
select
1::int8 snap_id, now() snap_ts,
r.locktype, r.mode r_mode,r.usename r_user,r.datname r_db,
r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,
w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
from t_wait w,t_run r where
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.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.transactionid is not distinct from w.transactionid and
r.pid <> w.pid
order by
(( case w.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 ) +
( case r.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,r.xact_start;
-- 打快照函数
create or replace function snap_global(erase_stats boolean default true) returns void as $_$
declare
snap_id int8;
ts timestamp := clock_timestamp();
begin
set search_path=__rds_pg_stats__,public,pg_catalog;
-- 库级, 快照清单
raise notice '%', ts;
insert into snap_list (snap_ts, snap_level) values (ts, 'global') returning id into snap_id;
-- 全局, pg_db_role_setting
insert into snap_pg_db_role_setting select snap_id, ts snap_ts, * from pg_db_role_setting;
-- 全局, 表空间占用
insert into snap_pg_tbs_size select snap_id, ts snap_ts, spcname, case when pg_tablespace_location(oid)='' then '-' else pg_tablespace_location(oid) end, pg_size_pretty(pg_tablespace_size(oid)) from pg_tablespace where spcname<>'pg_global' order by pg_tablespace_size(oid) desc;
-- 全局, 数据库空间占用
insert into snap_pg_db_size select snap_id, ts snap_ts, datname, pg_size_pretty(pg_database_size(oid)) from pg_database order by pg_database_size(oid) desc;
-- 全局, 当前活跃度
insert into snap_pg_stat_activity select snap_id, ts snap_ts, state, count(*) from pg_stat_activity group by 1,2,3;
-- 全局, 总剩余连接数
insert into snap_pg_conn_stats select snap_id, ts snap_ts, max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from (select count(*) used from pg_stat_activity) t1,(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,(select setting::int max_conn from pg_settings where name=$$max_connections$$) t3;
-- 全局, 用户连接数限制
insert into snap_pg_role_conn_limit select snap_id, ts snap_ts, a.rolname,a.rolconnlimit,b.connects from pg_roles a,(select usename,count(*) connects from pg_stat_activity group by usename) b where a.rolname=b.usename order by b.connects desc;
-- 全局, 数据库连接限制
insert into snap_pg_db_conn_limit select snap_id, ts snap_ts, a.datname, a.datconnlimit, b.connects from pg_database a,(select datname,count(*) connects from pg_stat_activity group by datname) b where a.datname=b.datname order by b.connects desc;
-- 全局, TOP CPUTIME 10 SQL
insert into snap_pg_cputime_topsql select snap_id, ts snap_ts, c.rolname,b.datname,a.total_time/a.calls per_call_time,a.* from pg_stat_statements a,pg_database b,pg_roles c where a.userid=c.oid and a.dbid=b.oid order by a.total_time desc limit 10;
-- 全局, 数据库统计信息, 回滚比例, 命中比例, 数据块读写时间, 死锁, 复制冲突
insert into snap_pg_stat_database select snap_id, ts snap_ts, datname,round(100*(xact_rollback::numeric/(case when xact_commit > 0 then xact_commit else 1 end + xact_rollback)),2)||$$ %$$ rollback_ratio, round(100*(blks_hit::numeric/(case when blks_read>0 then blks_read else 1 end + blks_hit)),2)||$$ %$$ hit_ratio, blk_read_time, blk_write_time, conflicts, deadlocks from pg_stat_database;
-- 全局, 检查点, bgwriter 统计信息
insert into snap_pg_stat_bgwriter select snap_id, ts snap_ts, * from pg_stat_bgwriter;
-- 全局, archiver 统计信息
insert into snap_pg_stat_archiver select snap_id, ts snap_ts, coalesce(pg_xlogfile_name(pg_current_xlog_insert_location()),'-') as now_insert_xlog_file, * from pg_stat_archiver;
-- 全局, 数据库年龄
insert into snap_pg_database_age select snap_id, ts snap_ts, datname,age(datfrozenxid),2^31-age(datfrozenxid) age_remain from pg_database order by age(datfrozenxid) desc;
-- 全局, 长事务, 2PC
insert into snap_pg_long_xact select snap_id, ts snap_ts, datname,usename,query,xact_start,ts-xact_start xact_duration,query_start,ts-query_start query_duration,state from pg_stat_activity where state<>$$idle$$ and (backend_xid is not null or backend_xmin is not null) and ts-xact_start > interval $$30 min$$ order by xact_start;
insert into snap_pg_long_2pc select snap_id, ts snap_ts, name,statement,prepare_time,ts-prepare_time,parameter_types,from_sql from pg_prepared_statements where ts-prepare_time > interval $$30 min$$ order by prepare_time;
-- 全局, 用户密码到期时间
insert into snap_pg_user_deadline select snap_id, ts snap_ts, rolname,rolvaliduntil from pg_roles order by rolvaliduntil;
-- 重置统计信息
if erase_stats then
perform pg_stat_reset_shared('bgwriter');
perform pg_stat_reset_shared('archiver');
perform pg_stat_statements_reset();
end if;
reset search_path;
end;
$_$ language plpgsql strict;
create or replace function snap_database(erase_stats boolean default true) returns void as $_$
declare
snap_id int8;
ts timestamp := clock_timestamp();
begin
set search_path=__rds_pg_stats__,public,pg_catalog;
-- 库级, 快照清单
raise notice '%', ts;
insert into snap_list (snap_ts, snap_level) values (ts, 'database') returning id into snap_id;
-- 库级, pg_stat_statements
insert into snap_pg_stat_statements select snap_id, ts snap_ts, * from pg_stat_statements;
-- 库级, 对象空间占用柱状图
insert into snap_pg_rel_space_bucket select snap_id, ts snap_ts, current_database(), buk this_buk_no, cnt rels_in_this_buk, pg_size_pretty(min) buk_min, pg_size_pretty(max) buk_max from
(
select row_number() over (partition by buk order by tsize), tsize, buk, min(tsize) over (partition by buk),max(tsize) over (partition by buk), count(*) over (partition by buk) cnt from
(
select pg_relation_size(a.oid) tsize, width_bucket(pg_relation_size(a.oid),tmin-1,tmax+1,10) buk from
(
select min(pg_relation_size(a.oid)) tmin, max(pg_relation_size(a.oid)) tmax from pg_class a, pg_namespace c where a.relnamespace=c.oid and nspname !~ $$^pg_$$ and nspname<>$$information_schema$$
) t, pg_class a, pg_namespace c where a.relnamespace=c.oid and nspname !~ $$^pg_$$ and nspname<>$$information_schema$$
) t
) t where row_number=1;
-- 库级, 空间占用前5000的表
insert into snap_pg_db_rel_size select snap_id, ts snap_ts, current_database(),b.nspname,c.relname,c.relkind,pg_relation_size(c.oid),a.seq_scan,a.seq_tup_read,a.idx_scan,a.idx_tup_fetch,a.n_tup_ins,a.n_tup_upd,a.n_tup_del,a.n_tup_hot_upd,a.n_live_tup,a.n_dead_tup from pg_stat_all_tables a, pg_class c,pg_namespace b where c.relnamespace=b.oid and c.relkind=$$r$$ and a.relid=c.oid order by pg_relation_size(c.oid) desc limit 5000;
-- 库级, pg_statio_all_tables
insert into snap_pg_statio_all_tables select snap_id, ts snap_ts, current_database(),* from pg_statio_all_tables;
-- 库级, pg_statio_all_indexes
insert into snap_pg_statio_all_indexes select snap_id, ts snap_ts, current_database(),* from pg_statio_all_indexes;
-- 库级, 索引数超过4并且SIZE大于10MB的表
insert into snap_pg_many_indexes_rel select snap_id, ts snap_ts, current_database(), t2.nspname, t1.relname, pg_relation_size(t1.oid), t3.idx_cnt from pg_class t1, pg_namespace t2, (select indrelid,count(*) idx_cnt from pg_index group by 1 having count(*)>4) t3 where t1.oid=t3.indrelid and t1.relnamespace=t2.oid and pg_relation_size(t1.oid)/1024/1024.0>10 order by t3.idx_cnt desc;
-- 库级, 上次巡检以来未使用,或者使用较少的索引
insert into snap_pg_notused_indexes select snap_id, ts snap_ts, current_database(),t2.schemaname,t2.relname,t2.indexrelname,t2.idx_scan,t2.idx_tup_read,t2.idx_tup_fetch,pg_relation_size(indexrelid) from pg_stat_all_tables t1,pg_stat_all_indexes t2 where t1.relid=t2.relid and t2.idx_scan<10 and t2.schemaname not in ($$pg_toast$$,$$pg_catalog$$) and indexrelid not in (select conindid from pg_constraint where contype in ($$p$$,$$u$$,$$f$$)) and pg_relation_size(indexrelid)>65536 order by pg_relation_size(indexrelid) desc;
-- 库级, 表膨胀前10
insert into snap_pg_table_bloat select snap_id, ts snap_ts,
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::bigint || $$ bytes$$ END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN $$0 bytes$$ ELSE (bs*(ipages-iotta))::bigint || $$ bytes$$ END AS wastedisize,
CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,$$?$$) AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting($$block_size$$)::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#"[0-9]+.[0-9]+#"%$$ for $$#$$)
IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind=$$r$$
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml order by wastedbytes desc limit 10;
-- 库级, 索引膨胀前10
insert into snap_pg_index_bloat select snap_id, ts snap_ts,
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::bigint || $$ bytes$$ END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN $$0 bytes$$ ELSE (bs*(ipages-iotta))::bigint || $$ bytes$$ END AS wastedisize,
CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,$$?$$) AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting($$block_size$$)::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#"[0-9]+.[0-9]+#"%$$ for $$#$$)
IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind=$$r$$
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml order by wastedibytes desc limit 10;
-- 库级, 垃圾数据前十
insert into snap_pg_dead_tup select snap_id, ts snap_ts, current_database(),schemaname,relname,n_dead_tup from pg_stat_all_tables where n_live_tup>0 and (n_dead_tup/n_live_tup)>0.2 and schemaname not in ($$pg_toast$$,$$pg_catalog$$) order by n_dead_tup desc limit 10;
-- 库级, 表年龄前100
insert into snap_pg_rel_age select snap_id, ts snap_ts, current_database(),rolname,nspname,relkind,relname,age(relfrozenxid),2^31-age(relfrozenxid) age_remain from pg_roles t1 join pg_class t2 on t1.oid=t2.relowner join pg_namespace t3 on t2.relnamespace=t3.oid where t2.relkind in ($$t$$,$$r$$) order by age(relfrozenxid) desc limit 100;
-- 库级, unlogged table 和 哈希索引
insert into snap_pg_unlogged_table select snap_id, ts snap_ts, current_database(),t3.rolname,t2.nspname,t1.relname from pg_class t1,pg_namespace t2,pg_roles t3 where t1.relnamespace=t2.oid and t1.relowner=t3.oid and t1.relpersistence=$$u$$;
insert into snap_pg_hash_idx select snap_id, ts snap_ts, current_database(),pg_get_indexdef(oid) from pg_class where relkind=$$i$$ and pg_get_indexdef(oid) ~ $$USING hash$$;
-- 库级, 剩余可使用次数不足1000万次的序列检查
insert into snap_pg_seq_deadline select snap_id, ts snap_ts, * from sequence_stats() where v_times_remain is not null and v_times_remain < 10240000 order by v_times_remain limit 10;
-- 库级, 清理未引用的大对象
perform vacuumlo(snap_id) ;
-- 库级, 锁等待
insert into snap_pg_waiting
with t_wait as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,
transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,
a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where
a.pid=b.pid and a.granted)
select
snap_id, ts snap_ts,
r.locktype, r.mode r_mode,r.usename r_user,r.datname r_db,
r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
r.query_start r_query_start,
ts-r.query_start r_locktime,r.query r_query,w.mode w_mode,
w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
ts-w.query_start w_locktime,w.query w_query
from t_wait w,t_run r where
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.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.transactionid is not distinct from w.transactionid and
r.pid <> w.pid
order by
(( case w.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 ) +
( case r.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,r.xact_start;
-- 重置统计信息
if erase_stats then
perform pg_stat_reset();
end if;
reset search_path;
end;
$_$ language plpgsql strict;
-- 清理快照函数
-- 清理库级、全局快照
-- 3种清理快照的方法
create or replace function snap_delete_data(i_snap_id int8) returns void as $$
declare
begin
set search_path=__rds_pg_stats__,public,pg_catalog;
delete from snap_list where id=i_snap_id;
delete from snap_pg_conn_stats where snap_id=i_snap_id;
delete from snap_pg_cputime_topsql where snap_id=i_snap_id;
delete from snap_pg_database_age where snap_id=i_snap_id;
delete from snap_pg_db_conn_limit where snap_id=i_snap_id;
delete from snap_pg_db_rel_size where snap_id=i_snap_id;
delete from snap_pg_db_role_setting where snap_id=i_snap_id;
delete from snap_pg_db_size where snap_id=i_snap_id;
delete from snap_pg_dead_tup where snap_id=i_snap_id;
delete from snap_pg_hash_idx where snap_id=i_snap_id;
delete from snap_pg_index_bloat where snap_id=i_snap_id;
delete from snap_pg_long_2pc where snap_id=i_snap_id;
delete from snap_pg_long_xact where snap_id=i_snap_id;
delete from snap_pg_many_indexes_rel where snap_id=i_snap_id;
delete from snap_pg_notused_indexes where snap_id=i_snap_id;
delete from snap_pg_rel_age where snap_id=i_snap_id;
delete from snap_pg_rel_space_bucket where snap_id=i_snap_id;
delete from snap_pg_role_conn_limit where snap_id=i_snap_id;
delete from snap_pg_seq_deadline where snap_id=i_snap_id;
delete from snap_pg_stat_activity where snap_id=i_snap_id;
delete from snap_pg_stat_archiver where snap_id=i_snap_id;
delete from snap_pg_stat_bgwriter where snap_id=i_snap_id;
delete from snap_pg_stat_database where snap_id=i_snap_id;
delete from snap_pg_stat_statements where snap_id=i_snap_id;
delete from snap_pg_statio_all_indexes where snap_id=i_snap_id;
delete from snap_pg_statio_all_tables where snap_id=i_snap_id;
delete from snap_pg_table_bloat where snap_id=i_snap_id;
delete from snap_pg_tbs_size where snap_id=i_snap_id;
delete from snap_pg_unlogged_table where snap_id=i_snap_id;
delete from snap_pg_user_deadline where snap_id=i_snap_id;
delete from snap_pg_vacuumlo where snap_id=i_snap_id;
delete from snap_pg_waiting where snap_id=i_snap_id;
reset search_path;
end;
$$ language plpgsql strict;
-- 删除指定snap_id以前的快照数据
create or replace function snap_delete(i_snap_id int8) returns void as $$
declare
v_snap_id int8;
begin
set search_path=__rds_pg_stats__,public,pg_catalog;
for v_snap_id in select id from snap_list where id<i_snap_id order by id
loop
perform snap_delete_data(v_snap_id);
end loop;
reset search_path;
end;
$$ language plpgsql strict;
-- 删除指定时间以前的快照数据
create or replace function snap_delete(i_snap_ts timestamp) returns void as $$
declare
v_snap_id int8;
begin
set search_path=__rds_pg_stats__,public,pg_catalog;
for v_snap_id in select id from snap_list where snap_ts<i_snap_ts order by id
loop
perform snap_delete_data(v_snap_id);
end loop;
reset search_path;
end;
$$ language plpgsql strict;
-- 保留最近几个快照
create or replace function snap_delete(i_reserved int) returns void as $$
declare
v_snap_id int8;
begin
set search_path=__rds_pg_stats__,public,pg_catalog;
if i_reserved < 1 then
raise notice 'You must give a value >=1';
reset search_path;
return;
end if;
for v_snap_id in select id from snap_list where id < (select id from snap_list order by id desc limit 1 offset 2) order by id
loop
perform snap_delete_data(v_snap_id);
end loop;
reset search_path;
end;
$$ language plpgsql strict;
-- 生成诊断报告函数
-- 生成全局报告
-- 指定ID范围
create or replace function snap_report_global(i_begin_id int8, i_end_id int8, i_level text default 'global') returns setof text as $$
declare
v_begin_ts timestamp;
v_end_ts timestamp;
res text[];
tmp text;
version text := '9.4';
begin
set search_path=__rds_pg_stats__,public,pg_catalog;
-- 判断 ID 是否存在
perform 1 from snap_list where id = i_begin_id and snap_level=i_level ;
if not found then
raise notice '% snapshot begin_id : % not exist.', i_level, i_begin_id;
reset search_path;
return;
end if;
perform 1 from snap_list where id = i_end_id and snap_level=i_level ;
if not found then
raise notice '% snapshot end_id : % not exist.', i_level, i_end_id;
reset search_path;
return;
end if;
-- 生成报告时间段, 如果没有前序快照, 默认往前加1小时
select snap_ts into v_begin_ts from snap_list where id<i_begin_id order by id desc limit 1;
if not found then
select snap_ts - interval '1 hour' into v_begin_ts from snap_list where id=i_begin_id;
end if;
select snap_ts into v_end_ts from snap_list where id=i_end_id;
res := array[format('## 报告时间段: ```%s``` ~ ```%s``` ', v_begin_ts, v_end_ts)];
res := array_append(res, ' ');
res := array_append(res, '## 一、数据库定制参数信息');
res := array_append(res, ' ');
res := array_append(res, '### 1. 用户或数据库级别定制参数');
res := array_append(res, '这种设置请务必符合业务需求需要,注意这些参数往往成为隐患。 ');
res := array_append(res, ' ');
res := array_append(res, 'database | role | snap_ts | setconfig');
res := array_append(res, '---|---|---|---');
for tmp in select '```'||coalesce(datname,'-')||'``` | ```'||coalesce(rolname,'-')||'``` | ```'||snap_ts||'``` | '||coalesce(setconfig::text,'-') from snap_pg_db_role_setting t1, pg_database t2, pg_roles t3 where t1.setdatabase=t2.oid and t1.setrole=t3.oid and t1.snap_id >=i_begin_id and t1.snap_id<=i_end_id order by datname,rolname,snap_ts
loop
res := array_append(res, tmp);
end loop;
res := array_append(res, ' ');
res := array_append(res, '#### 建议');
res := array_append(res, ' ');
res := array_append(res, '## 二、数据库空间使用分析');
res := array_append(res, ' ');
res := array_append(res, '### 1. 表空间在该时段的变化情况');
res := array_append(res, ' ');
res := array_append(res, 'tablespace | tbs_location | snap_ts | size');
res := array_append(res, '---|---|---|---');
for tmp in select coalesce(spcname,'-')||' | ```'||coalesce(pg_tablespace_location,'-')||'``` | ```'||snap_ts||'``` | '||coalesce(pg_size_pretty,'-') from snap_pg_tbs_size where snap_id >=i_begin_id and snap_id<=i_end_id order by spcname,pg_tablespace_location,snap_ts
loop
res := array_append(res, tmp);
end loop;
res := array_append(res, ' ');
res := array_append(res, '#### 建议');
res := array_append(res, ' ');
res := array_append(res, '注意检查表空间所在文件系统的剩余空间, (默认表空间在$PGDATA/base目录下), IOPS分配是否均匀, OS的sysstat包可以观察IO使用率. ');
res := array_append(res, ' ');
res := array_append(res, '### 2. 数据库在该时段的变化情况');
res := array_append(res, ' ');
res := array_append(res, 'database | snap_ts | size');
res := array_append(res, '---|---|---');
for tmp in select '```'||coalesce(datname,'-')||'``` | ```'||snap_ts||'``` | '||coalesce(pg_size_pretty,'-') from snap_pg_db_size where snap_id >=i_begin_id and snap_id<=i_end_id order by datname, snap_ts
loop
res := array_append(res, tmp);
end loop;
res := array_append(res, ' ');
res := array_append(res, '#### 建议');
res := array_append(res, ' ');
res := array_append(res, '注意检查数据库的大小, 是否需要清理历史数据. ');
res := array_append(res, ' ');
res := array_append(res, '## 三、数据库连接分析');
res := array_append(res, ' ');
res := array_append(res, '### 1. 活跃度在该时段的变化');
res := array_append(res, ' ');
res := array_append(res, 'state | snap_ts | connections');
res := array_append(res, '---|---|---');
for tmp in select coalesce(state,'-')||' | ```'||snap_ts||'``` | '||coalesce(count,-1) from snap_pg_stat_activity where snap_id >=i_begin_id and snap_id<=i_end_id order by state, snap_ts
loop
res := array_append(res, tmp);
end loop;
res := array_append(res, ' ');
res := array_append(res, '#### 建议');
res := array_append(res, ' ');
res := array_append(res, '如果active状态很多, 说明数据库比较繁忙. 如果idle in transaction很多, 说明业务逻辑设计可能有问题. 如果idle很多, 可能使用了连接池, 并且可能没有自动回收连接到连接池的最小连接数. ');
res := array_append(res, ' ');
res := array_append(res, '### 2. 剩余连接数在该时段的变化');
res := array_append(res, ' ');
res := array_append(res, 'snap_ts | max_enabled_connections | used | res_for_super | res_for_normal');
res := array_append(res, '---|---|---|---|---');
for tmp in select '```'||snap_ts||'``` | '||coalesce(max_conn,-1)||' | '||coalesce(used,-1)||' | '||coalesce(res_for_super,-1)||' | '||coalesce(res_for_normal,-1) from snap_pg_conn_stats where snap_id >=i_begin_id and snap_id<=i_end_id order by snap_ts
loop
res := array_append(res, tmp);
end loop;
res := array_append(res, ' ');
res := array_append(res, '#### 建议');
res := array_append(res, ' ');
res := array_append(res, '给超级用户和普通用户设置足够的连接, 以免不能登录数据库. ');
res := array_append(res, ' ');
res := array_append(res, '### 3. 用户连接数限制在该时段的变化');
res := array_append(res, ' ');
res := array_append(res, 'rolename | snap_ts | conn_limit | connects');
res := array_append(res, '---|---|---|---');
for tmp in select '```'||coalesce(rolname,'-')||'``` | ```'||snap_ts||'``` | '||coalesce(rolconnlimit,-1)||' | '||coalesce(connects,-1) from snap_pg_role_conn_limit where snap_id >=i_begin_id and snap_id<=i_end_id order by rolname, snap_ts
loop
res := array_append(res, tmp);
end loop;
res := array_append(res, ' ');
res := array_append(res, '#### 建议');
res := array_append(res, ' ');
res := array_append(res, '给用户设置足够的连接数, alter role ... CONNECTION LIMIT . ');
res := array_append(res, ' ');
res := array_append(res, '### 4. 数据库连接限制在该时段的变化');
res := array_append(res, ' ');
res := array_append(res, 'database | snap_ts | conn_limit | connects');
res := array_append(res, '---|---|---|---');
for tmp in select '```'||coalesce(datname,'-')||'``` | ```'||snap_ts||'``` | '||coalesce(datconnlimit,-1)||' | '||coalesce(connects,-1) from snap_pg_db_conn_limit where snap_id >=i_begin_id and snap_id<=i_end_id order by datname, snap_ts
loop
res := array_append(res, tmp);
end loop;
res := array_append(res, ' ');
res := array_append(res, '#### 建议');
res := array_append(res, ' ');
res := array_append(res, '给数据库设置足够的连接数, alter database ... CONNECTION LIMIT . ');
res := array_append(res, ' ');
res := array_append(res, '## 四、数据库性能分析');
res := array_append(res, ' ');
res := array_append(res, '### 1. TOP 10 SQL : total_cpu_time');
res := array_append(res, ' ');
-- res := array_append(res, 'rolename | database | calls | total_ms | min_ms | max_ms | mean_ms | stddev_ms | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | shared_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time | query');
-- res := array_append(res, '---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---');
-- for tmp in select '```'||coalesce(rolname,'-')||'``` | ```'||coalesce(datname,'-')||'``` | '||coalesce(sum(calls),-1)||' | '||coalesce(sum(total_time),-1)||' | '||coalesce(avg(min_time),-1)||' | '||coalesce(avg(max_time),-1)||' | '||coalesce(avg(mean_time),-1)||' | '||coalesce(avg(stddev_time),-1)||' | '||coalesce(sum(rows),-1)||' | '||coalesce(sum(shared_blks_hit),-1)||' | '||coalesce(sum(shared_blks_read),-1)||' | '||coalesce(sum(shared_blks_dirtied),-1)||' | '||coalesce(sum(shared_blks_written),-1)||' | '||coalesce(sum(local_blks_hit),-1)||' | '||coalesce(sum(local_blks_read),-1)||' | '||coalesce(sum(local_blks_dirtied),-1)||' | '||coalesce(sum(local_blks_written),-1)||' | '||coalesce(sum(temp_blks_read),-1)||' | '||coalesce(sum(temp_blks_written),-1)||' | '||coalesce(sum(blk_read_time),-1)||' | '||coalesce(sum(blk_write_time),-1)||' | ```'||coalesce(replace(regexp_replace(query,'\n',' ','g'), '|', '|'),'-')||'```' from snap_pg_cputime_topsql where snap_id >=i_begin_id and snap_id<=i_end_id group by rolname,datname,query order by sum(total_time) desc nulls last limit 10
res := array_append(res, 'rolename | database | calls | total_ms | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | shared_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time | query');
res := array_append(res, '---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---');
for tmp in select '```'||coalesce(rolname,'-')||'``` | ```'||coalesce(datname,'-')||'``` | '||coalesce(sum(calls),-1)||' | '||coalesce(sum(total_time),-1)||' | '||coalesce(sum(rows),-1)||' | '||coalesce(sum(shared_blks_hit),-1)||' | '||coalesce(sum(shared_blks_read),-1)||' | '||coalesce(sum(shared_blks_dirtied),-1)||' | '||coalesce(sum(shared_blks_written),-1)||' | '||coalesce(sum(local_blks_hit),-1)||' | '||coalesce(sum(local_blks_read),-1)||' | '||coalesce(sum(local_blks_dirtied),-1)||' | '||coalesce(sum(local_blks_written),-1)||' | '||coalesce(sum(temp_blks_read),-1)||' | '||coalesce(sum(temp_blks_written),-1)||' | '||coalesce(sum(blk_read_time),-1)||' | '||coalesce(sum(blk_write_time),-1)||' | ```'||coalesce(replace(regexp_replace(query,'\n',' ','g'), '|', '|'),'-')||'```' from snap_pg_cputime_topsql where snap_id >=i_begin_id and snap_id<=i_end_id group by rolname,datname,query order by sum(total_time) desc nulls last limit 10
loop
res := array_append(res, tmp);
end loop;
res := array_append(res, ' ');
res := array_append(res, '#### 建议');
res := array_append(res, ' ');
res := array_append(res, '从最耗时的SQL开始, 检查SQL是否有优化空间, 配合auto_explain插件在csvlog中观察LONG SQL的执行计划是否正确. ');
res := array_append(res, ' ');
res := array_append(res, '### 2. 数据库统计信息, 回滚比例, 命中比例, 数据块读写时间, 死锁, 复制冲突 在该时段的变化');
res := array_append(res, ' ');
res := array_append(res, 'database | snap_ts | rollback_ratio | hit_ratio | blk_read_time | blk_write_time | conflicts | deadlocks');
res := array_append(res, '---|---|---|---|---|---|---|---');
for tmp in select '```'||coalesce(datname,'-')||'``` | ```'||snap_ts||'``` | '||coalesce(rollback_ratio,'-1')||' | '||coalesce(hit_ratio,'-1')||' | '||coalesce(blk_read_time,-1)||' | '||coalesce(blk_write_time,-1)||' | '||coalesce(conflicts,-1)||' | '||coalesce(deadlocks,-1) from snap_pg_stat_database where snap_id >=i_begin_id and snap_id<=i_end_id order by datname, snap_ts
loop
res := array_append(res, tmp);
end loop;
res := array_append(res, ' ');
res := array_append(res, '#### 建议');
res := array_append(res, ' ');
res := array_append(res, '回滚比例大说明业务逻辑可能有问题, 命中率小说明shared_buffer要加大, 数据块读写时间长说明块设备的IO性能要提升, 死锁次数多说明业务逻辑有问题, 复制冲突次数多说明备库可能在跑LONG SQL. ');
res := array_append(res, ' ');
res := array_append(res, '### 3. 检查点, bgwriter 统计信息');
res := array_append(res, ' ');
res := array_append(res, 'checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_backend_fsync | buffers_alloc');