MySQL 8.0 Dynamic Redo Log Sizing
source link: https://www.percona.com/blog/mysql-8-0-dynamic-redo-log-sizing/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
MySQL 8.0 Dynamic Redo Log Sizing
This blog post will discuss the newest feature available in MySQL 8.0.30: dynamic redo log sizing. After the InnoDB buffer pool size, we can say that having a proper size for the redo logs is crucial for MySQL performance. There are numerous blog posts about how to calculate a good redo log size. One of our classic blog posts is this one from Baron: How to calculate a good InnoDB log file size (That blog post is from 2008 and is still a valid formula to use.)
Now, which problem is this feature trying to solve?
In MySQL, the buffer pool and redo log size are settings that do not change frequently. Usually, these settings are set during database installation; after that, they are forgotten until an issue arises. The problem is that they used to be static, which means that you had to restart MySQL so the changes could take effect.
MySQL solved the buffer pool problem with dynamic resizing in MySQL 5.7. Now, MySQL 8.0.30 solved the redo log problem. To resize is simple and straightforward:
mysql> SET GLOBAL innodb_redo_log_capacity = 2*1024*1024*1024; |
This setting works to resize to a higher and a lower value than the current one defined. To make this work, the redo log files now reside in a new directory inside the datadir named #innodb_redo unless a different directory is specified by the innodb_log_group_home_dir variable. There are two types of redo log files, ordinary and spare. Ordinary redo log files are those being used. Spare redo log files are those waiting to be used. InnoDB maintains 32 redo log files in total, with each file equal in size to 1/32 * innodb_redo_log_capacity; however, file sizes may differ for a time after modifying the innodb_redo_log_capacity setting.
Redo log files use a #ib_redoN naming convention, where N is the redo log file number. Spare redo log files are denoted by a _tmp suffix. The following example shows the redo log files in a #innodb_redo directory, where 18 active redo log files and 14 spare redo log files are numbered sequentially.
#ib_redo31 #ib_redo33 #ib_redo35 #ib_redo37 #ib_redo39 #ib_redo41 #ib_redo43 #ib_redo45 #ib_redo47 #ib_redo49_tmp #ib_redo51_tmp #ib_redo53_tmp #ib_redo55_tmp #ib_redo57_tmp #ib_redo59_tmp #ib_redo61_tmp #ib_redo32 #ib_redo34 #ib_redo36 #ib_redo38 #ib_redo40 #ib_redo42 #ib_redo44 #ib_redo46 #ib_redo48 #ib_redo50_tmp #ib_redo52_tmp #ib_redo54_tmp #ib_redo56_tmp #ib_redo58_tmp #ib_redo60_tmp #ib_redo62_tmp |
Several status variables are provided for monitoring the redo log and redo log capacity resize operations; for example, you can query Innodb_redo_log_resize_status to view the status of a resize operation:
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_resize_status'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_redo_log_resize_status | OK | +-------------------------------+-------+ |
The Innodb_redo_log_capacity_resized status variable shows the current redo log capacity limit:
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_capacity_resized'; +----------------------------------+------------+ | Variable_name | Value | +----------------------------------+------------+ | Innodb_redo_log_capacity_resized | 3221225472 | +----------------------------------+------------+ |
Other status variables include:
MySQL 8.0.30 also introduced some new error messages related to this issue. To mention a few:
In case you want to check for more, you can use this link. All redo log error-related messages use the ER_IB_MSG_LOG_WRITER_ naming convention.
Estimating the redo log capacity
With these new status variables, we can estimate the redo log size using the following query:
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_current_lsn'; SELECT SLEEP(60); SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_current_lsn'; |
And with the outputs provided by the previous query, we run the following:
mysql > SELECT ABS(20641693317 - 20903377487) / 1024 / 1024 AS MB_per_min; +--------------+ | MB_per_min | +--------------+ | 249.56147194 | +--------------+ 1 row in set (0.00 sec) |
So, in this case, writing around ~250MB/min in the redo log files, a good value would be 250*60(1 hour), equivalent to 15GB.
Deprecated parameters
To make this work, MySQL deprecated two parameters: innodb_log_files_in_group and innodb_log_file_size .
If you try to set them in in the my.cnf file, they will be ignored, and a warning will be printed in the error log:
2022-08-07T20:23:39.898370Z 0 [Warning] [MY-013869] [InnoDB] Ignored deprecated configuration parameter innodb_log_file_size. Used innodb_redo_log_capacity instead. 2022-08-07T20:23:39.898441Z 0 [Warning] [MY-013870] [InnoDB] Ignored deprecated configuration parameter innodb_log_files_in_group. Used innodb_redo_log_capacity instead. |
Conclusion
The dynamic redo log sizing brings more flexibility to the DBA in case he needs to resize, and the application cannot stop for a MySQL restart. At the moment of this writing, there are no bug reports related to this feature. One piece of advice is to check if your backup tools are compatible with MySQL 8.0.30 and its new features. Lastly, always make sure to review your existing my.cnf due to the deprecated parameters.
Useful Resources
You can reach us through social networks, our forum, or access our material using the links presented below:
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK