1

I need to UPDATE all rows for multiple columns on a large INNODB table. Each UPDATE processes one column separately and takes about 1 hour. I have to do this for 10 columns so it would take 10 h I do not want to wait. As I have a 16 core CPU I would like to UPDATE concurrently.

Currently, as I do UPDATE all rows for each column the whole table is locked and I cannot start a further UPDATE on the same table on another column. Is there a (unsafe) way to do it in parallel? Can I disable locking of the table / rows?

Maybe something equivilent to READ UNCOMMITTED...

I am using MySQL 8.0 with INNODB and I it is a single user system so I do not have to worry about uncommited changes.

Improve this question

2 Answers 2

Reset to default
0

Is there a (unsafe) way to do it in parallel?

If there is some mark that a particular record was processed by a particular update (or you can insert a field into table structure for this purposes) you may use parallel chunk updates like

SELECT 1
FROM table
WHERE mark = 'not processed'
ORDER BY primary_key
LIMIT chunk_count
FOR UPDATE;

UPDATE table
SET field = function(parameters)
WHERE mark = 'not processed'
ORDER BY primary_key
LIMIT chunk_count;

Moreover, you can start a lot of parallel updates for the same field.

Improve this answer
8
  • Yes, of course. That is a good hint! Thank you. Do chunk updates affect performance?
    – Olaf_SQL
    Oct 2, 2018 at 18:46
  • Do chunk updates affect performance? Yes. Not always as increase, but in most cases... The problem is to determine optimal chunk size (typical value range - 1k..100k, it is dependent by the amount of parallel updates both the same and different types, and can be determined only in practice).
    – Akina
    Oct 2, 2018 at 18:58
  • This will be even slower -- It is O(N^2) because it has to skip over the processed rows to find the next chunk's worth.
    – Rick James
    Oct 14, 2018 at 4:59
  • @RickJames ??? I'm describing a scheme that really exists and really works. Successfully works. The only difference is a type of a mark field - in a system I tell about it's type is BIGINT, and 'not processed' mark is a value less then 64k.
    – Akina
    Oct 14, 2018 at 18:00
  • @Akina - Sure it "works", I am questioning the efficiency. (Or I am not understanding the code.) If you are marking some rows, then needing to skip past them on the next SELECT, it becomes O(N^2).
    – Rick James
    Oct 14, 2018 at 18:32
0

It will probably work to have different threads working on different chunks of the table. But be sure to use a BETWEEN-like range for a chunk, not LIMIT, especially not with OFFSET.

I would not make the chunks bigger than 1K rows. When they get too big, you get into issues of slowing down the undo log.

How to chunk? Walk through the PRIMARY KEY if it is a simple AUTO_INCREMENT without too many gaps. If it is more complex (big gaps, non-integer PK, composite PK), see my advice here.

That still leaves the question of how to split the chunks across CPUs. I worry that if you have consecutive chunks there will be some kind of lock at the boundaries that cause conflicts between chunks. So, I might do the following.

  1. Calculate PK values for 16 equal-sized chunks for the table.
  2. Launch 16 processes, each given the limits for one of the chunks.
  3. Within each process, UPDATE in chunks of 1K at a time.

Note: The link above shows how to efficiently locate boundaries by using SELECT id FROM t WHERE id > $left_off ORDER BY id LIMIT 999,1

Then do UPDATE ... WHERE id >= $a AND id < $z; (I may have the inequalities wrong; see the link.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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