Symptoms

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

Impact

System slowdown. Poor postgres autovacuum performance.

Conditions

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.

Workaround

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

None

Behavior Change