Eatmydata: speed up mysql for large imports
Every had to do a very large MySQL import, taking several days or weeks?
Because MySQL will want to sync all data to disk to maintain integrity, everything will be sluggish and slow - especially if you have HDDs, not SSDs.
If you can afford running your MySQL server to loose data in case of a crash (because it's some initial import, populating of the database etc.), you may try to use "eatmydata". It is very simple:
- stop MySQL server and make sure it's not running (i.e. ps aux|grep mysql)
- run mysqld with "eatmydata":
# eatmydata /usr/bin/mysqld_safe 2016-07-21T06:56:39.372817Z mysqld_safe Logging to syslog. 2016-07-21T06:56:39.376467Z mysqld_safe Logging to '/var/log/mysql/mysql-error.log'. 2016-07-21T06:56:39.425780Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/
- you can monitor it's effectiveness with commands like:
- "iostat -mx 1" (where before the disks would be 90-100% saturated, they should be now much less utilized)
- /proc/meminfo will show that kernel's dirty buffers contains lots of unwritten data (which will be eventually pushed to disks):
# egrep '(Dirty|Writeback)' /proc/meminfo Dirty: 71196 kB Writeback: 0 kB
- after your huge import is done, stop mysql and start it "the normal way", without "eatmydata"
- "eatmydata" will help with just about any software which uses *sync a lot, not just with mysql
From eatmydata description:
libeatmydata is a small LD_PRELOAD library designed to (transparently) disable fsync (and friends, like open(O_SYNC)). This has two side-effects: making software that writes data safely to disk a lot quicker and making this software no longer crash safe. DO NOT use libeatmydata on software where you care about what it stores. It's called libEAT-MY-DATA for a reason.