17K finished jobs, tab not loading

[ambs]

I have a minion installation with a lot of small jobs. It finished running more than 16.800 jobs. When trying to open the "finished" tab, the UI loads forever. How can I make it load faster? Thanks

[preaction]

I don't know. CPAN Testers has 40000 finished jobs and the tab loads pretty fast. Are you using Minion features like locks and parent/child dependencies?

[ambs]

Hi. No, at the moment I am not. Fortunately jobs are being hidden when they make two days, and things get to normal again. If you want to debug this deeper, let me know how I can help. Best

[preaction]

Alright, my guess was that the joins for locks and parent/child dependencies were inefficient, but if you're not using them that's not the problem you're having...

There might be a way to get profiling information from DBI by using DBI_PROFILE. You don't have to set it in a running daemon, you can use the get command to run a single test:

$ DBI_PROFILE=2 ./myapp.pl get '/minion/jobs?state=finished'

That should print the queries being run and the time it took to run them, like so:

DBI::Profile: 1.738794s 14.49% (9866 calls) mysql.t @ 2019-11-23 18:42:19
'' =>
    0.074978s / 1526 = 0.000049s avg (first 0.000016s, min 0.000000s, max 0.012657s)
'SELECT
          id, name, UNIX_TIMESTAMP(expires) AS expires
      FROM minion_locks
      WHERE expires > now()
      ORDER BY id
      DESC LIMIT ? OFFSET ?' =>
    0.001240s / 27 = 0.000046s avg (first 0.000039s, min 0.000000s, max 0.000425s)
'SELECT
          id, name, UNIX_TIMESTAMP(expires) AS expires
      FROM minion_locks
      WHERE name in (?) AND expires > now()
      ORDER BY id
      DESC LIMIT ? OFFSET ?' =>
    0.000932s / 20 = 0.000047s avg (first 0.000043s, min 0.000000s, max 0.000377s)
'SELECT
      id, args, attempts,
      UNIX_TIMESTAMP(created) AS created,
      UNIX_TIMESTAMP(`delayed`) AS `delayed`,
      UNIX_TIMESTAMP(finished) AS finished, priority,
      queue, result, UNIX_TIMESTAMP(retried) AS retried, retries,
      UNIX_TIMESTAMP(started) AS started, state, task,
      GROUP_CONCAT( child_jobs.child_id SEPARATOR ':' ) AS children,
      GROUP_CONCAT( parent_jobs.parent_id SEPARATOR ':' ) AS parents,
      worker, notes
    FROM minion_jobs
    LEFT JOIN minion_jobs_depends child_jobs ON minion_jobs.id=child_jobs.parent_id
    LEFT JOIN minion_jobs_depends parent_jobs ON minion_jobs.id=parent_jobs.child_id

    GROUP BY minion_jobs.id, child_jobs.parent_id, parent_jobs.child_id
           , minion_jobs.args, minion_jobs.attempts, minion_jobs.created,
             minion_jobs.delayed, minion_jobs.finished, minion_jobs.notes,
             minion_jobs.priority, minion_jobs.queue, minion_jobs.result,
             minion_jobs.retried, minion_jobs.retries, minion_jobs.started,
             minion_jobs.state, minion_jobs.task, minion_jobs.worker
    ORDER BY id DESC
    LIMIT ?
    OFFSET ?' =>
    0.004312s / 27 = 0.000160s avg (first 0.000131s, min 0.000001s, max 0.001059s)

Find the query that's taking too long and run EXPLAIN SELECT ... and post the output of that along with the DBI profiling output and the output of mysqld --version just to be thorough.

Thanks for helping out!

[ambs]

This will be harder than expected. I have webui and workers running on docker images that... do not have bash installed... :grin:

[ambs]

Stupid question: is there a way to supply basic auth on the myapp.pl command line?

[preaction]

myapp.pl help get says that -u <user>:<pass> should work.

[ambs]

Thanks, used help, but not help get. Will try.

[preaction]

Closing this as irreproducible for now