[CDash] Optimizing build2test table

[Zack Galbreath]

On Tue, Aug 9, 2016 at 8:38 AM, Matějů Miroslav, Ing. <
Mateju.Miroslav at azd.cz> wrote:

> Hi,
>
>
>
> I have finally found the bottleneck of my CDash server. It is related to
> the build2test table as I expected. The performance limitation is caused
> by a suboptimal index.
>
>
>
> Most types of queries to build2test table that can be found in CDash
> sources do filter the rows using WHERE buildid=… AND testid=… Such
> queries cannot be supported by existing separate indexes buildid and
> testid but a multi-column index buildid+testid will cover them. (See
> https://www.percona.com/blog/2009/09/19/multi-column-
> indexes-vs-index-merge/)
>
>
>
> So I changed the buildid index to include also testid column on my
> testing CDash installation. The index size of build2test table rose by
> approx. 1 GiB to 5.6 GiB (while data size is 3.3 GiB). From that point,
> there are much less slow_queries
> <http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html> and any
> CDash submissions are handled much faster: The real time
> <http://stackoverflow.com/a/556411/711006> 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).
>

Great, thanks for investigating this.  I agree that a multi-column index
makes more sense for the build2test table.


 Should I create a patch or is anyone more experienced in the CDash source
> able to take care of it?
>

Feel free to give this a shot yourself.

You'll need to modify the CREATE TABLE statement for build2test in both
copies of cdash.sql (sql/mysql/ and sql/pgsql/).

Also add a call to AddTableIndex() somewhere around here
<https://github.com/Kitware/CDash/blob/master/public/upgrade.php#L739>.

I'm happy to help you out with this if you run into any problems.



>  I also have a few side notes which arose when I was examining the
> problem above:
>
>
>
> There is no tag corresponding to the current release 2.2.3 in the official
> Git repo. I tried *master* branch but it looks very different from the
> 2.2.x releases.
>

CDash still follows an old-school development style
<https://en.wikipedia.org/wiki/Software_versioning#Odd-numbered%5Fversions%5Ffor%5Fdevelopment%5Freleases>
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.


 The file monitor.php is causing most of the remaining slow queries. The
> function echo_project_data_sizes() contains pretty complicated queries
> and makes monitor.php 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 echo_project_data_sizes(). This function should
> have its own page and possibly a warning message that it may take long to
> load and reduce performance.
>

Good call, this page could definitely use some attention.  Relocating or
removing echo_project_data_sizes() sounds like a good first step.

Thanks again for helping to improve CDash!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://public.kitware.com/pipermail/cdash/attachments/20160810/3b212f10/attachment-0001.htm>