https://homeproj.cesnet.cz/https://homeproj.cesnet.cz/httpauth-login/favicon.ico?16194486082019-01-18T13:13:07ZHomeproj: Redmine for CESNETMentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=163652019-01-18T13:13:07ZPavel Káchaph@cesnet.cz
<ul></ul><p>Radko, could you please search up some backing (like what you said you've already read), like what might be good initial guess for bunch sizes, or what should be taken into consideration?</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=163792019-01-18T14:19:49ZRadko Krkoškrkos@cesnet.cz
<ul></ul><p>I have been searching for the resources, [1] is the best document about the topic that I remember. Unfortunately it only deals with loading the data into an empty table with no indices. That is not the case for us. There was a measurement indicating that with indices, the advantage of multiple-value INSERTs is diminishing, not so for multiple-INSERT transactions. I cannot find it.</p>
<p>Then there is [2], showing (Images 3, 4) that for tables with many columns (such as ours), the multiple-value INSERTs are less efficient. It goes into some details of this, but basically it boils down to the size of cleartext representation of the query, which can be large in our case (such as the LaBrea events).</p>
<p>To sum it up, multiple INSERTs per transaction will help, prepared statements might also (although this will mostly lower the CPU load, not disk IO). Multiple-row INSERTs might not be as efficient in our case. <strong>Should we make measurements to confirm this?</strong></p>
<p>[1] <a class="external" href="https://www.depesz.com/2007/07/05/how-to-insert-data-to-database-as-fast-as-possible/">https://www.depesz.com/2007/07/05/how-to-insert-data-to-database-as-fast-as-possible/</a><br />[2] <a class="external" href="https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/">https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/</a></p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=163882019-01-20T16:09:26ZPavel Káchaph@cesnet.cz
<ul></ul><p>However, second source is for MS SQL Server, isn't it?</p>
<p>How about COPY data FROM STDIN WITH BINARY? <a href="https://stackoverflow.com/questions/8144002/use-binary-copy-table-from-with-psycopg2/8150329#8150329" class="external">poc here</a></p>
<p>Programatically it would be similar concept, except instead of inserts, binary data would get appended to ByteIO and when counters expire, written into db in one COPY statement. However, depends on complexity of data conversion. Maybe simpler and still feasible could be COPY with CSV/TSV data.</p>
<p>At least crude test would be cool, to know we're not chasing ghosts, how much work would it take?</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=164132019-01-21T13:32:03ZRadko Krkoškrkos@cesnet.cz
<ul></ul><p><a class="user active" href="https://homeproj.cesnet.cz/users/17">Pavel Kácha</a> wrote:</p>
<blockquote>
<p>However, second source is for MS SQL Server, isn't it?</p>
</blockquote>
<p>Yeah, obviously it is. Sorry, I made a quick search and thought to have found the sources I used before (quite some time ago). I will search for the relevant sources, pity I cannot find my notes on this topic.</p>
<blockquote>
<p>How about COPY data FROM STDIN WITH BINARY? <a href="https://stackoverflow.com/questions/8144002/use-binary-copy-table-from-with-psycopg2/8150329#8150329" class="external">poc here</a></p>
<p>Programatically it would be similar concept, except instead of inserts, binary data would get appended to ByteIO and when counters expire, written into db in one COPY statement. However, depends on complexity of data conversion. Maybe simpler and still feasible could be COPY with CSV/TSV data.</p>
</blockquote>
<p>I do not like the binary mode, our source data is in text format, not in binary as in the poc case. Also, I am not sure <code>COPY</code> is actually that much less load if indices are present. I will research this more.</p>
<blockquote>
<p>At least crude test would be cool, to know we're not chasing ghosts, how much work would it take?</p>
</blockquote>
<p>Not that much. And it can be generally done in parallel with some other, more involved, work. <span class="wiking smiley smiley-smiley" title=":)"></span></p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=165592019-01-30T15:23:22ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>Assignee</strong> changed from <i>Jan Mach</i> to <i>Radko Krkoš</i></li></ul><p>Taking this over for testing. A test script is in the <code>mentat-dbbench</code> repo, preliminary results are interesting (grouping multiple events for one <code>INSERT</code> is more efficient even with indices and our table structure but the difference to grouping INSERTs in transaction is not big and as that is much easier to implement, I would choose it). Detailed results soon.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=165702019-01-31T13:17:10ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Feedback</i></li></ul><p>The tests were run at <code>mentat-dev</code>, here are the numbers: (the tests were done for metadata-only table - no event column)<br /><pre>
Inserting 40000 rows in 40000 transactions, 1 INSERTs per transaction, 1 rows per INSERT, with indices
> Took 231.945 s, 223.771 s, 235.124 s
> WALwriter: 2.05 M
> IO-wait: 6-7%, 200-250 writes/s, Utilization: 80%
> User: 1-2%
</pre></p>
<p>This is modeling the current approach - all INSERTs are done in it's own transaction. The tests were done on 40k events as that is roughly the amassed amount during overload. Insertion like this can load the disk subsystem considerably and is heavily IO bound.</p>
<pre>
Inserting 40000 rows in 2000 transactions, 20 INSERTs per transaction, 1 rows per INSERT, with indices
> Took 29.112 s, 28.976 s
> WALwriter: 2.06 M, 2032.00 K
> IO-wait: 2-3%, 70-80 writes/s, Utilization: 25-30%
> User: 5-6%
</pre>
<p>First approach is to perform multiple INSERTs in one transaction. With 20, about the number received per second, we can see great reduction in both run time and disk load. Much less time is spent waiting for disk operations to finish.</p>
<pre>
Inserting 40000 rows in 1000 transactions, 40 INSERTs per transaction, 1 rows per INSERT, with indices
> Took 24.888 s, 24.736 s
> WALwriter: 2.88 M, 2.14 M
> IO-wait: 2%, 40-50 writes/s, Utilization: 20%
> User: 6%
</pre>
<p>With the increase to 2 seconds of data in each transaction, there is still some improvement, albeit just a marginal.</p>
<pre>
Inserting 40000 rows in 2000 transactions, 1 INSERTs per transaction, 20 rows per INSERT, with indices
> Took 24.335 s, 24.145 s
> WALwriter: 1176.00 K, 1200.00 K
> IO-wait: 3-4%, 80-90 writes/s, Utilization: 35-40%
> User: 4-5%
</pre>
<p>Another approach is to bundle multiple events into one INSERT statement. This allows for lower write-ahead log (sequential writes only) overhead as the amount of modification commands is lower but does not offer the disk-load reduction of the first approach. The process is still somewhat IO-bound at this level.</p>
<pre>
Inserting 40000 rows in 1000 transactions, 1 INSERTs per transaction, 40 rows per INSERT, with indices
> Took 17.627 s, 17.297 s
> WALwriter: 1216.00 K
> IO-wait: 2-3%, 60 writes/s, Utilization: 25%
> User: 6%
</pre>
<p>With the increase to 2 seconds of data, the disk is no longer the limiting factor, the disk-load is still somewhat higher than with the first approach.</p>
<p><strong>Conclusion:</strong><br />Performing bulk INSERTs is definitely a good idea, they offer much better performance. As for the two alternative ways, they look roughly the same with multiple INSERTs per transaction allowing for lower disk load and multiple rows per INSERT leading to better total run time. The difference is small but measurable, the biggest argument should be the ease of implementation. For the problem we are solving, bundling multiple INSERTs into one transaction seems to work somewhat better.</p>
<strong>Remarks:</strong>
<ul>
<li>A combination of the two methods is also possible, the improvements are in line with the provided data. The implementation complication is not worth the effort in my opinion.</li>
<li>Another way of implementing data import in PostgreSQL is using <code>COPY FROM</code>. According to [1] (the whole thread is a good read; although older - 2005, no indication that this has since changes was found), this is not very different from bundling all the data into one transaction, so similar to the first approach. The differences are in harder error handling, skipping the parser (not that useful for IO-bound cases) and on-wire protocol (related to latency - not an issue on localhost).</li>
</ul>
<p>[1] <a class="external" href="https://www.postgresql.org/message-id/1115246490.1709.233393619%40webmail.messagingengine.com">https://www.postgresql.org/message-id/1115246490.1709.233393619%40webmail.messagingengine.com</a></p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=169702019-02-11T17:31:34ZRadko Krkoškrkos@cesnet.cz
<ul></ul><p>This was not discussed on the last meeting due to lack of time. Moving to next meeting.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=170282019-02-14T14:25:25ZPavel Káchaph@cesnet.cz
<ul><li><strong>Assignee</strong> changed from <i>Radko Krkoš</i> to <i>Jan Mach</i></li></ul><p>Conclusion from meeting: Write performance during peaks might go down one order of magnitude, so let's implement.</p>
<ul>
<li>Let's go for easier variant: txn start, N * execute insert, txn commit/rollback</li>
<li>Reasonable would be to have two limits - max event inserts per txn, max time per txn; close txn in whatever comes first. That should be doable by means of event driven daemon architecture, </li>
<li>Limits should be configurable, one second and some corresponding pretty high number of inserts (50? 100?)</li>
</ul>
<p>Reassigning for implementation.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=233542019-11-07T14:32:29ZPavel Káchaph@cesnet.cz
<ul><li><strong>Assignee</strong> changed from <i>Jan Mach</i> to <i>Radko Krkoš</i></li></ul><p>On today's meeting valid question of error handling was raised.</p>
<p>If we can get the cause of the error after aborted transaction, we can log (as we do now) and recover.</p>
<p>If we cannot, we could back off, bisect, or use another costly strategies, question is if it is viable.</p>
<p>If we can catch the error during processing, log it, and continue without transaction breaking, event better. Might be possible by means of WHENEVER.</p>
<p>Reassigning back to DB department for investigation. <span class="wiking smiley smiley-smiley" title=":)"></span> (Not urgent.)</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235262019-11-15T17:09:12ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>Assignee</strong> changed from <i>Radko Krkoš</i> to <i>Pavel Kácha</i></li><li><strong>To be discussed</strong> changed from <i>No</i> to <i>Yes</i></li></ul><p>The <code>WHENEVER</code> approach is an overkill, it would complicate the python code needlessly.<br />The DB department suggests using the following strategy:<br /><pre>
BEGIN;
SAVEPOINT last_known_good;
// Loop block for pending INSERTions:
try:
INSERT INTO events ...;
INSERT INTO events_json ...;
SAVEPOINT last_known_good;
except:
// Error info provided as usual
ROLLBACK TO SAVEPOINT last_known_good;
log_collision(id, ...)
if (insert_count_over or insert_delay_over):
COMMIT;
</pre></p>
<p>This provides the same error reporting and recovery capabilities as the currently deployed approach, just the <code>COMMITs</code> will be batched, allowing for benefits mentioned in detail above.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235362019-11-18T09:38:44ZPavel Káchaph@cesnet.cz
<ul><li><strong>Assignee</strong> changed from <i>Pavel Kácha</i> to <i>Radko Krkoš</i></li></ul><p><a class="user active" href="https://homeproj.cesnet.cz/users/391">Radko Krkoš</a> wrote:</p>
<blockquote>
<p>The <code>WHENEVER</code> approach is an overkill, it would complicate the python code needlessly.</p>
</blockquote>
<p>Why so? My understanding was that WHENEVER could fire stored procedure, which could swallow the error and note it somewhere, unaffecting other writes. Python code could then just pick up noted info after the transaction, log it and move affected events from tmp to error dir. Am I misguided?</p>
<blockquote>
<p>The DB department suggests using the following strategy:<br />[...]</p>
<p>This provides the same error reporting and recovery capabilities as the currently deployed approach, just the <code>COMMITs</code> will be batched, allowing for benefits mentioned in detail above.</p>
</blockquote>
<p>Mmmmkay, souns fine.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235692019-11-21T10:37:34ZJan Machjan.mach@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>Feedback</i> to <i>In Progress</i></li><li><strong>Assignee</strong> changed from <i>Radko Krkoš</i> to <i>Jan Mach</i></li><li><strong>Target version</strong> changed from <i>Backlog</i> to <i>2.6</i></li><li><strong>To be discussed</strong> changed from <i>Yes</i> to <i>No</i></li></ul><p>I`ll take it from here, thank you Radko.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235702019-11-21T10:39:05ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>Assignee</strong> changed from <i>Jan Mach</i> to <i>Radko Krkoš</i></li></ul><p>NO WAY! I still have to do the <code>SAVEPOINT</code> performance impact measurements! Mine! Mine! Mine! <span class="wiking smiley smiley-smiley" title=":)"></span></p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235712019-11-21T10:51:38ZJan Machjan.mach@cesnet.cz
<ul><li><strong>File</strong> <a href="/attachments/3481">giphy.gif</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/3481/giphy.gif">giphy.gif</a> added</li></ul><p><img src="https://homeproj.cesnet.cz/attachments/download/3481/giphy.gif" title="Sorry" alt="Sorry" /></p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235762019-11-21T16:54:03ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>Assignee</strong> changed from <i>Radko Krkoš</i> to <i>Pavel Kácha</i></li><li><strong>To be discussed</strong> changed from <i>No</i> to <i>Yes</i></li></ul><p>The measurement script was extended to allow SAVEPOINTs. The measurements were again performed on <code>mentat-dev</code>:<br /><pre>
krkos@mentat-dev:~$ ./insert_perf.py -t 1000 -i 40 -r 1
Inserting 40000 rows in 1000 transactions, 40 INSERTs per transaction, 1 rows per INSERT, with indices, without SAVEPOINTs
> Took 22.061 s
krkos@mentat-dev:~$ ./insert_perf.py -t 1000 -i 40 -r 1
Inserting 40000 rows in 1000 transactions, 40 INSERTs per transaction, 1 rows per INSERT, with indices, without SAVEPOINTs
> Took 22.034 s
krkos@mentat-dev:~$ ./insert_perf.py -t 1000 -i 40 -r 1 -s
Inserting 40000 rows in 1000 transactions, 40 INSERTs per transaction, 1 rows per INSERT, with indices, with SAVEPOINTs
> Took 25.851 s
krkos@mentat-dev:~$ ./insert_perf.py -t 1000 -i 40 -r 1 -s
Inserting 40000 rows in 1000 transactions, 40 INSERTs per transaction, 1 rows per INSERT, with indices, with SAVEPOINTs
> Took 25.710 s
krkos@mentat-dev:~$ ./insert_perf.py -t 40000 -i 1 -r 1
Inserting 40000 rows in 40000 transactions, 1 INSERTs per transaction, 1 rows per INSERT, with indices, without SAVEPOINTs
> Took 219.242 s
krkos@mentat-dev:~$ ./insert_perf.py -t 40000 -i 1 -r 1
Inserting 40000 rows in 40000 transactions, 1 INSERTs per transaction, 1 rows per INSERT, with indices, without SAVEPOINTs
> Took 198.840 s
</pre></p>
<p>1) As can be seen, there is some, <20% performance impact from using <code>SAVEPOINTs</code>, yet it is still much better than isolating each <code>INSERT</code> (pair) in its own transaction - roughly 8x faster.</p>
<p>2) If compared with the old results, there is a visible ~10% performance improvement across the board attributable to PostgreSQL, kernel and whatnot upgrade in the meantime. Keep in mind that this is an AMD system, with little negative impact from CPU vulnerability mitigations in the meantime (such an improvement cannot be expected on <code>mentat-hub</code> or <code>mentat-alt</code> - both Intel systems).</p>
<p>3) Non-scripted tests indicate that <code>ROLLBACK TO SAVEPOINT</code> is quite efficient when rolling back even successful <code>INSERTs</code> (another +10% time over <code>SAVEPOINT</code> above) - it definitely does not break the transaction. With unsuccessful <code>INSERTs</code>, this is even lower (as less work has to be done) - roughly +5% over <code>SAVEPOINT</code> time above.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235802019-11-22T10:17:59ZPavel Káchaph@cesnet.cz
<ul></ul><p><a class="user active" href="https://homeproj.cesnet.cz/users/391">Radko Krkoš</a> wrote:</p>
<blockquote>
<p>The measurement script was extended to allow SAVEPOINTs. The measurements were again performed on <code>mentat-dev</code>:<br />[...]</p>
<p>1) As can be seen, there is some, <20% performance impact from using <code>SAVEPOINTs</code>, yet it is still much better than isolating each <code>INSERT</code> (pair) in its own transaction - roughly 8x faster.</p>
</blockquote>
<p>So still very good.</p>
<blockquote>
<p>2) If compared with the old results, there is a visible ~10% performance improvement across the board attributable to PostgreSQL, kernel and whatnot upgrade in the meantime. Keep in mind that this is an AMD system, with little negative impact from CPU vulnerability mitigations in the meantime (such an improvement cannot be expected on <code>mentat-hub</code> or <code>mentat-alt</code> - both Intel systems).</p>
</blockquote>
<p>Sure, we have to count with some performance losses in Meltdown/Spectre/whatnot mitigations, but this we'll have to pay anyway.</p>
<blockquote>
<p>3) Non-scripted tests indicate that <code>ROLLBACK TO SAVEPOINT</code> is quite efficient when rolling back even successful <code>INSERTs</code> (another +10% time over <code>SAVEPOINT</code> above) - it definitely does not break the transaction. With unsuccessful <code>INSERTs</code>, this is even lower (as less work has to be done) - roughly +5% over <code>SAVEPOINT</code> time above.</p>
</blockquote>
<p>Very good. So we just have to take care to take the INSERT, which is more probable to fail, first. I'd guess its 'events' because of possible duplicities. But no big deal perhaps.</p>
<p>So what's exactly do discuss? Let's go for it, or not? <span class="wiking smiley smiley-smiley" title=":)"></span></p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235812019-11-22T10:29:01ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>Assignee</strong> changed from <i>Pavel Kácha</i> to <i>Jan Mach</i></li><li><strong>To be discussed</strong> changed from <i>Yes</i> to <i>No</i></li></ul><p><a class="user active" href="https://homeproj.cesnet.cz/users/17">Pavel Kácha</a> wrote:</p>
<blockquote>
<p><a class="user active" href="https://homeproj.cesnet.cz/users/391">Radko Krkoš</a> wrote:</p>
<blockquote>
<p>The measurement script was extended to allow SAVEPOINTs. The measurements were again performed on <code>mentat-dev</code>:<br />[...]</p>
<p>1) As can be seen, there is some, <20% performance impact from using <code>SAVEPOINTs</code>, yet it is still much better than isolating each <code>INSERT</code> (pair) in its own transaction - roughly 8x faster.</p>
</blockquote>
<p>So still very good.</p>
</blockquote>
<p>Yes.</p>
<blockquote><blockquote>
<p>2) If compared with the old results, there is a visible ~10% performance improvement across the board attributable to PostgreSQL, kernel and whatnot upgrade in the meantime. Keep in mind that this is an AMD system, with little negative impact from CPU vulnerability mitigations in the meantime (such an improvement cannot be expected on <code>mentat-hub</code> or <code>mentat-alt</code> - both Intel systems).</p>
</blockquote>
<p>Sure, we have to count with some performance losses in Meltdown/Spectre/whatnot mitigations, but this we'll have to pay anyway.</p>
</blockquote>
<p>Yes, this was more of a interesting fact I noticed, so to let you know too.</p>
<blockquote><blockquote>
<p>3) Non-scripted tests indicate that <code>ROLLBACK TO SAVEPOINT</code> is quite efficient when rolling back even successful <code>INSERTs</code> (another +10% time over <code>SAVEPOINT</code> above) - it definitely does not break the transaction. With unsuccessful <code>INSERTs</code>, this is even lower (as less work has to be done) - roughly +5% over <code>SAVEPOINT</code> time above.</p>
</blockquote>
<p>Very good. So we just have to take care to take the INSERT, which is more probable to fail, first. I'd guess its 'events' because of possible duplicities. But no big deal perhaps.</p>
</blockquote>
<p>The <code>INSERT</code> into `events` must go first because of the non-conflicting case - events_json(id) references events(id). As for the conflict, it would probably not matter which would go first.</p>
<blockquote>
<p>So what's exactly do discuss? Let's go for it, or not? <span class="wiking smiley smiley-smiley" title=":)"></span></p>
</blockquote>
<p>I agree. I posted this to give you the hard numbers you asked for. Mek already wanted to implement it yesterday. <span class="wiking smiley smiley-smiley" title=":)"></span></p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235822019-11-22T11:53:05ZPavel Káchaph@cesnet.cz
<ul></ul><p><a class="user active" href="https://homeproj.cesnet.cz/users/391">Radko Krkoš</a> wrote:</p>
<blockquote>
<p><a class="user active" href="https://homeproj.cesnet.cz/users/17">Pavel Kácha</a> wrote:</p>
<blockquote>
<p>Very good. So we just have to take care to take the INSERT, which is more probable to fail, first. I'd guess its 'events' because of possible duplicities. But no big deal perhaps.</p>
</blockquote>
<p>The <code>INSERT</code> into `events` must go first because of the non-conflicting case - events_json(id) references events(id). As for the conflict, it would probably not matter which would go first.</p>
</blockquote>
<p>Mmmkay, understood.</p>
<blockquote><blockquote>
<p>So what's exactly do discuss? Let's go for it, or not? <span class="wiking smiley smiley-smiley" title=":)"></span></p>
</blockquote>
<p>I agree. I posted this to give you the hard numbers you asked for. Mek already wanted to implement it yesterday. <span class="wiking smiley smiley-smiley" title=":)"></span></p>
</blockquote>
<p>Cool, he can still implement it yesterday, if he insists. <span class="wiking smiley smiley-smiley" title=":)"></span></p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=235832019-11-22T11:58:33ZRadko Krkoškrkos@cesnet.cz
<ul></ul><p><a class="user active" href="https://homeproj.cesnet.cz/users/17">Pavel Kácha</a> wrote:</p>
<blockquote>
<p>Cool, he can still implement it yesterday, if he insists. <span class="wiking smiley smiley-smiley" title=":)"></span></p>
</blockquote>
<p>Yes, fine by me. Mek?</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=236572019-11-27T17:22:30ZJan Machjan.mach@cesnet.cz
<ul><li><strong>To be discussed</strong> changed from <i>No</i> to <i>Yes</i></li></ul> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=237042019-12-03T12:54:49ZJan Machjan.mach@cesnet.cz
<ul><li><strong>% Done</strong> changed from <i>0</i> to <i>30</i></li></ul><p>Based on first implementation I have written and executed first benchmark with following results:</p>
<pre>
BENCHMARKING MENTAT.EVENTSTORAGE MODULE (v2.5.17)
2019-12-03 12:44:01.977904
====================================================================================
Name | Iterations (#) | Duration (s) | Speed (#/s)
====================================================================================
commit | 100,000 | 161.3952113680 | 619.597
bulk commit | 100,000 | 91.4733231670 | 1,093.215
====================================================================================
</pre>
<p>Each time 100 000 events are stored into the database. In the case of 'commit' each event insertion is immediately committed, in case of 'bulk commit' the commit is made after 1000 event insertions with savepoints between each event insertion. The performance improvement is significant. In real application there will be some additional overhead of course, because there must be additional mechanism to perform commits based on timeout (in case events are not coming in fast enough).</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=237342019-12-06T10:52:35ZJan Machjan.mach@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Feedback</i></li><li><strong>% Done</strong> changed from <i>30</i> to <i>100</i></li></ul><p>Attached commits should fully resolve this issue. I have updated the <code>mentat.services.eventservice</code> DBAL to support PGSQL savepoints and bulk commits. I have then adjusted the <code>mentat.daemon.component.storage</code> module to support these new features. The bulk commit mode is optional, but enabled by default. It is possible to disable this feature altogether, at which point the module reverts back to current mode of operation (commit after each insert). In other case the transaction savepoints, rollbacks to savepoint end delayed commits are used to enhance the event insertion performance. The whole mechanism is configurable with two new additional configuration parameters <code>commit_bulk_threshold</code> (number of events to savepoint before full commit is performed, defaults to 1000) and <code>commit_bulk_timeout</code> (timeout in seconds after which to force commit of currently uncommitted events, defaults to 5s).</p>
<p>Following statistics are gathered and periodically written to log:<br /><pre>
2019-12-06 11:47:07,867 mentat-storage.py [20761] INFO: Component 'storage': *** Processing statistics ***
cnt_stored 67,811 ( +2,410, 120.50 #/s)
cnt_errors 0 ( +0, 0.00 #/s)
cnt_eci_timeout 57,741 ( +340, 17.00 #/s)
cnt_eci_threshold 10,000 ( +2,000, 100.00 #/s)
cnt_cis_timeout 339 ( +1, 0.05 #/s)
cnt_cis_threshold 10 ( +2, 0.10 #/s)
</pre></p>
<ul>
<li>cnt_eci_timeout - Number of events committed via timeout mechanism</li>
<li>cnt_eci_threshold - Number of events committed via threshold mechanism</li>
<li>cnt_cis_timeout - Number of commits performed by timeout mechanism</li>
<li>cnt_cis_threshold - Number of commits performed by threshold mechanism</li>
</ul>
<p>These new statistics enable administrators to fine tune the configuration for best performance depending on the event processing workload of their particular installation.</p>
<p>The feature is already deployed on our <strong>mentat-alt</strong> server, you may monitor it in <code>/var/mentat/log/mentat-storage.py.log</code> file.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=237612019-12-09T12:23:59ZPavel Káchaph@cesnet.cz
<ul></ul><p>Just wow, you're realy quick. Very cool. <span class="wiking smiley smiley-smiley" title=":)"></span></p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=238572019-12-12T10:02:32ZPavel Káchaph@cesnet.cz
<ul></ul><p>Please, check error handling, and if ok, we can close.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=240632020-01-08T09:22:20ZJan Machjan.mach@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>Feedback</i> to <i>In Progress</i></li><li><strong>% Done</strong> changed from <i>100</i> to <i>90</i></li></ul><p>I think one more thing needs to be addressed before closing this issue. Currently it is not guaranteed, that all currently uncommitted IDEA events will be committed on daemon shutdown. It is necessary to implement some sort of stop event, that will trigger this cleanup to avoid data loss.</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=241402020-01-13T10:37:17ZJan Machjan.mach@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Closed</i></li><li><strong>% Done</strong> changed from <i>90</i> to <i>100</i></li></ul><p>I have reviewed the implementation of a mechanism for graceful daemon shutdown, it now commits all currently uncommitted IDEA messages and switches to immediate commit mode in case some other messages end up in the queue before final shutdown (it is not guaranteed the stop event will be last event handled).</p> Mentat - Feature #4572: Minimise number of commitshttps://homeproj.cesnet.cz/issues/4572?journal_id=261762020-01-27T15:07:47ZJan Machjan.mach@cesnet.cz
<ul><li><strong>To be discussed</strong> changed from <i>Yes</i> to <i>No</i></li></ul>