Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to set innodb_log_file_size in MySQL, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

The size setting of innodb_log_file_size will affect the write performance of MySQL database. If the setting is too small,

Will increase the number of checkpoint writes, the following test in the MySQL5.7 version of how to modify innodb_log_file_size

[root@endb local] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.7.9-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show variables like'% innodb_log_file%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_log_file_size | 1073741824 | |

| | innodb_log_files_in_group | 2 | |

+-+ +

2 rows in set (0.00 sec)

Mysql > select 1073741824Universe 1024Universe 1024

+-+

| | 1073741824Universe 1024According 1024 |

+-+

| | 1024.00000000 |

+-+

1 row in set (0.00 sec)

Confirm that innodb_fast_shutdown is 1 here, so that the ib_logfile file will not be used when restarting MySQL

Mysql > show variables like'% shutdown%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_buffer_pool_dump_at_shutdown | ON |

| | innodb_fast_shutdown | 1 | |

+-+ +

2 rows in set (0.00 sec)

Mysql > show variables like'% datadir%'

+-+ +

| | Variable_name | Value |

+-+ +

| | datadir | / home/mysql/ |

+-+ +

1 row in set (0.00 sec)

[root@endb local] # / etc/init.d/mysql stop

Shutting down MySQL... [OK]

[root@endb local] # grep innodb_log / etc/my.cnf

Innodb_log_file_size = 512m

[root@endb mysql] # cp ib_logfile*..

[root@endb mysql] # ll ib_logfile*

-rw-r- 1 mysql mysql 1073741824 Mar 3 15:03 ib_logfile0

-rw-r- 1 mysql mysql 1073741824 Nov 25 16:09 ib_logfile1

[root@endb mysql] # ll-h ib_logfile*

-rw-r- 1 mysql mysql 1.0G Mar 3 15:03 ib_logfile0

-rw-r- 1 mysql mysql 1.0G Nov 25 16:09 ib_logfile1

Mysql > show variables like'% innodb_log%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_log_buffer_size | 16777216 | |

| | innodb_log_checksums | ON |

| | innodb_log_compressed_pages | ON |

| | innodb_log_file_size | 536870912 | |

| | innodb_log_files_in_group | 2 | |

| | innodb_log_group_home_dir |. / | |

| | innodb_log_write_ahead_size | 8192 | |

+-+ +

7 rows in set (0.00 sec)

[root@endb mysql] # ll-h ib_logfile*

-rw-r- 1 mysql mysql 512m Mar 3 15:05 ib_logfile0

-rw-r- 1 mysql mysql 512m Mar 3 15:05 ib_logfile1

Thank you for reading this article carefully. I hope the article "how to set up innodb_log_file_size in MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.