Comparing UUIDv4, UUIDv7, and Sequential Integer Performance in MariaDB

Previous quick testing with BIGINT+SMALLINT and BIGINT+INT (composite keys) was worse than expected. Inserting 100 x 100 rows in table with > 350,000 rows:

Test Type	Duration	Cache Hit Ratio	Data Reads	Data Writes	Data Read (Bytes)	Data Written (Bytes)	Final Rows
repeat_insert (100,100)	N/A	N/A	N/A	N/A	N/A	N/A	N/A
test_bigint_smallint	206,869 µs (avg)	86.00%	31,024	15,920	508,297,216	188,497,920	378,125
test_bigint_int	211,382 µs (avg)	85.79%	30,094	18,547	493,060,096	268,926,976	378,125
test_sequential_int	189,051 µs (avg)	94.08%	14,679	15,783	240,500,736	177,815,552	378,125
test_uuid	189,515 µs (avg)	92.08%	23,308	11,771	381,878,272	44,761,088	378,125

The BIGINT attempt was to avoid the anticipated problems of UUID fragmentation, but UUIDv1 in above case preformed better, leading me to focus on UUIDv4 and UUIDv7.

The testing does for example 1,000 x 100, meaning inserting 100 rows a 1000 times and taking the average insert time of all batches of 100.

Average times may fluctuate by other system factors as this is not a controlled environment and I do not have experience with benchmarking. I may have missed other factors playing a role in the results.

I experimented with some MariaDB settings to optimize for UUIDv4, but lacking experience, I only got worse results, indicating to me MariaDB may be well optimized by default.

Clear is at least that UUIDv4 is increasing the disk i/o a significantly. We do see the fragmentation in the statistics. UUIDv7 on the other hand is not preforming bad at all and in all my testing, it is better than sequential int at times.

Aspects: Innodb_buffer_pool_reads, Innodb_buffer_pool_read_requests, Innodb_data_reads, Innodb_data_writes, Innodb_data_read, Innodb_data_written.

Host: Entry level VPS: 2 VCPU, 4 GB Ram. Arch Linux, MariaDB 11.7.0-preview-MariaDB, Default Configuration, Unix socket. Tested with SQL procedures.

AI summary is presented below and at end the raw output.

AI-generated Summary of Statistical Data

(ChatGPT 4o) Here is a summary of the benchmarking results for MariaDB, testing UUIDv4, UUIDv7, and Sequential Integer in terms of insert, select, and update performance. I’ll also give an overall summary of the observed trends.

Summary of Benchmark Results

Insert Performance

Inserting 100,000 rows (1,000 x 100) in a table with existing 200,000 rows.

Test	Average Duration (µs)	Cache Hit Ratio	Data Reads	Data Writes	Data Read (bytes)	Data Written (bytes)
UUIDv4	204,360	96.00%	63,349	183,302	1,037,910,016	2,586,640,384
UUIDv7	191,094	100.00%	68	106,236	1,114,112	127,041,536
Sequential Int	190,222	100.00%	41	104,198	671,744	77,545,472

Inserting 10,000 rows in a table with 300,000 rows.

Test	Average Duration (µs)	Cache Hit Ratio	Data Reads	Data Writes	Data Read (bytes)	Data Written (bytes)
UUIDv4	20,914	95.20%	76,538	196,923	1,253,998,592	3,006,939,136
UUIDv7	19,131	99.99%	83	105,094	1,359,872	96,190,464
Sequential Int	18,553	99.99%	173	104,632	2,834,432	86,114,304

Select Performance

Selecting 100,000 IDs (10,000 x 10) from a table with 300,000 rows.

Test	Cache Hit Ratio	Duration (s)	Data Reads	Data Writes	Data Read (bytes)	Data Written (bytes)
UUIDv4	73.86%	4.531	8,157	0	133,644,288	0
UUIDv7	100.00%	0.375	1	0	16,384	0
Sequential Int	80.09%	3.749	6,131	4	100,450,304	65,536

Selecting 100,000 IDs from a table with 300,000 rows.

Test	Cache Hit Ratio	Duration (s)	Data Reads	Data Writes	Data Read (bytes)	Data Written (bytes)
UUIDv4	98.14%	11.524	5,812	212	95,223,808	3,440,640
UUIDv7	100.00%	4.321	3	0	49,152	0
Sequential Int	97.65%	10.616	7,965	5	130,498,560	81,920

