XMLWordPrintable

    Details

    • Type: Performance
    • Status: Open
    • Resolution: Unresolved
    • Affects Version/s: 5.10.1
    • Fix Version/s: None
    • Component/s: Database
    • Environment:

      Artifactory = 5.10.1
      CentOS = 7.4.1708
      PostgreSQL = 9.6.8

      Description

      I'm seeing some constant, heavy CPU usage for PostgreSQL on my Artifactory server. Typically I always see 8 or so PostgreSQL processes using 100% CPU of an entire core. The command listed from ps output looks like postgres: artifactory artifactory 127.0.0.1(60526) SELECT.

      I first noticed this when I started populating a Docker repository with really large images (contains a 9 GB layer).

      If I run the this query in psql SELECT datname,usename,application_name,client_addr,backend_start,xact_start,query_start,state_change,state,query from pg_stat_activity; I get the following output:

      datname usename application_name client_addr backend_start xact_start query_start state_change state query
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:17:54.236831-05 2018-03-30 08:39:27.640467-05 2018-03-30 08:39:27.640517-05 2018-03-30 08:39:27.640518-05 active select distinct n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated from nodes n where ( n.repo = $1 and n.node_path like $2 escape '^' and n.node_name like $3 and n.depth = $4) and n.node_type = $5
      postgres postgres psql   2018-03-30 08:36:36.729583-05 2018-03-30 08:39:29.050055-05 2018-03-30 08:39:29.050055-05 2018-03-30 08:39:29.050058-05 active SELECT datname,usename,application_name,client_addr,backend_start,xact_start,query_start,state_change,state,query from pg_stat_activity;
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 07:50:00.326449-05   2018-03-30 07:50:00.333094-05 2018-03-30 07:50:00.333177-05 idle delete from distributed_locks where owner=$1
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:24:56.403774-05 2018-03-30 08:39:27.725928-05 2018-03-30 08:39:27.725963-05 2018-03-30 08:39:27.725964-05 active select distinct n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated from nodes n where ( n.repo = $1 and n.node_path like $2 escape '^' and n.node_name like $3 and n.depth = $4) and n.node_type = $5
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:24:56.733419-05 2018-03-30 08:39:27.640452-05 2018-03-30 08:39:27.640495-05 2018-03-30 08:39:27.640496-05 active select distinct n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated from nodes n where ( n.repo = $1 and n.node_path like $2 escape '^' and n.node_name like $3 and n.depth = $4) and n.node_type = $5
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:24:56.810924-05 2018-03-30 08:39:27.640541-05 2018-03-30 08:39:27.640573-05 2018-03-30 08:39:27.640574-05 active select distinct n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated from nodes n where ( n.repo = $1 and n.node_path like $2 escape '^' and n.node_name like $3 and n.depth = $4) and n.node_type = $5
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:24:56.865498-05 2018-03-30 08:39:27.725931-05 2018-03-30 08:39:27.725965-05 2018-03-30 08:39:27.725966-05 active select distinct n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated from nodes n where ( n.repo = $1 and n.node_path like $2 escape '^' and n.node_name like $3 and n.depth = $4) and n.node_type = $5
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:29:13.80615-05 2018-03-30 08:39:27.640448-05 2018-03-30 08:39:27.640495-05 2018-03-30 08:39:27.640497-05 active select distinct n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated from nodes n where ( n.repo = $1 and n.node_path like $2 escape '^' and n.node_name like $3 and n.depth = $4) and n.node_type = $5
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:29:20.771544-05 2018-03-30 08:39:27.725927-05 2018-03-30 08:39:27.725972-05 2018-03-30 08:39:27.725974-05 active select distinct n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated from nodes n where ( n.repo = $1 and n.node_path like $2 escape '^' and n.node_name like $3 and n.depth = $4) and n.node_type = $5
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:29:20.806336-05   2018-03-30 08:39:29.036913-05 2018-03-30 08:39:29.036942-05 idle SELECT * FROM node_props WHERE node_id = $1
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:29:24.985598-05   2018-03-30 08:39:00.184889-05 2018-03-30 08:39:00.184898-05 idle COMMIT
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:29:24.996534-05 2018-03-30 08:39:27.725925-05 2018-03-30 08:39:27.725957-05 2018-03-30 08:39:27.725958-05 active select distinct n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated from nodes n where ( n.repo = $1 and n.node_path like $2 escape '^' and n.node_name like $3 and n.depth = $4) and n.node_type = $5
      artifactory artifactory PostgreSQL JDBC Driver 127.0.0.1 2018-03-30 08:30:00.038407-05   2018-03-30 08:39:25.687826-05 2018-03-30 08:39:25.68895-05 idle UPDATE access_servers SET last_heartbeat = $1 WHERE server_name = $2

      I see a lot of SELECT DISTINCT queries. Is it possible some of these queries are not utilizing indexes properly? Similar to RTFACT-12908?

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            krische Brian Krische
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Sync Status

                Connection: RTFACT Sync
                RTMID-16309 -
                SYNCHRONIZED
                • Last Sync Date: