<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
        {font-family:"Cambria Math";
        panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
        {font-family:Calibri;
        panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
        {margin:0cm;
        margin-bottom:.0001pt;
        font-size:12.0pt;
        font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
        {mso-style-priority:99;
        color:blue;
        text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
        {mso-style-priority:99;
        color:purple;
        text-decoration:underline;}
p
        {mso-style-priority:99;
        mso-margin-top-alt:auto;
        margin-right:0cm;
        mso-margin-bottom-alt:auto;
        margin-left:0cm;
        font-size:12.0pt;
        font-family:"Times New Roman",serif;}
tt
        {mso-style-priority:99;
        font-family:"Courier New";}
span.EmailStyle19
        {mso-style-type:personal-reply;
        font-family:"Calibri",sans-serif;
        color:#1F497D;}
.MsoChpDefault
        {mso-style-type:export-only;
        font-size:10.0pt;}
@page WordSection1
        {size:612.0pt 792.0pt;
        margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
        {page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D;mso-fareast-language:EN-US">Hi Martin,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D;mso-fareast-language:EN-US">Just a note: I would not expect any change to the MyISAM timings - as MyISAM doesn't support transactions - so the inserts would just
 happen as they do now.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D;mso-fareast-language:EN-US">Cheers,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D;mso-fareast-language:EN-US">John<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p>&nbsp;</o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p>&nbsp;</o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif"> eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-bounces@ecs.soton.ac.uk]
<b>On Behalf Of </b>martin.braendle@id.uzh.ch<br>
<b>Sent:</b> 21 December 2016 09:11<br>
<b>To:</b> eprints-tech@ecs.soton.ac.uk<br>
<b>Subject:</b> [EP-tech] Antwort: Re: Antwort: IRStats2: Innodb tables; forks<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p>&nbsp;</o:p></p>
<p><span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">Hi John,</span><br>
<br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">just to report back: the IRStats2 update on your InnoDB test server went down from 17 hours to about 4 hours. Let's see what effect your changes will have on the MyISAM production system.</span><br>
<br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">Thank you,</span><br>
<br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">Martin</span><br>
<br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">--</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">Dr. Martin Brändle</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">Zentrale Informatik</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">Universität Zürich</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">Stampfenbachstr. 73</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">CH-8006 Zürich</span><br>
<br>
<br>
<img width="16" height="16" id="_x0000_i1025" src="cid:image001.gif@01D25B79.6F834510" alt="Inactive hide details for John Salter ---16/12/2016 15:56:48---Hi Martin, The changes in https://github.com/eprints/irstats2/pu"><span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#424282">John
 Salter ---16/12/2016 15:56:48---Hi Martin, The changes in <a href="https://github.com/eprints/irstats2/pull/84">
https://github.com/eprints/irstats2/pull/84</a> seem to help us quite a bit. O</span><br>
<br>
<span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif;color:#5F5F5F">Von: </span>
<span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">John Salter &lt;<a href="mailto:J.Salter@leeds.ac.uk">J.Salter@leeds.ac.uk</a>&gt;</span><br>
<span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif;color:#5F5F5F">An: </span>
<span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">&quot;<a href="mailto:eprints-tech@ecs.soton.ac.uk">eprints-tech@ecs.soton.ac.uk</a>&quot; &lt;<a href="mailto:eprints-tech@ecs.soton.ac.uk">eprints-tech@ecs.soton.ac.uk</a>&gt;</span><br>
<span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif;color:#5F5F5F">Datum:
</span><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">16/12/2016 15:56</span><br>
<span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif;color:#5F5F5F">Betreff:
</span><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">Re: [EP-tech] Antwort: &nbsp;IRStats2: Innodb tables; forks</span><br>
<span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif;color:#5F5F5F">Gesendet von:
</span><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif"><a href="mailto:eprints-tech-bounces@ecs.soton.ac.uk">eprints-tech-bounces@ecs.soton.ac.uk</a></span><o:p></o:p></p>
<div class="MsoNormal">
<hr size="2" width="100%" noshade="" style="color:#8091A5" align="left">
</div>
<p class="MsoNormal"><br>
<br>
<br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">Hi Martin,</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">The changes in
</span><a href="https://github.com/eprints/irstats2/pull/84"><span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif">https://github.com/eprints/irstats2/pull/84</span></a><span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">&nbsp;seem
 to help us quite a bit. Out daily stats start run at 06:30, and were still running at 12:00.</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">Implementing this fix yesterday (and with two days of data to process), the stats completed processing just after 09:00.</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">&nbsp;</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">Some of the tables (details in
<a href="https://github.com/eprints/irstats2/issues/83">https://github.com/eprints/irstats2/issues/83</a>) are re-created each time the stats are processed.</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">These tables may be using InnoDB as the engine isn't specified in the CREATE TABLE statements (if you're running MySQL &gt;= v5.5.5 the default engine is InnoDB).</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">&nbsp;</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">The structure of these tables means that the InnoDB engine has to do a lot of work (maintaining the index? moving data?) for each insert or transaction. If the inserts are in a few
 transactions, there's a lot less work to be done (so it's quicker!).</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">&nbsp;</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">I'll continue to monitor how quickly our stats process - it looks like it helps us, but it might not help everyone.</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">&nbsp;</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">Cheers,</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">John</span><br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1F497D">&nbsp;</span><br>
<b><span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif">From:</span></b><span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif">&nbsp;<a href="mailto:eprints-tech-bounces@ecs.soton.ac.uk">eprints-tech-bounces@ecs.soton.ac.uk</a> [<a href="mailto:eprints-tech-bounces@ecs.soton.ac.uk">mailto:eprints-tech-bounces@ecs.soton.ac.uk</a>]
<b>On Behalf Of </b><a href="mailto:martin.braendle@id.uzh.ch">martin.braendle@id.uzh.ch</a><b><br>
Sent:</b>&nbsp;16 December 2016 13:49<b><br>
To:</b>&nbsp;<a href="mailto:eprints-tech@ecs.soton.ac.uk">eprints-tech@ecs.soton.ac.uk</a><b><br>
Subject:</b>&nbsp;[EP-tech] Antwort: IRStats2: Innodb tables; forks</span><br>
&nbsp; <o:p></o:p></p>
<p><span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">Hi John,</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif"><br>
we migrated our test system to InnoDB about a year ago, did some tuning using the MySQL performance tuning primer script, and carried out some performance tests with the Apache Benchmark tool on both the test and production system.</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif"><br>
Result: For sequential accesses, response times with InnoDB were 930-960 ms, with MyISAM they were 700ms. For concurrent accesses, response times dropped to 250ms on average with InnoDB. But you need many concurrent accesses to achieve this number. Also, when
 Web browsing, the InnoDB test system felt sluggish compared to the MyISAM production system.
<br>
IRStats2 weekly update runs take about 10 hours on the MyISAM system, and 17 hours on the InnoDB system, although there are more updates to process on the production system. &nbsp;(I see that you have added transactions to IRStats2 on GitHub, we will check whether
 this helps).</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif"><br>
To bring InnoDB to fly, you would need a huge innodb_buffer_pool_size, around the total size of your databases (which in our case is around 30-40 GB), requiring a hefty DB server.</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif"><br>
Since we were not sure, in as much the current EPrints version supports InnoDB transactions, we decided to stay with MyISAM on the production system for the moment.</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif"><br>
Cheers,</span><br>
<span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif"><br>
Martin </span><br>
<br>
<br>
<img border="0" width="16" height="16" id="_x0000_i1027" src="cid:image001.gif@01D25B79.6F834510" alt="Inactive hide details for John Salter ---13/12/2016 12:59:06---Hi, Our IRStats2 processing runs slowly, and I suspect there mig"><span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#424282">John
 Salter ---13/12/2016 12:59:06---Hi, Our IRStats2 processing runs slowly, and I suspect there might be some improvements to be made -</span><br>
<span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif;color:#5F5F5F"><br>
Von: </span><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">John Salter &lt;</span><a href="mailto:J.Salter@leeds.ac.uk"><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">J.Salter@leeds.ac.uk</span></a><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">&gt;<span style="color:#5F5F5F"><br>
An: </span>&quot;'eprints-tech@ecs.soton.ac.uk'&quot; &lt;</span><a href="mailto:eprints-tech@ecs.soton.ac.uk"><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">eprints-tech@ecs.soton.ac.uk</span></a><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">&gt;<span style="color:#5F5F5F"><br>
Datum: </span>13/12/2016 12:59<span style="color:#5F5F5F"><br>
Betreff: </span>[EP-tech] IRStats2: Innodb tables; forks<span style="color:#5F5F5F"><br>
Gesendet von: </span></span><a href="mailto:eprints-tech-bounces@ecs.soton.ac.uk"><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,sans-serif">eprints-tech-bounces@ecs.soton.ac.uk</span></a><o:p></o:p></p>
<div class="MsoNormal">
<hr size="2" width="100%" noshade="" style="color:#A0A0A0" align="left">
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><br>
<br>
<br>
<span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif"><br>
Hi,<br>
Our IRStats2 processing runs slowly, and I suspect there might be some improvements to be made - especially as we're using InnoDB tables.<br>
Before I start trying to work out how to solve this, has anyone done any work in this area?<br>
<br>
Also, there are two forks of IRStats2 that might need to be merged:</span><u><span style="color:blue"><br>
</span></u><a href="https://github.com/eprints/irstats2"><span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#0563C1">https://github.com/eprints/irstats2</span></a><u><span style="color:blue"><br>
</span></u><a href="https://github.com/eprintsug/irstats2"><span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#0563C1">https://github.com/eprintsug/irstats2</span></a><span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif"><br>
<br>
(Most other forks come from the head of eprints/irstats2).<br>
<br>
Comparing these two forks:</span><u><span style="color:blue"><br>
</span></u><a href="https://github.com/eprints/irstats2/compare/master...eprintsug:master"><span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#0563C1">https://github.com/eprints/irstats2/compare/master...eprintsug:master</span></a><span style="font-size:10.0pt;font-family:&quot;Calibri&quot;,sans-serif"><br>
it looks like there has been some re-arrangement of files (these seem sensible to me), and a couple of fixes.<br>
<br>
Does anyone have any thoughts on whether these should be merged, so we have one main master again? Will doing this cause problems for people who have installed the package using the previous file-layout?<br>
<br>
Cheers,<br>
John</span><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;">*** Options: </span>
<a href="http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech"><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;">http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech</span></a><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;"><br>
*** Archive: </span><a href="http://www.eprints.org/tech.php/"><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;">http://www.eprints.org/tech.php/</span></a><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;"><br>
*** EPrints community wiki: </span><a href="http://wiki.eprints.org/"><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;">http://wiki.eprints.org/</span></a><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;"><br>
*** EPrints developers Forum: </span><a href="http://forum.eprints.org/"><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;">http://forum.eprints.org/</span></a><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;"><br>
</span><tt><span style="font-size:10.0pt">*** Options: <a href="http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech">
http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech</a></span></tt><span style="font-size:10.0pt;font-family:&quot;Courier New&quot;"><br>
<tt>*** Archive: <a href="http://www.eprints.org/tech.php/">http://www.eprints.org/tech.php/</a></tt><br>
<tt>*** EPrints community wiki: <a href="http://wiki.eprints.org/">http://wiki.eprints.org/</a></tt><br>
<tt>*** EPrints developers Forum: <a href="http://forum.eprints.org/">http://forum.eprints.org/</a></tt><br>
<br>
</span><o:p></o:p></p>
</div>
</body>
</html>