<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=utf-8">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<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;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 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.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Prostý text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.5pt;
font-family:Consolas;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Text bubliny Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.ProsttextChar
{mso-style-name:"Prostý text Char";
mso-style-priority:99;
mso-style-link:"Prostý text";
font-family:Consolas;}
span.TextbublinyChar
{mso-style-name:"Text bubliny Char";
mso-style-priority:99;
mso-style-link:"Text bubliny";
font-family:"Tahoma","sans-serif";}
span.StylE-mailovZprvy21
{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:70.85pt 70.85pt 70.85pt 70.85pt;}
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="CS" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I have finally found the bottleneck of my CDash server. It is related to the
</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">build2test</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> table as I expected. The performance limitation is caused
by a suboptimal index.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Most types of queries to
</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">build2test</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> table that can be found in CDash sources do filter the
rows using </span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">WHERE buildid=… AND testid=…</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> Such queries cannot be supported
by existing separate indexes </span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">buildid</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> and
</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">testid</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> but a multi-column index
</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">buildid+testid</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> will cover them. (See
<a href="https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/">
https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/</a>)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">So I changed the
</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">buildid</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> index to include also
</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">testid</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> column on my testing CDash installation. The index size of
</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">build2test</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> 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">
slow_queries</a> and any CDash submissions are handled much faster: The <a href="http://stackoverflow.com/a/556411/711006">
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).<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Should I create a patch or is anyone more experienced in the CDash source able to take care of it?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I also have a few side notes which arose when I was examining the problem above:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">The file
</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">monitor.php</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> is causing most of the remaining slow queries. The function
</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">echo_project_data_sizes()</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> contains pretty complicated queries and
makes </span><span lang="EN-US" style="font-size:11.0pt;font-family:"Courier New";color:#1F497D">monitor.php</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> 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:11.0pt;font-family:"Courier New";color:#1F497D">echo_project_data_sizes()</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">. This function should have its own page
and possibly a warning message that it may take long to load and reduce performance.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Best regards,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Miroslav<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">Ing. Miroslav Matějů</span></b><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">Programmer Analyst</span><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:9.5pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">AŽD Praha s.r.o.<o:p></o:p></span></b></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">Technology</span><span lang="EN-GB" style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194"> Division</span><span style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">Research and Development<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">Žirovnická 2/3146, 106 17
</span><span lang="EN-GB" style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">Prague<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">Czech Republic</span><span style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">Phone: +420 267 287 476</span><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#005194">Web:
</span><a href="http://www.azd.cz/"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps;color:#0563C1">www.azd.cz</span></a><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<div style="border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt">
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Zack Galbreath [<a href="mailto:zack.galbreath@kitware.com">mailto:zack.galbreath@kitware.com</a>]
<br>
<b>Sent:</b> Thursday, May 19, 2016 4:33 PM<br>
<b>To:</b> Matějů Miroslav, Ing.<br>
<b>Cc:</b> <a href="mailto:cdash@public.kitware.com">cdash@public.kitware.com</a><br>
<b>Subject:</b> Re: [CDash] Duplicate rows in build2test<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<div>
<div>
<div>
<p class="MsoNormal"><span lang="EN-US">On Thu, May 19, 2016 at 9:40 AM, Matějů Miroslav, Ing. <<a href="mailto:Mateju.Miroslav@azd.cz">Mateju.Miroslav@azd.cz</a>> wrote:<o:p></o:p></span></p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hi,</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I am sending a few updates regarding the build2test problem. I copied my DB to a virtual
machine and performed a few experiments to reduce the size of build2test table. I was experimenting with an older backup version with the size of 9.3 GiB. (The DB on my production server has 17.5 GiB currently while the single table build2test has 11.7 GiB.)</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I tried to remove the duplicates using the method from
<a href="https://stackoverflow.com/a/3312066/711006">https://stackoverflow.com/a/3312066/711006</a>. First of all, I had to convert the table to MyISAM because of an InnoDB bug (<a href="https://stackoverflow.com/a/8053812/711006">https://stackoverflow.com/a/8053812/711006</a>).
The MyISAM version has about one half of the size of InnoDB version.</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">There were 1.9 million duplicate rows out of 67.6 M and the data size decreased slightly
from 1.3 GiB to 1.2 GiB while creation of the new unique index caused the index grow from 2.3 GiB to 3.2 GiB. I have not tried to convert the table back to InnoDB yet but I would expect similar inflation of index. So I would not recommend to create the unique
index anymore, at least until I check it with InnoDB.</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I also found that the column status of type varchar(10) contains just three values.
Even the command</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> SELECT status FROM build2test PROCEDURE ANALYSE()\G</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">recommended to change the type to</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> ENUM('failed','notrun','passed') NOT NULL</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I tried it and the table size decreased from 9.3 GiB to 8.5 GiB reducing the size of
both data and index, at least for InnoDB. However, the MyISAM version grew slightly (from 4.4 GiB to 5 GiB).</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I am going to try more optimization methods if time allows.</span><span lang="EN-US"><o:p></o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Thanks for digging into this. I certainly appreciate it.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Often times I find that you need to run OPTIMIZE TABLE to reclaim disk space after making big changes to the database. Consider giving that a try if you haven't been doing so already.<o:p></o:p></span></p>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>