Update Performance

Updating 100,000 IDs on a table with 400,000 rows.

Test	Cache Hit Ratio	Duration (s)	Data Reads	Data Writes	Data Read (bytes)	Data Written (bytes)
UUIDv4	99.57%	3 min 2.416	8,198	111,184	134,316,032	323,371,008
UUIDv7	99.74%	2 min 56.344	4,620	106,888	75,694,080	203,177,984
Sequential Int	99.41%	2 min 58.412	10,611	120,686	173,850,624	434,847,744

Overall Summary

• Insert Performance: Sequential integers consistently showed the lowest duration for inserts, followed closely by UUIDv7 and then UUIDv4. UUIDv7 had an advantage in terms of fewer data reads and writes, indicating efficient storage operations with high cache hit ratios.
• Select Performance: UUIDv7 was the fastest for selecting both 10,000 and 100,000 rows, with minimal data reads, while UUIDv4 took significantly longer, with more reads and slightly lower cache hit ratios.
• Update Performance: UUIDv7 had the shortest update time, followed by sequential integers, with UUIDv4 performing slower than both. UUIDv7 also had lower data reads and writes compared to the other types, showing efficiency in update operations.

Conclusion

UUIDv7 generally provided a better balance of performance with high cache hit ratios, faster selects and updates, and efficient data usage. Sequential integers performed well in inserts but showed slightly higher resource usage in updates. UUIDv4 consistently lagged behind UUIDv7 and sequential integers in terms of performance, especially in select and update operations.

Raw data

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 11.7.0-preview-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test1]>

---

All tests are by 3, uuidv4, uuidv7 and sequential int, not always in that order but titled appropriately.

First testing insert 1000x100 into target table with 200 000 rows

Insert test, inserting 100 000 rows in table with 200 000

MariaDB [test1]> call repeat_insert(1000,100);
+--------------+
| current_rows |
+--------------+
| 200000 |
+--------------+
1 row in set (4.799 sec)

+----------------------------------------------------------+
| duration |
+----------------------------------------------------------+
| Average time taken for test_uuid_v4: 204360 microseconds |
+----------------------------------------------------------+
1 row in set (3 min 29.248 sec)

+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 96.00% |
+-------------------------------------+
1 row in set (3 min 29.256 sec)

+---------------------+------------+
| Metric | Value |
+---------------------+------------+
| Innodb_data_reads | 63349 |
| Innodb_data_writes | 183302 |
| Innodb_data_read | 1037910016 |
| Innodb_data_written | 2586640384 |
+---------------------+------------+
4 rows in set (3 min 29.257 sec)

+--------------+
| current_rows |
+--------------+
| 200000 |
+--------------+
1 row in set (3 min 29.980 sec)

+----------------------------------------------------------+
| duration |
+----------------------------------------------------------+
| Average time taken for test_uuid_v7: 191094 microseconds |
+----------------------------------------------------------+
1 row in set (6 min 41.142 sec)

+--------------------------------------+
| Cache_Hit_Ratio |
+--------------------------------------+
| Buffer Pool Cache Hit Ratio: 100.00% |
+--------------------------------------+
1 row in set (6 min 41.147 sec)

+---------------------+-----------+
| Metric | Value |
+---------------------+-----------+
| Innodb_data_reads | 68 |
| Innodb_data_writes | 106236 |
| Innodb_data_read | 1114112 |
| Innodb_data_written | 127041536 |
+---------------------+-----------+
4 rows in set (6 min 41.147 sec)

+--------------+
| current_rows |
+--------------+
| 200000 |
+--------------+
1 row in set (6 min 41.798 sec)

+-----------------------------------------------------------------+
| duration |
+-----------------------------------------------------------------+
| Average time taken for test_sequential_int: 190222 microseconds |
+-----------------------------------------------------------------+
1 row in set (9 min 52.088 sec)

+--------------------------------------+
| Cache_Hit_Ratio |
+--------------------------------------+
| Buffer Pool Cache Hit Ratio: 100.00% |
+--------------------------------------+
1 row in set (9 min 52.091 sec)

