Today was a particularly bad day. We had three events with over 40 CPU load (1 minute) where all calls dropped. I get this on the slow query log at the time of the overload:
Time: 210304 13:25:23
User@Host: freepbxuser[freepbxuser] @ localhost []
Thread_id: 92297 Schema: asterisk QC_hit: No
Query_time: 10.704160 Lock_time: 0.000085 Rows_sent: 30 Rows_examined: 113958
SET timestamp=1614885923;
SELECT SQL_CALC_FOUND_ROWS i.*,
IFNULL(st.archived, 0) AS archived,
IFNULL(sum(1 - sa.seen), 0) AS unseenstreams, – clever way to count unseen streams
IFNULL(st.notification, ‘normal’) AS notification,
MAX(s.when
) AS last_stream_event
FROM zulu_interactions_contacts c
JOIN zulu_interactions_members m ON m.linkedid = c.linkedid
JOIN zulu_interactions_interactions i ON m.interaction_id = i.id
LEFT OUTER JOIN zulu_interactions_interaction_states st
ON (st.interaction_id = i.id AND st.userman_id = c.userman_id)
LEFT OUTER JOIN zulu_interactions_streams s
ON (s.interaction_id = i.id)
LEFT OUTER JOIN zulu_interactions_stream_actions sa
ON (sa.stream_id = s.id AND sa.zulu_id = c.userman_id)
WHERE c.type = 'USER'
AND c.userman_id = '329'
GROUP BY i.id
HAVING archived = 0
ORDER BY
GREATEST(MAX(s.`when`), i.updated_at) DESC,
i.start_time DESC,
-- order by id to get stable ordering when timestamps are the same
i.id DESC
LIMIT 0, 30;
Zulu seems to be the offender there but I have no idea how to prevent this. I ran mysqltuner and implemented the recommendations to optimize the server but things seems to be getting worse.