I am looking to implement a table which will accessable via a Percona XtraDB cluster of 3+ nodes. I am expecting it to grow at about 1mil rows per month and the data will be kept for about 2 years (24mil rows).
The schema is reletively simple, but for brevity I will ommit the boring parts, using a PK of a binary(16) with an ordered UUID value. Details of this can be seen here (https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/).
On a single machine this would/should always result in a nice time-based insertion order (due to the ordered UUID v1) and this will keep the data in its intended order within the structure. However, if I start using multiple nodes in a PXC then the UUID being generated could be out of order time-wise on different hosts. Even keeping clock drift to an absolute minimum, with round trips across multiple datacenters, I could not be 100% sure that a timestamp would always be after the last one.
My conclusion would be to make a BIGINT field, with AUTO_INCREMENT enabled, the first part in a compound PK - but then, as far as the DB is concerned, I may as well do away with the UUID and just rely on the BIGINT.
Does anyone have any thoughts on this; am I missing something? It seems that the use of a ordered UUID v1 in a cluster can actually make things worse...