+---------------------+----------+
| Metric | Value |
+---------------------+----------+
| Innodb_data_reads | 41 |
| Innodb_data_writes | 104198 |
| Innodb_data_read | 671744 |
| Innodb_data_written | 77545472 |
+---------------------+----------+
4 rows in set (9 min 52.091 sec)

Query OK, 300036 rows affected, 1 warning (9 min 52.092 sec)

=======================================================
Same, but run now insert 10000 x 10, on table with 300 000


MariaDB [test1]> call repeat_insert(10000, 10);
+--------------+
| current_rows |
+--------------+
| 300000 |
+--------------+
1 row in set (6.274 sec)

+---------------------------------------------------------+
| duration |
+---------------------------------------------------------+
| Average time taken for test_uuid_v4: 20914 microseconds |
+---------------------------------------------------------+
1 row in set (3 min 36.067 sec)

+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 95.20% |
+-------------------------------------+
1 row in set (3 min 36.071 sec)

+---------------------+------------+
| Metric | Value |
+---------------------+------------+
| Innodb_data_reads | 76538 |
| Innodb_data_writes | 196923 |
| Innodb_data_read | 1253998592 |
| Innodb_data_written | 3006939136 |
+---------------------+------------+
4 rows in set (3 min 36.071 sec)

+--------------+
| current_rows |
+--------------+
| 300000 |
+--------------+
1 row in set (3 min 37.058 sec)

+---------------------------------------------------------+
| duration |
+---------------------------------------------------------+
| Average time taken for test_uuid_v7: 19131 microseconds |
+---------------------------------------------------------+
1 row in set (6 min 48.988 sec)

+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 99.99% |
+-------------------------------------+
1 row in set (6 min 48.998 sec)

+---------------------+----------+
| Metric | Value |
+---------------------+----------+
| Innodb_data_reads | 83 |
| Innodb_data_writes | 105094 |
| Innodb_data_read | 1359872 |
| Innodb_data_written | 96190464 |
+---------------------+----------+
4 rows in set (6 min 48.999 sec)

+--------------+
| current_rows |
+--------------+
| 300000 |
+--------------+
1 row in set (6 min 49.887 sec)

+----------------------------------------------------------------+
| duration |
+----------------------------------------------------------------+
| Average time taken for test_sequential_int: 18553 microseconds |
+----------------------------------------------------------------+
1 row in set (9 min 55.985 sec)

+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 99.99% |
+-------------------------------------+
1 row in set (9 min 55.992 sec)

+---------------------+----------+
| Metric | Value |
+---------------------+----------+
| Innodb_data_reads | 173 |
| Innodb_data_writes | 104632 |
| Innodb_data_read | 2834432 |
| Innodb_data_written | 86114304 |
+---------------------+----------+
4 rows in set (9 min 55.992 sec)

Query OK, 300036 rows affected, 1 warning (9 min 55.993 sec)

==============
==============
==============

===========
SELECTING 10 000 ids from table of 300 000 rows in its content. (First dumped random ids from each table to file, then loaded the file into table in database, then iterated over it selecting from the target test-data tables)
===========

Testing UUIDv4 ,
MariaDB [test1]> call pul4;
+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 73.86% |
+-------------------------------------+
1 row in set (4.531 sec)

+---------------------+-----------+
| Metric | Value |
+---------------------+-----------+
| Innodb_data_reads | 8157 |
| Innodb_data_writes | 0 |
| Innodb_data_read | 133644288 |
| Innodb_data_written | 0 |
+---------------------+-----------+
4 rows in set (4.532 sec)

Query OK, 10012 rows affected, 1 warning (4.532 sec)


Testing UUIDv7,
MariaDB [test1]> call pul7;
+--------------------------------------+
| Cache_Hit_Ratio |
+--------------------------------------+
| Buffer Pool Cache Hit Ratio: 100.00% |
+--------------------------------------+
1 row in set (0.375 sec)

+---------------------+-------+
| Metric | Value |
+---------------------+-------+
| Innodb_data_reads | 1 |
| Innodb_data_writes | 0 |
| Innodb_data_read | 16384 |
| Innodb_data_written | 0 |
+---------------------+-------+
4 rows in set (0.375 sec)

