We noticed sacct is making unindexed queries[1] on job tables, which take
several seconds on an installation with ~2M job_table rows, even after
tuning mysqld.
Adding a composite index across some of the more distinctive columns dropped
query time to a few milliseconds:
ALTER TABLE ${cluster}_job_table
ADD KEY `sacct` (`id_user`,`time_start`,`time_end`)
-john
[1]
--
select t1.account, t1.cpus_alloc, t1.cpus_req, t1.derived_ec, t1.derived_es,
t1.exit_code, t1.id_assoc, t1.id_block, t1.id_group, t1.id_job, t1.id_qos,
t1.id_resv, t1.id_user, t1.id_wckey, t1.job_db_inx, t1.job_name,
t1.kill_requid, t1.mem_req, t1.node_inx, t1.nodelist, t1.nodes_alloc,
t1.partition, t1.priority, t1.state, t1.time_eligible, t1.time_end,
t1.time_start, t1.time_submit, t1.time_suspended, t1.timelimit,
t1.track_steps, t1.wckey, t2.acct, t2.lft, t2.user from
odyssey_job_table_jwm as t1 left join odyssey_assoc_table as t2 on
t1.id_assoc=t2.id_assoc where (t1.id_user='57920') && ((t1.time_start &&
((!t1.time_end && t1.state=1) || (1381240540 between t1.time_start and
t1.time_end))) || (t1.time_eligible && ((!t1.time_start && !t1.time_end) ||
(1381240540 between t1.time_eligible and t1.time_start)))) group by id_job,
time_submit desc;
--
--
John Morrissey _o /\ ---- __o
[email protected] _-< \_ / \ ---- < \,
www.horde.net/ __(_)/_(_)________/ \_______(_) /_(_)__
