<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Tue, Aug 9, 2016 at 8:38 AM, Matějů Miroslav, Ing. <span dir="ltr"><<a href="mailto:Mateju.Miroslav@azd.cz" target="_blank">Mateju.Miroslav@azd.cz</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
<div lang="CS" link="blue" vlink="purple">
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Hi,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I have finally found the bottleneck of my CDash server. It is related to the
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">build2test</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> table as I expected. The performance limitation is caused
by a suboptimal index.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Most types of queries to
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">build2test</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> table that can be found in CDash sources do filter the
rows using </span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">WHERE buildid=… AND testid=…</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> Such queries cannot be supported
by existing separate indexes </span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">buildid</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> and
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">testid</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> but a multi-column index
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">buildid+testid</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> will cover them. (See
<a href="https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/" target="_blank">
https://www.percona.com/blog/<wbr>2009/09/19/multi-column-<wbr>indexes-vs-index-merge/</a>)<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">So I changed the
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">buildid</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> index to include also
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">testid</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> column on my testing CDash installation. The index size of
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">build2test</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> table rose by approx. 1 GiB to 5.6 GiB (while data size
is 3.3 GiB). From that point, there are much less <a href="http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html" target="_blank">
slow_queries</a> and any CDash submissions are handled much faster: The <a href="http://stackoverflow.com/a/556411/711006" target="_blank">
real time</a> of my CTest run (with several builds and thousands of tests) using synchronous submission decreased from 152 min to 69 min and no submission exceeds the processing timeout (unlike before). The processing delay of an asynchronous submission series
slightly decreased (max. 0.53 hours, compared to more than one hour).</span></p></div></div></blockquote><div><br></div><div>Great, thanks for investigating this. I agree that a multi-column index makes more sense for the build2test table. </div><div><br></div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div lang="CS" link="blue" vlink="purple"><div><p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> </span><span style="color:rgb(31,73,125);font-family:Calibri,sans-serif;font-size:11pt">Should I create a patch or is anyone more experienced in the CDash source able to take care of it?</span></p></div></div></blockquote><div><br></div><div>Feel free to give this a shot yourself.</div><div><br></div><div>You'll need to modify the CREATE TABLE statement for build2test in both copies of cdash.sql (sql/mysql/ and sql/pgsql/).</div><div><br></div><div>Also add a call to AddTableIndex() <a href="https://github.com/Kitware/CDash/blob/master/public/upgrade.php#L739">somewhere around here</a>.</div><div><br></div><div>I'm happy to help you out with this if you run into any problems. <br></div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div lang="CS" link="blue" vlink="purple"><div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> </span><span style="color:rgb(31,73,125);font-family:Calibri,sans-serif;font-size:11pt">I also have a few side notes which arose when I was examining the problem above:</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">There is no tag corresponding to the current release 2.2.3 in the official Git repo. I tried
<i>master</i> branch but it looks very different from the 2.2.x releases.</span></p></div></div></blockquote><div><br></div><div>CDash still follows an <a href="https://en.wikipedia.org/wiki/Software_versioning#Odd-numbered%5Fversions%5Ffor%5Fdevelopment%5Freleases">old-school development style</a> where even numbers represent stable releases and odd numbers represent development releases. So CDash 2.3 is a work-in-progress that will culminate with the release of CDash 2.4.</div><div> </div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div lang="CS" link="blue" vlink="purple"><div><p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> </span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">The file
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">monitor.php</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> is causing most of the remaining slow queries. The function
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">echo_project_data_sizes()</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> contains pretty complicated queries and
makes </span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">monitor.php</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> practically useless (i.e. unresponsive) or even
harmful when used on a busy DB: It’s slowing down even more and possibly causes exceeding of the processing timeout. This problem disappears when I comment out the call to
</span><span lang="EN-US" style="font-size:11pt;font-family:"Courier New";color:rgb(31,73,125)">echo_project_data_sizes()</span><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">. This function should have its own page
and possibly a warning message that it may take long to load and reduce performance.</span></p></div></div></blockquote><div><br></div><div>Good call, this page could definitely use some attention. Relocating or removing <font face="monospace, monospace">echo_project_data_sizes()</font> sounds like a good first step. </div><div><br></div><div>Thanks again for helping to improve CDash!</div></div></div></div>