[taler-grid5k] 42/189: hopefully better logging of slow queries with pg_stat_statements

[gnunet]

This is an automated email from the git hooks/post-receive script.

marco-boss pushed a commit to branch master
in repository grid5k.

commit e69cabff6e8cbe3983e15bead914270959546943
Author: Boss Marco <bossm8@bfh.ch>
AuthorDate: Thu Mar 3 16:42:56 2022 +0100

    hopefully better logging of slow queries with pg_stat_statements
---
 configs/etc/monitor/postgres-exporter.yaml | 19 +++++++++++--------
 experiment/scripts/database.sh             | 19 +++++++++++++++----
 2 files changed, 26 insertions(+), 12 deletions(-)

diff --git a/configs/etc/monitor/postgres-exporter.yaml 
b/configs/etc/monitor/postgres-exporter.yaml
index e473b6a..fc98cf8 100644
--- a/configs/etc/monitor/postgres-exporter.yaml
+++ b/configs/etc/monitor/postgres-exporter.yaml
@@ -159,7 +159,7 @@ pg_database:
         description: "Disk space used by the database"
 
 pg_stat_statements:
-  query: "SELECT t2.rolname, t3.datname, queryid, calls, total_exec_time / 
1000 as total_time_seconds, min_exec_time / 1000 as min_time_seconds, 
max_exec_time / 1000 as max_time_seconds, mean_exec_time / 1000 as 
mean_time_seconds, stddev_exec_time / 1000 as stddev_time_seconds, rows, 
shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, 
local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, 
temp_blks_read, temp_blks_written, blk_read_time / 1000 a [...]
+  query: "SELECT t2.rolname, t3.datname, substring(query from 1 for 265) as 
query, queryid, calls, total_exec_time / 1000 as total_time_seconds, 
min_exec_time / 1000 as min_time_seconds, max_exec_time / 1000 as 
max_time_seconds, mean_exec_time / 1000 as mean_time_seconds, stddev_exec_time 
/ 1000 as stddev_time_seconds, rows, shared_blks_hit, shared_blks_read, 
shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, 
local_blks_dirtied, local_blks_written, temp_blks_read, [...]
   master: true
   metrics:
     - rolname:
@@ -168,6 +168,9 @@ pg_stat_statements:
     - datname:
         usage: "LABEL"
         description: "Name of database"
+    - query:
+        usage: "LABEL"
+        description: "Query"
     - queryid:
         usage: "LABEL"
         description: "Query ID"
@@ -176,19 +179,19 @@ pg_stat_statements:
         description: "Number of times executed"
     - total_time_seconds:
         usage: "COUNTER"
-        description: "Total time spent in the statement, in milliseconds"
+        description: "Total time spent in the statement, in seconds"
     - min_time_seconds:
         usage: "GAUGE"
-        description: "Minimum time spent in the statement, in milliseconds"
+        description: "Minimum time spent in the statement, in seconds"
     - max_time_seconds:
         usage: "GAUGE"
-        description: "Maximum time spent in the statement, in milliseconds"
+        description: "Maximum time spent in the statement, in seconds"
     - mean_time_seconds:
         usage: "GAUGE"
-        description: "Mean time spent in the statement, in milliseconds"
+        description: "Mean time spent in the statement, in seconds"
     - stddev_time_seconds:
         usage: "GAUGE"
-        description: "Population standard deviation of time spent in the 
statement, in milliseconds"
+        description: "Population standard deviation of time spent in the 
statement, in seconds"
     - rows:
         usage: "COUNTER"
         description: "Total number of rows retrieved or affected by the 
statement"
@@ -224,10 +227,10 @@ pg_stat_statements:
         description: "Total number of temp blocks written by the statement"
     - blk_read_time_seconds:
         usage: "COUNTER"
-        description: "Total time the statement spent reading blocks, in 
milliseconds (if track_io_timing is enabled, otherwise zero)"
+        description: "Total time the statement spent reading blocks, in 
seconds (if track_io_timing is enabled, otherwise zero)"
     - blk_write_time_seconds:
         usage: "COUNTER"
-        description: "Total time the statement spent writing blocks, in 
milliseconds (if track_io_timing is enabled, otherwise zero)"
+        description: "Total time the statement spent writing blocks, in 
seconds (if track_io_timing is enabled, otherwise zero)"
 
 pg_process_idle:
   query: |
diff --git a/experiment/scripts/database.sh b/experiment/scripts/database.sh
index f1ca572..0fb70f5 100755
--- a/experiment/scripts/database.sh
+++ b/experiment/scripts/database.sh
@@ -68,14 +68,24 @@ function setup_config() {
   log_destination=syslog
   log_error_verbosity=terse
   syslog_ident='taler-database'
-  log_min_duration_statement=200
+  log_min_duration_statement=150
   shared_preload_libraries='pg_stat_statements,auto_explain'
-  auto_explain.log_min_duration='200ms'
+  auto_explain.log_min_duration='150ms'
+  auto_explain.log_verbose=true
   auto_explain.log_nested_statements=off
   auto_explain.log_analyze=true
   auto_explain.log_buffers=true
   auto_explain.log_wal=true
-  
+
+  # Large tables perform bad with the default settings
+  # However, they could also be set on each table indiviudally
+  # (NOTE: on partitions!)
+  # ALTER TABLE known_coins_default
+  #  SET (autovacuum_vacuum_scale_factor = 0.0, autovacuum_vacuum_threshold = 
1000);
+  log_autovacuum_min_duration=0
+  autovacuum_vacuum_scale_factor=0
+  autovacuum_vacuum_threshold=1000
+
   # use 25% of the available memory 
   # (https://www.postgresql.org/docs/13/runtime-config-resource.html)
   shared_buffers=${SHARED_MEM}kB
@@ -130,6 +140,7 @@ function setup_config() {
 
   max_worker_processes=${NUM_CPU}
   max_parallel_workers=${NUM_CPU}
+  max_parallel_workers_per_gather=10
   max_connections=500
 
   max_parallel_maintenance_workers=4
@@ -244,6 +255,7 @@ psql postgres -tAc "SELECT 1 FROM pg_roles WHERE 
rolname='taler-exchange-httpd'"
 psql -tAc "SELECT 1 FROM pg_database WHERE datname='${DB_NAME}'" | \
   grep -q 1 || \
   createdb -O taler-exchange-httpd "${DB_NAME}"
+psql -tAc "CREATE EXTENSION IF NOT EXISTS pg_stat_statements"
 EOF
  
   # Create the remote user "$DB_USER" and load pg_stat_statements for metrics
@@ -252,7 +264,6 @@ psql postgres -tAc "SELECT 1 FROM pg_roles WHERE 
rolname='${DB_USER}'" | \
   grep -q 1 || \
   psql << END
     CREATE USER "${DB_USER}" with encrypted password '${DB_PASSWORD}';
-    CREATE EXTENSION pg_stat_statements;
 END
 EOF
 

-- 
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.