In most cases, a wait event occurs when the system waits for a specific type of resources to execute an SQL statement.
You can use the polar_stat_activity view to check the wait events that frequently occur.
SELECT CASE WHEN wait_event_type IS NULL THEN 'CPU' ELSE wait_event_type END, CASE WHEN wait_event IS NULL THEN 'CPU' ELSE wait_event END, COUNT(*) AS wait_count FROM polar_stat_activity WHERE state='active' AND backend_type='client backend' GROUP BY wait_event_type, wait_event ORDER BY wait_count DESC; \watch 1
- If the number of CPU wait events is equal to or greater than 80% of the number of CPU cores for a consecutive period, a CPU bottleneck occurs.
- If the number of I/O wait events is large for a consecutive period, an I/O bottleneck occurs.
- If the number of lwlock wait events is large for a consecutive period, heavy lwlock contention occurs. A lwlock protects the data structure in your database.
- If the number of lock wait events is large for a consecutive period, heavy lock contention occurs. In this case, the locks are table locks or row locks in your database.