ADD INDEX crashes MySQL

Question

I am trying to optimize my queries but when I try to add some indexes with this query:

ALTER TABLE `user` ADD INDEX `last_activity_date` (`last_activity_date`);

... it crashes my MySQL server. I mean that my websites are not responding anymore and my queries are not executed. The only solution I have is to restart MySQL. It's really annoying because I have an app with hundreds of users who obviously can't use it when it's crashed.

I have a MySQL log file but I don't see any error in it. In your opinion what can be the problem here? Could it be because some users are interacting with the DB while I'm adding indexes? I use InnoDB.

I have more than 100.000 records in the user table which looks like that :

CREATE TABLE `user` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `encrypt_id` varchar(255) DEFAULT NULL,
 `register_date` datetime DEFAULT NULL,
 `last_login_date` datetime DEFAULT NULL,
 `username` varchar(255) DEFAULT NULL,
 `password` varchar(255) DEFAULT NULL,
 `email` varchar(255) DEFAULT NULL,
 `banned` int(11) DEFAULT NULL,
 `banned_reason` text,
 `first_step_form` int(11) DEFAULT '0',
 `status` int(11) DEFAULT NULL,
 `referer` varchar(255) DEFAULT NULL,
 `rank` int(11) DEFAULT NULL,
 `fb_id` bigint(20) DEFAULT NULL,
 `last_activity_date` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `last_activity_date` (`last_activity_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Can you post anymore information such as the number of records in the `user` table ... a ```SHOW CREATE TABLE like `user` ``` statement? — cerd, Mar 31 '15 at 19:50
possible duplicate of [Create an index on a huge mysql production table without table locking](http://stackoverflow.com/questions/4244685/create-an-index-on-a-huge-mysql-production-table-without-table-locking) — Mr. Llama, Mar 31 '15 at 19:52
how are you determining the mysql crash? are you sure it isn't just creating the index? Index creation with a large enough table can take more than the blink of an eye - I think i've seen an index creation take upwards of 30 seconds before, so I would imagine it could go further as well. — Kritner, Mar 31 '15 at 19:52
And have you tried running `SHOW FULL PROCESSLIST;` ? This will show you if you have a lock waiting for the ALTER TABLE statement. — cerd, Mar 31 '15 at 19:52
Creating an index in MySQL will lock the table for writes. If it's a table that's used regularly by your application then you may be preceiving it as a "crash" even though it isn't. See this answer for a possible workaround: http://stackoverflow.com/a/14248906/477563 — Mr. Llama, Mar 31 '15 at 19:52
I have edited my first post with some informations (number of records et table informations). Thanks for all of your comments, I am reading them. — fraxool, Mar 31 '15 at 19:56

score 2 · Answer 1 · answered Mar 31 '15 at 20:05

2

If you have a number of users using the table and the alter table takes a bit of time to execute you may be running out Memory. That would be the reason why you are not seeing anything in the error logs. You are running out of memory because you have one to many connections, since the users are getting a metadata data lock wait timeout while the Alter table query is running.

I had to add an index on a table with 42million records in production a few days back. It took 10min to complete. The database gets over 17k reads a minute. I had the processlist open and killed any query that its state showed metadata lock wait timeout so that I would not fall under my worst case scenario which can be determined like so:

innodb_buffer_pool_size + key_buffer_size + max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size) + max_connections * 2MB

If you do not have these parameters set properly and you reach max amount of connections, you can run out of memory causing the server to crash.

answered Mar 31 '15 at 20:05

BK435

3,076
3
19
27

Mostly correct. Maxing out connections is only tangentially-related to running out of memory though. Those are really two independent failure modes. One could get the perception of the MySQL server not responding (and unable to get new connections to it) just based on hitting the max_connections threshold due to a long running locking process, without coming close to memory allocation limits (which would typically just cause MySQL to start paging to disk more frequently). Similarly one could run into memory problems just due to high concurrency of load without reaching max connections. – Mike Brant Mar 31 '15 at 20:13
Based on the timing of this failure matching the `ADD INDEX` operation, I would venture to guess that the OP is getting failures due to connection stacking. – Mike Brant Mar 31 '15 at 20:16
Agreed. Thank you for adding to and clarifying my answer. I was only pointing out a potential issue, not necessary the cause. That is, if the parameters are not set right and he has too many connections due to lock wait timeouts than the server could be OOMing . – BK435 Mar 31 '15 at 20:30

score 2 · Accepted Answer · edited Mar 31 '15 at 20:37

Presuming your website is pretty active, you have 100000 users, and that last_activity_date field is updated every time a user does something (anything), then applying an index on that field will likely wreak havoc. That many rows will likely take up to a few minutes to apply, and if it does not lock the users out, then the index would never get created as it would have to keep updating before it completed while users constantly changed the field (it probably locks users out to prevent this).

The only reason to index a field like last_activity_date would be to run reporting against it (things such as who did something their user in the last 30 days), or regular maintenance (disabling users that haven't done anything in the last 30 days). If such is the case, you might be better off creating a secondary table and loading that with your user table data, and applying the index on that table. Your reports won't be against live data--but your reports shouldn't need instantaneous results. Updating that secondary table once a day or so probably wouldn't take long--perhaps a few minutes--and it won't lock out your users in the process.

That's a good idea! I will try with a copy of the user table. And yes, my website is pretty active. I am working on improving the MySQL queries because my server is actually very slow. — fraxool, Mar 31 '15 at 20:21
Indeces are a two-edged sword. Yes, they improve performance when querying--but they hurt performance when updating. It's a trade-off. If your table is primarily used for querying, then adding an index will help--and could help a lot. But if you primarily update the table--such as a lot of changes to last_activity_date--then adding an index will actually hurt your overall performance, as the database must keep that index updated. — Russ, Mar 31 '15 at 20:25

ADD INDEX crashes MySQL

2 Answers2