5 ms·

You generally want to keep your CPU fully utilized. It looks like Oriole is doing significantly more transactions and is CPU-bound, due to much lower IO require

You generally want to keep your CPU fully utilized. It looks like Oriole is doing significantly more transactions and is CPU-bound, due to much lower IO requirements. The good news is that it implies you could get even more performance out of Oriole by vertically scaling to a more powerful CPU, whereas Postgres would not continue to increase in performance this way. Those idle times on the Postgres server could be used for something else, if you're thinking in a desktop OS mindset. But for servers, you tend to want machines that are doing one thing and are optimized for that thing.

raggi 3y ago
It’s hard to generalize on these points. In a situation where the throughput was inverted but the proportional system usage was the same, you would instead say “you can still vertically scale by adding more disks”, rather than saying adding bigger cpu. It’s not meaningful in isolation. It may be reasonable to suggest that for a new code base that is cpu bound there’s a good chance there is low hanging fruit for cpu optimizations that may further increase the throughput gap. It’s also the case however that the prior engines tuning starting life on much older computer architectures, drastically different proportional syscall costs and so on, it very often means that there’s low hanging fruit in configuration to improve baseline benchmarks such as these. High io time suggests poor caching which in many scenarios you’d consider a suboptimal deployed configuration. It’s not just the devil that’s in the details, it’s everything.
- acjohnson55 3y ago
  That makes sense. I'm mostly just trying to explain the counterintuitive reason that the high CPU usage shouldn't be interpreted as a flaw.
- raggi 3y ago
  To be a little more clear on what the detail of the benchmark in question is: it’s a benchmark that explicitly exercises a pathological use case for postgresqls current design, one that nonetheless functions, and demonstrates that the advertised engine does not have that pathology. A key takeaway should probably be, if you’re a Postgres user: if your workload looks exactly like this (sparse upserts into a large data set at a high rate) then you might want to evaluate your the runway of your architecture before the geometric costs or latency stalls become relevant - just as for cost analysis of any other system. What is somewhat interesting in this article, and not super clearly presented, is that this workload is actually fairly pathological for most existing engines offering this set of structural and query facilities, and that’s interesting, if this is the niche you need. Most people do some amount of this, but not always at a super high rate, and there are ways to get the same effective writable/readable data using a different schema, while avoiding it. Nice thing here is you can do the one-liner version.
- gary_0 3y ago
  > you can still vertically scale by adding more disks Parallelizing IO is a lot different from scaling up CPU power, though. I'd imagine DB server IO performance has a lot less lower-hanging fruit than CPU/software performance.
  - raggi 3y ago
    That depends, the ratio of free bus capacity for data fetch, and free capacity for inter-CPU synchronization is skewed _massively_ in favor of capacity for data fetch. An x86 system is already under-capacity at the cpu/bus interface, which is why we keep throwing more and more cache at the problem and it works. Similarly in the cloud on AWS fro example, you have publicly available scalability options starting from 5k IOPS up to 2M IOPS, >400x or 3 orders of magnitude. By contrast you're going from 1vcpu to 192 cores, about half the raise, and a lower performance scaling due to the increased cost of cross-package shootdowns. Yup, they're different, for sure, but the implication that CPU is easier is not all that clear. In either case, with a database style workload, and with either of these engines in practice you're going to hit a limit at the bus in practice long before you hit a limit on compute or disk io, for any sustained workload - bursts are different.
tanelpoder 3y ago
> You generally want to keep your CPU fully utilized. Not in real life concurrent systems where latency matters. In addition to the queuing/random request arrival rate reasons, all kinds of funky latency hiccups start happening both at the DB and OS level when you run your CPU average utilization near 100%. Spinlocks, priority inversion, etc. Some bugs show up that don’t manifest when running with lower CPU utilization etc.
- deadbeeves 3y ago
  So what you're saying is that an acceptable way to compensate the system's bugginess is by making it more inefficient? I'd rather use a system that's stable under load.
  - ignoramous 3y ago
    OP has a point [0], though I'm unsure if Kernels under load are really that unstable / untested. [0] https://brooker.co.za/blog/2021/05/24/metastable.html https://brooker.co.za/blog/2021/05/24/metastable.html / https://archive.is/6Qtet https://archive.is/6Qtet
- mlyle 3y ago
  Here the system is doing a pretty consistent 750k TPS instead of oscillating between 0 and 225k-- often sitting near 0TPS for tens of seconds. Which system do you think will have better latency for any given loading?
- newaccount74 3y ago
  This is a benchmark that tries to execute as many queries as possible, so the interesting stat is transactions per second, not CPU usage. This benchmark is testing top speed, not real world behaviour. If you tested both systems with the same workload (eg. a specific number of queries per second), then the average CPU usage would be much lower for the more efficient engine. The low CPU usage in this benchmark is just a sign that the performance is not CPU bound, but limited by other factors like locking or IO.
- arghwhat 3y ago
thayne 3y ago
> You generally want to keep your CPU fully utilized Only if your load is very predictable. If there is a chance of a spike, you often want enough headroom to handle it. Even if you have some kind of automated scaling, that can take time, and you probably want a buffer until your new capacity is available.
- mlyle 3y ago
  I think many here is misunderstanding what was likely meant: postgresql was not able to use all the available CPU under this situation, in that it was oscillating from 10% to 70% CPU use. That 40% average cpu use isn't an asset on a dedicated database server: it just means that the other 60% of available cycles are a perishable resource that are immediately spoiling. In that sense, you want to be able to have your database be able to use all the resources available: all the IOPS, all the CPU cycles, etc. And, of course, the real thing is the amount of work you get done: this thing does more work-- partially by using more CPU cycles, and partially by doing more work per CPU cycle.

