Satellite6: High average CPU load and Postgres connection exausted

Solution Verified - Updated -

Red Hat Insights can detect this issue

Proactively detect and remediate issues impacting your systems.
View matching systems and remediation

Environment

  • Red Hat Satellite 6

Issue

  • Impossible to promote new content and patch systems
  • WebUI is very slow and subscription-manager commands on clients fail sometimes with time outs, yum queries are also slow from clients
  • Unable to run "foreman-rake console" because of error message with string "PGError: FATAL: remaining connection slots are reserved for non-replication superuser connections"

Resolution

  • Stop katello services
    $ katello-service stop

  • Start postgresql
    $ systemctl start postgresql

  • Login to psql console:
    $ su - postgres
    $ psql

  • Paste these queries to psql console and kill annoying queries:
    select pg_cancel_backend(pid) from pg_stat_activity where query like 'SELECT 1 AS one FROM "trend_counters"%';

  • Start katello services again
    $ katello-service start

Alternative solution:
Increase max_connections value in /var/lib/pgsql/data/postgresql.conf. You have to restart postgresql service to apply new value.

Root Cause

The number of concurrent connections to postgresql exceeds max_connections value in /var/lib/pgsql/data/postgresql.conf.

Diagnostic Steps

  • Export running queries from postgresql
# su - postgres 
# psql -c "SELECT * FROM pg_stat_activity" > /tmp/pgstat.out
# cat /tmp/pgstat.out
  • Analyze /tmp/pgstat.out and find rogue queries
17609 | foreman   | 17152 |    16384 | foreman   |                  |             |                 |          -1 | 2015-11-11 00:33:19.178276+13 | 2015-11-12 12:00:52.391891+13 | 2015-11-12 12:00:52.394309+13 | 2015-11-12 12:00:52.39431+13  | f       | active | SELECT  1 AS one FROM "trend_counters"  WHERE ("trend_counters"."created_at" = '2015-11-10 11:35:41.595690' AND "trend_counters"."trend_id" = 1093) LIMIT 1
 17609 | foreman   | 31159 |    16384 | foreman   |                  |             |                 |          -1 | 2015-11-10 21:32:55.62262+13  |                               | 2015-11-10 21:32:55.853688+13 | 2015-11-10 21:32:55.853831+13 | f       | idle   | SELECT "version" FROM "dynflow_schema_info" LIMIT 1
 17609 | foreman   | 13568 |    16384 | foreman   |                  |             |                 |          -1 | 2015-11-11 05:32:23.876038+13 | 2015-11-12 12:00:48.821929+13 | 2015-11-12 12:00:48.829143+13 | 2015-11-12 12:00:48.829145+13 | f       | active | SELECT  1 AS one FROM "trend_counters"  WHERE ("trend_counters"."created_at" = '2015-11-10 16:38:44.430222' AND "trend_counters"."trend_id" = 14425) LIMIT 1
 17609 | foreman   | 22859 |    16384 | foreman   |                  |             |                 |          -1 | 2015-11-11 01:32:35.802982+13 | 2015-11-12 12:00:55.964499+13 | 2015-11-12 12:00:55.989807+13 | 2015-11-12 12:00:55.989809+13 | f       | active | SELECT  1 AS one FROM "trend_counters"  WHERE ("trend_counters"."created_at" = '2015-11-10 12:36:34.005021' AND "trend_counters"."trend_id" = 1513) LIMIT 1
 17609 | foreman   |  2348 |    16384 | foreman   |                  |             |                 |          -1 | 2015-11-11 09:15:33.377849+13 |                               | 2015-11-11 09:15:35.539959+13 | 2015-11-11 09:15:35.540256+13 | f       | idle   | SELECT "version" FROM "dynflow_schema_info" LIMIT 1
 17609 | foreman   |  4555 |    16384 | foreman   |                  |             |                 |          -1 | 2015-11-10 22:31:12.845335+13 | 2015-11-12 12:00:26.329204+13 | 2015-11-12 12:00:26.521124+13 | 2015-11-12 12:00:26.521126+13 | f       | active | SELECT  1 AS one FROM "trend_counters"  WHERE ("trend_counters"."created_at" = '2015-11-10 09:34:07.822462' AND "trend_counters"."trend_id" = 19645) LIMIT 1
 17609 | foreman   | 17270 |    16384 | foreman   |                  |             |                 |          -1 | 2015-11-11 00:34:37.548053+13 |                               | 2015-11-11 00:34:37.789457+13 | 2015-11-11 00:34:37.789668+13 | f       | idle   | SELECT "version" FROM "dynflow_schema_info" LIMIT 1

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.

Comments