Technology Sharing | MySQL Replication Retry Parameter Configuration

Author: code0

An unknown coder from the Eikesheng DMP team, full of mystery...

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

1. Cause

Non-root users run MySQL. When the MySQL configuration is relatively high, the parameter values that take effect during MySQL operation are different from the configured values, so let's analyze specifically how MySQL adjusts these parameter values.

So the purpose of this article is to explain how MySQL adjusts the three parameters when the system resources are insufficient.

2. Description

This article involves 3 parameters:

• open_files_limit
• max_connections
• table_open_cache

These three parameters and system-related resources are the files that can be opened at the same time ( ulimit -n view), which is actually the file descriptor ( fd ).

The relationship between system parameters and file descriptors-max_connection & fd: Each MySQL connection requires a file descriptor-table_open_cache & fd to open a table requires at least one file descriptor, such as opening MyISAM requires two fd

Third, the way MySQL adjusts parameters

1. Calculate request_open_files (required file descriptors) according to the configuration (the configured 3 parameter values or default values)
2. Get the effective system limit value effective_open_files
3. Adjust request_open_files according to effective_open_files
4. According to the adjusted request_open_files, calculate the actual effective parameter values (3 parameter values viewed by show variables

1. Calculate request_open_files

There are three calculation formulas for request_open_files:

# 最大连接数+同时打开的表的最大数量+其他（各种日志等等）
limit_1= max_connections + table_cache_size * 2 + 10;

# 假设平均每个连接打开的表的数量（2-4）
# 源码中是这么写的：
# We are trying to allocate no less than  
# max_connections*5 file handles 
limit_2= max_connections * 5;

# MySQL 默认的最低值是 5000
limit_3= open_files_limit ? open_files_limit : 5000;

# 所以 open_files_limit 期待的最低 
request_open_files= max(limit_1, limit_2,limit_3);

2. Calculate effective_open_files

The idea of MySQL: MySQL tries to set the value of effective_open_files as large as possible within the range of limited values

3. Modify request_open_files

requested_open_files= min(effective_open_files, request_open_files);

Recalculate parameter values

1. Modify open_files_limit

open_files_limit = effective_open_files

2. Modify max_connections

max_connections is modified according to request_open_files.

limit = requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2;

• If the configured max_connections value is greater than limit, then the max_connections value will be revised to limit
• In other cases, max_connections retains the configuration value

3. Modify table_cache_size

table_cache_size will be modified according to request_open_files.

# MySQL table_cache_size 最小值，400
limit1 = TABLE_OPEN_CACHE_MIN 

# 根据 requested_open_files 计算
limit2 = (requested_open_files - 10 - max_connections) / 2
limit = max(limit1,limt2);

• If the configured table_cache_size value is greater than limit, the value of table_cache_size is corrected to limit
• In other cases table_cache_size retains the configured value

Four, for example

The following use cases are all run under non-root users

- 系统资源不够且无法调整
# 参数设置
mysql max_connections = 1000 //ulimit -n 1024

# 生效的值
open_files_limit = 1024 max_connections = 1024 - 10 - 800 = 214 table_open_cache = ( 1024 - 10 - 214) / 2 = 400

---
- 系统资源不够可以调整
# 参数设置
mysql max_connections = 1000 //ulimit -S -n 1000 //ulimit -H -n 65535

# 生效的值
open_files_limit = 65535 max_connections = 1000 table_open_cache = ( 1024 - 10 - 214) / 2 = 400

---
- mysql 修改 open_files_limit
# 参数设置
//mysql max_connections = 1000 max_connections = 1000 //ulimit -n 65535

# 生效的值
open_files_limit = 65535 max_connections = 1000 table_open_cache = 2000 ```

Five, other

The relevant content in the Taobao Database Kernel Monthly Report: "MySQL·Questions and Answers·Open File Limits" This article mainly talks about which operations MySQL will perform the operation of opening files.

and Answers·open file limits": 160becf2c63cad http://mysql.taobao.org//monthly/2015/08/07/