Ticket 1897

Dear Slurm developers:

We experienced the issue of Slurm controller can't delete an account. When tried
  sacctmgr delete account pmosp
It's either hanging for over 10 mins doing nothing or reported like
  Database is busy or waiting for lock from other user  

Then we took a look of the mysql and find a SQL command hanging there (cluster name is m2-m2):
  select t0.id_assoc from `m2-m2_job_table` as t0, `m2-m2_assoc_table` as t1, `m2-m2_assoc_table` as t2 where t1.lft between t2.lft and t2.rgt && (t2.acct='pmosp') and t0.id_assoc=t1.id_assoc limit 1;

It seems to be Three-way join: t0-t2-t3. In our production DB, size of job_table: 6000000 entries, size of id_assoc table: 2000. 

We managed to run this query on a test server with no user job in, It takes 2 min 49.02 sec. We suspected the table joining is not optimized. We rewrote an near-equivalent version which does Two Step Join- t2-t3 first, then t0-(t2-t3), as follows:
  select distinct t0.id_assoc from `m2-m2_job_table` as t0 where exists (select distinct t1.id_assoc from `m2-m2_assoc_table` as t1, `m2-m2_assoc_table` as t2 where t1.lft between t2.lft and t2.rgt && (t2.acct='pmosp')) limit 1;

It takes 3.04 sec.

Could you please have a look and see if it's a bug or not? And how many jobs can Slurm handle in theory? Thanks.

Regards,


Dr Kai Xi
HPC Consultant
Multi-modal Australian ScienceS Imaging and Visualisation Environment (www.massive.org.au)
Ph: 9902 4845 Mob: 0433 658 618