Performance Tuning

• Here are some InnoDB performance tuning tips.
  • A long PRIMARY KEY wastes a lot of disk space because its value must be stored with every secondary index record.
    • Create an AUTO_INCREMENT column as the primary key if the primary key is long.
  • If top shows the CPU usage percentage is less than 70%, the workload is probably disk-bound.
    • Perhaps there are too many transaction commits, or the buffer pool is too small.
    • Making the buffer pool bigger can help, but do not set it equal to more than 80% of physical memory.
  • Wrap several modifications into one transaction.
    • InnoDB must flush the log to disk at each transaction commit if that transaction made modifications tothe database.
    • The rotation speed of a disk constrains the number of commits.
  • Make log files big, even as big as the buffer pool.
    • When InnoDB has written the log files full, it has to write the modified contents of the buffer pool to disk in a checkpoint.
    • Small log files cause many unnecessary disk writes.
    • The drawback of big log files is that the recovery time is longer.
    • Make the log buffer quite large as well (on the order of 8MB).
  • Use the VARCHAR data type instead of CHAR if storing variable-length strings or if the column may contain many NULL values.
    • A CHAR(N) column always takes N characters to store data, even if the string is shorter or its value is NULL.
    • Smaller tables fit better in the buffer pool and reduce disk I/O.

Are there any more performance tuning tips?

---

---

© 2007 John Michael Pierobon

Notes