0

I'm running MySQL 5.7.10 in MacOS 10.11.2 on a Mac Mini with 16 GB of memory and a 1 TB SSD drive that has 160 GB free space.

There is a table with 1.2 billion records, Totaling about 400 GB in size. It's working fine for al intents and purposes, gathering data for a year. But now I need a new composite index.

Using the mysql command-line utility:

CREATE INDEX composite_IDX USING BTREE ON db.stats (foo,bar(2),baz,quux,fum,shme);

However, whether I run it remotely, locally, locally in a screen session, during the day, at night, or while listening to Patience by Guns n' Roses, sooner or later it will always fail:

ERROR 2013 (HY000): Lost connection to MySQL server during query

This message doesn't tell me a lot. Why can't I create this index? What can I do?

I've tried adding absurd timeouts to /etc/mysql/my.cnf:

[mysqld]
default_password_lifetime=0
max_allowed_packet = 1G  
net_read_timeout = 240
net_write_timeout = 1800

innodb_buffer_pool_size = 8G
innodb_flush_log_at_trx_commit = 2

# Add absurd timeout
wait_timeout = 172800
interactive_timeout = 172800

[mysqldump]
max_allowed_packet = 1G

But it didn't change anything. I'm basically grasping at straws here.

Redsandro
  • 11,060
  • 13
  • 76
  • 106
  • Dunno if it counts as a duplicate but [this question](https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query) might help. – Alfabravo Nov 08 '17 at 14:11
  • 1
    CREATE INDEX will use diskspace i think you run out off diskspace and MySQL terminated the connection – Raymond Nijland Nov 08 '17 at 14:28
  • And not too few, given how many rows you have and how many columns you want to have in your index. You should see something in your error log. – fancyPants Nov 08 '17 at 14:32

2 Answers2

0

Do you have a prefix for this index? If so the create statement can error out if its a non-unique index and the prefix exceeds your max column data type size.

You are creating an index that is supported by your table type, yes? For example, FULLTEXT indexes are only supported with InnoDB and MyISAM tables.

Here's a link to the documentation: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

But, it may be that too much of the table has to be read into memory/loaded into temp storage for your server to create the composite index. You only have 160 Gb of free space, but your table is 400Gb in size, and you're creating an index on 6 columns. With 1.2 billion records you are looking at 7.2 billion individual fields. I'm wondering if it is a temporary storage problem or a RAM issue.

Lea Klein
  • 408
  • 2
  • 2
  • `ALTER ... ALGORITHM=INPLACE` avoids copying over the table. So, your Answer _may_ not be relevant. (The OP has not given enough details to say for sure.) – Rick James Nov 09 '17 at 03:32
0

It's just as well that it died. Prefixing, such as bar(2), prevents the use of the columns after that in the index. Hence the index would not be as useful as you thought. So, let's see the query that it was supposed to help, and let's see SHOW CREATE TABLE. Then we can focus on finding a better index. (Start a new Question.)

Meanwhile...

  • It is unwise to let the free disk space shrink below the size of the largest table.
  • I hope you are running with innodb_file_per_table?
  • 5.7 has ALTER TABLE .. ADD INDEX .. ALGORITHM=INPLACE, which is needed for such small spare space.
  • The timeouts do not take effect until mysqld is restarted.
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Your evaluation of the index' usefulness is outside the scope of this question. Also, it is wrong. Prefix indexes within compound indexes are a necesity in advanced cases. – Redsandro Nov 09 '17 at 09:54
  • @Redsandro - I try to help solve the ultimate question (how to speed up a query), not just the immediate question (about timeout). What is the query that should benefit from the index in question? – Rick James Nov 09 '17 at 16:54