Choose the right Innodb_log_file_size

Choosing Proper Innodb_log_file_size

Choose the right Innodb_log_file_size

If you are doing significant amount of writes to Innodb tables decent size of innodb_log_file_size are important for MySQL P Erformance. However setting it too large would increase recovery time, so in case of MySQL crash or power failure it may take long Before MySQL Server is operational again.

If you have a large number of writes to the INNODB datasheet, choosing the appropriate Innodb_log_file_size value is important to improve MySQL performance. However, the set is too large, it will increase the time to restore, so in the case of MySQL crashes or sudden power outages will take a long time to restore the MySQL server.

So how do you find the optimal combination?

So how do you find the best configuration mix?

The explain what happens on recovery and why large innodb_log_file_size the slows down recovery. During startup after crash Innodb scans log files to find log records which only have been applied in memory and does not ex ist in tablespace. Log records for modifications which did does not make it to the tablespace are the then. This is called redo phase of recovery. It can take pretty long time depends on number of variables-how large are rows? (smaller log records mean more records for same sized logs), how random were data modifications (random updates would need Random IO to check if pages are up to date), number of unflushed pages in InnoDB buffer pool and its size as OK as Perfo Rmance of IO subsystem. As there are so many factors, it's hard to come up and any general guidelines, something like 1GB per minutes of reco Very time-instead you would need to apply load which are typical for your application, crash MySQL in the middle and WATC H it to recover. DoinG This several should is able to estimate how long recovery the time take and adjust your logs. The good thing Is-redo phase are close to being proportional to size of log files, so expect 1GB logs to take twice Apply compared to 512MB logs.

First, let me explain what happened at the time of recovery and why setting Innodb_log_file_size is too big to slow the recovery process. Innodb data table crashes are started again, MySQL scans the log files to find the log records that apply only to memory and do not exist in the tablespace. Changes to log records that have not been put into the table space are added. This is called redo phase recovery. It takes quite a long time, depending on the value of the variable-how many rows are there? (The smaller the value of logging means that more records can be stored in the same size log), the probability of random data modifications is high (random updates require more random IO to check the memory page for updates), the number of memory pages not refreshed in the InnoDB buffer pool, and it is also the performance performance of the IO subsystem. With so many factors, it's hard to produce common criteria, such as duration to restore 1GB of data every 10 minutes--instead, you should determine the load in a typical application, and monitor how it recovers during the MySQL crash. After doing this a few times, you should be able to roughly estimate the amount of time it takes to recover and adjust the log size more appropriately. The good thing is that the redo phase is proportional to the log file size, so it is estimated that the time required to restore the 1GB log is approximately twice times that of 512MB.

Redo phase is however only one of the phases of recovery. The other important one is Undophase-after log file are applied and database are in ' physically consistent ' state, Innodb Would need to roll back certain transactions which where not commited, but changes from which already it to the made Ase. Unlike "Redo" phase "undo" phase can ' t is reduced by sizing your log files. Even more undo phase can is slower with small log files. Undo phase takes considerable time if tranactions are long-ie if your would delete 10000000 rows in the same transaction And crash in the middle recovery can take quite a long time. The only way can reduce "undo" phase are size your transactions appropriately-so updates/inserts/deletes can be sized To affect limited number of rows.

However, the redo phase is the only way to phase recovery. Another important method is to undo the phase--When the log file is applied and the database is in a "physically consistent" state, Innodb rolls back those uncommitted transactions, but changes to the database are not taken care of. Unlike the "Redo" phase, the "undo" phase does not become faster because the log size is smaller. Even the undo phase may be slower because the log is smaller. The time taken to undo the phase is due to the length of the transaction-for example, if you need to delete 10000000 rows of records in a transaction, and the error crashes in the middle of a transaction, then it will take a while to recover. The only way to reduce the "undo" phase is to set the appropriate log size value-so that the record update/INSERT/delete is limited to a finite number.

Good thing about updo phase however is-it can do in background as in MySQL 5.0. The rows affected by background rollback however might the not to modified until rollback is complete.

The advantage of the undo phase, though, is that in MySQL 5.0, it can be done in the background. Background rollback records are not modified until they are restored.

One more thing to consider-how large log files does you need in all? You are could run benchmark with 1GB log files and 2GB and the If there is any performance benefit. After certain size increasing log file size does not dramatically increase performance however this again on Config Uration and workload.

The other thing to consider is-how much log is needed? You can run benchmark tests to check the 1GB size of logs for relative 2GB benefits. Adding a log file to a certain size does not necessarily dramatically improve performance, but it also relies on configuration and MySQL workloads.

Note at this poing 4GB are maximum combined size allowed for InnoDB log files, which are however large enough for limit Onfigurations.

Note that the 4GB in this example is the maximum value of the InnoDB log file, but it is significantly larger than the usual configuration.