Postgres cpu hitting higher cpu

Narender · March 21, 2024, 8:10pm

We are using external database and we are having an issue where we are hitting higher cpu. We see below query is executing every 2 secs. Why is this query running this frequent and how can we stop this query from executing if it is not needed

   SELECT
                c.relname,
                CASE
                    WHEN c.relispartition THEN 'p'
                    WHEN c.relkind IN ('m', 'v') THEN 'v'
                    ELSE 't'
                END,
                obj_description(c.oid, 'pg_class')
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)

Denney-tech · March 22, 2024, 7:43pm

What version of Postgres are you using? What version of AWX are you using? Did this start happening after an upgrade? If so, what version were you using before you noticed high CPU usage on the PG side? Has the high CPU persisted, or has it relaxed since? Upgrading AWX does perform some initialization/migration steps on the DB, but that only occurs once each upgrade.

Unfortunately, I don’t know enough about the backend of AWX to really help much further, but those who do would probably like a lot more information than you initially provided.

Maybe @TheRealHaoLiu or @rooftopcellist or someone else on the AWX team might be able to help more.

TheRealHaoLiu · March 24, 2024, 11:30pm

From first look it doesn’t seem to be from AWX. Ping Jeff Bradberry to confirm

TheRealHaoLiu · March 25, 2024, 1:31pm

From Jeff the query seems to be coming from

github.com

django/django/blob/ae10146793dca0e0594c7acdee20ca3810983f39/django/db/backends/postgresql/introspection.py#L56-L79


      
          def get_table_list(self, cursor):
              """Return a list of table and view names in the current database."""
              cursor.execute(
                  """
                  SELECT
                      c.relname,
                      CASE
                          WHEN c.relispartition THEN 'p'
                          WHEN c.relkind IN ('m', 'v') THEN 'v'
                          ELSE 't'
                      END,
                      obj_description(c.oid, 'pg_class')
                  FROM pg_catalog.pg_class c
                  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                  WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
                      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                      AND pg_catalog.pg_table_is_visible(c.oid)
              """
              )
              return [

This file has been truncated. show original

TheRealHaoLiu · March 25, 2024, 1:57pm

@Narender what version of AWX and AWX-operator do you have?

Narender · March 25, 2024, 2:04pm

@TheRealHaoLiu Thank you for your response AWX 23.3.1

Awx operator: quay.io/ansible/awx-operator:2.7.1

TheRealHaoLiu · March 25, 2024, 2:12pm

Ok so it’s most likely not due to any of the big awx-operator change we made recently…or any change we made related to PG15 (AWX 24.0.0)

chrismeyersfsu · March 25, 2024, 2:23pm

@Narender

For each django db connection we set application_name to be the AWX service + the process ID. This is helpful to trace back which process is using which query.

can you run SELECT application_name, query FROM pg_stat_activity; until you find the problematic query and post the results here?

Below is an example set of results

awx-manage dbshell
awx=> SELECT application_name, query FROM pg_stat_activity;

         application_name         |                                                                                                                                                                   >
----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------->
                                  | <insufficient privilege>
                                  | <insufficient privilege>
 awx-272-cache_clear-awx_1        | LISTEN "tower_settings_change";
                                  | LISTEN web_ws_heartbeat
 awx-270-ws_heartbeat-awx_1       | SELECT pg_notify('web_ws_heartbeat', '{"hostname": "awx_1", "ip": null, "action": "online"}');
 awx-271-rsyslog_configurer-awx_1 | SELECT "conf_setting"."id", "conf_setting"."created", "conf_setting"."modified", "conf_setting"."key", "conf_setting"."value", "conf_setting"."user_id" FROM "conf>
                                  | LISTEN "awx_1";
 awx-250-dispatcher-awx_1         | SELECT "conf_setting"."id", "conf_setting"."created", "conf_setting"."modified", "conf_setting"."key", "conf_setting"."value", "conf_setting"."user_id" FROM "conf>
 awx-470-dispatcher_worker-awx_1  | SELECT pg_advisory_unlock(503423469)
 awx-474-dispatcher_worker-awx_1  | SELECT pg_advisory_unlock(-756382330)
 awx-472-dispatcher_worker-awx_1  | SELECT pg_advisory_unlock(1226251610)
 psql                             | SELECT application_name, query FROM pg_stat_activity;
                                  | <insufficient privilege>
                                  | <insufficient privilege>
                                  | <insufficient privilege>

Narender · March 25, 2024, 3:47pm

@chrismeyersfsu Please find the attached screenshot

chrismeyersfsu · March 25, 2024, 5:17pm

I can’t find the query in question in that screenshot.

Please find the query in question for me. Circle it if it’s in the screenshot. Otherwise please paste the text so I can search for it.

I need you to do some grunt work here.