Fragmentation with PXC setup using an ordered UUID as PK

Question

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

Answer 1

Good. You understand the problem inherit in UUIDs, and understand how to "fix" it.

Let's start from the other end... Since you are keeping the data for 2 years, let's start with partitioning by date. I don't care whether the data is INT, TIMESTAMP, DATETIME, BIGINT, or a rearranged V1 UUID. (However, you should probably use an date that matches your application.)

PARTITION by the date into 24 partitions. Each month, DROP the oldest partition and REORGANIZE the future partition into future and nextmonth. See my blog for more details.

OK, that solves the problem you have not yet encountered -- the ugly overhead of deleting a million rows a month.

But, your concern was about ordering of the clock. It is not really a problem. And it is not worth chasing. Even if an INSERT is a second late -- or even an hour late -- it will make very little difference in the overall performance of the inserts.

Sure, the 'next' row onto the 'end' of a table is well optimized. But inserting it somewhere in the 'last' block, or even last 100 blocks, is efficient enough. At best, InnoDB keeps blocks not quite full. At steadystate with a lot of churn, a BTree is 69% full. For "Point queries" this number is mostly irrelevant. For range scans, it has some impact. I suggest that preventing fragmentation is more costly than ignoring it.

Even with just AUTO_INCREMENT, you cannot be assured that rows will be inserted visible in order! Think about what happens with multiple threads doing:

BEGIN;
INSERT ... -- the next AUTO_INCREMENT value is grabbed now
...        -- take a variable amount of time
COMMIT;    -- only now is the AI visible to the rest of the world.

So, give up on strict ordering.