search

percona / pg_stat_monitor

Query Performance Monitoring Tool for PostgreSQL
https://docs.percona.com/pg-stat-monitor/
Other
477 stars 59 forks source link

DB does not start after setting pgsm_max to its maximum #412

Closed dutow closed 7 months ago

dutow commented 1 year ago

The pg_stat_monitor.pgsm_max should accept up to 10240MB. When setting it to the maximum, the DB does not start anymore. It seems to work with the maximum - 1MB

https://jira.percona.com/browse/PG-632

[root@pl242-pablo-svampa-default ~]# psql --pset pager -c "SELECT name, setting, unit, min_val, max_val, short_desc FROM pg_settings WHERE name like 'pg_stat_monitor.pgsm_max'"
           name           | setting | unit | min_val | max_val |                                                short_desc
--------------------------+---------+------+---------+---------+----------------------------------------------------------------------------------------------------------
 pg_stat_monitor.pgsm_max | 10      | MB   | 10      | 10240   | Sets the maximum size of shared memory in (MB) used for statement's metadata tracked by pg_stat_monitor.
(1 row)
[root@pl242-pablo-svampa-default ~]# psql --pset pager -c "ALTER SYSTEM SET pg_stat_monitor.pgsm_max = 10239"
ALTER SYSTEM
[root@pl242-pablo-svampa-default ~]# systemctl restart postgresql-14.service
[root@pl242-pablo-svampa-default ~]# psql --pset pager -c "SELECT name, setting, unit, min_val, max_val, short_desc FROM pg_settings WHERE name like 'pg_stat_monitor.pgsm_max'"
           name           | setting | unit | min_val | max_val |                                                short_desc
--------------------------+---------+------+---------+---------+----------------------------------------------------------------------------------------------------------
 pg_stat_monitor.pgsm_max | 10239   | MB   | 10      | 10240   | Sets the maximum size of shared memory in (MB) used for statement's metadata tracked by pg_stat_monitor.
(1 row)
[root@pl242-pablo-svampa-default ~]# psql --pset pager -c "ALTER SYSTEM SET pg_stat_monitor.pgsm_max = 10240"
ALTER SYSTEM
[root@pl242-pablo-svampa-default ~]# systemctl restart postgresql-14.service
Job for postgresql-14.service failed because the control process exited with error code. See "systemctl status postgresql-14.service" and "journalctl -xe" for details.
[root@pl242-pablo-svampa-default ~]#
2023-05-15 22:59:48.174 UTC [6303] LOG:  starting PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-05-15 22:59:48.175 UTC [6303] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-05-15 22:59:48.175 UTC [6303] LOG:  listening on IPv6 address "::", port 5432
2023-05-15 22:59:48.176 UTC [6303] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-05-15 22:59:48.177 UTC [6303] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-05-15 22:59:48.180 UTC [6305] LOG:  database system was shut down at 2023-05-15 22:59:46 UTC
2023-05-15 22:59:48.184 UTC [6303] LOG:  database system is ready to accept connections
2023-05-15 23:00:04.616 UTC [6303] LOG:  received fast shutdown request
2023-05-15 23:00:04.617 UTC [6303] LOG:  aborting any active transactions
2023-05-15 23:00:04.619 UTC [6303] LOG:  background worker "logical replication launcher" (PID 6311) exited with exit code 1
2023-05-15 23:00:04.620 UTC [6306] LOG:  shutting down
2023-05-15 23:00:04.701 UTC [6303] LOG:  [pg_stat_monitor] pgsm_shmem_shutdown: Shutdown initiated.
2023-05-15 23:00:05.127 UTC [6303] LOG:  database system is shut down