Not the answer you need?
Register and ask your own question!

can PK UPDATE be slow ?

sungju hongsungju hong EntrantCurrent User Role Beginner
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

Comments

  • Rick JamesRick James Contributor Current User Role Novice
    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?
  • sungju hongsungju hong Entrant Current User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.