I have a follow up to my previous question regarding import speed with Inno-Tables (surprise!).
Scenario
I try to import some big* database dump on my local dev machine in reasonable time. We have a lot of KEY
s attached to the tables which have turned out to be a bottleneck but are still important for our live system.
My approach after asking question above was to delete KEY ...
statements from dump, import and re-add keys.
However I often find myself editing a current dump to import it locally and I stumbled across these funny "comments" (The disable/enable keys
-lines)
--
-- Dumping data for table `monster`
--
LOCK TABLES `monster` WRITE;
/*!40000 ALTER TABLE `monster` DISABLE KEYS */;
INSERT … INSERT … INSERT
/*!40000 ALTER TABLE `monster` ENABLE KEYS */;
UNLOCK TABLES;
But in fact these "comments" are conditional MySql-Statements
That was news for me but ok, given the output form mysql --version
everything looks fine to me:
mysql Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.3
What I assume
The table is locked (fine, it's just me on the dev mashine). Then the keys as defined in table schema are disabled, data is imported, keys are enabled.
So during "data insertion"-phase there should be no time wasted on keys but rather examined after all data is inserted.
I would be thinking this is the same behaviour as if I delete all the KEY 'foo' (foo)'
-lines from the dump, import the dump and run a script with ADD KEY 'foo' ...
afterwards.
What I observe
It is way faster to manually delete the keys, import and re-add keys then relying on conditional DISABLE KEYS
statements created my mysqldump
Manual editing of dump + mysql import + adding keys = 15+8+8 ≈ 30min
Plain mysql import: given up, (I'm just getting paid for 8 hours/day >:) )
I can't help but thinking I'm missing something very fundamental here (or database is trolling me).
mysqldump --innodb-optimize-keys
from Percona percona.com/doc/percona-server/5.5/management/… Long term: stop using mysqldump and use mydumper or xtrabackup.