Bug 6796 - Very long conversion time of cheops_job_table with mysql 5.1 (with patch)
Summary: Very long conversion time of cheops_job_table with mysql 5.1 (with patch)
Status: RESOLVED WONTFIX
Alias: None
Product: Slurm
Classification: Unclassified
Component: Database (show other bugs)
Version: 17.11.13
Hardware: Linux Linux
: --- C - Contributions
Assignee: Tim Wickberg
QA Contact:
URL:
Depends on:
Blocks:
 
Reported: 2019-04-03 04:25 MDT by Lech
Modified: 2019-04-03 23:30 MDT (History)
1 user (show)

See Also:
Site: -Other-
Alineos Sites: ---
Atos/Eviden Sites: ---
Confidential Site: ---
Coreweave sites: ---
Cray Sites: ---
DS9 clusters: ---
HPCnow Sites: ---
HPE Sites: ---
IBM Sites: ---
NOAA SIte: ---
OCF Sites: ---
Recursion Pharma Sites: ---
SFW Sites: ---
SNIC sites: ---
Linux Distro: ---
Machine Name:
CLE Version:
Version Fixed:
Target Release: ---
DevPrio: ---
Emory-Cloud Sites: ---

Attachments
small patch for job_table conversion (1.75 KB, patch)
2019-04-03 04:25 MDT, Lech 		Details | Diff
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Lech 2019-04-03 04:25:25 MDT 
Created attachment 9775 [details]
small patch for job_table conversion

Hello,

our system runs on RHEL6 with the default MySQL 5.1 server.
We've tried to upgrade slurm 17.02 to 17.11 and have run into a very long (>90 hours) execution time of a certain conversion query of the <clustername>_job_table. After some work, we were able to make a simple solution for the issue which may be beneficial to others with RHEL6/CentOS6.

Analysis of the issue has shown that the mysql optimizer didn't interpret the nested query correctly and has choosen the wrong execution plan. This was probably due to the relatively old mysql version 5.1.69.
Apparently, the "right table" of the join operation of the query wasn’t created first, as it should be, which resulted in a massive performance loss.
The solution was straightforward - enforce the correct execution order by extracting the subquery that creates the "right table" and perform it before the main query. This makes the main query parseable to older mysql versions and at the same time doesn't impede performance for newer versions, as the temporary table needs to be created anyway, albeit as a derived one. We've used a temporary table because it is dropped automatically after the mysql session ends.

With the attached patch the conversion of the <clustername>_job_table, with ca. 11 million entries, was completed in 17 minutes. The rest of the upgrade went without problems. So did the second upgrade to 18.08. So this should fix the upgrade process for RHEL6/CentOS6 when mysql is concerned.

I've attached the patch for 17.11.13-2. It's the same code for 18.08.x, only the offset changes.

Kind regards,
Lech
Comment 1 Tim Wickberg 2019-04-03 04:37:43 MDT 
Hi Lech -

Thank you for the submission, but I will not be merging this upstream at this time.

Support for the 17.11 release is nearly ended, and we do not expect to make any further maintenance releases on that branch.

For 18.08, we're rather late in to the lifecycle to make a change this significant to the MySQL conversion code, and I will not be passing this along in to our review process for further evaluation. Our recommendation to sites to run a modern MySQL/MariaDB version stands as our primary means of avoiding this class of issues.

Thank you again for the submission,
- Tim
Comment 2 Ole.H.Nielsen@fysik.dtu.dk 2019-04-03 08:02:46 MDT 
(In reply to Tim Wickberg from comment #1)
> Thank you for the submission, but I will not be merging this upstream at
> this time.
> 
> Support for the 17.11 release is nearly ended, and we do not expect to make
> any further maintenance releases on that branch.
> 
> For 18.08, we're rather late in to the lifecycle to make a change this
> significant to the MySQL conversion code, and I will not be passing this
> along in to our review process for further evaluation. Our recommendation to
> sites to run a modern MySQL/MariaDB version stands as our primary means of
> avoiding this class of issues.

My 2 cents: A lot of SchedMD customers are probably running CentOS 7 or RHEL 7 on their clusters.  The database provided by CentOS 7/RHEL 7 is MariaDB version 5.5. This is probably the database version which most of these customers are running.

I second Lech's request for a patch to improve the database upgrade efficiency of MariaDB version 5.5.  Would you kindly consider the patch again for inclusion in Slurm 19.05?

Thanks,
Ole
Comment 3 Tim Wickberg 2019-04-03 23:30:04 MDT 
> My 2 cents: A lot of SchedMD customers are probably running CentOS 7 or RHEL
> 7 on their clusters.  The database provided by CentOS 7/RHEL 7 is MariaDB
> version 5.5. This is probably the database version which most of these
> customers are running.

SchedMD customers should already be running some version of 17.11 or 18.08, and thus should have finished the conversion process at some point by now. So this does not affect them at this point in time.

> I second Lech's request for a patch to improve the database upgrade
> efficiency of MariaDB version 5.5.  Would you kindly consider the patch
> again for inclusion in Slurm 19.05?

This patch, or a variant thereof, is not necessary for the upcoming 19.05 release. The conversion it is optimizing to avoid issues with MySQL's query planning is not performed any longer, as it must have been completed during a prior Slurm major version upgrade.

Thus, this ticket remains resolved/wontfix.

Lech - I thank you again for the submission, but as mentioned I will not be considering it for review at this time.

- Tim