5 ms·

> We can clearly see how our old CPUs were reaching their limit. In the week before we upgraded our primary database server, its CPU usage (from /proc/stat) ave

> We can clearly see how our old CPUs were reaching their limit. In the week before we upgraded our primary database server, its CPU usage (from /proc/stat) averaged over 90% 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? > Once you have a server full of NVMe drives, you have to decide how to manage them. Our previous generation of database servers used hardware RAID in a RAID-10 configuration, but there is no effective hardware RAID for NVMe, so we needed another solution... we got several recommendations for OpenZFS and decided to give it a shot. Again, traditional OLTP row stores have included a mechanism for recovering from media failure: place the WAL log on separate device from the DB. Early MySQL used a proprietary backup add-on as a revenue model so maybe this technique is now obfuscated and/or missing. You may still need/want a mechanism to federate the DB devices and incremental volume snapshots are far superior to full DB backup but placing the WAL log on a separate device is a fantastic technique for both performance and availability. The Let's Encrypt post does not describe how they implement off-machine and off-site backup-and-recovery. I'd like to know if and how they do this.

snuxoll 6y ago
MySQL backup is more or less a “solved” issue with xtrabackup, and I assume that’s exactly what they are using with a database of this size.
e12e 6y ago
> The Let's Encrypt post does not describe how they implement off-machine and off-site backup-and-recovery. I'd like to know if and how they do this. The section: > There wasn’t a lot of information out there about how best to set up and optimize OpenZFS for a pool of NVMe drives and a database workload, so we want to share what we learned. You can find detailed information about our setup in this GitHub repository. points to: https://github.com/letsencrypt/openzfs-nvme-databases https://github.com/letsencrypt/openzfs-nvme-databases Which states: > Our primary database server rapidly replicates to two others, including two locations, and is backed up daily. The most business- and compliance-critical data is also logged separately, outside of our database stack. As long as we can maintain durability for long enough to evacuate the primary (write) role to a healthier database server, that is enough. Which sounds like traditional master/slave setup, with fail over?
- sradman 6y ago
  > Which sounds like traditional master/slave setup, with fail over? Yes, thank you. I assumed that the emphasis on the speed of NVMe drives meant that master/slave synchronous replication was avoided and asynchronous replication could not keep up. In my mind, this leaves room for interesting future efficiency/performance gains, especially surrounding the "...and is backed up daily" approach mentioned in your quote. The bottom line is that the old RPO (Recovery Point Objective) and RTO (Recovery Time Objective) are as important as ever.
mike_d 6y ago
> traditional OLTP row stores are I/O bound due to contention (locking and latching). Does anyone have an explanation for this? I have seen CPU bound database servers when developers push application logic in to the database. Everything from using server-side functions like MD5() to needless triggers and stored procedures that could have been done application side.
- cdcarter 6y ago
  Indeed, at $PLACE_OF_WORK over the last 15 years a lot of logic was built in PL/SQL and dbcpu has become one of our most precious resources. For some applications its perfectly reasonable until you need to horizontally scale.
- jeffbee 6y ago
  Any MySQL with more than about 100 concurrent queries of the same InnoDB table is going to be CPU bound on locks. Their whole locking scheme doesn't scale; it's designed to look great in benchmarks with few clients.
  - mike_d 6y ago
    It sounds like you just hit the threshold where defaults don't cut it anymore. With >100 concurrent clients you need to tune your DB for your workload. innodb_thread_concurrency and innodb_concurrency_tickets would be a good starting point, and optimal values depend on your r/w balance and number of rows touched per type of query.
    - jeffbee 6y ago
      I'm saying the innodb buffer pool mutex doesn't scale over 100 contenders and you are saying that I can tune mysql so there's never more than that, which it seems to me like we're in agreement.
      - sradman 6y ago
        I did a quick search and it looks like InnoDB implements a spin-lock. Do you see increased CPU utilization when the buffer pool is overloaded? This could explain the behavior described in the article.
PeterCorless 6y ago
> 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.

snuxoll 6y ago

MySQL backup is more or less a “solved” issue with xtrabackup, and I assume that’s exactly what they are using with a database of this size.

e12e 6y ago

> The Let's Encrypt post does not describe how they implement off-machine and off-site backup-and-recovery. I'd like to know if and how they do this. The section: > There wasn’t a lot of information out there about how best to set up and optimize OpenZFS for a pool of NVMe drives and a database workload, so we want to share what we learned. You can find detailed information about our setup in this GitHub repository. points to: https://github.com/letsencrypt/openzfs-nvme-databases https://github.com/letsencrypt/openzfs-nvme-databases Which states: > Our primary database server rapidly replicates to two others, including two locations, and is backed up daily. The most business- and compliance-critical data is also logged separately, outside of our database stack. As long as we can maintain durability for long enough to evacuate the primary (write) role to a healthier database server, that is enough. Which sounds like traditional master/slave setup, with fail over?

sradman 6y ago
> Which sounds like traditional master/slave setup, with fail over? Yes, thank you. I assumed that the emphasis on the speed of NVMe drives meant that master/slave synchronous replication was avoided and asynchronous replication could not keep up. In my mind, this leaves room for interesting future efficiency/performance gains, especially surrounding the "...and is backed up daily" approach mentioned in your quote. The bottom line is that the old RPO (Recovery Point Objective) and RTO (Recovery Time Objective) are as important as ever.

mike_d 6y ago

> traditional OLTP row stores are I/O bound due to contention (locking and latching). Does anyone have an explanation for this? I have seen CPU bound database servers when developers push application logic in to the database. Everything from using server-side functions like MD5() to needless triggers and stored procedures that could have been done application side.

cdcarter 6y ago
Indeed, at $PLACE_OF_WORK over the last 15 years a lot of logic was built in PL/SQL and dbcpu has become one of our most precious resources. For some applications its perfectly reasonable until you need to horizontally scale.
jeffbee 6y ago
Any MySQL with more than about 100 concurrent queries of the same InnoDB table is going to be CPU bound on locks. Their whole locking scheme doesn't scale; it's designed to look great in benchmarks with few clients.
- mike_d 6y ago
  It sounds like you just hit the threshold where defaults don't cut it anymore. With >100 concurrent clients you need to tune your DB for your workload. innodb_thread_concurrency and innodb_concurrency_tickets would be a good starting point, and optimal values depend on your r/w balance and number of rows touched per type of query.
  - jeffbee 6y ago
    I'm saying the innodb buffer pool mutex doesn't scale over 100 contenders and you are saying that I can tune mysql so there's never more than that, which it seems to me like we're in agreement.
    - sradman 6y ago
      I did a quick search and it looks like InnoDB implements a spin-lock. Do you see increased CPU utilization when the buffer pool is overloaded? This could explain the behavior described in the article.

PeterCorless 6y ago

> 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.