INSERT ... ON DUPLICATE KEY UPDATE behavior in extreme situations

A few weeks ago, I was working on a client problem that faced a drop in database performance and even its failures that occurred approximately every 4 weeks. Nothing special about environment, hardware or queries. In essence, for the most part, the database had one table, in which, among other things, were present INT AUTO_INCREMENT PRIMARY KEY and UNIQUE KEY .

The queries that work with this table were almost all of the INSERT ... ON DUPLICATE KEY UPDATE (hereafter, the INSERT ODKU ), where the columns listed in the INSERT corresponded to the columns with the UNIQUE KEY . And they were executed with a frequency of approximately 1500-2000 requests per second, continuously 24 hours a day. If you are good at mathematics, then probably guessed what was the matter.

For discussion purposes, we will use the following table as an illustration of the situation:

 CREATE TABLE update_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED NOT NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE=InnoDB; 

Now imagine the following sequence of events:
')

 (root@localhost) [test]> INSERT INTO update_test (username, host_id, last_modified) VALUES ('foo',3,NOW()); Query OK, 1 row affected (0.00 sec) (root@localhost) [test]> select * from update_test; +----+----------+---------+---------------------+ | id | username | host_id | last_modified | +----+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:36:30 | +----+----------+---------+---------------------+ 

Nothing unusual, huh? We inserted one row into an empty table and if we make SHOW CREATE TABLE , we will see that the AUTO_INCREMENT counter now has the value 2 . If we make an INSERT ODKU into this table, we will see the following:

 (root@localhost) [test]> insert into update_test (username,host_id) values ('foo',1) on duplicate key update last_modified=NOW(); Query OK, 2 rows affected (0.00 sec) (root@localhost) [test]> select * from update_test; +----+----------+---------+---------------------+ | id | username | host_id | last_modified | +----+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:58:28 | +----+----------+---------+---------------------+ 1 row in set (0.00 sec) 

And now, even if we have not inserted a new row, our AUTO_INCREMENT counter has grown to 3 . This is, in fact, the expected behavior. InnoDB checks the constraints in the order in which they were defined, and PRIMARY KEY always comes first. Therefore, MySQL checks our INSERT , sees that the next AUTO_INCREMENT value is available and uses it, but then, it checks the UNIQUE KEY and finds a violation, so instead of INSERT it does UPDATE . If we look at the handler status counters, we can see that there was one insert request that failed, and one update request that was successful (this explains why the 2 rows are changed, not 1).

 (root@localhost) [test]> show status like 'handler%'; *** some rows omitted *** +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_rollback | 0 | | Handler_update | 1 | | Handler_write | 1 | +----------------------------+-------+ 

In this place you might think - “So what?”. Let's go back to our client. 1500 INSERT ODKU per second, continuously 24 hours a day. PRIMARY KEY their tables is the same as I used in the demo table - INT UNSIGNED . We consider. The maximum value for INT UNSIGNED is 4294967295 . We divide it by 1500 requests per second and divide by 86400, which is the number of seconds in a day, and we get 33.1 days, or a little more than 4 weeks. Coincidence? I do not think so. So what exactly happens when we go beyond the limits? Some behavior may surprise you. Let's go back to our demo table and insert a row with the maximum value for a column with AUTO_INCREMENT , and then insert another one.

 (root@localhost) [test]> insert into update_test (id,username,host_id) values (4294967295, 'bar', 10); Query OK, 1 row affected (0.00 sec) (root@localhost) [test]> flush status; (root@localhost) [test]> insert into update_test (username,host_id) values ('baz', 10); ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY' (root@localhost) [test]> show status like 'handler%'; *** some rows omitted *** +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_rollback | 1 | | Handler_write | 1 | +----------------------------+-------+ 

So, we tried to insert a series and it did not work, because AUTO_INCREMENT already had the maximum value and the request did not pass. But, what happens if we try to do an INSERT ODKU ? First, let's see what we have in the table:

 (root@localhost) [test]> select * from update_test; +------------+----------+---------+---------------------+ | id | username | host_id | last_modified | +------------+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:58:28 | | 4294967295 | bar | 10 | NULL | +------------+----------+---------+---------------------+ 2 rows in set (0.00 sec) (root@localhost) [test]> INSERT INTO update_test (username, host_id) VALUES ('foo', 7) ON DUPLICATE KEY UPDATE host_id=7, last_modified=NOW(); Query OK, 2 rows affected (0.00 sec) 

Looks ok right? 2 rows changed, it is obvious that for the row that met the condition username = "foo" , host_id and last_modified were updated, and we can rejoice. Unfortunately, this is not the case:

 (root@localhost) [test]> select * from update_test; +------------+----------+---------+---------------------+ | id | username | host_id | last_modified | +------------+----------+---------+---------------------+ | 1 | foo | 3 | 2012-10-05 22:58:28 | | 4294967295 | bar | 7 | 2012-10-05 23:24:49 | +------------+----------+---------+---------------------+ 2 rows in set (0.00 sec) 

Oops, the last row was updated, whose id is equal to the maximum value of our AUTO_INCREMENT , and the UNIQUE KEY on the username column was ignored.

Now we can easily understand the problem of the client, whose database served as inspiration for this post. 1500 requests per second, trying to block and update the same series, will not lead to anything good. Of course, there is a simple solution - change the AUTO_INCREMENT data type of the AUTO_INCREMENT c INT to BIGINT .

It turns out that this behavior is documented . The manual says that our INSERT ODKU on a table with several unique indexes will be equivalent to the UPDATE update_test SET host_id = 7, last_modified = NOW() WHERE id = 4294967295 OR username = "foo" LIMIT 1 request UPDATE update_test SET host_id = 7, last_modified = NOW() WHERE id = 4294967295 OR username = "foo" LIMIT 1 and of course the optimizer will choose PRIMARY rather than secondary UNIQUE .

What have we learned here?

• Exhausting AUTO_INCREMENT much easier than it seems. The real customer table contained less than 500k rows.
• Using SIGNED types for AUTO_INCREMENT is almost always a bad idea. You lose half the range of available values.
• Intuition, like the laws of physics, often fails in extreme situations.