1

im upgrading our mariadb database from PRIMARY KEY INT AUTO_INCREMENT to UUID PRIMARY KEY. We have around 80 tables with multiple relations between them. I've build a migration script to change the id field to BINARY(16) and created for every row a Uuid v4. Then i've updated the relations with the new uuid (also as BINARY(16)). When i run multiple queries to do a performance benchmark, i have an average performance decrease from 15% on my local docker environment. The id field is a PRIMARY KEY. The relation fields are marked as index and have Foreign Key constraints.

What can i do to improve the performance?

Most of the tables has round about 200k entries. The whole database has a size of 3.2GB

Improve this question
3
  • Why are you doing this change? UUIDv4 is random so you won't get any locality in your indexes which you do get with steadily incrementing numbers.
    – Colin 't Hart
    Feb 19, 2021 at 10:14
  • I need it for replication and analyses
    – Tanktiger
    Feb 19, 2021 at 10:22
  • 1
    What can i do to improve the performance? INT occupies 4 bytes, BINARY(16) - 16 bytes. The comparing process for 4-byte values is, of course, faster than for 16-byte values. This cannot be improved. Additionally you increase the storage size and hence the amount of disk operations (and decrease the percent of cached data). This cannot be improved too.
    – Akina
    Feb 19, 2021 at 11:15

1 Answer 1

Reset to default
1

UUIDs are not nice when it comes to performance. In particular, there is no relationship between the 'next' row you fetch via a UUID and all the other rows that are currently cached.

Docker possibly has a "small" value for innodb_buffer_pool_size, thereby leading to I/O when random accesses into the table happen.

UUID v1 can have its bits shuffled to make them "chronological", would remove most of the "randomness" of accesses. But you are using v4.

Plan A: Abandon UUIDs.

Plan B: Use UUID v1 and shuffle the bits

Plan C: Give docker enough room to have a big buffer_pool so that most of the indexes can be kept in RAM.

More: http://mysql.rjweb.org/doc.php/uuid

Improve this answer
3
  • Plan D: If UUIDs are absolutely necessary, keep the auto-incremental primary keys but just add a new column that is UNIQUE. You will get most of the advantages but potentially less of a performance impact (depending on what you are doing with them).
    – jynus
    Feb 20, 2021 at 22:07
  • 1
    @jynus - Yes, but as you note, "it depends". That secondary index will be accessed randomly; however, it will be "smaller" than if the UUID is the PK.
    – Rick James
    Feb 21, 2021 at 1:20
  • Yes, I just wanted to complete your answer with a "compromise" option. I've had a similar issue to OP issue and uuids had terrible performance for me, but I put myself in the skin of someone that may not have an option of not adding them.
    – jynus
    Feb 21, 2021 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.