0

For 3 days I have the following query running:

alter table pois modify column id bigint(12) not null;

with the purpose to change the type from int(11) to bigint(12) and remove AUTO_INCREMENT on a table with 600.000.000 rows (40gb).

According the processlist the progress is now at: 31,4 with the status "copy to tmp table".

Is there any way to give more resources/speed up this process temporarily? Or is there a way to execute this query without backing up the table?

6
  • 1
    Sometimes it’s faster to just create a new table and insert stuff there, especially if it already needs to copy data to a temporary table. But there’s no real rule that I know of Commented Jul 18, 2022 at 9:46
  • ALTERing the PRIMARY KEY necessarily requires a copy. Removing AUTO_INCREMENT seems unnecessary; what was your rationale?
    – Rick James
    Commented Jul 18, 2022 at 17:03
  • @RickJames i wanted to remove auto_increament because i decided to use own unique values for id based on a formula, this requires altering the table with bigint. But its true i could keep auto_increament. So should i stop the query, try to remove the column (id, primary key) and readd with bigint ? Would that be faster ? Commented Jul 18, 2022 at 20:37
  • Depends on the version of MySQL.
    – Rick James
    Commented Jul 18, 2022 at 21:24
  • @RickJames 10.1.48-MariaDB-0ubuntu0.18.04.1 ; libmysql - mysqlnd 7.4.29. The query got stopped anyway as my provider moved my vps to another host to increase performance. Commented Jul 18, 2022 at 21:27

1 Answer 1

0
pt-online-schema-change --alter "MODIFY COLUMN id BIGINT(12) NOT NULL" D=your_database,t=your_table

Percona Toolkit provides a tool called "pt-online-schema-change" that can be used to make non-blocking schema changes

ALTER TABLE your_table MODIFY COLUMN id BIGINT(12) NOT NULL, ALGORITHM=INPLACE;

MySQL's ALTER TABLE statement allows you to specify the ALGORITHM option

Your Answer

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

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