4 ms·

A couple of observations: It seems that MySQL seems somehow connected to all the outages, especially the unexpected crashes. >GitHub’s monitoring systems curr

A couple of observations: It seems that MySQL seems somehow connected to all the outages, especially the unexpected crashes. >GitHub’s monitoring systems currently alert when tables hit 70% of the primary key size used. We are now extending our test frameworks to include a linter in place for int / bigint foreign key mismatches. In 2020, should we have Integer(32-bit) primary keys anymore? I think at this time, everyone should just go with BigInt or UUID for primary keys/foreign keys, and basically not have running out of key space be an issue you have to worry about.

minxomat 6y ago
I always use UUID PRIMARY KEY DEFAULT uuid_generate_v1mc() In Postgres. It will give you UUIDs and the larger keyspace, without being excessively random (they're basically almost sequential). I do this always, even if the table has other int columns that look like friendly values that could be used as PKs. Some time in the future they will ruin your day.
- fulafel 6y ago
  For others curious about this uuid type: "This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications." I guess that's not immune to problems either (i can imagine problems with both mac address and time).
  - viraptor 6y ago
    This is not the right function. V1mc randomises the MAC. Also it's only one of the possibilities. There are many ways if generating uuids depending on your situation.
    - deleted 6y ago
      [deleted]
- abhishekjha 6y ago
  Isn't UUID discouraged for a PRIMARY KEY pertaining indexes? What would the B+Tree look like for over 2 billions UUIDS where there is no order within? Also, the cache locality problem.
  - tasogare 6y ago
    This is why GP wrote "It will give you UUIDs and the larger keyspace, without being excessively random (they're basically almost sequential)". The main point for not screwing up the clustered index is the "almost sequential" part.
  - dtech 6y ago
    That is for random (v4) UUIDs. v1 UUIDs are mostly sequential in time on a single machine so don't have this problem, altough they do have problems that others indicated already.
- hvidgaard 6y ago
  If you want to use UUID for primary keys, the argument should not be the larger key space. A 64 bit int is enough for a single server. If a 64 bit int PK is exhausted, the data used to store the keys alone would require 128 exabytes, or in perspective, a SAN with 6.4 million 20tb hdds. Then you need several times that to store any kind of meaningful data If you need to use UUIDs for other reasons such as a high degree of unique keys between servers that do not coordinate, then go for it, but not for the key size.
  - majewsky 6y ago
    > 128 exabytes, or in perspective, a SAN with 6.4 million 20tb hdds Nitpick: It's actually 7.38 million disks since "20 TB" for a disk means terabyte, not tebibyte.
    - hvidgaard 6y ago
      True - not that it changes the soundness of the argument.
- dx034 6y ago
  UUIDs can have severe performance implications. Even without massive randomness, a change in the MAC address used or non sequential numbers can slow down writes massively. As others have written, a simple 64bit integer should be enough for pretty much any case. Even Google won't exhaust those for their user database.
  - viraptor 6y ago
    v1mc does not depend on the MAC address.
grep_it 6y ago
I could be wrong, but I thought UUIDs could cause some performance troubles for larger table when there is an index on it? edit: This explains it a bit > When you insert a new row with a random primary key value, InnoDB has to find the page where the row belongs, load it in the buffer pool if it is not already there, insert the row and then, eventually, flush the page back to disk. With purely random values and large tables, all b-tree leaf pages are susceptible to receive the new row, there are no hot pages. Rows inserted out of the primary key order cause page splits causing a low filling factor. For tables much larger than the buffer pool, an insert will very likely need to read a table page from disk. The page in the buffer pool where the new row has been inserted will then be dirty. The odds the page will receive a second row before it needs to be flushed to disk are very low. Most of the time, every insert will cause two IOPs – one read and one write. The first major impact is on the rate of IOPs and it is a major limiting factor for scalability. [1] 1: https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/ https://www.percona.com/blog/2019/11/22/uuids-are-popular-bu...
- remus 6y ago
  OP mentioned they are using one of the sequential UUID variants so this is not an issue.
  - dx034 6y ago
    I believe they still use the MAC address? If the master node changes, the number could suddenly start at a different block, causing massive performance impacts. If they're purely sequential you can as well use a 64 bit int.
    - lmm 6y ago
      > If the master node changes, the number could suddenly start at a different block, causing massive performance impacts. What massive performance impact? You'd have one extra IOP, total, as it loaded the page for the new mac address, and then it would carry on as normal. It would be no different from when a sequential ID crosses a page boundary.

minxomat 6y ago

I always use UUID PRIMARY KEY DEFAULT uuid_generate_v1mc() In Postgres. It will give you UUIDs and the larger keyspace, without being excessively random (they're basically almost sequential). I do this always, even if the table has other int columns that look like friendly values that could be used as PKs. Some time in the future they will ruin your day.

fulafel 6y ago
For others curious about this uuid type: "This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications." I guess that's not immune to problems either (i can imagine problems with both mac address and time).
- viraptor 6y ago
  This is not the right function. V1mc randomises the MAC. Also it's only one of the possibilities. There are many ways if generating uuids depending on your situation.
  - deleted 6y ago
    [deleted]
abhishekjha 6y ago
Isn't UUID discouraged for a PRIMARY KEY pertaining indexes? What would the B+Tree look like for over 2 billions UUIDS where there is no order within? Also, the cache locality problem.
- tasogare 6y ago
  This is why GP wrote "It will give you UUIDs and the larger keyspace, without being excessively random (they're basically almost sequential)". The main point for not screwing up the clustered index is the "almost sequential" part.
- dtech 6y ago
  That is for random (v4) UUIDs. v1 UUIDs are mostly sequential in time on a single machine so don't have this problem, altough they do have problems that others indicated already.
hvidgaard 6y ago
If you want to use UUID for primary keys, the argument should not be the larger key space. A 64 bit int is enough for a single server. If a 64 bit int PK is exhausted, the data used to store the keys alone would require 128 exabytes, or in perspective, a SAN with 6.4 million 20tb hdds. Then you need several times that to store any kind of meaningful data If you need to use UUIDs for other reasons such as a high degree of unique keys between servers that do not coordinate, then go for it, but not for the key size.
- majewsky 6y ago
  > 128 exabytes, or in perspective, a SAN with 6.4 million 20tb hdds Nitpick: It's actually 7.38 million disks since "20 TB" for a disk means terabyte, not tebibyte.
  - hvidgaard 6y ago
    True - not that it changes the soundness of the argument.
dx034 6y ago
UUIDs can have severe performance implications. Even without massive randomness, a change in the MAC address used or non sequential numbers can slow down writes massively. As others have written, a simple 64bit integer should be enough for pretty much any case. Even Google won't exhaust those for their user database.
- viraptor 6y ago
  v1mc does not depend on the MAC address.

grep_it 6y ago

I could be wrong, but I thought UUIDs could cause some performance troubles for larger table when there is an index on it? edit: This explains it a bit > When you insert a new row with a random primary key value, InnoDB has to find the page where the row belongs, load it in the buffer pool if it is not already there, insert the row and then, eventually, flush the page back to disk. With purely random values and large tables, all b-tree leaf pages are susceptible to receive the new row, there are no hot pages. Rows inserted out of the primary key order cause page splits causing a low filling factor. For tables much larger than the buffer pool, an insert will very likely need to read a table page from disk. The page in the buffer pool where the new row has been inserted will then be dirty. The odds the page will receive a second row before it needs to be flushed to disk are very low. Most of the time, every insert will cause two IOPs – one read and one write. The first major impact is on the rate of IOPs and it is a major limiting factor for scalability. [1] 1: https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/ https://www.percona.com/blog/2019/11/22/uuids-are-popular-bu...

remus 6y ago
OP mentioned they are using one of the sequential UUID variants so this is not an issue.
- dx034 6y ago
  I believe they still use the MAC address? If the master node changes, the number could suddenly start at a different block, causing massive performance impacts. If they're purely sequential you can as well use a 64 bit int.
  - lmm 6y ago
    > If the master node changes, the number could suddenly start at a different block, causing massive performance impacts. What massive performance impact? You'd have one extra IOP, total, as it loaded the page for the new mac address, and then it would carry on as normal. It would be no different from when a sequential ID crosses a page boundary.