can PK UPDATE be slow ?

MySQL & MariaDB Other MySQL® Questions
1

Hello,

Can I ask some question regarding update error?

I use mysql 5.6 community version.

I have a table like below.

create table t1
(
col01 varchar(32) primary key
…
col20 varchar(32)
) innodb;

the table has about 500,000 rows.

I ran update statements 16 times in series. each update statements are different and they update about 90% of data each.
When eight update statement was executed, it died with an error - Statement cancelled due to timeout or client request
until seventh they were successful and take 2 or 3 mins each.
update statements are like

update t1 set col01 = ‘#uid’, col02=‘…’, … , col20=‘…’ where col01=‘#uid’;
commit;

they execute commit row by row.

Can anyone let me know the cause of the error?
Can the cause be the pk update?

Thanks

2

Several issues may be relevant.

  • What is the value of innodb_buffer_pool_size and how much RAM do you have?
  • What is the setting of autocommit when you run the UPDATEs?
  • Do you wrap the UPDATEs in BEGIN…COMMIT?
  • Are the values UUIDs?
  • What kind of UPDATE needs to change 90% of the rows? (Caution: This will lead to an argument that you are not using a “database” in a “proper” manner.)
  • SHOW VARIABLES LIKE ‘%timeout%’;

Changing the PK does require removing the row from one place and inserting it in another place. If you are modifying 90% of 500K rows, that is a lot. How big is the table – in MB?

3

Hello, James

I could get the hint from you.

After applying one commit per 3000 rows, the problem was solved.
It might be performance issue.

Thanks a lot.