Too slow postgres-exporter metrics cause monitoring to fail.
Summary
Monitoring for SG cluster is not getting the metrics data:
This is caused because there are two queries taking to long to get the metrics:
pg_table_bloat:
rows: 443 rows
Time: 34792.858 ms (00:34.793)
and:
pg_index:
rows: 72
Time: 4257.598 ms (00:04.258)
Prometheus scrapes cannot finish successfully and make targets to finish with context deadline exceeded
Steps to reproduce
This is a new installation with only one database:
Tables: 443:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-------------+----------+----------+---------+---------+-----------------------+---------+------------+--------------------------------------------
odoo | odoo | UTF8 | C.UTF-8 | C.UTF-8 | | 67 MB | pg_default |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 7977 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 7793 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 7793 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
Expected Behaviour
Be able to check all the dashboard graphics with information about the cluster state.
Possible Solution
- Reduce the query time
- Increase the Scrape intervals time
Environment
- StackGres version: 0.9.1
- Kubernetes version: 1.17
- Cloud provider or hardware configuration: AWS
Relevant logs and/or screenshots
time kubectl exec -it -n odoo odoo-db-0 -c prometheus-postgres-exporter -- curl http://localhost:9187/metrics
....
promhttp_metric_handler_requests_total{code="200"} 41
promhttp_metric_handler_requests_total{code="500"} 496
promhttp_metric_handler_requests_total{code="503"} 0
real 0m44,817s
user 0m0,556s
sys 0m0,104s
Edited by Kadaffy Talavera