-
Task
-
Resolution: Done
-
Undefined
-
None
The customer is currently using Postgres DB external to OpenShift for the Clair application. During the queries against DB, the CPU usage is spiking above 90% and the Postgres node is going down affecting multiple applications.
Business Impact : query coming from Clair app caused CPU spike which let the server unresponsive.
Comment from the DBA
------------------------------------
"Below query need to be looked at, maybe a compound index on id and updater will help".
SELECT id FROM vuln WHERE vuln.updater = 'text' AND id > 100 ORDER BY id ASC LIMIT 10000;
------------------------------------
Additional Information from DBA with respect to Clair CPU usage.
------------------------------------
The team was mentioning about testing by passing the limit value as a bind parameter?
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28358 enterpr+ 20 0 4523072 4.1g 4.1g R 52.2 55.2 3:53.22 postgres: svc-ahiahit clr01t1 172.18.xx.xx[47740] BIND
10013 enterpr+ 20 0 4521664 4.1g 4.1g R 34.8 54.5 0:24.97 postgres: svc-ahiahit clr01t1 172.18.xx.xx[47000] SELECT
pid | 28358
usesysid | 18983
usename | svc-ahiahit
application_name |
client_addr | 172.18.xx.xx
client_hostname |
client_port | 47740
backend_start | 2021-12-29 14:51:41.293668-05
xact_start | 2021-12-29 15:45:24.839114-05
query_start | 2021-12-29 15:45:24.839244-05
state_change | 2021-12-29 15:45:24.839245-05
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 125920
query | +
| SELECT id FROM vuln WHERE vuln.updater = $1 AND id > $2 ORDER BY id ASC LIMIT 10000; + |
backend_type | client backend
------------------------------------
pid | 10013
usesysid | 18983
usename | svc-ahiahit
application_name |
client_addr | 172.18.xx.xx
client_hostname |
client_port | 47000
backend_start | 2021-12-29 15:35:39.392967-05
xact_start | 2021-12-29 15:45:25.089831-05
query_start | 2021-12-29 15:45:25.469658-05
state_change | 2021-12-29 15:45:25.469659-05
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 125920
query | +
| SELECT NOT EXISTS(SELECT 1 FROM uo_vuln WHERE vuln = $1); + |
backend_type | client backend
==============================
Customer request:
How can we tune these limit values based on what DBA is recommending?
List of all SQL commands that get executed by the quay application.
