Test preformance of database

▼▲

Only a very simple test, not a real benchmark. Just ran a few tests. Would have to run like 1000s of tests for each to get a good average.

Surprise the UUID keys preform well...

From ChatGPT

Here’s a summary of why the UUID primary key performs well in MariaDB:

Optimized Storage Structure: In MariaDB 10.7 and later, UUIDs are stored in an "index-friendly" format where certain components are rearranged to improve indexing efficiency. This reduces index fragmentation and makes UUIDs more suitable for B-tree indexing than a purely random structure would be.
Efficient Insertion Handling: Although UUIDs are random, MariaDB’s internal ordering of UUID components introduces a degree of sequential structure, reducing the typical overhead associated with inserting random values into an indexed structure.
Single Column Simplicity: A single UUID column, though slightly larger (16 bytes), simplifies index management compared to composite keys. MariaDB only needs to track one column, potentially reducing complexity and overhead in handling the index.
Moderate Key Size: While UUIDs take up more storage than integers, the difference in size isn’t substantial enough to impact performance significantly, especially at the scale of your test.

In short, MariaDB's optimizations make UUIDs almost as efficient as integer-based primary keys, combining universal uniqueness with solid insertion performance.

Performance Summary and Analysis of Primary Key Configurations

This report summarizes the performance benchmarks for four different table configurations with various primary key structures: BIGINT + SMALLINT, BIGINT + INT, AUTO_INCREMENT INT, and UUID. Each table was tested across three operations—INSERT, SELECT, and UPDATE—to observe any performance differences.

1. INSERT Operation

The INSERT operation was measured by running the popd procedure, which inserts 10,000 rows into each table. Here’s a summary of the average time taken for each configuration:

Table Configuration	Average Time (microseconds)	Average Time (seconds)
`BIGINT + SMALLINT`	10,110,649	~10.11 sec
`BIGINT + INT`	10,075,510	~10.08 sec
`AUTO_INCREMENT INT`	9,975,756	~9.98 sec
`UUID`	10,040,532	~10.04 sec

Observations:

The AUTO_INCREMENT INT configuration was the fastest for INSERT operations, which aligns with expectations since it minimizes index fragmentation.
The UUID configuration performed similarly to the composite keys, thanks to MariaDB’s optimized UUID storage, which orders UUIDs in an index-friendly manner.
Both BIGINT + SMALLINT and BIGINT + INT configurations performed nearly identically, with only slight variations.

2. SELECT Operation

The SELECT operation was benchmarked using the popd_select procedure, which retrieves all rows from each table without printing them. Here’s a summary of the time taken:

Table Configuration	Time (microseconds)	Time (milliseconds)
`BIGINT + SMALLINT`	5,055	~5.06 ms
`BIGINT + INT`	5,040	~5.04 ms
`AUTO_INCREMENT INT`	4,470	~4.47 ms
`UUID`	4,318	~4.32 ms

Observations:

The UUID configuration was slightly faster for SELECT operations, followed closely by AUTO_INCREMENT INT.
The composite key configurations (BIGINT + SMALLINT and BIGINT + INT) were marginally slower but still performed well.
This result suggests that MariaDB’s UUID ordering optimization does not negatively impact read operations.

3. UPDATE Operation

The UPDATE operation was benchmarked using the popd_update procedure, which updates all rows in each table by appending " updated" to the data_column. Here’s a summary of the results:

Table Configuration	Average Time (microseconds)	Average Time (milliseconds)
`BIGINT + SMALLINT`	111,196	~111.20 ms
`BIGINT + INT`	110,097	~110.10 ms
`AUTO_INCREMENT INT`	88,911	~88.91 ms
`UUID`	94,816	~94.82 ms

Observations:

The AUTO_INCREMENT INT configuration performed the fastest for UPDATE operations, likely due to its straightforward indexing structure.
The UUID configuration performed comparably, likely benefiting from efficient index organization.
Composite keys (BIGINT + SMALLINT and BIGINT + INT) took slightly longer, possibly due to additional overhead in managing multi-column indexes.

Summary of Findings

Best Overall Performance: The AUTO_INCREMENT INT configuration consistently performed well across all operations, making it ideal for workloads with heavy inserts and updates, especially where strict ordering is needed.
UUID Configuration: The UUID primary key performed surprisingly well across all tests, benefiting from MariaDB’s optimized storage and ordering for UUIDs. This configuration is highly suitable for distributed systems where unique identifiers are necessary.
Composite Key Performance: Both BIGINT + SMALLINT and BIGINT + INT composite key configurations had similar performance across all operations, showing only slight performance differences. They are still good options when a composite key is needed for data partitioning or organization, though they introduce marginal overhead compared to single-column keys.

Conclusion

Each primary key configuration has its strengths:

AUTO_INCREMENT INT: Best for simple, high-performance insertions, updates, and reads in scenarios without distributed key requirements.
UUID: Excellent choice for distributed or globally unique key requirements, with performance that nearly matches integer-based keys.
BIGINT + SMALLINT and BIGINT + INT: Viable for cases requiring composite keys, with only slightly higher overhead compared to single-column keys.

This analysis indicates that MariaDB’s optimizations allow UUIDs to perform efficiently, making them a practical option in many scenarios where unique identifiers are essential.

