MySQL (Percona 5.7) Slow ALTER table with InnoDB engine ...?

0
votes

I have a table that is in InnoDB engine, quite a simple one with 25000 rows. When I do a simple ALTER, it runs for almost 10 minutes:

mysql> ALTER TABLE `quote_followups_istvan` 
       ADD `customer_ip2` VARCHAR(20) NOT NULL DEFAULT '' AFTER `comment`;

Query OK, 0 rows affected (10 min 52.82 sec) Records: 0 Duplicates: 0 Warnings: 0

But when I change it's engine to MyISAM, I get this:

mysql> alter table quote_followups_istvan engine="MyISAM";

Query OK, 25053 rows affected (0.56 sec) Records: 25053 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE `quote_followups_istvan` 
       ADD `customer_ip3` VARCHAR(20) NOT NULL DEFAULT '' AFTER `comment`;

Query OK, 25053 rows affected (0.37 sec) Records: 25053 Duplicates: 0 Warnings: 0

So 10minutes vs 0.37s....

What amd I missing here?

1
mysqlperformanceinnodbalterpercona
Edit: In fact that speciffic ALTER query took a while (obviously), and I'm not sure why. For example, altering the engine was fast, so it's not the ALTER itself that is the issue. – Istvan Prosinger
Eating an orange takes 90 seconds. Bying an apple takes 30 seconds. Eating an apple takes 12 seconds. So the next time I want to eat an orange, I will just buy an apple and eat it instead, because it is faster. Back to your problem: MyISAM works differently than InnoDB. Some things are faster. If you want to end up with the same result (an InnoDB table with an additional column), you will have to switch your table back to InnoDB and include that in your time. Although the actual reason probably is that something locked your table, since a table with 25k rows should be faster to alter. – Solarflare
The table was not locked, it's a test table, not used by any process. Other than that I don't really understand why would I convert back to InnoDB and count that time in. My goal is actually to make a query that would work fast with InnoDb. I have only converted the table to MyISAM because I was experimenting with it, no plans to keep it with that engine. – Istvan Prosinger
Well, it was just a guess, since 10 minutes seems a bit much (so try to repeat it, it might also have been e.g. caching). I would expect it to be more in the range of 1:10 than 1:200. But still, you are comparing apples and oranges. InnoDB and MyISAM work completely (!!) differently. InnoDB will e.g. allow you to update data while altering the table, MySQL doesn't. Not important if it takes 0.37s, but in the real world, it's takes longer. So yes, some operations on different engines are faster than others. Not really sure why you are wondering about that. – Solarflare
I'm quite aware that InnoDB and MyISAM don't work the same, in fact, I have several reasons to keep the table with InnoDB engine. Converting it to MyISAM was just an experiment as I didn't know what to do anymore. The idea was not at all to convert it into MyISAM engine, do the altering and covert it back.... Still, this is a very large difference in execution time (yes, I've tried several times, so caching isn't making a signifficant difference), I was rather thinking (hoping) that someone would point out that I'm missing something quite obvious. – Istvan Prosinger

1 Answers

0
votes

Let me answer my own question. Reading on, articles like this one

optimize mySql for faster alter table add column

and many more, actually say that this is the "issue" with InnoDb tables, and suggest some alternative approaches.

So I can only conclude that this is a normal behavior.