Query OK, 10012 rows affected, 1 warning (0.375 sec)


Testing Sequential int
MariaDB [test1]> call seqint;
+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 80.09% |
+-------------------------------------+
1 row in set (3.749 sec)

+---------------------+-----------+
| Metric | Value |
+---------------------+-----------+
| Innodb_data_reads | 6131 |
| Innodb_data_writes | 4 |
| Innodb_data_read | 100450304 |
| Innodb_data_written | 65536 |
+---------------------+-----------+
4 rows in set (3.751 sec)

===========
Now instead testing by selecting 100 000 ids from the target test-table
===========


Calling with sequential int list

MariaDB [test1]> call seqint;
+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 97.65% |
+-------------------------------------+
1 row in set (10.616 sec)

+---------------------+-----------+
| Metric | Value |
+---------------------+-----------+
| Innodb_data_reads | 7965 |
| Innodb_data_writes | 5 |
| Innodb_data_read | 130498560 |
| Innodb_data_written | 81920 |
+---------------------+-----------+
4 rows in set (10.617 sec)

Query OK, 110012 rows affected, 1 warning (10.617 sec)

Calling with uuid7 list

MariaDB [test1]> call pul7;
+--------------------------------------+
| Cache_Hit_Ratio |
+--------------------------------------+
| Buffer Pool Cache Hit Ratio: 100.00% |
+--------------------------------------+
1 row in set (4.321 sec)

+---------------------+-------+
| Metric | Value |
+---------------------+-------+
| Innodb_data_reads | 3 |
| Innodb_data_writes | 0 |
| Innodb_data_read | 49152 |
| Innodb_data_written | 0 |
+---------------------+-------+
4 rows in set (4.321 sec)

Query OK, 100012 rows affected, 1 warning (4.321 sec)

Calling with uuid4 list

MariaDB [test1]> call pul4;
+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 98.14% |
+-------------------------------------+
1 row in set (11.524 sec)

+---------------------+----------+
| Metric | Value |
+---------------------+----------+
| Innodb_data_reads | 5812 |
| Innodb_data_writes | 212 |
| Innodb_data_read | 95223808 |
| Innodb_data_written | 3440640 |
+---------------------+----------+
4 rows in set (11.525 sec)

Query OK, 100012 rows affected, 1 warning (11.525 sec)

===========================

Testing to UPDATE content. UPDATES 100 000 ids on target table with 400 000 rows in content

===================================

for uuid v 4
MariaDB [test1]> call pul4_update;

+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 99.57% |
+-------------------------------------+
1 row in set (3 min 2.416 sec)

+---------------------+-----------+
| Metric | Value |
+---------------------+-----------+
| Innodb_data_reads | 8198 |
| Innodb_data_writes | 111184 |
| Innodb_data_read | 134316032 |
| Innodb_data_written | 323371008 |
+---------------------+-----------+
4 rows in set (3 min 2.416 sec)

Query OK, 100012 rows affected, 1 warning (3 min 2.416 sec)

for uuid v 7


MariaDB [test1]> call pul7_update;
+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 99.74% |
+-------------------------------------+
1 row in set (2 min 56.344 sec)

+---------------------+-----------+
| Metric | Value |
+---------------------+-----------+
| Innodb_data_reads | 4620 |
| Innodb_data_writes | 106888 |
| Innodb_data_read | 75694080 |
| Innodb_data_written | 203177984 |
+---------------------+-----------+
4 rows in set (2 min 56.345 sec)

Query OK, 100012 rows affected, 1 warning (2 min 56.345 sec)

for sequential int

MariaDB [test1]> call seqint_update;
+-------------------------------------+
| Cache_Hit_Ratio |
+-------------------------------------+
| Buffer Pool Cache Hit Ratio: 99.41% |
+-------------------------------------+
1 row in set (2 min 58.412 sec)

+---------------------+-----------+
| Metric | Value |
+---------------------+-----------+
| Innodb_data_reads | 10611 |
| Innodb_data_writes | 120686 |
| Innodb_data_read | 173850624 |
| Innodb_data_written | 434847744 |
+---------------------+-----------+
4 rows in set (2 min 58.414 sec)

Query OK, 106661 rows affected, 1 warning (2 min 58.414 sec)