-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpg_profile--0.0.5a--0.0.6.sql
3413 lines (3123 loc) · 141 KB
/
pg_profile--0.0.5a--0.0.6.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
\echo Use "ALTER EXTENSION pg_profile UPDATE" to load this file. \quit
ALTER INDEX snapshots_pkey RENAME TO snapshots_pkey_old;
ALTER INDEX ix_snap_time RENAME TO ix_snap_time_old;
ALTER INDEX pk_snap_params RENAME TO pk_snap_params_old;
ALTER INDEX baselines_pkey RENAME TO baselines_pkey_old;
ALTER INDEX baselines_bl_name_key RENAME TO baselines_bl_name_key_old;
ALTER INDEX bl_snaps_pk RENAME TO bl_snaps_pk_old;
ALTER INDEX ix_bl_snaps_blid RENAME TO ix_bl_snaps_blid_old;
ALTER INDEX pk_snap_users RENAME TO pk_snap_users_old;
ALTER INDEX pk_snap_statements_n RENAME TO pk_snap_statements_n_old;
ALTER INDEX ix_snap_stmts_qid RENAME TO ix_snap_stmts_qid_old;
ALTER INDEX pk_snap_statements_total RENAME TO pk_snap_statements_total_old;
ALTER INDEX pk_tables_list RENAME TO pk_tables_list_old;
ALTER INDEX pk_snap_stat_user_tables RENAME TO pk_snap_stat_user_tables_old;
ALTER INDEX pk_indexes_list RENAME TO pk_indexes_list_old;
ALTER INDEX pk_snap_stat_user_indexes RENAME TO pk_snap_stat_user_indexes_old;
ALTER INDEX pk_funcs_list RENAME TO pk_funcs_list_old;
ALTER INDEX pk_snap_stat_user_functions RENAME TO pk_snap_stat_user_functions_old;
ALTER INDEX pk_snap_statio_user_tables RENAME TO pk_snap_statio_user_tables_old;
ALTER INDEX pk_snap_statio_user_indexes RENAME TO pk_snap_statio_user_indexes_old;
ALTER INDEX pk_snap_stat_database RENAME TO pk_snap_stat_database_old;
ALTER INDEX pk_snap_stat_cluster RENAME TO pk_snap_stat_cluster_old;
ALTER TABLE snap_params RENAME CONSTRAINT snap_params_snap_id_fkey TO snap_params_snap_id_fkey_old;
ALTER TABLE bl_snaps RENAME CONSTRAINT bl_snaps_bl_id_fkey TO bl_snaps_bl_id_fkey_old;
ALTER TABLE bl_snaps RENAME CONSTRAINT bl_snaps_snap_id_fkey TO bl_snaps_snap_id_fkey_old;
ALTER TABLE snap_statements RENAME CONSTRAINT snap_statements_queryid_md5_fkey TO snap_statements_queryid_md5_fkey_old;
ALTER TABLE snap_statements RENAME CONSTRAINT snap_statements_snap_id_fkey TO snap_statements_snap_id_fkey_old;
ALTER TABLE snap_statements_total RENAME CONSTRAINT snap_statements_total_snap_id_fkey TO snap_statements_total_snap_id_fkey_old;
ALTER TABLE snap_stat_user_tables RENAME CONSTRAINT snap_stat_user_tables_relid_fkey TO snap_stat_user_tables_relid_fkey_old;
ALTER TABLE snap_stat_user_tables RENAME CONSTRAINT snap_stat_user_tables_snap_id_fkey TO snap_stat_user_tables_snap_id_fkey_old;
ALTER TABLE snap_stat_user_indexes RENAME CONSTRAINT snap_stat_user_indexes_indexrelid_fkey TO snap_stat_user_indexes_indexrelid_fkey_old;
ALTER TABLE snap_stat_user_indexes RENAME CONSTRAINT snap_stat_user_indexes_relid_fkey TO snap_stat_user_indexes_relid_fkey_old;
ALTER TABLE snap_stat_user_indexes RENAME CONSTRAINT snap_stat_user_indexes_snap_id_fkey TO snap_stat_user_indexes_snap_id_fkey_old;
ALTER TABLE snap_stat_user_functions RENAME CONSTRAINT snap_stat_user_functions_funcid_fkey TO snap_stat_user_functions_funcid_fkey_old;
ALTER TABLE snap_stat_user_functions RENAME CONSTRAINT snap_stat_user_functions_snap_id_fkey TO snap_stat_user_functions_snap_id_fkey_old;
ALTER TABLE snap_statio_user_tables RENAME CONSTRAINT snap_statio_user_tables_relid_fkey TO snap_statio_user_tables_relid_fkey_old;
ALTER TABLE snap_statio_user_tables RENAME CONSTRAINT snap_statio_user_tables_snap_id_fkey TO snap_statio_user_tables_snap_id_fkey_old;
ALTER TABLE snap_statio_user_indexes RENAME CONSTRAINT snap_statio_user_indexes_indexrelid_fkey TO snap_statio_user_indexes_indexrelid_fkey_old;
ALTER TABLE snap_statio_user_indexes RENAME CONSTRAINT snap_statio_user_indexes_relid_fkey TO snap_statio_user_indexes_relid_fkey_old;
ALTER TABLE snap_statio_user_indexes RENAME CONSTRAINT snap_statio_user_indexes_snap_id_fkey TO snap_statio_user_indexes_snap_id_fkey_old;
ALTER TABLE snap_stat_database RENAME CONSTRAINT snap_stat_database_snap_id_fkey TO snap_stat_database_snap_id_fkey_old;
ALTER TABLE snap_stat_cluster RENAME CONSTRAINT snap_stat_cluster_snap_id_fkey TO snap_stat_cluster_snap_id_fkey_old;
DROP VIEW v_snap_statements;
DROP VIEW v_snap_stat_user_tables;
DROP VIEW v_snap_stat_user_indexes;
DROP VIEW v_snap_stat_user_functions;
DROP VIEW v_snap_statio_user_tables;
DROP VIEW v_snap_statio_user_indexes;
CREATE TABLE nodes (
node_id SERIAL PRIMARY KEY,
node_name name UNIQUE NOT NULL,
enabled boolean,
connstr text,
last_snap_id integer DEFAULT 0 NOT NULL
);
COMMENT ON TABLE nodes IS 'Monitored nodes (Postgres clusters) list';
INSERT INTO nodes (node_name,enabled,connstr,last_snap_id)
SELECT 'local',true,'dbname='||current_database()||' port='||current_setting('port'),coalesce(max(snap_id),0) FROM snapshots;
ALTER TABLE snapshots RENAME TO snapshots_old;
CREATE TABLE snapshots (
node_id integer NOT NULL REFERENCES nodes(node_id) ON DELETE CASCADE,
snap_id integer NOT NULL,
snap_time timestamp (0) with time zone,
CONSTRAINT pk_snapshots PRIMARY KEY (node_id, snap_id)
);
CREATE INDEX ix_snap_time ON snapshots(node_id, snap_time);
COMMENT ON TABLE snapshots IS 'Snapshot times list';
INSERT INTO snapshots SELECT 1,* FROM snapshots_old;
ALTER TABLE snap_params RENAME TO snap_params_old;
CREATE TABLE snap_params (
node_id integer NOT NULL,
snap_id integer NOT NULL,
p_name text,
setting text,
CONSTRAINT fk_snap_params_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots(node_id, snap_id) ON DELETE CASCADE,
CONSTRAINT pk_snap_params PRIMARY KEY (node_id, snap_id, p_name)
);
COMMENT ON TABLE snap_params IS 'PostgreSQL parameters at time of snapshot';
INSERT INTO snap_params SELECT 1,* FROM snap_params_old;
ALTER TABLE baselines RENAME TO baselines_old;
CREATE TABLE baselines (
node_id integer NOT NULL REFERENCES nodes(node_id) ON DELETE CASCADE,
bl_id SERIAL,
bl_name varchar (25) NOT NULL,
keep_until timestamp (0) with time zone,
CONSTRAINT pk_baselines PRIMARY KEY (node_id, bl_id),
CONSTRAINT uk_baselines UNIQUE (node_id,bl_name)
);
COMMENT ON TABLE baselines IS 'Baselines list';
INSERT INTO baselines SELECT 1,* FROM baselines_old;
ALTER TABLE bl_snaps RENAME TO bl_snaps_old;
CREATE TABLE bl_snaps (
node_id integer NOT NULL,
snap_id integer NOT NULL,
bl_id integer NOT NULL,
CONSTRAINT fk_bl_snaps_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots(node_id, snap_id) ON DELETE RESTRICT,
CONSTRAINT fk_bl_snaps_baselines FOREIGN KEY (node_id, bl_id) REFERENCES baselines(node_id, bl_id) ON DELETE CASCADE,
CONSTRAINT bl_snaps_pk PRIMARY KEY (node_id, snap_id, bl_id)
);
CREATE INDEX ix_bl_snaps_blid ON bl_snaps(bl_id);
COMMENT ON TABLE bl_snaps IS 'Snapshots in baselines';
INSERT INTO bl_snaps SELECT 1,* FROM bl_snaps_old;
ALTER TABLE snap_statements RENAME TO snap_statements_old;
CREATE TABLE snap_statements (
node_id integer NOT NULL,
snap_id integer NOT NULL,
userid oid,
dbid oid,
queryid bigint,
queryid_md5 char(10) REFERENCES stmt_list (queryid_md5) ON DELETE RESTRICT ON UPDATE CASCADE,
calls bigint,
total_time double precision,
min_time double precision,
max_time double precision,
mean_time double precision,
stddev_time double precision,
rows bigint,
shared_blks_hit bigint,
shared_blks_read bigint,
shared_blks_dirtied bigint,
shared_blks_written bigint,
local_blks_hit bigint,
local_blks_read bigint,
local_blks_dirtied bigint,
local_blks_written bigint,
temp_blks_read bigint,
temp_blks_written bigint,
blk_read_time double precision,
blk_write_time double precision,
CONSTRAINT pk_snap_statements_n PRIMARY KEY (node_id,snap_id,userid,dbid,queryid),
CONSTRAINT fk_statments_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots(node_id, snap_id) ON DELETE CASCADE
);
CREATE INDEX ix_snap_stmts_qid ON snap_statements (queryid_md5);
COMMENT ON TABLE snap_statements IS 'Snapshot statement statistics table (fields from pg_stat_statements)';
INSERT INTO snap_statements SELECT 1,* from snap_statements_old;
CREATE OR REPLACE VIEW v_snap_statements AS
SELECT
st.node_id as node_id,
st.snap_id as snap_id,
st.userid as userid,
st.dbid as dbid,
st.queryid as queryid,
queryid_md5 as queryid_md5,
st.calls as calls,
st.total_time as total_time,
st.min_time as min_time,
st.max_time as max_time,
st.mean_time as mean_time,
st.stddev_time as stddev_time,
st.rows as rows,
st.shared_blks_hit as shared_blks_hit,
st.shared_blks_read as shared_blks_read,
st.shared_blks_dirtied as shared_blks_dirtied,
st.shared_blks_written as shared_blks_written,
st.local_blks_hit as local_blks_hit,
st.local_blks_read as local_blks_read,
st.local_blks_dirtied as local_blks_dirtied,
st.local_blks_written as local_blks_written,
st.temp_blks_read as temp_blks_read,
st.temp_blks_written as temp_blks_written,
st.blk_read_time as blk_read_time,
st.blk_write_time as blk_write_time,
l.query as query
FROM
snap_statements st
JOIN stmt_list l USING (queryid_md5);
ALTER TABLE snap_statements_total RENAME TO snap_statements_total_old;
CREATE TABLE snap_statements_total (
node_id integer NOT NULL,
snap_id integer NOT NULL,
dbid oid,
calls bigint,
total_time double precision,
rows bigint,
shared_blks_hit bigint,
shared_blks_read bigint,
shared_blks_dirtied bigint,
shared_blks_written bigint,
local_blks_hit bigint,
local_blks_read bigint,
local_blks_dirtied bigint,
local_blks_written bigint,
temp_blks_read bigint,
temp_blks_written bigint,
blk_read_time double precision,
blk_write_time double precision,
statements bigint,
CONSTRAINT pk_snap_statements_total PRIMARY KEY (node_id, snap_id, dbid),
CONSTRAINT fk_statments_t_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots(node_id, snap_id) ON DELETE CASCADE
);
COMMENT ON TABLE snap_statements_total IS 'Aggregated stats for snapshot, based on pg_stat_statements';
INSERT INTO snap_statements_total SELECT 1,* FROM snap_statements_total_old;
ALTER TABLE tables_list RENAME TO tables_list_old;
CREATE TABLE tables_list(
node_id integer REFERENCES nodes(node_id) ON DELETE CASCADE,
relid oid,
schemaname name,
relname name,
CONSTRAINT pk_tables_list PRIMARY KEY (node_id, relid)
);
COMMENT ON TABLE tables_list IS 'Table names and scheams, captured in snapshots';
INSERT INTO tables_list SELECT 1,* FROM tables_list_old;
ALTER TABLE snap_stat_user_tables RENAME TO snap_stat_user_tables_old;
CREATE TABLE snap_stat_user_tables (
node_id integer NOT NULL,
snap_id integer NOT NULL,
dbid oid,
relid oid,
seq_scan bigint,
seq_tup_read bigint,
idx_scan bigint,
idx_tup_fetch bigint,
n_tup_ins bigint,
n_tup_upd bigint,
n_tup_del bigint,
n_tup_hot_upd bigint,
n_live_tup bigint,
n_dead_tup bigint,
n_mod_since_analyze bigint,
last_vacuum timestamp with time zone,
last_autovacuum timestamp with time zone,
last_analyze timestamp with time zone,
last_autoanalyze timestamp with time zone,
vacuum_count bigint,
autovacuum_count bigint,
analyze_count bigint,
autoanalyze_count bigint,
relsize bigint,
relsize_diff bigint,
CONSTRAINT pk_snap_stat_user_tables PRIMARY KEY (node_id, snap_id, dbid, relid),
CONSTRAINT fk_user_tables_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots(node_id, snap_id) ON DELETE CASCADE,
CONSTRAINT fk_user_tables_tables FOREIGN KEY (node_id, relid) REFERENCES tables_list(node_id, relid) ON DELETE RESTRICT ON UPDATE RESTRICT
);
COMMENT ON TABLE snap_stat_user_tables IS 'Stats increments for user tables in all databases by snapshots';
INSERT INTO snap_stat_user_tables SELECT 1,* FROM snap_stat_user_tables_old;
CREATE OR REPLACE VIEW v_snap_stat_user_tables AS
SELECT
node_id,
snap_id,
dbid,
relid,
schemaname,
relname,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_tup_hot_upd,
n_live_tup,
n_dead_tup,
n_mod_since_analyze,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
vacuum_count,
autovacuum_count,
analyze_count,
autoanalyze_count,
relsize,
relsize_diff
FROM ONLY snap_stat_user_tables JOIN tables_list USING (node_id, relid);
COMMENT ON VIEW v_snap_stat_user_tables IS 'Reconstructed stats view with table names and schemas';
ALTER TABLE last_stat_user_tables RENAME TO last_stat_user_tables_old;
CREATE TABLE last_stat_user_tables AS SELECT * FROM v_snap_stat_user_tables WHERE 0=1;
COMMENT ON TABLE last_stat_user_tables IS 'Last snapshot data for calculating diffs in next snapshot';
INSERT INTO last_stat_user_tables SELECT 1,* FROM last_stat_user_tables_old;
ALTER TABLE indexes_list RENAME TO indexes_list_old;
CREATE TABLE indexes_list(
node_id integer NOT NULL REFERENCES nodes(node_id) ON DELETE CASCADE,
indexrelid oid,
schemaname name,
indexrelname name,
CONSTRAINT pk_indexes_list PRIMARY KEY (node_id, indexrelid)
);
COMMENT ON TABLE indexes_list IS 'Index names and scheams, captured in snapshots';
INSERT INTO indexes_list SELECT 1,* FROM indexes_list_old;
ALTER TABLE snap_stat_user_indexes RENAME TO snap_stat_user_indexes_old;
CREATE TABLE snap_stat_user_indexes (
node_id integer NOT NULL,
snap_id integer NOT NULL,
dbid oid,
relid oid,
indexrelid oid,
idx_scan bigint,
idx_tup_read bigint,
idx_tup_fetch bigint,
relsize bigint,
relsize_diff bigint,
indisunique bool,
CONSTRAINT fk_user_indexes_tables FOREIGN KEY (node_id, relid) REFERENCES tables_list(node_id, relid) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_user_indexes_indexes FOREIGN KEY (node_id, indexrelid) REFERENCES indexes_list(node_id, indexrelid) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_user_indexes_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots(node_id, snap_id) ON DELETE CASCADE,
CONSTRAINT pk_snap_stat_user_indexes PRIMARY KEY (node_id, snap_id, dbid, relid, indexrelid)
);
COMMENT ON TABLE snap_stat_user_indexes IS 'Stats increments for user indexes in all databases by snapshots';
INSERT INTO snap_stat_user_indexes SELECT 1,* FROM snap_stat_user_indexes_old;
CREATE OR REPLACE VIEW v_snap_stat_user_indexes AS
SELECT
s.node_id,
s.snap_id,
s.dbid,
s.relid,
s.indexrelid,
il.schemaname,
tl.relname,
il.indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
relsize,
relsize_diff,
indisunique
FROM ONLY
snap_stat_user_indexes s
JOIN indexes_list il ON (il.indexrelid = s.indexrelid AND il.node_id = s.node_id)
JOIN tables_list tl ON (tl.relid = s.relid AND tl.node_id = s.node_id);
COMMENT ON VIEW v_snap_stat_user_indexes IS 'Reconstructed stats view with table and index names and schemas';
ALTER TABLE last_stat_user_indexes RENAME TO last_stat_user_indexes_old;
CREATE TABLE last_stat_user_indexes AS SELECT * FROM v_snap_stat_user_indexes WHERE 0=1;
COMMENT ON TABLE last_stat_user_indexes IS 'Last snapshot data for calculating diffs in next snapshot';
INSERT INTO last_stat_user_indexes SELECT 1, * FROM last_stat_user_indexes_old;
ALTER TABLE funcs_list RENAME TO funcs_list_old;
CREATE TABLE funcs_list(
node_id integer NOT NULL REFERENCES nodes(node_id) ON DELETE CASCADE,
funcid oid,
schemaname name,
funcname name,
CONSTRAINT pk_funcs_list PRIMARY KEY (node_id, funcid)
);
COMMENT ON TABLE funcs_list IS 'Function names and scheams, captured in snapshots';
INSERT INTO funcs_list SELECT 1,* FROM funcs_list_old;
ALTER TABLE snap_stat_user_functions RENAME TO snap_stat_user_functions_old;
CREATE TABLE snap_stat_user_functions (
node_id integer NOT NULL,
snap_id integer NOT NULL,
dbid oid,
funcid oid,
calls bigint,
total_time double precision,
self_time double precision,
CONSTRAINT fk_user_functions_functions FOREIGN KEY (node_id, funcid) REFERENCES funcs_list (node_id, funcid) ON DELETE RESTRICT,
CONSTRAINT fk_user_functions_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots (node_id, snap_id) ON DELETE CASCADE,
CONSTRAINT pk_snap_stat_user_functions PRIMARY KEY (node_id, snap_id, dbid, funcid)
);
COMMENT ON TABLE snap_stat_user_functions IS 'Stats increments for user functions in all databases by snapshots';
INSERT INTO snap_stat_user_functions SELECT 1,* FROM snap_stat_user_functions_old;
CREATE OR REPLACE VIEW v_snap_stat_user_functions AS
SELECT
node_id,
snap_id,
dbid,
funcid,
schemaname,
funcname,
calls,
total_time,
self_time
FROM ONLY snap_stat_user_functions JOIN funcs_list USING (node_id,funcid);
COMMENT ON VIEW v_snap_stat_user_indexes IS 'Reconstructed stats view with function names and schemas';
ALTER TABLE last_stat_user_functions RENAME TO last_stat_user_functions_old;
CREATE TABLE last_stat_user_functions AS SELECT * FROM v_snap_stat_user_functions WHERE 0=1;
COMMENT ON TABLE last_stat_user_functions IS 'Last snapshot data for calculating diffs in next snapshot';
INSERT INTO last_stat_user_functions SELECT 1,* FROM last_stat_user_functions_old;
ALTER TABLE snap_statio_user_tables RENAME TO snap_statio_user_tables_old;
CREATE TABLE snap_statio_user_tables (
node_id integer NOT NULL,
snap_id integer NOT NULL,
dbid oid NOT NULL,
relid oid NOT NULL,
heap_blks_read bigint,
heap_blks_hit bigint,
idx_blks_read bigint,
idx_blks_hit bigint,
toast_blks_read bigint,
toast_blks_hit bigint,
tidx_blks_read bigint,
tidx_blks_hit bigint,
relsize bigint,
relsize_diff bigint,
CONSTRAINT fk_statio_tables_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots (node_id, snap_id) ON DELETE CASCADE,
CONSTRAINT fk_statio_tables_tables FOREIGN KEY (node_id, relid) REFERENCES tables_list(node_id, relid) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT pk_snap_statio_user_tables PRIMARY KEY (node_id, snap_id, dbid, relid)
);
COMMENT ON TABLE snap_statio_user_tables IS 'IO Stats increments for user tables in all databases by snapshots';
INSERT INTO snap_statio_user_tables SELECT 1,* FROM snap_statio_user_tables_old;
CREATE OR REPLACE VIEW v_snap_statio_user_tables AS
SELECT
node_id,
snap_id,
dbid,
relid,
schemaname,
relname,
heap_blks_read,
heap_blks_hit,
idx_blks_read,
idx_blks_hit,
toast_blks_read,
toast_blks_hit,
tidx_blks_read,
tidx_blks_hit,
relsize,
relsize_diff
FROM ONLY snap_statio_user_tables JOIN tables_list USING (node_id, relid);
COMMENT ON VIEW v_snap_statio_user_tables IS 'Reconstructed stats view with table names and schemas';
ALTER TABLE last_statio_user_tables RENAME TO last_statio_user_tables_old;
CREATE TABLE last_statio_user_tables AS SELECT * FROM v_snap_statio_user_tables WHERE 0=1;
COMMENT ON TABLE last_statio_user_tables IS 'Last snapshot data for calculating diffs in next snapshot';
INSERT INTO last_statio_user_tables SELECT 1,* FROM last_statio_user_tables_old;
ALTER TABLE snap_statio_user_indexes RENAME TO snap_statio_user_indexes_old;
CREATE TABLE snap_statio_user_indexes (
node_id integer NOT NULL,
snap_id integer NOT NULL,
dbid oid,
relid oid,
indexrelid oid,
idx_blks_read bigint,
idx_blks_hit bigint,
relsize bigint,
relsize_diff bigint,
CONSTRAINT fk_statio_indexes_tables FOREIGN KEY (node_id, relid) REFERENCES tables_list(node_id, relid) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_statio_indexes_indexes FOREIGN KEY (node_id, indexrelid) REFERENCES indexes_list(node_id, indexrelid) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_statio_indexes_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots (node_id, snap_id) ON DELETE CASCADE,
CONSTRAINT pk_snap_statio_user_indexes PRIMARY KEY (node_id, snap_id, dbid, relid, indexrelid)
);
COMMENT ON TABLE snap_statio_user_indexes IS 'Stats increments for user indexes in all databases by snapshots';
INSERT INTO snap_statio_user_indexes SELECT 1,* FROM snap_statio_user_indexes_old;
CREATE OR REPLACE VIEW v_snap_statio_user_indexes AS
SELECT
s.node_id,
s.snap_id,
s.dbid,
s.relid,
s.indexrelid,
il.schemaname,
tl.relname,
il.indexrelname,
idx_blks_read,
idx_blks_hit,
relsize,
relsize_diff
FROM
ONLY snap_statio_user_indexes s
JOIN tables_list tl ON (s.relid = tl.relid and s.node_id=tl.node_id)
JOIN indexes_list il ON (s.indexrelid = il.indexrelid and s.node_id=il.node_id);
COMMENT ON VIEW v_snap_statio_user_indexes IS 'Reconstructed stats view with table and index names and schemas';
ALTER TABLE last_statio_user_indexes RENAME TO last_statio_user_indexes_old;
CREATE TABLE last_statio_user_indexes AS SELECT * FROM v_snap_statio_user_indexes WHERE 0=1;
COMMENT ON TABLE last_statio_user_indexes IS 'Last snapshot data for calculating diffs in next snapshot';
INSERT INTO last_statio_user_indexes SELECT 1,* FROM last_statio_user_indexes_old;
ALTER TABLE snap_stat_database RENAME TO snap_stat_database_old;
CREATE TABLE snap_stat_database
(
node_id integer NOT NULL,
snap_id integer NOT NULL,
datid oid,
datname name,
xact_commit bigint,
xact_rollback bigint,
blks_read bigint,
blks_hit bigint,
tup_returned bigint,
tup_fetched bigint,
tup_inserted bigint,
tup_updated bigint,
tup_deleted bigint,
conflicts bigint,
temp_files bigint,
temp_bytes bigint,
deadlocks bigint,
blk_read_time double precision,
blk_write_time double precision,
stats_reset timestamp with time zone,
datsize_delta bigint,
CONSTRAINT fk_statdb_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots (node_id, snap_id) ON DELETE CASCADE,
CONSTRAINT pk_snap_stat_database PRIMARY KEY (node_id,snap_id,datid,datname)
);
COMMENT ON TABLE snap_stat_database IS 'Snapshot database statistics table (fields from pg_stat_database)';
INSERT INTO snap_stat_database SELECT 1,* FROM snap_stat_database_old;
ALTER TABLE last_stat_database RENAME TO last_stat_database_old;
CREATE TABLE last_stat_database AS SELECT * FROM snap_stat_database WHERE 0=1;
COMMENT ON TABLE last_stat_database IS 'Last snapshot data for calculating diffs in next snapshot';
INSERT INTO last_stat_database SELECT 1,* FROM last_stat_database_old;
ALTER TABLE snap_stat_cluster RENAME TO snap_stat_cluster_old;
CREATE TABLE snap_stat_cluster
(
node_id integer NOT NULL,
snap_id integer NOT NULL,
checkpoints_timed bigint,
checkpoints_req bigint,
checkpoint_write_time double precision,
checkpoint_sync_time double precision,
buffers_checkpoint bigint,
buffers_clean bigint,
maxwritten_clean bigint,
buffers_backend bigint,
buffers_backend_fsync bigint,
buffers_alloc bigint,
stats_reset timestamp with time zone,
wal_size bigint,
CONSTRAINT fk_statcluster_snapshots FOREIGN KEY (node_id, snap_id) REFERENCES snapshots (node_id, snap_id) ON DELETE CASCADE,
CONSTRAINT pk_snap_stat_cluster PRIMARY KEY (node_id, snap_id)
);
COMMENT ON TABLE snap_stat_cluster IS 'Snapshot cluster statistics table (fields from pg_stat_bgwriter, etc.)';
INSERT INTO snap_stat_cluster SELECT 1,* FROM snap_stat_cluster_old;
ALTER TABLE last_stat_cluster RENAME TO last_stat_cluster_old;
CREATE TABLE last_stat_cluster AS SELECT * FROM snap_stat_cluster WHERE 0=1;
COMMENT ON TABLE last_stat_cluster IS 'Last snapshot data for calculating diffs in next snapshot';
INSERT INTO last_stat_cluster SELECT 1,* FROM last_stat_cluster_old;
DROP TABLE baselines_old,
snapshots_old,
snap_params_old,
bl_snaps_old,
snap_statements_old,
snap_statements_total_old,
snap_stat_user_tables_old,
last_stat_user_tables_old,
snap_stat_user_indexes_old,
last_stat_user_indexes_old,
tables_list_old,
indexes_list_old,
funcs_list_old,
snap_stat_user_functions_old,
last_stat_user_functions_old,
snap_statio_user_tables_old,
last_statio_user_tables_old,
snap_statio_user_indexes_old,
last_statio_user_indexes_old,
snap_stat_database_old,
last_stat_database_old,
snap_stat_cluster_old,
last_stat_cluster_old
CASCADE;
DROP FUNCTION statements_stats_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION cluster_stats_htbl(start_id integer, end_id integer);
DROP FUNCTION top_scan_tables_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION top_dml_tables_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION top_upd_vac_tables_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION top_growth_tables_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION nodata_wrapper(section_text text);
DROP FUNCTION report(start_id integer, end_id integer);
DROP FUNCTION snapshot();
DROP FUNCTION collect_obj_stats(s_id integer);
DROP FUNCTION snapshot_dbobj_delta(s_id integer, topn integer);
DROP FUNCTION snapshot_show(days integer);
DROP FUNCTION baseline_new(name character varying, start_id integer, end_id integer, days integer);
DROP FUNCTION baseline_drop(name character varying);
DROP FUNCTION baseline_keep(name character varying, days integer);
DROP FUNCTION baseline_show();
DROP FUNCTION dbstats_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION top_growth_indexes_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION tbl_top_dead_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION tbl_top_mods_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION ix_unused_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION tbl_top_io_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION ix_top_io_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION func_top_time_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION func_top_calls_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION check_stmt_cnt(start_id integer, end_id integer);
DROP FUNCTION top_elapsed_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION top_exec_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION top_iowait_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION top_gets_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION top_temp_htbl(start_id integer, end_id integer, topn integer);
DROP FUNCTION collect_queries(query_id character, query_text text);
DROP FUNCTION report_queries();
/* ========= Internal functions ========= */
CREATE OR REPLACE FUNCTION get_connstr(IN snode_id integer) RETURNS text SET search_path=@extschema@,public SET lock_timeout=300000 AS $$
DECLARE
node_connstr text = null;
BEGIN
--Getting node_connstr
SELECT connstr INTO node_connstr FROM nodes n WHERE n.node_id = snode_id;
IF (node_connstr IS NULL) THEN
RAISE 'node_id not found';
ELSE
RETURN node_connstr;
END IF;
END;
$$ LANGUAGE plpgsql;
/* ========= Snapshot functions ========= */
CREATE OR REPLACE FUNCTION snapshot(IN snode_id integer) RETURNS integer SET search_path=@extschema@,public SET lock_timeout=300000 AS $$
DECLARE
id integer;
topn integer;
ret integer;
lockid bigint;
stat_stmt_avail integer;
pg_version varchar(10);
qres record;
node_connstr text;
BEGIN
-- Only one running snapshot() function allowed!
-- Explicitly locking nodes table
BEGIN
LOCK nodes IN SHARE ROW EXCLUSIVE MODE NOWAIT;
EXCEPTION
WHEN OTHERS THEN RAISE 'Can''t get lock on nodes table. Is there another snapshot() running?';
END;
-- Getting TopN setting
BEGIN
topn := current_setting('pg_profile.topn')::integer;
EXCEPTION
WHEN OTHERS THEN topn := 20;
END;
-- Getting retention setting
BEGIN
ret := current_setting('pg_profile.retention')::integer;
EXCEPTION
WHEN OTHERS THEN ret := 7;
END;
node_connstr := get_connstr(snode_id);
IF dblink_get_connections() @> ARRAY['node_connection'] THEN
PERFORM dblink_disconnect('node_connection');
END IF;
PERFORM dblink_connect('node_connection',node_connstr);
--Getting postgres version
SELECT setting INTO STRICT pg_version FROM dblink('node_connection','SELECT setting FROM pg_catalog.pg_settings WHERE name = ''server_version_num''') AS t (setting text);
-- Deleting obsolete baselines
DELETE FROM baselines WHERE keep_until < now();
-- Deleting obsolote snapshots
DELETE FROM snapshots WHERE snap_time < now() - (ret || ' days')::interval
AND (node_id,snap_id) NOT IN (SELECT node_id,snap_id FROM bl_snaps);
-- Deleting unused statements
DELETE FROM stmt_list
WHERE queryid_md5 NOT IN
(SELECT queryid_md5 FROM snap_statements);
-- Creating a new snapshot record
UPDATE nodes SET last_snap_id = last_snap_id + 1 WHERE node_id = snode_id
RETURNING last_snap_id INTO id;
INSERT INTO snapshots(snap_time,node_id,snap_id)
VALUES (now(),snode_id,id);
-- Collecting postgres parameters
INSERT INTO snap_params
SELECT snode_id,id,name,setting FROM dblink('node_connection','SELECT name,setting
FROM pg_catalog.pg_settings
WHERE name IN (''pg_stat_statements.max'',''pg_stat_statements.track'')') AS dbl (
name text,
setting text
);
INSERT INTO snap_params
VALUES (snode_id,id,'pg_profile.topn',topn);
-- collect pg_stat_statements stats if available
SELECT cnt INTO stat_stmt_avail FROM
dblink('node_connection','SELECT count(1) FROM pg_catalog.pg_extension WHERE extname=''pg_stat_statements''')
as t1(cnt integer);
IF stat_stmt_avail > 0 THEN
PERFORM collect_statements_stats(snode_id, id, topn);
END IF;
-- pg_stat_database data
INSERT INTO last_stat_database (
node_id,
snap_id,
datid,
datname,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted,
conflicts,
temp_files,
temp_bytes,
deadlocks,
blk_read_time,
blk_write_time,
stats_reset,
datsize_delta)
SELECT
snode_id,
id,
datid,
datname,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted,
conflicts,
temp_files,
temp_bytes,
deadlocks,
blk_read_time,
blk_write_time,
stats_reset,
datsize
FROM dblink('node_connection','SELECT
datid,
datname,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted,
conflicts,
temp_files,
temp_bytes,
deadlocks,
blk_read_time,
blk_write_time,
stats_reset,
pg_database_size(datid) as datsize
FROM pg_catalog.pg_stat_database') AS rs (
datid oid,
datname name,
xact_commit bigint,
xact_rollback bigint,
blks_read bigint,
blks_hit bigint,
tup_returned bigint,
tup_fetched bigint,
tup_inserted bigint,
tup_updated bigint,
tup_deleted bigint,
conflicts bigint,
temp_files bigint,
temp_bytes bigint,
deadlocks bigint,
blk_read_time double precision,
blk_write_time double precision,
stats_reset timestamp with time zone,
datsize bigint
);
INSERT INTO snap_stat_database
SELECT
rs.node_id,
rs.snap_id,
rs.datid,
rs.datname,
rs.xact_commit-ls.xact_commit,
rs.xact_rollback-ls.xact_rollback,
rs.blks_read-ls.blks_read,
rs.blks_hit-ls.blks_hit,
rs.tup_returned-ls.tup_returned,
rs.tup_fetched-ls.tup_fetched,
rs.tup_inserted-ls.tup_inserted,
rs.tup_updated-ls.tup_updated,
rs.tup_deleted-ls.tup_deleted,
rs.conflicts-ls.conflicts,
rs.temp_files-ls.temp_files,
rs.temp_bytes-ls.temp_bytes,
rs.deadlocks-ls.deadlocks,
rs.blk_read_time-ls.blk_read_time,
rs.blk_write_time-ls.blk_write_time,
rs.stats_reset,
rs.datsize_delta-ls.datsize_delta
FROM ONLY(last_stat_database) rs
JOIN ONLY(last_stat_database) ls ON (rs.datid = ls.datid AND rs.datname = ls.datname AND ls.node_id = rs.node_id
AND rs.stats_reset = ls.stats_reset AND ls.snap_id = rs.snap_id - 1)
WHERE rs.snap_id = id AND rs.node_id = snode_id;
PERFORM snapshot_dbobj_delta(snode_id,id,topn);
DELETE FROM last_stat_database WHERE node_id = snode_id AND snap_id = id - 1;
-- pg_stat_bgwriter data
IF pg_version::integer < 100000 THEN
INSERT INTO last_stat_cluster (
node_id,
snap_id,
checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
maxwritten_clean,
buffers_backend,
buffers_backend_fsync,
buffers_alloc,
stats_reset,
wal_size)
SELECT
snode_id,
id,
checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
maxwritten_clean,
buffers_backend,
buffers_backend_fsync,
buffers_alloc,
stats_reset,
wal_size
FROM dblink('node_connection','SELECT
checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
maxwritten_clean,
buffers_backend,
buffers_backend_fsync,
buffers_alloc,
stats_reset,
CASE WHEN pg_is_in_recovery() THEN 0
ELSE pg_xlog_location_diff(pg_current_xlog_location(),''0/00000000'')
END AS wal_size
FROM pg_catalog.pg_stat_bgwriter') AS rs (
checkpoints_timed bigint,
checkpoints_req bigint,
checkpoint_write_time double precision,
checkpoint_sync_time double precision,
buffers_checkpoint bigint,
buffers_clean bigint,
maxwritten_clean bigint,
buffers_backend bigint,
buffers_backend_fsync bigint,
buffers_alloc bigint,
stats_reset timestamp with time zone,
wal_size bigint);
ELSIF pg_version::integer >= 100000 THEN
INSERT INTO last_stat_cluster (
node_id,
snap_id,
checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
maxwritten_clean,
buffers_backend,
buffers_backend_fsync,
buffers_alloc,
stats_reset,
wal_size)
SELECT
snode_id,
id,
checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
maxwritten_clean,
buffers_backend,
buffers_backend_fsync,
buffers_alloc,
stats_reset,
wal_size
FROM dblink('node_connection','SELECT
checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
maxwritten_clean,
buffers_backend,
buffers_backend_fsync,
buffers_alloc,
stats_reset,
CASE WHEN pg_is_in_recovery() THEN 0
ELSE pg_wal_lsn_diff(pg_current_wal_lsn(),''0/00000000'')
END AS wal_size
FROM pg_catalog.pg_stat_bgwriter') AS rs (
checkpoints_timed bigint,
checkpoints_req bigint,
checkpoint_write_time double precision,
checkpoint_sync_time double precision,
buffers_checkpoint bigint,
buffers_clean bigint,
maxwritten_clean bigint,
buffers_backend bigint,
buffers_backend_fsync bigint,
buffers_alloc bigint,
stats_reset timestamp with time zone,
wal_size bigint);
END IF;
INSERT INTO snap_stat_cluster
SELECT
rs.node_id,
rs.snap_id,
rs.checkpoints_timed-ls.checkpoints_timed,
rs.checkpoints_req-ls.checkpoints_req,
rs.checkpoint_write_time-ls.checkpoint_write_time,
rs.checkpoint_sync_time-ls.checkpoint_sync_time,
rs.buffers_checkpoint-ls.buffers_checkpoint,
rs.buffers_clean-ls.buffers_clean,
rs.maxwritten_clean-ls.maxwritten_clean,
rs.buffers_backend-ls.buffers_backend,
rs.buffers_backend_fsync-ls.buffers_backend_fsync,
rs.buffers_alloc-ls.buffers_alloc,
rs.stats_reset,
rs.wal_size-ls.wal_size
FROM last_stat_cluster rs
JOIN ONLY(last_stat_cluster) ls ON (rs.stats_reset = ls.stats_reset AND rs.node_id=ls.node_id
AND ls.snap_id = rs.snap_id - 1)
WHERE rs.snap_id = id AND rs.node_id = snode_id;
DELETE FROM last_stat_cluster WHERE node_id = snode_id AND snap_id = id - 1;
-- Delete unused tables from tables list
DELETE FROM tables_list WHERE (node_id,relid) NOT IN (
SELECT node_id, relid FROM snap_stat_user_tables
UNION ALL
SELECT node_id, relid FROM snap_statio_user_tables
UNION ALL
SELECT node_id, relid FROM snap_stat_user_indexes
UNION ALL
SELECT node_id, relid FROM snap_statio_user_indexes
);
-- Delete unused indexes from indexes list
DELETE FROM indexes_list WHERE (node_id, indexrelid) NOT IN (
SELECT node_id, indexrelid FROM snap_stat_user_indexes
UNION ALL
SELECT node_id, indexrelid FROM snap_statio_user_indexes
);
-- Delete unused functions from functions list
DELETE FROM funcs_list WHERE (node_id, funcid) NOT IN (
SELECT node_id, funcid FROM snap_stat_user_functions
);
PERFORM dblink_disconnect('node_connection');
RETURN id;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION snapshot(IN snode_id integer) IS 'Statistics snapshot creation function (by node_id).';