3 ms·

At scale you’re probably sharded across multiple DBs and you’re already operating through replicas. Point being you’re less likely to hit a warm cache as you sc

At scale you’re probably sharded across multiple DBs and you’re already operating through replicas. Point being you’re less likely to hit a warm cache as you scale up anyway as your application layer gets load balanced to different DB endpoints.

jongjong 3y ago
Yes exactly, with proper sharding, raw performance is not as important; to some extent, you trade it away for improved concurrency. In fact, I struggle to see how one would implement sharding with auto-incrementing integers (you would get ID collisions for different resources across different shards/database instances); there needs to be a way to uniquely refer to resources across potentially multiple databases and UUIDs are one of the best ways to achieve that. Auto-incrementing IDs simply don't scale beyond a single host so I don't see how they can be good for scalability. Too many devs conflate raw performance with scalability. They are not the same at all - In fact, high scalability often incurs a performance overhead.
- coredog64 3y ago
  We did auto-increment integers with multiple servers like 20 years ago: The caveat is that you have to know how many servers are in the set in advance. Each server increments by the population size, and their starting number is their position within the pool. Not hyper scale, but good enough for failover or a 3-5 node setup.
  - catlifeonmars 3y ago
    Oh this is clever! I guess it’s actually very similar conceptually to a vector clock. (In that you have partitioned the space of natural numbers into K countably infinite sequences). If you wanted to (practically speaking anyway) overcome the requirement that K is known, you could borrow prefix based counting from the p-adics.
  - viraptor 3y ago
    Or increment by more than you need. If you think you'll only ever have 5 nodes, increment by 20. Lots of space for expansion and in practice you'll hit the datatype limits just a bit earlier - you'd need to work around them anyways.
  - egeozcan 3y ago
    Hi/Lo algorithm also works fine for most cases
  - ilyt 3y ago
    IIRC that's what MySQL Galera is doing by default mysql> CREATE TABLE animals ( -> id MEDIUMINT NOT NULL AUTO_INCREMENT, -> name CHAR(30) NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.34 sec) mysql> INSERT INTO animals (name) VALUES -> ('dog'),('cat'),('penguin'), -> ('lax'),('whale'),('ostrich'); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM animals; +----+---------+ | id | name | +----+---------+ | 3 | dog | | 6 | cat | | 9 | penguin | | 12 | lax | | 15 | whale | | 18 | ostrich | +----+---------+ 6 rows in set (0.00 sec)
- pgaddict 3y ago
  I respectfully disagree with the notion that sharding makes resource usage somehow less important. Sure, it allows you to overcome the limits that would apply to a single node, but if you stop caring about using resources efficiently (e.g. memory / disk / network bandwidth), it's not very different from building huge and expensive single boxes. Also, I don't think the blog (or me) suggested going back to using autoincrement IDs. There are other (better) options.
magicalhippo 3y ago
Reminds me of a talk about ZFS performance, where they presented some benchmark results showing that as the number of concurrent clints doing pure sequential IO increases, the more the load appears as random IO to the filesystem. So with high enough concurrent load it's effectively all random IO and that's the primary thing worth optimizing for.

jongjong 3y ago

Yes exactly, with proper sharding, raw performance is not as important; to some extent, you trade it away for improved concurrency. In fact, I struggle to see how one would implement sharding with auto-incrementing integers (you would get ID collisions for different resources across different shards/database instances); there needs to be a way to uniquely refer to resources across potentially multiple databases and UUIDs are one of the best ways to achieve that. Auto-incrementing IDs simply don't scale beyond a single host so I don't see how they can be good for scalability. Too many devs conflate raw performance with scalability. They are not the same at all - In fact, high scalability often incurs a performance overhead.

coredog64 3y ago
We did auto-increment integers with multiple servers like 20 years ago: The caveat is that you have to know how many servers are in the set in advance. Each server increments by the population size, and their starting number is their position within the pool. Not hyper scale, but good enough for failover or a 3-5 node setup.
- catlifeonmars 3y ago
  Oh this is clever! I guess it’s actually very similar conceptually to a vector clock. (In that you have partitioned the space of natural numbers into K countably infinite sequences). If you wanted to (practically speaking anyway) overcome the requirement that K is known, you could borrow prefix based counting from the p-adics.
- viraptor 3y ago
  Or increment by more than you need. If you think you'll only ever have 5 nodes, increment by 20. Lots of space for expansion and in practice you'll hit the datatype limits just a bit earlier - you'd need to work around them anyways.
- egeozcan 3y ago
  Hi/Lo algorithm also works fine for most cases
- ilyt 3y ago
  IIRC that's what MySQL Galera is doing by default mysql> CREATE TABLE animals ( -> id MEDIUMINT NOT NULL AUTO_INCREMENT, -> name CHAR(30) NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.34 sec) mysql> INSERT INTO animals (name) VALUES -> ('dog'),('cat'),('penguin'), -> ('lax'),('whale'),('ostrich'); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM animals; +----+---------+ | id | name | +----+---------+ | 3 | dog | | 6 | cat | | 9 | penguin | | 12 | lax | | 15 | whale | | 18 | ostrich | +----+---------+ 6 rows in set (0.00 sec)
pgaddict 3y ago
I respectfully disagree with the notion that sharding makes resource usage somehow less important. Sure, it allows you to overcome the limits that would apply to a single node, but if you stop caring about using resources efficiently (e.g. memory / disk / network bandwidth), it's not very different from building huge and expensive single boxes. Also, I don't think the blog (or me) suggested going back to using autoincrement IDs. There are other (better) options.

magicalhippo 3y ago

Reminds me of a talk about ZFS performance, where they presented some benchmark results showing that as the number of concurrent clints doing pure sequential IO increases, the more the load appears as random IO to the filesystem. So with high enough concurrent load it's effectively all random IO and that's the primary thing worth optimizing for.