<html 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="Title" content="">
<meta name="Keywords" content="">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Arial;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@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:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
@font-face
{font-family:PMingLiU;
panose-1:2 2 5 0 0 0 0 0 0 0;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:Calibri;}
h1
{mso-style-priority:9;
mso-style-link:"Heading 1 Char";
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:24.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:Calibri;}
span.Heading1Char
{mso-style-name:"Heading 1 Char";
mso-style-priority:9;
mso-style-link:"Heading 1";
font-family:"Calibri Light";
color:#2F5496;}
span.EmailStyle18
{mso-style-type:personal;
font-family:Calibri;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal;
font-family:Calibri;
color:#1F497D;}
p.Nadpis1, li.Nadpis1, div.Nadpis1
{mso-style-name:"Nadpis 1";
mso-style-link:"Nadpis 1 Char";
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:Calibri;}
span.Nadpis1Char
{mso-style-name:"Nadpis 1 Char";
mso-style-priority:9;
mso-style-link:"Nadpis 1";
font-weight:bold;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:Calibri;
color:windowtext;}
span.msoIns
{mso-style-type:export-only;
mso-style-name:"";
text-decoration:underline;
color:teal;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:900556060;
mso-list-type:hybrid;
mso-list-template-ids:1795485876 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style>
</head>
<body bgcolor="white" lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt">I found some time and set up a copy of our installation, modified the build2test table as referenced in your mail thread and noted 2 things<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo1"><![if !supportLists]><span style="font-size:11.0pt"><span style="mso-list:Ignore">1.<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span style="font-size:11.0pt">I stopped getting duplicate entries in this table. Since our size is in the 10’s of millions anyway that is a welcome change.<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo1"><![if !supportLists]><span style="font-size:11.0pt"><span style="mso-list:Ignore">2.<span style="font:7.0pt "Times New Roman"">
</span></span></span><![endif]><span style="font-size:11.0pt">Query times are not changed. Running explain on that query still shows a write to a temporary table.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">I will get a pull request together as I see this change as somewhat helpful, but it does not solve my initial problem<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">----------------------------------------------------------------------------------------<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">At the heart of science is an essential tension between two seemingly<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">contradictory attitudes -- an openness to new ideas, no matter how bizarre<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">or counterintuitive they may be, and the most ruthless skeptical scrutiny<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">of all ideas, old and new. This is how deep truths are winnowed from deep<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">nonsense. Of course, scientists make mistakes in trying to understand the<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">world, but there is a built-in error-correcting mechanism: The collective<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">enterprise of creative thinking and skeptical thinking together keeps the<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">field on track.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">-- Carl Sagan, "The Fine Art of Baloney Detection"<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Paul R. Wolfenbarger, P.E., M.S.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Computational Simulation Infrastructure<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">DevOps Product Owner<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Sandia National Laboratories<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">P.O. Box 5800 MS 0845<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Albuquerque, NM 87185-0845<o:p></o:p></span></p>
</div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">505-844-5458 phone<o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><a href="mailto:prwolfe@sandia.gov"><span style="color:blue">prwolfe@sandia.gov</span></a><o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">----------------------------------------------------------------------------------------</span><span style="font-size:11.0pt"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="color:black">From: </span></b><span style="color:black">"Matějů Miroslav, Ing." <Mateju.Miroslav@azd.cz></span><span style="font-family:PMingLiU;color:black"><br>
</span><b><span style="color:black">Date: </span></b><span style="color:black">Monday, February 27, 2017 at 12:31 AM<br>
<b>To: </b>"Wolfenbarger, Paul R" <prwolfe@sandia.gov><br>
<b>Cc: </b>"cdash@public.kitware.com" <cdash@public.kitware.com><br>
<b>Subject: </b>[EXTERNAL] RE: slow label query<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Times New Roman""><o:p> </o:p></span></p>
</div>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">Hi Paul,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">As far as I know (but I am rather a C++ programmer like you than a DB expert), it is better to let MySQL do as much as possible and avoid the overhead of transfers and conversions between MySQL
and PHP.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">I think your issue is related to my problem with build2test table performance that I described in the mail thread
<b><a href="http://public.kitware.com/pipermail/cdash/2016-August/001692.html">Optimizing build2test table</a></b>. Unfortunately, I haven’t been able to test and push the solution yet but it is quite easy to implement in an existing installation: Just add
the <b>testid</b> column as the second column to the <b>buildid</b> index for the
<b>build2test</b> table. I have done it using PhpMyAdmin on my production machine. It took about 2 hours (for more than 100 million rows) and the submissions (which were the main issue for me) begun to be processed about 3 times faster after that.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">Hope this helps,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:Arial;font-variant:small-caps;color:#005194">Ing. Miroslav Matějů</span></b><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:Arial;font-variant:small-caps;color:#005194">Programmer Analyst</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Arial;font-variant:small-caps;color:#005194"> </span><o:p></o:p></p>
<p class="MsoNormal"><b><span style="font-size:9.5pt;font-family:Arial;font-variant:small-caps;color:#005194">AŽD Praha s.r.o.</span></b><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:Arial;font-variant:small-caps;color:#005194">Technology</span><span lang="EN-GB" style="font-size:8.0pt;font-family:Arial;font-variant:small-caps;color:#005194"> Division</span><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:8.0pt;font-family:Arial;font-variant:small-caps;color:#005194">Research and Development</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:Arial;font-variant:small-caps;color:#005194">Žirovnická 2/3146, 106 17
</span><span lang="EN-GB" style="font-size:8.0pt;font-family:Arial;font-variant:small-caps;color:#005194">Prague</span><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:8.0pt;font-family:Arial;font-variant:small-caps;color:#005194">Czech Republic</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:Arial;font-variant:small-caps;color:#005194">Phone: +420 267 287 476</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:Arial;font-variant:small-caps;color:#005194">Web:
</span><span style="font-size:11.0pt;font-family:"Verdana","serif";color:#1F497D"><a href="http://www.azd.cz/"><span style="font-size:8.0pt;font-family:Arial;font-variant:small-caps">www.azd.cz</span></a></span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:Tahoma">From:</span></b><span style="font-size:10.0pt;font-family:Tahoma"> CDash [mailto:cdash-bounces@public.kitware.com]
<b>On Behalf Of </b>Wolfenbarger, Paul R<br>
<b>Sent:</b> Thursday, February 23, 2017 5:35 PM<br>
<b>To:</b> cdash@public.kitware.com<br>
<b>Subject:</b> [CDash] slow label query</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">I am hoping for someone with more sql understanding than I have to help with an issue. In api/v1/index.php at line 1004 there is a label query that is causing a bottleneck in my installation</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $label_query_base =</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> "SELECT b2t.status, b2t.newstatus</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> FROM build2test AS b2t</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> INNER JOIN label2test AS l2t ON</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> (l2t.testid=b2t.testid AND l2t.buildid=b2t.buildid)</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> WHERE b2t.buildid = '$buildid' AND</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> l2t.labelid IN $label_ids";</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $label_filter_query = $label_query_base . $limit_sql;</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $labels_result = pdo_query($label_filter_query);</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">This is returning two non-indexed fields from build2test and joining on indexed fields and since my build2test and label2test tables are each about 43 million records, this takes 20 to 300 seconds each. Multipled
by at least 40 builds in the loop and you can see my issue.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">As a c++ programmer my first instinct was to split the query and make all the operations work on indexes. I did some queries at the command line and got sub-second returns from each query and the php would
now look something like</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $testids = pdo_query(</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> “<span style="text-transform:uppercase">select</span> testid
<span style="text-transform:uppercase">from</span> label2test </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> <span style="text-transform:uppercase">where</span> label2test.buildid=$buildid
</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> <span style="text-transform:uppercase">and</span> label2test.labelid
<span style="text-transform:uppercase">in</span> $label_ids") ;</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $test_ids = implode("','", $testids) ;</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> $label_filter_query =
</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> "<span style="text-transform:uppercase">select</span> status, newstatus
</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> <span style="text-transform:uppercase">from</span> build2test
<span style="text-transform:uppercase">where</span> buildid = $buildid </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> <span style="text-transform:uppercase">and
</span>testid in $test_ids" ;</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">However, from my reading it seems that multiple queries of this sort are frowned on in the sql world. Before I set up a test instance to check this out do any of you have any insight into how to re-structure
the query to get the kind of performance I need?</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Thanks!</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt"> </span><o:p></o:p></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">----------------------------------------------------------------------------------------</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">"If you understand what you're doing, you're not learning anything."</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"> -- A. L.</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"> </span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Paul R. Wolfenbarger, P.E., M.S.</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Computational Simulation Infrastructure</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">DevOps Product Owner</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Sandia National Laboratories</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">P.O. Box 5800 MS 0845</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">Albuquerque, NM 87185-0845</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">505-844-5458 phone</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"><a href="mailto:prwolfe@sandia.gov"><span style="color:blue">prwolfe@sandia.gov</span></a></span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black">--------------------------------------------------------------------------------</span><span style="font-size:13.5pt;font-family:Consolas;color:black">--------</span><o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.5pt;color:black"> </span><o:p></o:p></p>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
</body>
</html>