[midPoint] DB utilization at 200% -- Slow query Traced

[Florin. Stingaciu]

Hello,

This is actually the second time this happened in our production
environment. We are using a Galera mySQL cluster and are noticing 200% CPU
utilization on this node, causing the midPoint app to only be able to
perform reads but not writes. After some debuging, I managed to enable slow
query logging on the Galera cluster, and consistently find that the
following query is taking 15 seconds or longer and the same query happens
repetedly:

# Time: 161027 18:29:08
# User at Host: midpoint[midpoint] @  [100.122.2.52]  Id: 3803614
# Schema: midpoint_prod_live  Last_errno: 0  Killed: 0
# Query_time: 158.144047  Lock_time: 0.000370  Rows_sent: 0  Rows_examined:
426520838  Rows_affected: 500
# Bytes_sent: 13
SET timestamp=1477592948;
delete from m_audit_event where id in (select id from HT_m_audit_event);
# Time: 161027 18:30:20
# User at Host: midpoint[midpoint] @  [100.122.2.52]  Id: 3803793
# Schema: midpoint_prod_live  Last_errno: 0  Killed: 0
# Query_time: 68.407496  Lock_time: 0.000258  Rows_sent: 0  Rows_examined:
163394207  Rows_affected: 191
# Bytes_sent: 11
SET timestamp=1477593020;
delete from m_audit_event where id in (select id from HT_m_audit_event);

This is preventing midPoint from doing any writes at all. I tried
restarting the service but that didn't help. I can't help to think this has
something to do with the Clean Up Task.

Any help, suggestions, or further debugging tips are highly welcome.

Thanks,
-F
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20161027/87ac2056/attachment.htm>