Reduce database load

[donald]

mysqld has a continuous cpu load of >100%. This is caused by the database polling of the ~50 daemons.

The long-running operation, according to SHOW FULL PROCESSLIST seems to be Creating sort index of SELECT group_id, ..., stats_idle_sec FROM mxq_group WHERE (group_jobs_inq > 0 OR group_jobs_running > 0) ORDER BY user_name, group_mtime

buczek@macheteinfach:~$ time mysql --defaults-group-suffix=mxqreal -e 'SELECT group_id,group_jobs_inq, group_jobs_running, user_name, group_mtime FROM mxq_group WHERE (group_jobs_inq > 0 OR group_jobs_running > 0) ORDER BY user_name, group_mtime'
+----------+----------------+--------------------+-----------+---------------------+
| group_id | group_jobs_inq | group_jobs_running | user_name | group_mtime         |
+----------+----------------+--------------------+-----------+---------------------+
|   550312 |              0 |                  1 | ilik      | 2023-12-29 20:04:49 |
|   550311 |              0 |                  3 | ilik      | 2023-12-31 23:54:09 |
|   547624 |              0 |                  1 | spwprj    | 2023-12-30 18:32:21 |
+----------+----------------+--------------------+-----------+---------------------+

real	0m0.485s
user	0m0.008s
sys	0m0.004s

Without the ORDER BY clause, the statements takes about the same time, but SHOW FULL PROCESSLIST shows it typically in the state Sending data instead of Creating sort index.

[donald]

Useful command: mysqladmin -h mxq -u mxqprj -p -i 1 processlist

[donald]

On a test server (MySQL 8), the query time is reduced like this:

time	what
0,623s	baseline
0.600s	with index in mxq_group(group_jobs_inq, group_jobs_running)
0.622s	with index on mxq_group(group_jobs_inq) only
0.032s	with index on mxq_group(group_jobs_inq) and index on mxq_group(group_jobs_running)

Another idea: If "active" groups are queried often, we could maintain a column "active" ( when inq>0 or running>0 ) and create an index on that column.

[donald]

create index group_jobs_inq on mxq_group(group_jobs_inq) , create index group_jobs_running on mxq_group(group_jobs_running) at the red arrow. :-)