4 ms·

I wrote a system that used the database as essentially a rules engine for "what work to execute next?" There were constraints about max throughput per key, wait

by sb8244 3y ago

I wrote a system that used the database as essentially a rules engine for "what work to execute next?" There were constraints about max throughput per key, waiting periods, etc. That system broke me down and built me back up over time. Really deep explaining, understanding the internals of how the data is actually being processed, then finding ways to improve it. I had 1-2 other people who were really interested in postgres that would help work on debugging. We would continuously get 100x query improvements every time we dove in. Including after we thought we maxed it out. I hope everyone gets the chance to work on something like that. It was really fun.

PTOB 3y ago
I would slaver at mouth over that opportunity.
renegade-otter 3y ago
If anyone starts a new side project, do yourself a favor - write your data access layer in straight SQL. With minimal wiring and safe variable substitution that most libraries provide, you will learn a lot.
derefr 3y ago
> a rules engine for "what work to execute next?" I'm afraid to inform you, that you have built a cluster workload manager. (Where a "cluster workload manager" is a type of job scheduler designed for — usually HPC — clusters, that prioritizes among ongoing abstract batch "jobs" that can each broken down into more granular atomic scheduleable work-units; and which accounts the often-unpredictable resource-consumption outcomes of those work-units, to the account of the job and its owning user+group+org, to then weight further executions of that job or other jobs owned by that user+group+org, so that ultimately, jobs can proceed at a QoS/rate that's 1. "fair", and 2. in proportion to the amount that a given customer is willing to pay for prioritization. "Business rules" in such a system, are expressed in terms of conditional coefficients or formulae to apply to the "costs" used to re-weight jobs.) Examples of cluster workload management software: • Slurm (https://en.wikipedia.org/wiki/Slurm_Workload_Manager https://en.wikipedia.org/wiki/Slurm_Workload_Manager) • IBM Spectrum LSF (https://www.ibm.com/products/hpc-workload-management https://www.ibm.com/products/hpc-workload-management) • technically, Kubernetes' kube-scheduler — though it's the most trivial possible implementation of one, without any of the ongoing-fairness-of-QoS logic. The key thing to know about these, is that it's nearly impossible to build one that lives "quiesced" inside a database (as you've probably found.) Imagine taking an OS process scheduler and trying to keep the state for that canonically inside a database. Then imagine multiplying the write load by 1000x, while keeping the realtime read requirements the same. It's just a bad idea. You can certainly persist the state of these systems into a DB — but what you really want is one or more active scheduler nodes, that keep a big in-memory ledger matrix, that can be mutated moment-to-moment, without needing to persist every single change to disk in some WAL/journal/etc. Like a regular OS scheduler. Or like, say, a game physics engine — very similar in how it's all about tiny little "integration" steps, many times per second.
- sb8244 3y ago
  I was generic in how I described it because I didn't want to talk about the system (it doesn't matter). The system was for a very specific use case and the set of rules were all specific to that domain. The domain was not related to workload management or jobs, but you could generalize what it did in those terms. edit: removed some snark. I did want to rewrite this into an in-memory data structure (Elixir-based). It generally worked but the postgres solution was actually really really good for the domain it was built for.
  - derefr 3y ago
    My point was that — for just your use-case as originally stated — you probably could have taken a FOSS cluster workload manager like SLURM and "narrowed" it to your domain + use-case; rather than starting from a DBMS and adding all the stuff necessary to make it understand how to be a scheduler and then fighting with it to scale its use-case-impedance-mismatched state-storage backend to your production workload. (Where I assume you were "fighting with it" because, as you said, you were "torn down and built back up" in the process of making this system scale.) If, however, you had other database-related needs you didn't mention — e.g. being able to arbitrarily join the state data of this system as dimension data in OLAP queries on other, non-scheduler-y tables in that DB — then your constraints probably did indeed make using a DBMS the best choice here, despite the scaling challenges. (Also, funny enough, I am myself in the middle of writing a cluster-workload-manager as a custom in-memory Elixir daemon! But that's with the unique constraint of needing lightweight, realtime prioritization of workloads, each backing synchronous HTTP RPC requests. Having millions of teeny-tiny jobs that are usually over in milliseconds [but which can sometimes run for multiple minutes], isn't something any HPC-targeting workload manager is really architected for.)
    - sb8244 3y ago
      I don't think that would've been a good idea. I guess it's possible, but I'd rather trade known obstacles versus unknown obstacles (that will definitely exist). I said "torn down and built back up" in an endearing way. Sure it was difficult work at times, but it was super fun and we would spend 2 days at a time fixing it until something popped up 6 months later. But the amount of stuff I learned from that was (I will wager) exponentially more useful than figuring out how to adapt/run a fairly major FOSS.

