Remove statistics from servers table to improve performance

Description

In the Admin UI, the Systems / Servers table shows some stats which are of doubtful value and expensive to generate:

Jobs completed
Mean runtime
Mean queue time

For example on a 3.x production system, the total jobs over 14 days exceeds 110,000.

The mean runtime is not helpful because there are different types of jobs with very different runtimes.

The query that generates these is very expensive because it has to summarize in this case 110K+ rows to get these figures.

If we remove those 3 columns, the query can summarize non-completed jobs (status <> 3) which is hugely faster.

A useful column to add would be total job load per server, as the job load is what load balancing is based on, rather than job count.

For example:

1.71s:

SELECT t0.id, t1.status, COUNT(t1.status), AVG(t1.queue_time), AVG(t1.run_time) FROM mh_service_registration t0, mh_job t1 WHERE (((t1.date_created >= '2017-10-20 16:30:21.214') AND (t1.date_created <= '2017-10-25 16:30:21.214')) AND (t0.id = t1.processor_service)) and t1.status <> 3 GROUP BY t0.id, t1.status;

0.01s:

SELECT t0.id, t1.status, COUNT(t1.status), AVG(t1.queue_time), AVG(t1.run_time) FROM mh_service_registration t0, mh_job t1 WHERE (((t1.date_created >= '2017-10-20 16:30:21.214') AND (t1.date_created <= '2017-10-25 16:30:21.214')) AND (t0.id = t1.processor_service)) and t1.status <> 3 GROUP BY t0.id, t1.status;

Status

Assignee

Greg Logan

Reporter

Stephen Marquard

Tags (folksonomy)

None

Components

Fix versions

Affects versions

Priority

Major
Configure