1025InnoDB log file settings how much appropriate

Transfer from http://blog.csdn.net/langkeziju/article/details/51094289

Database of things, often a parameter involves n multiple knowledge points. So simply say it. Everyone knows that InnoDB is a storage engine that supports transactions. The four characteristics of a transaction are acid-atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability). Where atomicity, consistency, and persistence are achieved through redo log and undo. Redo log, known as redo logs, is used to guarantee the atomicity and durability of transactions. Undo log is used to ensure transactional consistency. When a transaction commits, it must first write all the logs of the transaction to the Redo log file (redo log) for persistence (of course, write to the log buffer, rather than write directly to the file, about when to flush to disk file, is a more complex problem, the students themselves access to the data), until the transaction commit operation is completed. InnoDB's redo log is sequential I/O, so setting the appropriate values can greatly improve database performance. So is it better to set the bigger the better?

Too small: When a log file is full, InnoDB automatically switches to another log file and triggers a checkpoint (Checkpoint) of the database, which results in a small batch refresh of InnoDB cache dirty pages, which can significantly degrade innodb performance.

The settings are too large: the checkpoint is reduced after a large setting, and because redo log is sequential I/O, it greatly improves I/O performance. However, if there is an unexpected problem with the database, such as an unexpected outage, it is necessary to replay the log and restore the committed transaction, which can result in a long recovery time if the log is large. Even to the extent that we cannot accept.

So how do we estimate how big the log file is set to? Usually we can see how much data InnoDB writes to the log file by observing the Innodb_os_log_written state variable in show status. A good experience is to look at the 10-100-second interval number and then record the spike. This can be used to determine whether the log buffer is set exactly. For example, if you see spikes that write 100kb data to the log per second, then 1MB of log buffering is sufficient. It is also possible to use this metric to determine the log file settings more than the assembly is better. If the peak is 100kb/s, then 256M of log files are sufficient to store at least 2,560 seconds of log records. In general, the full size of the log file should be sufficient to accommodate the server's one-hour active content.

Here is the actual test (because it is a virtual machine, so use sysbench to simulate the amount of write, the production environment select the most busy time the database test):

[Email protected] ~]# sysbench--test=oltp--oltp-table-size=100000--oltp-read-only=off--init-rng=on--num-threads= --max-requests=0--oltp-dist-type=uniform--max-time=180--mysql-user=root--mysql-socket=/tmp/mysqld.sock-- mysql-password=123456--db-driver=mysql--mysql-table-engine=innodb--oltp-test-mode=complex Prepare    

1. First calculate the amount of log generated by InnoDB per minute:

([email protected]) [(None)]>pager grep Sequencepager set to ' grep sequence ' ([email protected]) [(none)]>show engine InnoDB status\g sel ECT Sleep (63772752591 row in Set (0.00 sec)1 row in Set (1  0.0064039455551 row in Set (0.00 sec) ([email protected]) [(none)]>nopagerpager set to stdout ([email protected]) [(none)]>select (64039455556377275259  1024x768 as Mb_per_min;                    +-------------+| Mb_per_min  25.43477631 |+-------------+1 row in Set (0.02 sec) (email protected ]) [(none)]>

Note The log sequence number, which is the total bytes written to the transaction log. So now you can see how many MB log writes per minute (here the technology applies to all versions of MySQL, at 5.0 and later, you can see the value of Innodb_os_log_written from the output of show GLOBAL status).

A log write of 25M per minute is obtained by calculation.

According to the rule of thumb. Usually we set the redo log size to be large enough to hold 1 hours of log writes.

1 hours Log Write volume =25m * 60=1500m, approximately equal to 1.5G. Because of the default there are two log redo log files Ib_logfile0 and ib_logfile1. The size of each redo log file in the log group is consistent and is written in a circular manner. InnoDB Storage Engine first writes redo log file 0, when the end of the file is reached, it switches to redo log 1, and checkpoint. In this loop.

So we can set about innodb_log_file_size=800m. Note: Before the innodb1.2.x version, the total size of the redo log file must not be greater than or equal to 4G, and the 1.2.x version expands the limit to 521G.

Innodb_log_file_size = 800M

1025InnoDB log file settings how much appropriate