8 ms·
This article talks about random IDs leading to page thrashing, and MySQL b-tree indexes not handling them well. They are bad for _MySQL performance_. It doesn'
by web007 5y ago
This article talks about random IDs leading to page thrashing, and MySQL b-tree indexes not handling them well. They are bad for _MySQL performance_.
It doesn't talk about NoSQL or sharding, where random IDs usually perform much better than sequential due to a lack of hot shards.
If you distribute your reads and writes at random across N machines you can get ~Nx performance vs one machine. If you make your writes sequential, you'll usually get ~1x performance because every insert goes to the same machine for this second/minute/day, then rolls to a new one for the next period. There are sharding schemes that can counter this, but they require insight into your data design before implementation.
- tehlike 5y agoEven in MySQL - one can use Sequential UUIDs.
- inetknght 5y agoI never understood why people would use sequential UUIDs. That rather defeats the purpose of a UUID. If you need something sequential then just use a much more simple number
- rco8786 5y agoBecause simple integers are not universally unique, a major feature of UUIDs…
- inetknght 5y agoIs this sarcasm? If you generate an incrementing-UUID then its predictability is going to make it not-universally unique too
- rco8786 5y agoThat is…not what universally unique means.
- rwoerz 5y agoFrom RFC4122: "A UUID is an identifier that is unique across both space and time, with respect to the space of all UUIDs." Hard to achieve if everyone starts from 00000000-0000-0000-0000-000000000000.
- rco8786 5y agoRight that’s why nobody starts at 0. And you don’t have to only add 1 to be ordered, no DB I’m aware of just adds 1 to sequential UUIDs. There’s always randomness/entropy involved.
- ztorkelson 5y agoSequential UUIDs don’t start at 0. They are a 128-bit composite of two integers; a temporal component in the high order bits and a random component in the low order bits.
- michaelmcmillan 5y agoNo. Incrementing an integer might be unique for the local database, but not unique for the universe.
- atq2119 5y agoUUIDs are integers, they just happen to be 128 bits long instead of the more common 32 or 64, and they are usually printed in a specific way that differs from how integers are usually printed. But that's just smoke and mirrors. UUIDs are generally not guaranteed to be universally unique. The name is misleading marketing. If you generate them randomly, then the probability of collision is small enough not to matter, but the more you introduce deterministic elements, like generating them sequentially, the more your probability of collision increases.
- anamexis 5y agoUUIDs are just 128-bit simple integers.
- tehlike 5y agohttps://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ... Each GUID generated by using NEWSEQUENTIALID is unique on that computer. GUIDs generated by using NEWSEQUENTIALID are unique across multiple computers only if the source computer has a network card.
- dspillett 5y agoIt depends on how the non-sequential part is derived. IIRC this is often a mix of hardware network address and a portion that is either random or based on a high-precision time, so it is still unlikely that you'll see collisions between machines. MS SQL Server's NEWSEQUENTIALID function returns something akin to a v4 UUID (fully random, aside from variant indicator bits, I'm not sure if the variant bits are respected in NEWSEQUENTIALIDs output or if it just returns a 128-bit number) but after the first is generated the rest follow in sequence until the sequence is reset (by a reboot). Assuming the variant indicators are present, there is are 122 random bits. Even if your system is up long enough to use 2^58 (2.8810^17 if you want that in decimal) IDs generated this way, you still effectively have 64-bits of randomness even if the variant bits are present. For most systems the chance of collision with sequential UUIDs is, while larger than with other types, still so small as to be inconsequential. These are "atoms in several galaxies" level numbers. You don't want to use sequential UUIDs in place of v4 UUIDs where security matters, or course, as it is easy to work the next in sequence. > If you need something sequential then just use a much more simple number* Sequential isn't their only property. UUIDs, including those that increment from the start point, are intended not to collide with those generated elsewhere. Sequential UUIDs are a compromise - giving away a small amount of collision protection in order to gain what could be a significant efficiency boost in some circumstances (DB indexes being the main one).
- nightpool 5y agoSure, but each individual machine still has to do the same slow random lookups, right? Generally you want some deterministic component (for caching) and some random component (for sharding). Snowflakes work well for this, since you can use the upper bits for predictable caching and the lower bits for random entropy.
- hifriends 5y agoits a MySQL blog, why would it talk about NoSQL?
- m0rphling 5y agoI don't know if you've actually been to their blog recently, but Percona's community includes NoSQL as they maintain their own distro of MongoDB--similar in spirit to their MySQL and PostgreSQL offerings. https://www.percona.com/blog/category/mongodb https://www.percona.com/blog/category/mongodb https://www.percona.com/software/mongodb https://www.percona.com/software/mongodb