<div dir="ltr">Hey Pavol, <div><br></div><div>Thanks for the response. The query time is in seconds. Meaning that each batch of 500 deletions, takes approximately 2.5 minutes. I'm not sure if this is due to the size of each entry, or due to the way the second select (<span style="font-size:12.8px">select id from HT_m_audit_event) is performed or the way the temp table HT_m_audit_event is crafted. The fact that this only happens once in a while also indicates that certain audit records or certain days with a large amount of audit records trigger this problem. </span></div><div><span style="font-size:12.8px"><br></span></div><div><span style="font-size:12.8px">This last, the total clean up operation only took an hour or so. Last time this happened, it took about 16 hours due to the same query. We're in the process of evaluation a DB change, however I fear this is not a DB issue. Is there anywhere in particular I could look in the DB to check if there's something weird about the audit logs? </span></div><div><span style="font-size:12.8px"><br></span></div><div><span style="font-size:12.8px">Thanks, </span></div><div><span style="font-size:12.8px">-F </span></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Oct 28, 2016 at 11:17 AM, Pavol Mederly <span dir="ltr"><<a href="mailto:mederly@evolveum.com" target="_blank">mederly@evolveum.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div 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="m_-5445603403205450789moz-signature" cols="72">Pavol Mederly
Software developer
<a href="http://evolveum.com" target="_blank">evolveum.com</a>
</pre><div><div class="h5">
<div class="m_-5445603403205450789moz-cite-prefix">On 27.10.2016 20:35, Florin. Stingaciu
wrote:<br>
</div>
</div></div><blockquote type="cite"><div><div class="h5">
<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="m_-5445603403205450789mimeAttachmentHeader"></fieldset>
<br>
</div></div><pre>______________________________<wbr>_________________
midPoint mailing list
<a class="m_-5445603403205450789moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a>
<a class="m_-5445603403205450789moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint" target="_blank">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a>
</pre>
</blockquote>
<br>
</div>
<br>______________________________<wbr>_________________<br>
midPoint mailing list<br>
<a href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a><br>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" rel="noreferrer" target="_blank">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a><br>
<br></blockquote></div><br></div>