<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;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Calibri","sans-serif";}
h1
{mso-style-priority:9;
mso-style-link:"Nadpis 1 Char";
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:24.0pt;
font-family:"Times New Roman","serif";}
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;}
span.StylE-mailovZprvy17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.StylE-mailovZprvy18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.Nadpis1Char
{mso-style-name:"Nadpis 1 Char";
mso-style-priority:9;
mso-style-link:"Nadpis 1";
font-weight:bold;}
.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 bgcolor="white" lang="CS" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:#1F497D">Hi Paul,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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
</span><b><span style="font-size:11.0pt;color:#1F497D"><a href="http://public.kitware.com/pipermail/cdash/2016-August/001692.html">Optimizing build2test table</a></span></b><span lang="EN-US" style="font-size:11.0pt;color:#1F497D">. 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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;color:#1F497D">Hope this helps,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;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;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;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;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><span style="font-size:11.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><a href="http://www.azd.cz/"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";font-variant:small-caps">www.azd.cz</span></a></span><span style="font-size:11.0pt;color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> 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<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US" 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<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> $label_query_base =<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> "SELECT b2t.status, b2t.newstatus<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> FROM build2test AS b2t<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> INNER JOIN label2test AS l2t ON<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> (l2t.testid=b2t.testid AND l2t.buildid=b2t.buildid)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> WHERE b2t.buildid = '$buildid' AND<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> l2t.labelid IN $label_ids";<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> $label_filter_query = $label_query_base . $limit_sql;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> $labels_result = pdo_query($label_filter_query);<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> $testids = pdo_query(<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> “<span style="text-transform:uppercase">select</span> testid
<span style="text-transform:uppercase">from</span> label2test <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> <span style="text-transform:uppercase">where</span> label2test.buildid=$buildid
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> <span style="text-transform:uppercase">and</span> label2test.labelid
<span style="text-transform:uppercase">in</span> $label_ids") ;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> $test_ids = implode("','", $testids) ;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> $label_filter_query =
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> "<span style="text-transform:uppercase">select</span> status, newstatus
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> <span style="text-transform:uppercase">from</span> build2test
<span style="text-transform:uppercase">where</span> buildid = $buildid <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"> <span style="text-transform:uppercase">and
</span>testid in $test_ids" ;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt">Thanks!<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt"><o:p> </o:p></span></p>
<div>
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;color:black">----------------------------------------------------------------------------------------<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;color:black">"If you understand what you're doing, you're not learning anything."<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;color:black"> -- A. L.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" 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 lang="EN-US" style="font-size:10.5pt;color:black">Computational Simulation Infrastructure<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;color:black">DevOps Product Owner<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;color:black">Sandia National Laboratories<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" 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 lang="EN-US" style="font-size:10.5pt;color:black">Albuquerque, NM 87185-0845<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;color:black">505-844-5458 phone<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" 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 lang="EN-US" style="font-size:10.5pt;color:black">--------------------------------------------------------------------------------</span><span lang="EN-US" style="font-size:13.5pt;font-family:Consolas;color:black">--------</span><span lang="EN-US" style="font-size:10.5pt;color:black"><o:p></o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;color:black"><o:p> </o:p></span></p>
</div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
</body>
</html>