5

We're having a problem where a mysqldump script is spending 90% of it's time populating a small handful of the tables it deals with. Eliminating FK's and indexes eliminates the speed problem, but is not an acceptable solution.

The dump script does have:

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

Can we expect any different behavior from ALTER TABLE foo DISABLE KEYS?

Also, is disable keys session-scoped or is it permanent until I re-enable it? Can I disable keys from one mysql session and have it effect the import issued from another session?

Pacerier
  • 86,231
  • 106
  • 366
  • 634
Brian Deacon
  • 21,384
  • 13
  • 39
  • 41
  • 1
    Assuming you are using InnoDB engine, consider tuning innodb_log_file_size (But be careful, see http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/) and innodb_log_buffer_size. For more information, see my answer here: http://stackoverflow.com/a/12688184/1148030 – Peter Lamberg Oct 02 '12 at 10:08
  • @Brian, The crucial point is, Are your tables MyISAM or InnoDB? – Pacerier Feb 23 '15 at 14:54

2 Answers2

6

Yes, you should get significant benefits out of DISABLE KEYS. It isn't session-scoped, it's a table property, so your keys will be dead for everybody until you do ENABLE KEYS.

chaos
  • 122,029
  • 33
  • 303
  • 309
  • 2
    Actually, not so much. DISABLE KEYS doesn't seem to be improving our import time, yet dropping the keys from the table entirely gives us 20X performance. Suspicious now of max temp file sizes... – Brian Deacon Aug 24 '09 at 16:04
  • Hunh. Odd. Do let me know if you find out what's going on there. – chaos Aug 24 '09 at 16:11
  • Yeap, remove indexes, do bunches makes more efficiency. Millions of record inserted within minutes. – Sándor Tóth May 02 '13 at 12:57
  • @BrianDeacon, Are you sure that they are disabled? [Check it with `show keys`](http://stackoverflow.com/questions/4980917/how-to-check-if-enable-disable-keys-work/28667994#28667994). Also, you can only disable non-unique keys, and, regardless of unique or not, you [cannot `disable keys`](http://serverfault.com/questions/234893/how-to-disable-keys-in-mysql-innodb/234901#comment820478_234901) in InnoDB. – Pacerier Feb 24 '15 at 04:22
4

DISABLE KEYS is MyISAM only:

If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.

This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

from ALTER TABLE Syntax

Using DISABLE KEYS with any other storage engine results in a warning:

mysql> ALTER TABLE `foo` DISABLE KEYS;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1031
Message: Table storage engine for 'foo' doesn't have this option
1 row in set (0.00 sec)
Community
  • 1
  • 1
sfussenegger
  • 35,575
  • 15
  • 95
  • 119
  • Actually it says *"can be activated explicitly for a MyISAM table"* but it doesn't say that **only** MyISAM supports it........... – Pacerier Feb 23 '15 at 14:55