MySQL Tuning of parameters under the limit of maximum open files for Linux

Http://www.actionsky.com/docs/archives/78April 7, 2016 Zhou Wenja

Directory

• 1 causes
• 2 description
• 3 MySQL how to adjust parameters
  • 3.1 Calculation Request_open_files
    • 3.1.1 Compute request_open_files based on configuration values
    • 3.1.2 Calculation effective_open_files
    • 3.1.3 Correction Request_open_files
  • 3.2 Calculating the value of the parameter in effect
    • 3.2.1 Correction Open_files_limit
    • 3.2.2 Correction Max_connections
    • 3.2.3 Correction Table_cache_size
• 4 examples

causes

Non-root users run MySQL, and when the MySQL configuration is high, the parameter values that are in effect in MySQL run are different from the configured values.
The purpose of this article is to illustrate how MySQL adjusts the following three parameters in case of insufficient system resources: open_files_limit , max_connections table_open_cache .

Description

This article deals with three parameters open_files_limit , max_connections and table_open_cache . The system resources associated with these three parameters are limited by the number of open files, which is the file descriptor ( fd ) limit.

The relationship between system parameters and file descriptors
– max_connection & fd : Each MySQL connection requires a file descriptor
– table_open_cache & fd : Opening a table requires at least one file descriptor, such as opening MyISAM requires twofd
– The number of open files limits for the system can be ulimit -n viewed by

how MySQL adjusts the parameters

1. Calculated based on configuration (three parameter configuration values or default values)request_open_files(需要的文件描述符)
2. Get a valid system limit valueeffective_open_files
3. According to the effective_open_files adjustmentrequest_open_files
4. Calculates the value of the parameter that is actually in effect according to the adjusted value request_open_files ( show variables view parameter values)

Calculation request_open_files calculated request_open_files based on configuration values

request_open_filesThere are three calculation conditions

    // 最大连接数+同时打开的表的最大数量+其他（各种日志等等）      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);

Calculation effective_open_files

MySQL's idea:
In the range of finite values mysql tries to effective_open_files set the value to a large

fixed request_open_files

requested_open_files= Min ( effective_open_files , request_open_files );

calculate the value of the parameter in effectfixed open_files_limit

open_files_limit=effective_open_files

fixed max_connections

max_connectionsrequest_open_filesmake corrections based on them.

limit = requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2;

• If the configured max_connections value is greater than limit, the max_connections value is corrected to limit
• Other cases max_connections Preserve configuration values

fixed table_cache_size

table_cache_sizeIt's going to request_open_files make corrections.

// mysql table_cache_size 最小值，400limit1 = TABLE_OPEN_CACHE_MIN // 根据 requested_open_files 计算limit2 = (requested_open_files - 10 - max_connections) / 2limit = max(limit1，limt2);

• If the configured table_cache_size value is greater than limit, the table_cache_size value is corrected to limit
• Other cases table_cache_size Preserve configuration values

Example

The following use cases run under a non-root user

参数设置：//mysql  max_connections = 500  table_open_cache = 999//ulimit -n   1500生效的值：open_files_limit = 1500max_connections = min[(1500 - 10 - 800)，500] = 500table_open_cache = ( 1500 - 10 - 500) / 2 = 495

MySQL Tuning of parameters under the limit of maximum open files for Linux