<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>Hello Florin,</p>
<p>yes, this queries are used to delete old audit log entries. These
are deleted in batches of 500 entries - just because we didn't
want to block midPoint by executing deletion of thousands of
records in one transaction. But maybe something has to be tuned
here.<br>
</p>
<p>I'm not sure I understand your report. E.g. "Query_time:
158.144047" - what time units are used here?</p>
<p>Best regards,<br>
</p>
<pre class="moz-signature" cols="72">Pavol Mederly
Software developer
evolveum.com
</pre>
<div class="moz-cite-prefix">On 27.10.2016 20:35, Florin. Stingaciu
wrote:<br>
</div>
<blockquote
cite="mid:CAMQHPY3zn00HYPsEWme61FKTQ+sLTYUs7oiS5c_YTgL-LDikfw@mail.gmail.com"
type="cite">
<div dir="ltr">Hello,
<div><br>
</div>
<div>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: </div>
<div><br>
</div>
<div># Time: 161027 18:29:08</div>
<div># User@Host: midpoint[midpoint] @ [100.122.2.52] Id:
3803614</div>
<div># Schema: midpoint_prod_live Last_errno: 0 Killed: 0</div>
<div># Query_time: 158.144047 Lock_time: 0.000370 Rows_sent: 0
Rows_examined: 426520838 Rows_affected: 500</div>
<div># Bytes_sent: 13</div>
<div>SET timestamp=1477592948;</div>
<div>delete from m_audit_event where id in (select id from
HT_m_audit_event);</div>
<div># Time: 161027 18:30:20</div>
<div># User@Host: midpoint[midpoint] @ [100.122.2.52] Id:
3803793</div>
<div># Schema: midpoint_prod_live Last_errno: 0 Killed: 0</div>
<div># Query_time: 68.407496 Lock_time: 0.000258 Rows_sent: 0
Rows_examined: 163394207 Rows_affected: 191</div>
<div># Bytes_sent: 11</div>
<div>SET timestamp=1477593020;</div>
<div>delete from m_audit_event where id in (select id from
HT_m_audit_event); </div>
<div><br>
</div>
<div>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. </div>
<div><br>
</div>
<div>Any help, suggestions, or further debugging tips are highly
welcome. </div>
<div><br>
</div>
<div>Thanks, </div>
<div>-F </div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
midPoint mailing list
<a class="moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a>
<a class="moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
</pre>
</blockquote>
<br>
</body>
</html>