This repository has been archived by the owner on Dec 22, 2021. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 10
/
query_all.yaml
1702 lines (1702 loc) · 54.3 KB
/
query_all.yaml
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
pg_stat_bgwriter:
name: pg_stat_bgwriter
desc: OpenGauss background writer metrics
query:
- name: pg_stat_bgwriter
sql: |-
SELECT checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
buffers_backend,
maxwritten_clean,
buffers_backend_fsync,
buffers_alloc,
stats_reset
FROM pg_stat_bgwriter
version: '>=0.0.0'
timeout: 1
ttl: 60
status: enable
metrics:
- name: checkpoints_timed
description: scheduled checkpoints that have been performed
usage: COUNTER
- name: checkpoints_req
description: requested checkpoints that have been performed
usage: COUNTER
- name: checkpoint_write_time
description: time spending on writing files to disk, in µs
usage: COUNTER
- name: checkpoint_sync_time
description: time spending on syncing files to disk, in µs
usage: COUNTER
- name: buffers_checkpoint
description: buffers written during checkpoints
usage: COUNTER
- name: buffers_clean
description: buffers written by the background writer
usage: COUNTER
- name: buffers_backend
description: buffers written directly by a backend
usage: COUNTER
- name: maxwritten_clean
description: times that bgwriter stopped a cleaning scan
usage: COUNTER
- name: buffers_backend_fsync
description: times a backend had to execute its own fsync
usage: COUNTER
- name: buffers_alloc
description: buffers allocated
usage: COUNTER
- name: stats_reset
description: time when statistics were last reset
usage: COUNTER
status: enable
ttl: 60
timeout: 1
pg_database:
name: pg_database
desc: OpenGauss Database size
query:
- name: pg_database
sql: |-
SELECT datname,
pg_database_size(pg_database.datname) as size_bytes,
age(datfrozenxid64) AS age,
datistemplate AS is_template,
datallowconn AS allow_conn,
datconnlimit AS conn_limit,
datfrozenxid::TEXT::BIGINT as frozen_xid
FROM pg_database
where datname NOT IN ('template0','template1');
version: '>=0.0.0'
timeout: 1
ttl: 60
status: enable
metrics:
- name: datname
description: Name of this database
usage: LABEL
- name: size_bytes
description: Disk space used by the database
usage: GAUGE
- name: age
description: database age calculated by age(datfrozenxid64)
usage: GAUGE
- name: is_template
description: 1 for template db and 0 for normal db
usage: GAUGE
- name: allow_conn
description: 1 allow connection and 0 does not allow
usage: GAUGE
- name: conn_limit
description: connection limit, -1 for no limit
usage: GAUGE
- name: frozen_xid
description: tuple with xmin below this will always be visable (until wrap around)
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_meta:
name: pg_meta
desc: OpenGauss database directory
query:
- name: pg_meta
sql: |-
SELECT (SELECT system_identifier FROM pg_control_system()) AS cluster_id,
current_setting('port') AS listen_port,
current_setting('wal_level') AS wal_level,
current_setting('server_version') AS version,
current_setting('server_version_num') AS ver_num,
'N/A' AS primary_conninfo,
1 AS info;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: cluster_id
description: cluster system identifier
usage: LABEL
- name: listen_port
description: listen port
usage: LABEL
- name: wal_level
description: wal level
usage: LABEL
- name: version
description: server version in human readable format
usage: LABEL
- name: ver_num
description: server version number in machine readable format
usage: LABEL
- name: primary_conninfo
description: connection string to upstream (do not set password here)
usage: LABEL
- name: info
description: constant 1
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_connections:
name: pg_connections
desc: OpenGauss database connections
query:
- name: pg_connections
sql: select max_conn,used_conn,max_conn-used_conn res_for_normal from (select count(*) used_conn from pg_stat_activity) t1,(select setting::int max_conn from pg_settings where name='max_connections') t2;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: max_conn
description: total of connections
usage: GAUGE
- name: used_conn
description: used of connections
usage: GAUGE
- name: res_for_normal
description: reserve of connections
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_stat_activity:
name: pg_stat_activity
desc: OpenGauss backend activity group by state
query:
- name: pg_stat_activity
sql: |-
SELECT datname,
state,
coalesce(count, 0) AS count,
coalesce(max_duration, 0) AS max_duration,
coalesce(max_tx_duration, 0) AS max_tx_duration,
coalesce(max_conn_duration, 0) AS max_conn_duration
FROM (SELECT d.oid AS database, d.datname, a.state
FROM pg_database d,
unnest(ARRAY ['active','idle','idle in transaction','idle in transaction (aborted)','fastpath function call','disabled']) a(state)
WHERE d.datname NOT IN ('template0','template1')) base
LEFT JOIN (
SELECT datname, state,
count(*) AS count,
max(extract(epoch from now() - state_change)) AS max_duration,
max(extract(epoch from now() - xact_start)) AS max_tx_duration,
max(extract(epoch from now() - backend_start)) AS max_conn_duration
FROM pg_stat_activity WHERE pid <> pg_backend_pid()
GROUP BY datname, state
) a USING (datname, state);
version: '>=1.0.0'
timeout: 1
ttl: 60
status: enable
metrics:
- name: datname
description: Name of this database
usage: LABEL
- name: state
description: connection state
usage: LABEL
- name: count
description: number of connections in this state
usage: GAUGE
- name: max_duration
description: max duration since state change among (datname, state)
usage: GAUGE
- name: max_tx_duration
description: max duration in seconds any active transaction has been running
usage: GAUGE
- name: max_conn_duration
description: max backend session duration since state change among (datname, state)
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_stat_replication:
name: pg_stat_replication
query:
- name: pg_stat_replication
sql: |-
select
pid,
client_addr,
application_name,
state,
sync_state,
lsn,
lsn - sent_lsn as sent_diff,
lsn - write_lsn as write_diff,
lsn - flush_lsn as flush_diff,
lsn - replay_lsn as replay_diff,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
backend_uptime,
sync_priority
from
(
select
pr.pid,
client_addr,
application_name,
pr.state,
pr.sync_state,
pg_xlog_location_diff (case when pg_is_in_recovery() then pg_last_xlog_receive_location() else pg_current_xlog_location() end, '0/0') as lsn,
pg_xlog_location_diff(pr.sender_sent_location,'0/0') as sent_lsn,
pg_xlog_location_diff(pr.receiver_write_location,'0/0') as write_lsn,
pg_xlog_location_diff(pr.receiver_flush_location,'0/0') as flush_lsn,
pg_xlog_location_diff(pr.receiver_replay_location,'0/0') as replay_lsn,
pg_xlog_location_diff(pr.receiver_replay_location, pg_current_xlog_location()) as replay_lag,
extract(EPOCH from now() - backend_start) as backend_uptime,
pr.sync_priority
from
pg_stat_replication pr
);
version: '>=1.0.0'
timeout: 1
ttl: 60
status: enable
metrics:
- name: pid
description: unique walsender pid
usage: LABEL
- name: client_addr
description: client address of wal receiver
usage: LABEL
- name: application_name
description: application name of standby
usage: LABEL
- name: state
description: replication state startup|catchup|streaming|backup|stopping
usage: LABEL
- name: sync_state
description: replication sync state async|potential|sync|quorum
usage: LABEL
- name: lsn
description: current log position on this server
usage: COUNTER
- name: sent_diff
description: last log position sent to this standby server diff with current lsn
usage: GAUGE
- name: write_diff
description: last log position written to disk by this standby server diff with current lsn
usage: GAUGE
- name: flush_diff
description: last log position flushed to disk by this standby server diff with current lsn
usage: GAUGE
- name: replay_diff
description: last log position replayed into the database on this standby server diff with current lsn
usage: GAUGE
- name: sent_lsn
description: last log position sent to this standby server
usage: COUNTER
- name: write_lsn
description: last log position written to disk by this standby server
usage: COUNTER
- name: flush_lsn
description: last log position flushed to disk by this standby server
usage: COUNTER
- name: replay_lsn
description: last log position replayed into the database on this standby server
usage: COUNTER
- name: write_lag
description: latest ACK lsn diff with write (sync-remote-write lag)
usage: GAUGE
- name: flush_lag
description: latest ACK lsn diff with flush (sync-remote-flush lag)
usage: GAUGE
- name: replay_lag
description: latest ACK lsn diff with replay (sync-remote-apply lag)
usage: GAUGE
- name: backend_uptime
description: how long since standby connect to this server
usage: GAUGE
- name: backend_xmin
description: this standby's xmin horizon reported by hot_standby_feedback.
usage: GAUGE
- name: sync_priority
description: priority of being chosen as synchronous standby
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_downstream:
name: pg_downstream
desc: OpenGauss database downstream
query:
- name: pg_downstream
sql: |-
SELECT l.state, coalesce(count, 0 ) AS count
FROM unnest(ARRAY ['Streaming','Startup','Catchup', 'Backup', 'Stopping']) l(state)
LEFT JOIN (SELECT state, count(*) AS count FROM pg_stat_replication GROUP BY state)r ON l.state = r.state;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: state
description: downstream state
usage: LABEL
- name: count
description: downstream count
usage: GAUGE
status: enable
ttl: 5
timeout: 1
pg_replication_slots:
name: pg_replication_slots
query:
- name: pg_replication_slots
sql: |-
select slot_name,
database as datname,
database as datname,
coalesce(plugin,'_') as plugin,
slot_type,datoid,coalesce(database,'_') as database,
active,
coalesce(xmin,'_') as xmin,
coalesce(catalog_xmin,'_') as catalog_xmin,
restart_lsn,
pg_xlog_location_diff(pg_current_xlog_location(),restart_lsn) as delay_lsn,
dummy_standby,
pg_xlog_location_diff(restart_lsn ,'0/0'::text) AS restart_lsn,
pg_xlog_location_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_receive_location()
ELSE pg_current_xlog_location() END , restart_lsn) AS retained_bytes
from pg_replication_slots;
version: '>=1.0.0'
timeout: 1
ttl: 60
status: enable
metrics:
- name: slot_name
description: Slot name
usage: LABEL
- name: plugin
description: Logical plugin
usage: LABEL
- name: slot_type
description: Slot type
usage: LABEL
- name: datoid
description: Database oid
usage: LABEL
- name: database
description: Database name
usage: LABEL
- name: active
description: Is active
usage: LABEL
- name: xmin
description: replication xid
usage: LABEL
- name: catalog_xmin
description: logical decode xid
usage: LABEL
- name: restart_lsn
description: Xlog info
usage: LABEL
- name: delay_lsn
description: delay lsn from pg_current_xlog_location()
usage: GAUGE
- name: dummy_standby
description: Is real standby
usage: DISCARD
status: enable
ttl: 60
timeout: 1
pg_func:
name: pg_func
query:
- name: pg_func
sql: |-
SELECT CURRENT_CATALOG AS datname,
schemaname AS nspname,
funcname,
funcid,
calls,
total_time,
self_time
FROM pg_stat_user_functions
ORDER BY 4 DESC LIMIT 128;
version: '>=1.0.0'
timeout: 1
ttl: 60
status: enable
metrics:
- name: datname
description: database name of this function
usage: LABEL
- name: nspname
description: schema name of this function
usage: LABEL
- name: funcname
description: function name
usage: LABEL
- name: funcid
description: function id, dedupe for funcname override
usage: LABEL
- name: calls
description: how many times this function has been called
usage: COUNTER
- name: total_time
description: how much time spent in this function and it's child function, in ms
usage: COUNTER
- name: self_time
description: how much time spent in this function itself (other func not included), in ms
usage: COUNTER
status: enable
ttl: 60
timeout: 1
pg_lock:
name: pg_lock
desc: OpenGauss lock distribution by mode
query:
- name: pg_lock
sql: |-
SELECT datname, mode, coalesce(count, 0) AS count
FROM (
SELECT d.oid AS database, d.datname, l.mode
FROM pg_database d,unnest(ARRAY ['AccessShareLock','RowShareLock','RowExclusiveLock','ShareUpdateExclusiveLock','ShareLock','ShareRowExclusiveLock','ExclusiveLock','AccessExclusiveLock']) l(mode)
WHERE d.datname NOT IN ('template0','template1')) base
LEFT JOIN (SELECT database, mode, count(1) AS count
FROM pg_locks
WHERE database IS NOT NULL GROUP BY database, mode) cnt
USING (database, mode);
version: '>=0.0.0'
timeout: 1
ttl: 60
status: enable
metrics:
- name: datname
description: Name of this database
usage: LABEL
- name: mode
description: Type of Lock
usage: LABEL
- name: count
description: Number of locks
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_stat_database_conflicts:
name: pg_stat_database_conflicts
desc: OpenGauss database statistics conflicts
query:
- name: pg_stat_database_conflicts
sql: select * from pg_stat_database_conflicts where datname NOT IN ('template0','template1');
version: '>=0.0.0'
timeout: 1
ttl: 60
status: enable
metrics:
- name: datid
description: OID of a database
usage: LABEL
- name: datname
description: Name of this database
usage: LABEL
- name: confl_tablespace
description: Number of queries in this database that have been canceled due to dropped tablespaces
usage: COUNTER
- name: confl_lock
description: Number of queries in this database that have been canceled due to lock timeouts
usage: COUNTER
- name: confl_snapshot
description: Number of queries in this database that have been canceled due to old snapshots
usage: COUNTER
- name: confl_bufferpin
description: Number of queries in this database that have been canceled due to pinned buffers
usage: COUNTER
- name: confl_deadlock
description: Number of queries in this database that have been canceled due to deadlocks
usage: COUNTER
status: enable
ttl: 60
timeout: 1
pg_run_times:
name: pg_run_times
desc: OpenGauss database run times
query:
- name: pg_run_times
sql: select 'cluster_runtime' as run_name,(case pg_is_in_recovery() when 'f' then 1 else 0 end) as db_role,extract(epoch from(now() - pg_postmaster_start_time())) as run_time;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: run_name
description: Name of cluster
usage: LABEL
- name: db_role
description: Role of database
usage: GAUGE
- name: run_time
description: Run times of cluster
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_tables_size:
name: pg_tables_size
desc: OpenGauss tables size
query:
- name: pg_tables_size
sql: |-
SELECT CURRENT_CATALOG AS datname,
nsp.nspname,
rel.relname,
pg_total_relation_size(rel.oid) AS bytes,
pg_relation_size(rel.oid) AS relsize,
pg_indexes_size(rel.oid) AS indexsize,
pg_total_relation_size(reltoastrelid) AS toastsize
FROM pg_namespace nsp
JOIN pg_class rel ON nsp.oid = rel.relnamespace
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND rel.relkind = 'r';
version: '>=0.0.0'
timeout: 1
status: disable
metrics:
- name: datname
description: database name of this relation
usage: LABEL
- name: nspname
description: schema name of this relation
usage: LABEL
- name: relname
description: relation name of this relation
usage: LABEL
- name: bytes
description: total size of this table (including toast, index, toast index)
usage: GAUGE
- name: relsize
description: size of this table itself (main, vm, fsm)
usage: GAUGE
- name: indexsize
description: size of all related indexes
usage: GAUGE
- name: toastsize
description: size of corresponding toast tables
usage: GAUGE
status: disable
ttl: 60
timeout: 1
pg_toast_tables_size:
name: pg_toast_tables_size
desc: OpenGauss toast tables info
query:
- name: pg_toast_tables_size
sql: select t3.schemaname||'.'||t3.relname as tablename,pg_table_size(t1.oid) as tablesize,t3.schemaname||'.'||t2.relname as toast_name,pg_table_size(t2.oid) as toastsize from pg_class t1 join pg_class t2 on t1.reltoastrelid=t2.oid and t1.reltoastrelid !=0 join pg_stat_user_tables t3 on t1.oid=relid;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: tablename
description: Name of table
usage: LABEL
- name: table_size
description: Size of table
usage: LABEL
- name: toast_name
description: Name of toast
usage: LABEL
- name: toastsize
description: Size of toast
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_indexes_size:
name: pg_indexes_size
desc: OpenGauss database indexes size
query:
- name: pg_indexes_size
sql: |-
SELECT n.nspname as schema_name,c2.relname as table_name,c.relname as index_name,pg_catalog.pg_table_size(c.oid) as index_size
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','I','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND c.relname not like 'matviewmap_%' AND c.relname not like 'mlog_%'
ORDER BY 4 desc;
version: '>=0.0.0'
timeout: 1
status: disable
metrics:
- name: schema_name
description: Schema name of index
usage: LABEL
- name: table_name
description: Table name of index
usage: LABEL
- name: index_name
description: Name of index
usage: LABEL
- name: index_size
description: Size of index
usage: GAUGE
status: disable
ttl: 60
timeout: 1
pg_need_indexes:
name: pg_need_indexes
desc: OpenGauss tables need indexes
query:
- name: pg_need_indexes
sql: |-
select schemaname||'.'||relname as tablename, pg_size_pretty(pg_table_size(relid)) as table_size, seq_scan, seq_tup_read, coalesce(idx_scan,0) idx_scan, coalesce(idx_tup_fetch,0) idx_tup_fetch,coalesce((idx_scan/(case when (seq_scan+idx_scan) >0 then (seq_scan+idx_scan) else 1 end) * 100),0) as rate
from pg_stat_user_tables
where pg_table_size(relid) > 1024*1024*1024 and coalesce((idx_scan/(case when (seq_scan+idx_scan) >0 then (seq_scan+idx_scan) else 1 end) * 100),0) < 90 order by seq_scan desc limit 10;
version: '>=0.0.0'
timeout: 1
status: disable
metrics:
- name: tablename
description: Name of table
usage: LABEL
- name: table_size
description: Size of table
usage: LABEL
- name: seq_scan
description: Scan numbers of seq
usage: GAUGE
- name: seq_tup_read
description: Tup read numbers of seq
usage: GAUGE
- name: idx_scan
description: Scan numbers of indexes
usage: GAUGE
- name: idx_tup_fetch
description: Tup fetch numbers of indexes
usage: GAUGE
- name: rate
description: Index used rate
usage: GAUGE
status: disable
ttl: 60
timeout: 1
pg_never_used_indexes:
name: pg_never_used_indexes
desc: OpenGauss indexes never used
query:
- name: pg_never_used_indexes
sql: |-
select pi.schemaname, pi.relname, pi.indexrelname, pg_size_pretty(pg_table_size(pi.indexrelid)) as index_size
from pg_indexes pis
join pg_stat_user_indexes pi
on pis.schemaname = pi.schemaname and pis.tablename = pi.relname and pis.indexname = pi.indexrelname
left join pg_constraint pco
on pco.conname = pi.indexrelname and pco.conrelid = pi.relid
where pi.schemaname='public'
and pco.contype is distinct from 'p' and pco.contype is distinct from 'u'
and (idx_scan,idx_tup_read,idx_tup_fetch) = (0,0,0)
and pis.indexdef !~ ' UNIQUE INDEX '
and pi.relname !~ 'backup$'
order by pg_table_size(indexrelid) desc;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: schemaname
description: Schema of table
usage: LABEL
- name: relname
description: Name of table
usage: LABEL
- name: indexrelname
description: Name of index
usage: LABEL
- name: index_size
description: Size of index
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_tables_expansion_rate:
name: pg_tables_expansion_rate
desc: OpenGauss database tables expansion rate
query:
- name: pg_tables_expansion_rate
sql: |-
select schemaname,relname,n_live_tup,n_dead_tup,round((n_dead_tup/(n_dead_tup+n_live_tup) *100),2) as dead_rate,
coalesce(last_vacuum,'1970-01-01')::text as last_vacuum,
coalesce(last_autovacuum,'1970-01-01')::text as last_autovacuum ,
coalesce(last_analyze,'1970-01-01')::text as last_analyze,
coalesce(last_autoanalyze,'1970-01-01')::text as last_autoanalyze,
vacuum_count,autovacuum_count,analyze_count,autoanalyze_count
from pg_stat_user_tables
where n_live_tup > 0
order by 5 asc;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: schemaname
description: Schema name of table
usage: LABEL
- name: relname
description: Table name of table
usage: LABEL
- name: n_live_tup
description: live tup of table
usage: LABEL
- name: n_dead_tup
description: dead tup of table
usage: LABEL
- name: dead_rate
description: Dead rate of table
usage: GAUGE
- name: last_vacuum
description: dead tup of table
usage: LABEL
- name: last_autovacuum
description: dead tup of table
usage: LABEL
- name: last_analyze
description: dead tup of table
usage: LABEL
- name: last_autoanalyze
description: dead tup of table
usage: LABEL
- name: vacuum_count
description: count of vacuum
usage: GAUGE
- name: autovacuum_count
description: Count of autovacuum
usage: GAUGE
- name: analyze_count
description: Count of analyze
usage: GAUGE
- name: autoanalyze_count
description: Count of autoanalyze
usage: GAUGE
status: enable
ttl: 60
timeout: 1
pg_active_slowsql:
name: pg_active_slowsql
desc: OpenGauss active slow query
query:
- name: pg_active_slowsql
sql: select datname,usename,client_addr,pid,query_start::text,extract(epoch from (now() - query_start)) as query_runtime,xact_start::text,extract(epoch from(now() - xact_start)) as xact_runtime,state,query from pg_stat_activity where state not in('idle') and query !='';
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: datname
description: Name of database
usage: LABEL
- name: usename
description: Name of user
usage: LABEL
- name: client_addr
description: Client address
usage: LABEL
- name: pid
description: Client address
usage: LABEL
- name: query_start
description: Query start time
usage: LABEL
- name: query_runtime
description: Query running time
usage: GAUGE
- name: xact_start
description: Start time of transaction
usage: LABEL
- name: xact_runtime
description: transaction running time
usage: LABEL
- name: state
description: Query state
usage: LABEL
- name: query
description: Query sql
usage: LABEL
status: enable
ttl: 5
timeout: 1
pg_sql_history:
name: pg_sql_history
desc: OpenGauss history query statement
query:
- name: pg_sql_history
sql: select unique_sql_id,n_calls,cpu_time,min_elapse_time,max_elapse_time,total_elapse_time,query from dbe_perf.statement where n_calls > 10000 order by total_elapse_time desc limit 10;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: unique_sql_id
description: Name of database
usage: LABEL
- name: n_calls
description: Name of user
usage: GAUGE
- name: cpu_time
description: Client address
usage: GAUGE
- name: min_elapse_time
description: Query start time
usage: LABEL
- name: max_elapse_time
description: Query running time
usage: GAUGE
- name: total_elapse_time
description: Start time of transaction
usage: GAUGE
- name: query
description: transaction running time
usage: LABEL
status: enable
ttl: 60
timeout: 1
pg_wait_events:
name: pg_wait_events
desc: OpenGauss wait event statements
query:
- name: pg_wait_events
sql: select nodename,type,event,wait,failed_wait,total_wait_time from dbe_perf.wait_events where wait !=0 order by total_wait_time desc;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: nodename
description: Name of node
usage: LABEL
- name: type
description: Type of wait events
usage: LABEL
- name: event
description: Event name
usage: LABEL
- name: wait
description: Numbers of wait
usage: COUNTER
- name: failed_wait
description: failed wait
usage: LABEL
- name: total_wait_time
description: Total wait times
usage: COUNTER
status: enable
ttl: 60
timeout: 1
pg_lock_sql:
name: pg_lock_sql
desc: OpenGauss lock sqls
query:
- name: pg_lock_sql
sql: |-
select distinct locker.pid as locker_pid,
locked.pid as locked_pid,
coalesce(locker_act.client_addr,'127.0.0.1')::inet as locker_addr,
coalesce(locked_act.client_addr,'127.0.0.1')::inet as locked_addr,
locker_act.usename as locker_username,
locked_act.usename as locked_username,
locker.mode as locker_mode,
locked.mode as locked_mode,
locker.locktype as locker_locktype,
locked.locktype as locked_locktype,
locker_act.usename as locker_user,
locked_act.usename as locker_user,
(locker_act.xact_start)::text as locker_xact_start,
(locked_act.xact_start)::text as locked_xact_start,
(locker_act.query_start)::text as locker_query_start,
(locked_act.query_start)::text as locked_query_start,
extract(epoch from now() - locked_act.query_start) as locked_times,
locker_act.query as locker_query,
locked_act.query as locked_query
from pg_locks locked,
pg_locks locker,
pg_stat_activity locked_act,
pg_stat_activity locker_act
where locker.granted=true
and locked.granted=false
and locked.pid=locked_act.pid
and locker.pid=locker_act.pid
and locker_act.query not like '%select distinct locker.pid %'
and locker.pid <> locked.pid
and locker.mode not like 'AccessShareLock' and locker.mode not like 'ExclusiveLock'
order by 13 asc limit 10;
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: locker_pid
description: Pid of locker
usage: LABEL
- name: locked_pid
description: Pid of locked
usage: LABEL
- name: locker_addr
description: Event name
usage: LABEL
- name: locked_addr
description: Event name
usage: LABEL
- name: locker_username
description: Numbers of wait
usage: LABEL
- name: locked_username
description: Numbers of wait
usage: LABEL
- name: locker_mode
description: failed wait
usage: LABEL
- name: locked_mode
description: failed wait
usage: LABEL
- name: locker_locktype
description: Total wait times
usage: LABEL
- name: locked_locktype
description: Total wait times
usage: LABEL
- name: locker_user
description: Total wait times
usage: LABEL
- name: locked_user
description: Total wait times
usage: LABEL
- name: locker_xact_start
description: Total wait times
usage: LABEL
- name: locked_xact_start
description: Total wait times
usage: LABEL
- name: locker_query_start
description: Total wait times
usage: LABEL
- name: locked_query_start
description: Total wait times
usage: LABEL
- name: locked_times
description: Total wait times
usage: GAUGE
- name: locker_query
description: Total wait times
usage: LABEL
- name: locked_query
description: Total wait times
usage: LABEL
status: enable
ttl: 60
timeout: 1
og_memory_info:
name: og_memory_info
desc: OpenGauss memory usage informations
query:
- name: og_memory_info
sql: select memorytype,memorymbytes from pv_total_memory_detail();
version: '>=0.0.0'
timeout: 1
status: enable
metrics:
- name: memorytype
description: Name of memorytype
usage: LABEL