Eatmydata: speed up mysql for large imports

From lxadm | Linux administration tips, tutorials, HOWTOs and articles
Jump to: navigation, search

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.