MySQL – Understanding MVCC Implementation

innodbisolation-levelMySQL

The question is about the behavior of simultaneous SELECT and UPDATE in MySQL InnoDB table:

We have a relatively large table which we periodically scan reading several fields including a field named, say, LastUpdate. During the scan we update previously scanned rows. Updates are batched and performed in a background thread – using a different connection. It is important to note that we update rows that have already been read.

Three questions:

Will InnoDB will save previous versions of the updated rows since the SELECT is still in progress?
Would using READ-UNCOMMITTED for the SELECT help?
How can I confirm that InnoDB saves or does not save previous versions of the modified rows in its redo-log.

Best Answer

InnoDB always saves the previous version of the row. Everything is copy on write therefore every update is a select -> copy -> write. For how long it is being kept is dependent on
- the open transactions (obviously as long as you have an open transaction that needs to be able to see an old version it cannot be removed)
- how busy your server is (purge lag)
Purge behaviour had been improved a lot in the recent version. Look at dev.mysql.com for more info if you're interested.
I'm not sure what you mean by help. You read first and update next so I don't see too much benefit. You most likely don't want to go below READ-COMMITTED isolation level. That will already eliminate a lot of locking issue (gap locks and next-key locking).
InnoDB redo log contains every change that happens in the innodb tablespace to be able to recover in case of a system failure. The format is: page pointer offset and the changed bytes. So to answer the explicit use case if the previous version is written to the undo space (which it is) then InnoDB will write that change to the log files too.

I hope this helps!

If you want to look into the details more I always recommend people to play with innodb_ruby that is a very handy tool to look into innodb files and understand the basic principles of operations.

Related Solutions

MySQL performance issue – intermittently slow queries

Comments about that Tuner output:

Data in InnoDB tables: 575M (Tables: 194)
Total fragmented tables: 194
Run OPTIMIZE TABLE to defragment tables for better performance

Notice how all InnoDB tables are fragmented? That is the nature of how it is implemented. Do not bother running OPTIMIZE; it will almost never provide substantive benefit.

Joins performed without indexes: 7320

Irrelevant if the tables are tiny. The important ones will show up in the slowlog even without log_queries_not_using_indexes. (Leave that setting off; it only clutters the slowlog.)

Temporary tables created on disk: 38% (31K on disk / 84K total)
Slow queries: 1% (476/982K)

Let's see the worst ones. Please provide EXPLAIN and SHOW CREATE TABLE. I recommend long_query_time of 2 or lower.

Don't increase any of the last 5 items from Tuner.

The rest of Tuner's comments are reasonable.

Next step

Get the output from pt-query-digest and let's see the first couple, plus EXPLAIN and SHOW CREATE TABLE.

MySQL InnoDB MVCC vs Locking – Key Differences Explained

MVCC applies to isolation levels read-committed and repeatable read (default).

You don't need to specify anything for both of these features to work together. Maybe one way to think about it, is that row level locking is important so that you can update multiple rows at a time, and MVCC is so that the updates don't affect read operations at all.

Best Answer

Related Solutions

MySQL performance issue – intermittently slow queries

MySQL InnoDB MVCC vs Locking – Key Differences Explained

Related Question