More Performance Tuning

• Here are some more InnoDB performance tuning tips.
  • When importing data into InnoDB, make sure MySQL does not have autocommit mode enabled because it requires a log flush to disk for every insert.
    • To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements:

      SET AUTOCOMMIT=0;
      

      ... SQL import statements ...
      

      COMMIT;

  • If using the mysqldump --opt dump files are fast to import into an InnoDB table, even without wrapping them with the SET AUTOCOMMIT and COMMIT statements.
  • Beware of big rollbacks of mass inserts.
    • InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback.
    • A disk-bound rollback can take 30 times as long to perform as the corresponding insert.
    • Killing the database process does not help because the rollback starts again on server startup.
    • The only way to get rid of a runaway rollback is to increase the buffer pool so that the rollback becomes CPU-bound and runs fast.
  • Beware also of other big disk-bound operations.
    • Use DROP TABLE and CREATE TABLE to empty a table.
    • Do not use DELETE FROM tbl_name.
  • When inserting many rows, use the multiple-row INSERT syntax to reduce communication overhead between the client and the server.
  • If FOREIGN KEY constraints are in tables, speed up table imports by turning the foreign key checks off for the duration of the import session.

    SET FOREIGN_KEY_CHECKS=0;
    

    ... import operation ...
    

    SET FOREIGN_KEY_CHECKS=1;

    • For big tables, this can save a lot of disk I/O.

How does InnoDB implement versioning?

---

---

© 2007 John Michael Pierobon

Notes