Prepare MySQL for big UPDATE query on production server Surprisingly I have not found anything relevant on this subject.

What are best practices to prepare production MySQL server for a big update query? The question is broad but I'd like to know details for a typical setup:


MySQL 5.6
80/20 read/write workflow ratio
Only InnoDB
Replication Master
Query Cache enabled
Data fits entirely into memory buffer pool with some spare room left
Healthy HDD drives on hardware RAID-1
Millions of rows in the update query
The query only covers single table (1 primary key - INT)


And the questions:


What to look for?
What mysql configuration parameters will be most relevant for the process to go quickly and smoothly?
Is it worth splitting the update into multiple batches?
The Query cache is a hindrance.  (In general, it should be OFF for production systems.)  All entries in the QC for that table will be purged by the Update.

Millions of rows -- all of the table?  A small fraction of a billion row table, but no good index?  What?

Let's dissect the query...

Scan through the entire table.  This takes time for the following reasons:


Read the blocks
Write the "undo" records to disk (in case of ROLLBACK or crash)
Write the blocks back to disk
Toss the undo blocks


The size of "undo" may necessitate a less efficient method, adding to the overall time.

The affected rows will be locked; well you may as well plan on the 'entire' table being blocked from other usage.

Batching...


Avoids blocking as long
Probably takes longer overall
Complexity in the code
Not atomic (if that matters)


Why do you need to do such a big Update?  Perhaps the column you are changing should be in a separate, parallel, table to avoid this much impact?  Or 'computed' by subsequent SELECTs?

Config changes?  Assuming the innodb_buffer_pool_is big enough, nothing useful to do.

For chunking, see advice on chunking -- that talks about deleting, but can be adapted to updating.