Increase Job Throughput

[jmchilton]

With metadata handled as part of the remote process - Galaxy still struggles a bit when creating and finishing hundreds of jobs a time. I've spent a good amount of time working on this problem in 2016 as part of scaling up collections but I think I need to take some more passes. Here are some things I think should be done:

TODO:

• job_wrapper.change_state should just be one database statement execute - at least in some threads sometimes. There is probably no need to load multiple objects and do things in SA. (EDIT: done in Implement HDCA update_time #9722)
• Explore faster non-SA object batch insert methods - https://stackoverflow.com/questions/12095176/optimizing-batch-inserts-sqlite.
• Implement a number of pre-created threads to work on creating jobs for a single workflow thread - this is almost ready but we should implement the last few things, have test cases, and document the entry point. Multi-threaded job scheduling in workflows. #3903
• job.mark_deleted should be made an atomic database method when change_state above is - but I guess it isn't used in the main creation and finish threads.
• https://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query

DONE:

• Pre-allocate all HIDs need for output implicit collections to eliminate a few database flushes in that process,
• Flush all collection mapping jobs at once. Last optimization left in [WIP] More Job Scheduling Optimizations #6563. Done in Flush once per mapped over execution #9985
• Optimizations for dynamic output file collection. #6367 (could be back ported to at least 18.01 safely I believe).
• Optimize object store interaction to not require so much DB flushing when creating jobs.
• Optimize checking permissions for large collections - keeping permissions objects in the DB and not loading.
• Optimize checking stage for large collections - use newer dataset matchers.

FAILED PRS:

• [18.05] Fix to speedup, reduce memory of large number of JobToInput associations. #6348
• [18.01] Expunge jobs as being created in web threads. #6343
• [18.01][WIP] Fix to speedup, reduce memory of large number of JobToInput associations. #6342
• Optimize object store interaction during job creation. #1957

[mvdbeek]

I'm gonna call this one complete (even though we didn't do batch inserts yet). I think the next step is to break down pieces into tasks we can schedule with celery, where we're not limited by the GIL. Job handlers are often close to 100% in CPU utilization, so I don't think threading is going to help much there. It'll also be much easier to scale celery tasks based on load, and they can be retried and resumed more easily.