Databases Reference
In-Depth Information
Figure 8-1. InnoDB's buffers and files
The overall log file size is controlled by
innodb_log_file_size
and
innodb_log
_files_in_group
, and it's very important for write performance. The total size is the
sum of each file's size. By default there are two 5 MB files, for a total of 10 MB. This is
much too small for a high-performance workload. You need hundreds of megabytes,
or even gigabytes, of log files.
InnoDB uses multiple files as a single circular log. You usually don't need to change
the default number of logs, just the size of each log file. To change the log file size, shut
down MySQL cleanly, move the old logs away, reconfigure, and restart. Be sure MySQL
shuts down cleanly, or the log files will actually have entries that need to be applied to
the data files! Watch the MySQL error log when you restart the server. After you've
restarted successfully, you can delete the old log files.
To determine the ideal size for your log files, you'll have to
weigh the overhead of routine data changes against the recovery time required in the
event of a crash. If the log is too small, InnoDB will have to do more checkpoints,
causing more log writes. In extreme cases, write queries might stall and have to wait
for changes to be applied to the data files before there is room to write into the log. On
the other hand, if the log is too large, InnoDB might have to do a lot of work when it
recovers. This can greatly increase recovery time, although this process is much more
efficient in newer MySQL versions.
Log file size and the log buffer.