MariaDB [test1]> call popd;
+------------------------------------------------------------+
| duration                                                   |
+------------------------------------------------------------+
| Time taken for test_bigint_smallint: 10159660 microseconds |
+------------------------------------------------------------+
1 row in set (10.161 sec)

+-------------------------------------------------------+
| duration                                              |
+-------------------------------------------------------+
| Time taken for test_bigint_int: 10092702 microseconds |
+-------------------------------------------------------+
1 row in set (20.254 sec)

+----------------------------------------------------------+
| duration                                                 |
+----------------------------------------------------------+
| Time taken for test_sequential_int: 9967741 microseconds |
+----------------------------------------------------------+
1 row in set (30.222 sec)

+-------------------------------------------------+
| duration                                        |
+-------------------------------------------------+
| Time taken for test_uuid: 10076600 microseconds |
+-------------------------------------------------+
1 row in set (40.299 sec)

Query OK, 40000 rows affected (40.299 sec)

MariaDB [test1]> call popd;
+------------------------------------------------------------+
| duration                                                   |
+------------------------------------------------------------+
| Time taken for test_bigint_smallint: 10029638 microseconds |
+------------------------------------------------------------+
1 row in set (10.030 sec)

+-------------------------------------------------------+
| duration                                              |
+-------------------------------------------------------+
| Time taken for test_bigint_int: 10058319 microseconds |
+-------------------------------------------------------+
1 row in set (20.088 sec)

+----------------------------------------------------------+
| duration                                                 |
+----------------------------------------------------------+
| Time taken for test_sequential_int: 9984771 microseconds |
+----------------------------------------------------------+
1 row in set (30.073 sec)

+-------------------------------------------------+
| duration                                        |
+-------------------------------------------------+
| Time taken for test_uuid: 10001464 microseconds |
+-------------------------------------------------+
1 row in set (40.075 sec)

Query OK, 40000 rows affected (40.075 sec)

----------------------------------------


MariaDB [test1]> call popd_select;
+----------+
| COUNT(*) |
+----------+
|    20000 |
+----------+
1 row in set (0.006 sec)

+------------------------------------------------------------------+
| duration                                                         |
+------------------------------------------------------------------+
| Time taken for SELECT on test_bigint_smallint: 5055 microseconds |
+------------------------------------------------------------------+
1 row in set (0.006 sec)

+----------+
| COUNT(*) |
+----------+
|    20000 |
+----------+
1 row in set (0.011 sec)

+-------------------------------------------------------------+
| duration                                                    |
+-------------------------------------------------------------+
| Time taken for SELECT on test_bigint_int: 5040 microseconds |
+-------------------------------------------------------------+
1 row in set (0.011 sec)

+----------+
| COUNT(*) |
+----------+
|    20000 |
+----------+
1 row in set (0.016 sec)

+-----------------------------------------------------------------+
| duration                                                        |
+-----------------------------------------------------------------+
| Time taken for SELECT on test_sequential_int: 4470 microseconds |
+-----------------------------------------------------------------+
1 row in set (0.016 sec)

+----------+
| COUNT(*) |
+----------+
|    20000 |
+----------+
1 row in set (0.020 sec)

+-------------------------------------------------------+
| duration                                              |
+-------------------------------------------------------+
| Time taken for SELECT on test_uuid: 4318 microseconds |
+-------------------------------------------------------+
1 row in set (0.020 sec)

Query OK, 0 rows affected (0.020 sec)

----------------------------------------

MariaDB [test1]> CALL popd_update();
+--------------------------------------------------------------------+
| duration                                                           |
+--------------------------------------------------------------------+
| Time taken for UPDATE on test_bigint_smallint: 116209 microseconds |
+--------------------------------------------------------------------+
1 row in set (0.117 sec)

+--------------------------------------------------------------+
| duration                                                     |
+--------------------------------------------------------------+
| Time taken for UPDATE on test_bigint_int: 95608 microseconds |
+--------------------------------------------------------------+
1 row in set (0.213 sec)

+-------------------------------------------------------------------+
| duration                                                          |
+-------------------------------------------------------------------+
| Time taken for UPDATE on test_sequential_int: 108012 microseconds |
+-------------------------------------------------------------------+
1 row in set (0.321 sec)

+---------------------------------------------------------+
| duration                                                |
+---------------------------------------------------------+
| Time taken for UPDATE on test_uuid: 119579 microseconds |
+---------------------------------------------------------+
1 row in set (0.441 sec)

Query OK, 80000 rows affected (0.441 sec)


MariaDB [test1]> CALL popd_update();
+--------------------------------------------------------------------+
| duration                                                           |
+--------------------------------------------------------------------+
| Time taken for UPDATE on test_bigint_smallint: 106182 microseconds |
+--------------------------------------------------------------------+
1 row in set (0.107 sec)

+---------------------------------------------------------------+
| duration                                                      |
+---------------------------------------------------------------+
| Time taken for UPDATE on test_bigint_int: 125586 microseconds |
+---------------------------------------------------------------+
1 row in set (0.232 sec)

+------------------------------------------------------------------+
| duration                                                         |
+------------------------------------------------------------------+
| Time taken for UPDATE on test_sequential_int: 69809 microseconds |
+------------------------------------------------------------------+
1 row in set (0.302 sec)

+--------------------------------------------------------+
| duration                                               |
+--------------------------------------------------------+
| Time taken for UPDATE on test_uuid: 69054 microseconds |
+--------------------------------------------------------+
1 row in set (0.371 sec)