# can PK UPDATE be slow ?

**URL:** <https://forums.percona.com/t/can-pk-update-be-slow/4038>\
**Category:** Other MySQL® Questions\
**Created:** [February 3, 2015, 5:00am UTC](https://forums.percona.com/t/can-pk-update-be-slow/4038 "2015-02-03T05:00:29Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sungju\_hong](https://avatars.discourse-cdn.com/v4/letter/s/57b2e6/32.png) [@sungju\_hong](https://forums.percona.com/u/sungju_hong)\
**Post date:** [February 3, 2015, 5:00am UTC](https://forums.percona.com/t/can-pk-update-be-slow/4038/1 "2015-02-03T05:00:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Rick\_James](https://avatars.discourse-cdn.com/v4/letter/r/22d042/32.png) [@Rick\_James](https://forums.percona.com/u/Rick_James)\
**Post date:** [February 4, 2015, 10:22pm UTC](https://forums.percona.com/t/can-pk-update-be-slow/4038/2 "2015-02-04T22:22:51Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![sungju\_hong](https://avatars.discourse-cdn.com/v4/letter/s/57b2e6/32.png) [@sungju\_hong](https://forums.percona.com/u/sungju_hong)\
**Post date:** [February 24, 2015, 7:10pm UTC](https://forums.percona.com/t/can-pk-update-be-slow/4038/3 "2015-02-24T19:10:00Z")

</div>

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.