raggi 3y ago

It’s hard to generalize on these points. In a situation where the throughput was inverted but the proportional system usage was the same, you would instead say “you can still vertically scale by adding more disks”, rather than saying adding bigger cpu. It’s not meaningful in isolation. It may be reasonable to suggest that for a new code base that is cpu bound there’s a good chance there is low hanging fruit for cpu optimizations that may further increase the throughput gap. It’s also the case however that the prior engines tuning starting life on much older computer architectures, drastically different proportional syscall costs and so on, it very often means that there’s low hanging fruit in configuration to improve baseline benchmarks such as these. High io time suggests poor caching which in many scenarios you’d consider a suboptimal deployed configuration. It’s not just the devil that’s in the details, it’s everything.

acjohnson55 3y ago
That makes sense. I'm mostly just trying to explain the counterintuitive reason that the high CPU usage shouldn't be interpreted as a flaw.
raggi 3y ago
To be a little more clear on what the detail of the benchmark in question is: it’s a benchmark that explicitly exercises a pathological use case for postgresqls current design, one that nonetheless functions, and demonstrates that the advertised engine does not have that pathology. A key takeaway should probably be, if you’re a Postgres user: if your workload looks exactly like this (sparse upserts into a large data set at a high rate) then you might want to evaluate your the runway of your architecture before the geometric costs or latency stalls become relevant - just as for cost analysis of any other system. What is somewhat interesting in this article, and not super clearly presented, is that this workload is actually fairly pathological for most existing engines offering this set of structural and query facilities, and that’s interesting, if this is the niche you need. Most people do some amount of this, but not always at a super high rate, and there are ways to get the same effective writable/readable data using a different schema, while avoiding it. Nice thing here is you can do the one-liner version.
gary_0 3y ago
> you can still vertically scale by adding more disks Parallelizing IO is a lot different from scaling up CPU power, though. I'd imagine DB server IO performance has a lot less lower-hanging fruit than CPU/software performance.
- raggi 3y ago
  That depends, the ratio of free bus capacity for data fetch, and free capacity for inter-CPU synchronization is skewed _massively_ in favor of capacity for data fetch. An x86 system is already under-capacity at the cpu/bus interface, which is why we keep throwing more and more cache at the problem and it works. Similarly in the cloud on AWS fro example, you have publicly available scalability options starting from 5k IOPS up to 2M IOPS, >400x or 3 orders of magnitude. By contrast you're going from 1vcpu to 192 cores, about half the raise, and a lower performance scaling due to the increased cost of cross-package shootdowns. Yup, they're different, for sure, but the implication that CPU is easier is not all that clear. In either case, with a database style workload, and with either of these engines in practice you're going to hit a limit at the bus in practice long before you hit a limit on compute or disk io, for any sustained workload - bursts are different.

tanelpoder 3y ago

> You generally want to keep your CPU fully utilized. Not in real life concurrent systems where latency matters. In addition to the queuing/random request arrival rate reasons, all kinds of funky latency hiccups start happening both at the DB and OS level when you run your CPU average utilization near 100%. Spinlocks, priority inversion, etc. Some bugs show up that don’t manifest when running with lower CPU utilization etc.

deadbeeves 3y ago
So what you're saying is that an acceptable way to compensate the system's bugginess is by making it more inefficient? I'd rather use a system that's stable under load.
- ignoramous 3y ago
  OP has a point [0], though I'm unsure if Kernels under load are really that unstable / untested. [0] https://brooker.co.za/blog/2021/05/24/metastable.html https://brooker.co.za/blog/2021/05/24/metastable.html / https://archive.is/6Qtet https://archive.is/6Qtet
mlyle 3y ago
Here the system is doing a pretty consistent 750k TPS instead of oscillating between 0 and 225k-- often sitting near 0TPS for tens of seconds. Which system do you think will have better latency for any given loading?
newaccount74 3y ago
This is a benchmark that tries to execute as many queries as possible, so the interesting stat is transactions per second, not CPU usage. This benchmark is testing top speed, not real world behaviour. If you tested both systems with the same workload (eg. a specific number of queries per second), then the average CPU usage would be much lower for the more efficient engine. The low CPU usage in this benchmark is just a sign that the performance is not CPU bound, but limited by other factors like locking or IO.
arghwhat 3y ago

thayne 3y ago

> You generally want to keep your CPU fully utilized Only if your load is very predictable. If there is a chance of a spike, you often want enough headroom to handle it. Even if you have some kind of automated scaling, that can take time, and you probably want a buffer until your new capacity is available.

mlyle 3y ago
I think many here is misunderstanding what was likely meant: postgresql was not able to use all the available CPU under this situation, in that it was oscillating from 10% to 70% CPU use. That 40% average cpu use isn't an asset on a dedicated database server: it just means that the other 60% of available cycles are a perishable resource that are immediately spoiling. In that sense, you want to be able to have your database be able to use all the resources available: all the IOPS, all the CPU cycles, etc. And, of course, the real thing is the amount of work you get done: this thing does more work-- partially by using more CPU cycles, and partially by doing more work per CPU cycle.