0

We want to test the performance of a sequence/bigint vs UUID(VERSION-4) VS UUID(version-7) on mysql database for primary key/index. Being new to mysql and struggling to find an easy way to create data volume easily and test the performance and also 'explain analyze' seems not working here too. Basically want to do something similar to what below code does in postgres.Can you please guide me here?

(UUID Version-4 is the default random UUID version and Version-7 is the timestamp sorted UUID which is more sequential then random so better with regards to caching). Wanted some asy way to generate these in mysql DB itself and test these out.

Below code works fine in postgres and I want to mimic the code similar in mysql to test the performance of UUID vs sequence for generation and INSERT and Querying.

CREATE UNLOGGED TABLE bigint ( id bigint PRIMARY KEY);

CREATE UNLOGGED TABLE uuid7 ( id uuid PRIMARY KEY);

create sequence myseq cache 32767;

1 Answer 1

0

(Too many comments for Comments.)

"Caching" -- Be sure test with at least twice as much data as will fit in the innodb_buffer_pool_size. Otherwise, most or all of the data will be cached, and you won't see much difference.

Since "100000" rows may not fill up the default buffer_pool_size, you may want to lower that setting for your testing. (Be sure to include that in the test plan.)

Are the "generate" and "nextval" functions Stored Functions? UDFs? Client code? Please include that code.

LIMIT without ORDER BY is not realistic -- you get some subset of the ids. They will usually, but not necessarily, have consecutive ids.

IN ( SELECT ... ) is a notoriously bad performer. Formulate the test differently so that it is not biased by that problem.

BIGINT -- Almost no application needs more than will fit in a 4-byte INT (versus 8 bytes for BIGINT).

EXPLAIN ANALYZE won't necessarily give you clues about performance. If estimates, sometimes poorly, various things. You must actually time the queries to get useful results.

"timestamp sorted UUID" -- This is relevant only if the access pattern is based on time. An example: A News site will usually be probed for "recent" articles. Be sure to simulate both usage patterns.

SELECT * -- Need details. There are at least 3 unrelated issues that could impact the benchmarking. () Is * so small that the INDEX is "covering"? () Does * include any TEXT or BLOB columns, thereby leading to "off-record" fetches that will be a significant part of the time taken. (*) Other.

UNLOGGED is not a MySQL term.

Use AUTO_INCREMENT, not SEQUENCE. (And several syntax changes.)

For comparing -4 and -7, you need (1) more rows in the table than will fit in the buffer_pool and (2) an access pattern that favors an index that is clustered by time.

MySQL has very little of the functionality you found in elsewhere. About the only thing is BENCHMARK(expression, count), which is probably not useful for your task. Stored Routines can do some of what you need, but they cannot accept or return "arrays". MariaDB has pseudo tables such as seq_1_to_100000.

9
  • Thank you so much @RickJames. I am kind of new to mysql so was thinking there must be some quick way(using simple sql queries) to create large data volume like it exists in postgres or oracle. Thanks for clarifying on this. As you mentioned there exists some way like stored subroutine which can do the same job. Can you please guide me to some sample subroutines or documents which I can follow to create large volume test data for executing performance test scenario.
    – sud
    Feb 15, 2023 at 5:17
  • @sud - There are simple techniques with UNION and/or self-JOIN and/or looping in Stored Procedures that can quickly build up huge tables. But they are not as straightforward ad what others have. I also o mentioned MariaDB's sequence table.
    – Rick James
    Feb 15, 2023 at 5:34
  • I suggest you start a different question focusing on how to use a MySQL Stored Procedure to build a table with, say, 100M rows. (I have a table with a million integers that I created a long time ago; I sometimes use it for building large tables. A simple self-JOIN could build a trillion row table; I won't try that - it could take "forever".)
    – Rick James
    Feb 15, 2023 at 5:38
  • Thank you for quick response. I will start a new question for this.
    – sud
    Feb 15, 2023 at 8:45
  • Thank you @RickJames. Finally, I am able to create the performance test scenario as mentioned in below dbfiddle. Also I did set the "innodb_buffer_pool_size" very small (i.e. ~10bytes) so that the insert/select will go for disk read for the UUID scenario. But I am seeing the UUID and Random UUID performance is better as compared to the sequence/bigint for the INSERT query. Why so? Or am I missing anything here? dbfiddle.uk/WBWHDawW
    – sud
    Feb 15, 2023 at 19:27

Your Answer

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

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