2

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.

  • 1
    Unless you're hitting a hard limit (IO / CPU / etc), you probably won't see a difference. You need to properly stress-test. Modern CPUs and SSDs are FAST. – Philᵀᴹ Apr 18 '17 at 8:47
  • My experience also same with MySQL 5.7. Difference is storage of data and Index and also Index scan speed. Most of the blogs are written based on versions prior to MySQL 5.7. What is the version of MySQL you are using? – SQL.RK Apr 18 '17 at 14:10
  • @SQL.RK I'm using 5.7, i updated the question. – RenkoSmith Apr 18 '17 at 14:45
  • 1
    I don't know of anything in 5.7 that would impact this question. SSDs are fast enough to somewhat hide the sluggishness of guids at scale. – Rick James Apr 18 '17 at 15:40
2

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.

  • The innodb_buffer_pool_size is 5 Megabytes. So, if I understand well is small enough, and the average row length is 84 bytes. – RenkoSmith Apr 18 '17 at 17:19
  • What is Data_length? Compare that to 5MB. If it is 8MB, then 37% of the time, a uuid insert will hit the disk. Either crank up the number of rows, or artificially decrease the buffer_pool. – Rick James Apr 18 '17 at 19:15
  • For a 4GB VM running mostly MySQL, you can crank up the buffer pool to, say, 1500M. But this particular test would continue to show that uuids are as fast as auto-incs -- until you get to a huge number of rows. – Rick James Apr 18 '17 at 19:17
  • Data_length is 570 408 960 (544 MB) – RenkoSmith Apr 18 '17 at 19:52
  • Then I expect 99% of the inserts to need to read a block from disk in order to complete the guid insert. – Rick James Apr 18 '17 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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