GUID vs Auto Increment Insert performance

I'm testing the performance of inserts between Auto increment integers vs GUIDs (v4) in tables with a lot of data. Following this blog post, i was expecting seeing a difference. But right now with more than 6 millions rows, i don't see any difference. These are the definitions of my tables:

Auto Increment

CREATE TABLE `auto` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`))

GUID

CREATE TABLE `guid` (
`id` CHAR(32) NOT NULL,
PRIMARY KEY (`id`))

The code is the same as the referenced blog post, insert 100K rows in one transaction several times. As I said before with more than 6 millions rows, there is no difference in performance, it is practically the same. I am trying to figure out why. The script is in a C# y and the GUID is being generating in the application (not in MySql). To be more explicit what i'm doing is a for loop from 1 to 1M, send inserts to the DB, and each time i reach 100K rows commit the transaction and measure the elapsed time on committing.

My hardware: Mac mini i5 2.3 GHz, 16GB Ram, 960GB SSD, but the script is running on a Fusion virtual machine with 4GB of Ram with Windows 10 x64, MySql server it is installed on the virtual machine.

MySql version: 5.7

So, Am I missing something?. Do I need more data?.

Thanks in advance.

What you are missing is the setting of innodb_buffer_pool_size (assuming you are using InnoDB), and how it compares to the size of the index on id. The "more data" needed:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW TABLE STATUS LIKE 'guid';

-
When id is AUTO_INCREMENT or some kind of "increasing" timestamp, all the work will be done at the "last" block of the index. Hence, very little needs to be cached to avoid I/O.

-
When id is a uuid/guid/md5/etc, the cache space used (buffer_pool) grows as the table grows. This is because the id is jumping around "randomly". You won't notice much performance hit until the buffer_pool is no longer big enough. Then things gradually hit the fan.

Let's carry things even farther... Let's say the buffer_pool that can hold 1M entires, but there are 20M entries. When the next row is to be inserted, it has a 1M/20M chance that the desired block is currently cached in the buffer_pool. That is, only 5%. Or, a 95% chance of a "miss" -- almost always requiring a disk hit. I/O is the major cause of sluggish SQL.

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

Bottom line: If you have a huge table, guids will kill performance, whether it is the PRIMARY KEY or some other index.

StackExchange Database Administrators Q#171251, answer score: 3