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

Observe many copy threads blocking there when use pt-online-schema-change

lujinkelujinke EntrantInactive User Role Participant
The other day, i use pt-online-schema-change to add a column and index to a table,the table is about 1 Minion rows and the .ibd file size is about 5G Bytes.
I tried several times each time i got an error says the process is aborted duo to the critical load is reached(which is threads_running=60, i always use this and never failed before)

I found something strange using show full process list: there are so many copy threads there like:
REPLACE INTO `db`.`_some_table_new` ...
running for a long time(tens and hundreds of seconds) and in a status of 'update'.

As i know the pt-online-schema-change uses only one copy threads and i use this tool many times and each time i can only observe one thread chunking and copying but this time everything changed, there were so many copy threads blocking there.

I searched the documents in percona.com and can not find any information about the option about multi-threaded copying.

The table i altered this time does be accessed very frequently, but i think this will not cause a problem as long as there is a single copying thread. Could anyone give me some help,thanks!

Comments

  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    pt-online-schema-change is a Perl program with no multi-thread capabilities.
    The row copy process use INSERT IGNORE ... not REPLACE.

    The reason why you see many threads running UPDATES could be because the original table is receiving too many INSERT/DELETE/UPDATE and the triggers created for pt-online-schema-change to work, are trying to update those rows into the new table (these queries are par of the triggers created by osc and they are REPLACE).

    Regards
  • lujinkelujinke Entrant Inactive User Role Participant
    Yes, you are right! There are caused by triggers. I can not use pt-online-schema-change to finish the alter, finally i use the build-in online DDL function to finish this(no running thread boost).Thank you!
  • lujinkelujinke Entrant Inactive User Role Participant
    I check the process lists on that time, i found a lot of lock waits there, one REPLACE INTO wait for another on AUTO_INC table lock,may be pt-online-schema-change is not suitable for table having a very heavy access on it.
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.