0

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?

Prabhat G
  • 2,974
  • 1
  • 22
  • 31
Istvan Prosinger
  • 145
  • 2
  • 11
  • 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 Jul 05 '17 at 09:47
  • 1
    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 Jul 05 '17 at 10:18
  • 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 Jul 05 '17 at 10:38
  • 1
    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 Jul 05 '17 at 11:04
  • 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 Jul 05 '17 at 12:31
  • So, yhe actuall question is, how to make this fast(er) with InnoDB engine. – Istvan Prosinger Jul 05 '17 at 12:39
  • seriously, a simple alter table add column doesn't usually take this long on an innoDB table. there have to be some other factors involved, maybe (wild guess) some indexes on the table? – cypherabe Jul 05 '17 at 13:20
  • Thanks for the comment. No indexes either. If this would be such a simple problem, I wouldn't be asking around :) I am puzzled. Specially about the enormous difference when it's in one engine compared to the other. If the difference would be somewhat less, I wouldn't be wondering about it. – Istvan Prosinger Jul 05 '17 at 19:13
  • I repeated your test. I filled an InnoDB table with 32768 rows (176MB) and then added a column. It completed in 2.16 seconds. I alter to MyISAM, and added another column. It completed in 0.21 seconds. So InnoDB is slower than MyISAM, but it's not 10 minutes. I suspect you had some other contention going on, maybe a metadata lock on the table? – Bill Karwin Jul 05 '17 at 19:29
  • Please provide `SHOW CREATE TABLE` and `SHOW TABLE STATUS`. I would like to see the indexes and whether there is off-record storage involved. – Rick James Jul 08 '17 at 00:07
  • And, what was `innodb_buffer_pool_size`? – Rick James Jul 08 '17 at 00:11
  • Perhaps this deserves a bug report at bugs.mysql.com . – Rick James Jul 08 '17 at 00:11

1 Answers1

0

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.

Istvan Prosinger
  • 145
  • 2
  • 11
  • 1
    Yes, it's normal - ten minutes is a bit high for 25k rows, but they could be unusually large rows, have unusual amounts of indexing, the server could be slow, etc. If you're making a schema change to production databases, something like https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html can help prevent downtime. – ceejayoz Jul 05 '17 at 19:22
  • The database is in production (the table itself is not) and the server is a Skylake i7 with 64Gb RAM, with a reelatively large innodb cache pool and some query caching enabled at this moment for MyISAMs – Istvan Prosinger Jul 06 '17 at 07:04