by sb8244 3y ago

PTOB 3y ago

I would slaver at mouth over that opportunity.

renegade-otter 3y ago

If anyone starts a new side project, do yourself a favor - write your data access layer in straight SQL. With minimal wiring and safe variable substitution that most libraries provide, you will learn a lot.

derefr 3y ago

> a rules engine for "what work to execute next?" I'm afraid to inform you, that you have built a cluster workload manager. (Where a "cluster workload manager" is a type of job scheduler designed for — usually HPC — clusters, that prioritizes among ongoing abstract batch "jobs" that can each broken down into more granular atomic scheduleable work-units; and which accounts the often-unpredictable resource-consumption outcomes of those work-units, to the account of the job and its owning user+group+org, to then weight further executions of that job or other jobs owned by that user+group+org, so that ultimately, jobs can proceed at a QoS/rate that's 1. "fair", and 2. in proportion to the amount that a given customer is willing to pay for prioritization. "Business rules" in such a system, are expressed in terms of conditional coefficients or formulae to apply to the "costs" used to re-weight jobs.) Examples of cluster workload management software: • Slurm (https://en.wikipedia.org/wiki/Slurm_Workload_Manager https://en.wikipedia.org/wiki/Slurm_Workload_Manager) • IBM Spectrum LSF (https://www.ibm.com/products/hpc-workload-management https://www.ibm.com/products/hpc-workload-management) • technically, Kubernetes' kube-scheduler — though it's the most trivial possible implementation of one, without any of the ongoing-fairness-of-QoS logic. The key thing to know about these, is that it's nearly impossible to build one that lives "quiesced" inside a database (as you've probably found.) Imagine taking an OS process scheduler and trying to keep the state for that canonically inside a database. Then imagine multiplying the write load by 1000x, while keeping the realtime read requirements the same. It's just a bad idea. You can certainly persist the state of these systems into a DB — but what you really want is one or more active scheduler nodes, that keep a big in-memory ledger matrix, that can be mutated moment-to-moment, without needing to persist every single change to disk in some WAL/journal/etc. Like a regular OS scheduler. Or like, say, a game physics engine — very similar in how it's all about tiny little "integration" steps, many times per second.

sb8244 3y ago
I was generic in how I described it because I didn't want to talk about the system (it doesn't matter). The system was for a very specific use case and the set of rules were all specific to that domain. The domain was not related to workload management or jobs, but you could generalize what it did in those terms. edit: removed some snark. I did want to rewrite this into an in-memory data structure (Elixir-based). It generally worked but the postgres solution was actually really really good for the domain it was built for.
- derefr 3y ago
  My point was that — for just your use-case as originally stated — you probably could have taken a FOSS cluster workload manager like SLURM and "narrowed" it to your domain + use-case; rather than starting from a DBMS and adding all the stuff necessary to make it understand how to be a scheduler and then fighting with it to scale its use-case-impedance-mismatched state-storage backend to your production workload. (Where I assume you were "fighting with it" because, as you said, you were "torn down and built back up" in the process of making this system scale.) If, however, you had other database-related needs you didn't mention — e.g. being able to arbitrarily join the state data of this system as dimension data in OLAP queries on other, non-scheduler-y tables in that DB — then your constraints probably did indeed make using a DBMS the best choice here, despite the scaling challenges. (Also, funny enough, I am myself in the middle of writing a cluster-workload-manager as a custom in-memory Elixir daemon! But that's with the unique constraint of needing lightweight, realtime prioritization of workloads, each backing synchronous HTTP RPC requests. Having millions of teeny-tiny jobs that are usually over in milliseconds [but which can sometimes run for multiple minutes], isn't something any HPC-targeting workload manager is really architected for.)
  - sb8244 3y ago
    I don't think that would've been a good idea. I guess it's possible, but I'd rather trade known obstacles versus unknown obstacles (that will definitely exist). I said "torn down and built back up" in an endearing way. Sure it was difficult work at times, but it was super fun and we would spend 2 days at a time fixing it until something popped up 6 months later. But the amount of stuff I learned from that was (I will wager) exponentially more useful than figuring out how to adapt/run a fairly major FOSS.