6 ms·

I would be interested about a more detailed architecture overview of the io scheduler (like this: https://www.scylladb.com/2021/04/06/scyllas-new-io-scheduler/

I would be interested about a more detailed architecture overview of the io scheduler (like this: https://www.scylladb.com/2021/04/06/scyllas-new-io-scheduler/ https://www.scylladb.com/2021/04/06/scyllas-new-io-scheduler...) and the thread scheduler. PostgreSQL has historically been bad at managing the noisy neighbor problem, but with thread pools, and io priorities, it can be solved. Has this been tackled here ?

malisper 1mo ago
I'll need to write up how the scheduler works at some point, but it's heavily based on these papers[0][1]. It solves two different problems. First, it lets us throttle resource-intensive queries. Second, it enables work stealing. If you have idle cores on your machine, we'll assign those cores to running queries to help speed them up. That means if you have an over-provisioned machine, we'll make use of the extra capacity to speed your queries up. [0] https://15721.courses.cs.cmu.edu/spring2016/papers/p743-leis.pdf https://15721.courses.cs.cmu.edu/spring2016/papers/p743-leis... [1] https://db.in.tum.de/~kohn/papers/query-scheduling-sigmod21.pdf https://db.in.tum.de/~kohn/papers/query-scheduling-sigmod21....
- rastignack 1mo ago
  Interesting. It would need some work to handle all the workloads I’ve faced where you have two applications with different priorities (ie oltp or grid workloads and analytics). In those cases you want to make sure that BI users do not interrupt the transaction processing by assigning them a set of cores, a priority, and work mem limits for example. Looks doable without major changes.
  - malisper 1mo ago
    We don't expose the priorities right now, but we have the priorities decay over time. That way faster queries get prioritized over long running queries. That should achieve the behavior you're looking for.
    - rastignack 1mo ago
      Yes for this particular use case it might help (no per-application aggregated temp file limit though). Also my grid workloads generate thousands of threads running queries with lots of small updates (job queues, calculation results..). I wish there was a way for postgresql to isolate those connections to a limited set of cores (or number of cores). To avoid completely saturating the server. Right now we advise using s3 and storing results in files, and asynchronously loading them with batch processes.

malisper 1mo ago

I'll need to write up how the scheduler works at some point, but it's heavily based on these papers[0][1]. It solves two different problems. First, it lets us throttle resource-intensive queries. Second, it enables work stealing. If you have idle cores on your machine, we'll assign those cores to running queries to help speed them up. That means if you have an over-provisioned machine, we'll make use of the extra capacity to speed your queries up. [0] https://15721.courses.cs.cmu.edu/spring2016/papers/p743-leis.pdf https://15721.courses.cs.cmu.edu/spring2016/papers/p743-leis... [1] https://db.in.tum.de/~kohn/papers/query-scheduling-sigmod21.pdf https://db.in.tum.de/~kohn/papers/query-scheduling-sigmod21....

rastignack 1mo ago
Interesting. It would need some work to handle all the workloads I’ve faced where you have two applications with different priorities (ie oltp or grid workloads and analytics). In those cases you want to make sure that BI users do not interrupt the transaction processing by assigning them a set of cores, a priority, and work mem limits for example. Looks doable without major changes.
- malisper 1mo ago
  We don't expose the priorities right now, but we have the priorities decay over time. That way faster queries get prioritized over long running queries. That should achieve the behavior you're looking for.
  - rastignack 1mo ago
    Yes for this particular use case it might help (no per-application aggregated temp file limit though). Also my grid workloads generate thousands of threads running queries with lots of small updates (job queues, calculation results..). I wish there was a way for postgresql to isolate those connections to a limited set of cores (or number of cores). To avoid completely saturating the server. Right now we advise using s3 and storing results in files, and asynchronously loading them with batch processes.