MySQL Minutiae & InnoDB Internals | Hacker News Reader

MySQL Minutiae & InnoDB Internals

engineeringblog.yelp.com

17 pointseklitzke8 comments

In addition to this blog post there is an internal presentation that Yelp has provided slides for[1] covering MySQL's transaction isolation levels, replication, InnoDB locking and so on.

[1] - http://cloud.github.com/downloads/eklitzke/mysql-minutiae/pr...

Nice slides (linked from the article).

Since I'm MySQL ignorant, you say:

"the code wasn't sure if a row would already exist in the table, so it would issue a DELETE just in case followed by an INSERT with the correct data"

I come from a batch oriented background, so whenever I read something like this, my 'use a merge statement' light goes on.

I have literally no clue about the locking behavior of such an approach in MySQL, so I'm curious if (a) it was an option at all and (b) what locking behavior MySQL would throw into play with a merge (upsert) directly.

You could use MySQL's `ON DUPLICATE KEY` syntax to work around the deadlock, which provides a syntax like `UPSERT` in some other databases. This would work because the operation would be atomic. However, at Yelp we've had replication issues with this statement serializing incorrectly into the binlog, so we generally prefer to explicitly select and then insert/update, or delete and then insert.

You could use mysql's REPLACE INTO which is basically a macro for delete + insert

less efficient than on duplicate key update because it will replace the entire row every time. But that's not any worse if you were going to do that anyway manually.

In reply to your comment, and the one below, we generally avoid REPLACE INTO and ON DUPLICATE KEY UPDATE. I'm not 100% sure about REPLACE INTO, but the ON DUPLICATE KEY UPDATE syntax definitely causes replication issues, even though it's allegedly been fixed numerous times. This has been observed by us in the 5.0.87 Percona build, as well as one of the recent 5.1 releases. I couldn't tell you for sure about REPLACE INTO, but having been bitten by these things in the past I'm always a little bit wary of these SQL extensions.

As kind of a sidenote to anyone using these features, I would recommend that you regularly run mk-table-checksum against your master/slave databases.[1] This will tell you if there's inconsistent data between the master and the slaves. Depending on the type of error, you may not get replication errors when tables get out of sync, and this tool catches the case where no replication errors occur (e.g. UPDATE that modified data on the master, but failed to modify data on the slave). Running this, or a similar tool, is really the only way to know that the queries you're running are safe, regardless of Internet hearsay or what the MySQL bugzilla says.

[1] http://www.maatkit.org/doc/mk-table-checksum.html

I think using on DUPLICATE KEY UPDATE is a much better idea than replace into. The REPLACE does an insert possibly followed by a delete. I'd steer clear of that in newer version of MySQL. I wouldn't want to have people deleting rows in my tables unless they really needed to.

We have people write scripts that populate data in our database keep a set of IDs around that they want to delete and if they have new data to insert somewhere along the way we have them update the rows they were going to delete with the new data they were going to insert. We insert and delete data only as we need to. Doing this to 3-4 tables that have high contention cut my slow query log size by half and all the queries that I don't see in that log anymore impact customers so it was a great change.

I like reading stuff like this. I deal with strange issues with MySQL all the time. It's nice to know others feel my pain and take the time to write about it. I don't usually see deadlocks anymore though thankfully.

Although we try to avoid deadlocks as much as possible at Yelp, retrying when a deadlock occurs is usually a viable strategy to take. Some deadlocks seem to be somewhat unavoidable.

Reply on news.ycombinator.com