-
Type:
Performance
-
Status: Open
-
Resolution: Unresolved
-
Affects Version/s: 5.10.1
-
Fix Version/s: None
-
Component/s: Database
-
Labels:
-
Environment:
Artifactory = 5.10.1
CentOS = 7.4.1708
PostgreSQL = 9.6.8
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?
