Bug ID 958317: CPU usage reaches 100% due to concurrent SELECT statements

Last Modified: Nov 07, 2022

Bug Tracker

Affected Product:  See more info
BIG-IQ Platform(all modules)

Known Affected Versions:
7.1.0,,,, 7.1.6,, 7.1.7,,, 7.1.8,,,,,, 7.1.9,,,

Opened: Oct 22, 2020
Severity: 4-Minor


Concurrent Postgres SELECT statements occur every 12 hours, causing high CPU usage


System slowdown. Poor postgres autovacuum performance.


Running the command: tmsh show sys performance system raw will show many concurrent requests: "SELECT COUNT(*) OVER () AS ttl_count, _value FROM bigiqevents.shared_event_alerts WHERE (_value->>'alertType' = $1 AND _value->>'source' = $2) ORDER BY _value->>'alertDateTime' desc LIMIT 1 OFFSET 0" CPU usage will be at 100% for about 10min every 12 hours.


This is due to stock postgres autovacuum settings being insufficient. You can use the following steps to reconfigure appropriately. 1. Backup postgresql.conf 2. Edit the conf (4 changes below) autovacuum_max_workers = 6 # max number of autovacuum subprocesses autovacuum_naptime = 15 # time between autovacuum runs autovacuum_vacuum_scale_factor = 0.05 # fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.05 # fraction of table size before analyze 3. Reboot 4. If able, user should identify any bloated tables and perform a one-time VACUUM FULL to purge all the dead tuples and to rebuild the indexes

Fix Information


Behavior Change