0

Am running below query

Query

Alter table dbname.tablename 
add column column1 varchar(50),
add column column2 text , 
ALGORITHM=INPLACE, LOCK=NONE;

It takes more time to execute.

Ideally running this without ALGORITHM=INPLACE, LOCK=NONE takes 30mins to execute.

Query am running for table size=24.4G and records-count=97lkahs.

Any suggestions on why does it take more time to execute?

Is there any way that i could achieve better on the same?

Version= 5.6.40 , Engine = Innodb , Buffer pool size = 44.8 GB

  • mysql version? Innodb? innodb buffer pool size? innodb log file size? – danblack Mar 20 at 8:28
  • Version: 5.6.40-log , Engine : innodb, Buffer pool size: 44.856445312500 Am running on AWS RDS – dbarocks Mar 20 at 8:31
  • 1) Does you perform the operation using CLI console client? 2) Does two separate altering queries takes more time while compare with altering using default algorithm too? – Akina Mar 20 at 8:54
  • I run the queries using mysql terminal. Individually running the query without algorithm and lock=none would complete in 30mins for above mentioned table size. But the same when run with above mentioned query is taking more time. Infact its still running there is no query displayed in show processlist; I wonder if there is anything i missed out. FYI i am running this on AWS RDS – dbarocks Mar 20 at 9:29
  • Individually running the query without algorithm and lock=none would complete in 30mins for above mentioned table size. But what if separate queries WITH ALGORITHM=INPLACE, LOCK=NONE? more than 30 min. totally too? And how much time is spent on each separate request? – Akina Mar 20 at 9:38
0

Found out the issue. Ran out of storage space on RDS while running Alter table query.

For some reason it did not throw error disk space issue.

But when i tried without ALGORITHM=INPLACE like the query below, it throws out error of disk space issue.

Alter table dbname.tablename add column column1 varchar(50), add column column2 text , LOCK=NONE;

I increased 100G to see if that suffices the problem, it worked.

But i still did not understand the fact why does the alter query take 60mins to add two columns when i run the Alter statement above.

While Alter statement to add single column takes 30mins to complete.

How can we improvise the execution time for Alter statement, any suggestions would help?

  • It is wise to keep half your disk free. Or at least as much free space as the largest table. – Rick James Mar 22 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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