3 ms·

> This strikes me as odd. In my experience, traditional OLTP row stores are I/O bound due to contention (locking and latching). Does anyone have an explanation

> This strikes me as odd. In my experience, traditional OLTP row stores are I/O bound due to contention (locking and latching). Does anyone have an explanation for this? Yes. My CTO, Avi Kivity did a great talk about this at Core C++ 2019: https://www.scylladb.com/2020/03/26/avi-kivity-at-core-c-2019/ https://www.scylladb.com/2020/03/26/avi-kivity-at-core-c-201... Let me boil it down to a few points; some beyond Avi's talk: • Traditional RDBMS with strong consistency and ACID guarantees are always going to exhibit delays. That's what you want them for. Slow, but solid. • Even many NoSQL databases written (supposedly) for High Availability still use highly synchronous mechanisms internally. • You need to think about a multi-processor, multi-core server as its own network internally. You need to consider rewriting everything with the fundamental consideration of async processing, even within the same node. Scylla uses C++ futures/promises, shared-nothing shard-per-core architecture, as well as new async methods like io_uring. • Between nodes, you also have to consider highly async mechanisms. For example, the tunable eventual consistency model you'd find in Cassandra or Scylla. While we also support Paxos for LWT, if you need strong linearizability, read-before-write conditional updates, that comes at a cost. Many classes of transactions will treat that as overkill. • And yes, backups are also a huge issue for those sorts of data volumes. Scylla, for example, has implemented different priority classes for certain types of activities. It handles all the scheduling between OLTP transactions as highest priority, while allowing the system to plug away at, say, backups or repairs. More on where we're going with all this is written in a blog about our new Project Circe: https://www.scylladb.com/2021/01/12/making-scylla-a-monstrous-database-introducing-project-circe/ https://www.scylladb.com/2021/01/12/making-scylla-a-monstrou... But the main point is that you have to really think about how to re-architect your software to take advantage of huge multi-processor machines. If you invest in all this hardware, but your software is limiting your utility of it, you're not getting the full bang you spent your buck on.

sradman 6y ago
> But the main point is that you have to really think about how to re-architect your software to take advantage of huge multi-processor machines. I appreciate the response but it doesn't address my question: given that Let's Encrypt's MySQL-family RDBMS does not implement any of the multi-core/multi-socket/cpu-affinity/lock-free/asyncIO techniques used by databases like ScyllaDB, MemSQL, and VoltDB, why were they seeing 90% CPU utilization on their old Intel servers while the upgraded AMD servers were 25% (the expected range)? I think mike_d's suggestion is most plausible: they probably included custom functions/procedures that invoke CPU-expensive code. I also thought this was a single-node scale-up architecture but since they are using a three-or-more node master/slave architecture, network I/O could somehow be involved.
- nijave 6y ago
  On "cloud" servers we usually see that when disk I/O stops being the bottleneck (given enough disk I/O you can account for small amounts of RAM). So I'd guess their disk setup had some capacity left when they upgraded or they just have a lot of reads and the data fits in memory
- PeterCorless 6y ago
  I kind of did answer a different question, but you sort of affirmed my answer & proved my point. If the "expected range" of your CPU utilization is only 25%, then you are knowingly paying for 3x more CPU than you are actually every planning to use. I suppose if you got that cash money and are willing to burn it, nothing I can say will stop you. I just question the logic of it. As for why they suddenly dropped? I'll leave that to someone who knows this particular system far better than I do.
  - sradman 6y ago
    I think Jeffbee resolved my confusion; InnoDB uses spin locks that chew up cpu cycles when the request rate exceeds the I/O rate. This is not as bad as it sounds; it uses extra power and generates extra heat but it is not doing any extra real work. InnoDB cores run at either 25% or 90% but the 90% indicates that you need an I/O upgrade not more CPU cycles. Optimizing for CPU efficiency in a system that is I/O bound will not save significant money. Memory and NVMe are the critical factors and with the master/slave replication used, network I/O significantly undercuts the peak performance this single server is capable of.

sradman 6y ago

> But the main point is that you have to really think about how to re-architect your software to take advantage of huge multi-processor machines. I appreciate the response but it doesn't address my question: given that Let's Encrypt's MySQL-family RDBMS does not implement any of the multi-core/multi-socket/cpu-affinity/lock-free/asyncIO techniques used by databases like ScyllaDB, MemSQL, and VoltDB, why were they seeing 90% CPU utilization on their old Intel servers while the upgraded AMD servers were 25% (the expected range)? I think mike_d's suggestion is most plausible: they probably included custom functions/procedures that invoke CPU-expensive code. I also thought this was a single-node scale-up architecture but since they are using a three-or-more node master/slave architecture, network I/O could somehow be involved.

nijave 6y ago
On "cloud" servers we usually see that when disk I/O stops being the bottleneck (given enough disk I/O you can account for small amounts of RAM). So I'd guess their disk setup had some capacity left when they upgraded or they just have a lot of reads and the data fits in memory
PeterCorless 6y ago
I kind of did answer a different question, but you sort of affirmed my answer & proved my point. If the "expected range" of your CPU utilization is only 25%, then you are knowingly paying for 3x more CPU than you are actually every planning to use. I suppose if you got that cash money and are willing to burn it, nothing I can say will stop you. I just question the logic of it. As for why they suddenly dropped? I'll leave that to someone who knows this particular system far better than I do.
- sradman 6y ago
  I think Jeffbee resolved my confusion; InnoDB uses spin locks that chew up cpu cycles when the request rate exceeds the I/O rate. This is not as bad as it sounds; it uses extra power and generates extra heat but it is not doing any extra real work. InnoDB cores run at either 25% or 90% but the 90% indicates that you need an I/O upgrade not more CPU cycles. Optimizing for CPU efficiency in a system that is I/O bound will not save significant money. Memory and NVMe are the critical factors and with the master/slave replication used, network I/O significantly undercuts the peak performance this single server is capable of.