How to configure the data_engine probe thread_count_insert setting for MySQL
Article ID: 74449
Updated On:
Products
Issue/Introduction
When using MySQL as the backend database, if you set the data_engine thread_count_insert to any value higher than 0, thereby enabling multi-threading, you need to be aware that you could run out of connections. This would manifest itself in the data_engine log that there were "no free connections (err: 1040)" because the data_engine could exceed the max number of connections to MySQL.
ERROR 1040 (xxxxx): Too many open connections
This setting should not be changed in a MySQL environment without investigating the MySQL server setting first. There is a hard cap in connections on MySQL that is defined within the MySQL configuration file. The default value for this is 151. If the setting is not high enough, this can cause the data_engine to crash repeatedly once it has uninterrupted access to the DB (e.g., after maintenance is completed.)
To avoid this problem, error and inconsistent connections to the database when inserting data, you need to increase the max connections settings in MySQL.
This error occurs when connection reaches the maximum limit as defined in the configuration file. The variable holding this value is 'max_connections.'
Environment
Cause
- MySQL database configuration
Resolution
Max connections
To check the current value of this variable, login as the root user and run the following command:
show global variables like '%max_connections%';
You can login to MySQL using the root user and increase the max_connections variable to a higher value.
SET GLOBAL max_connections = 1000;
This method above does not require a server restart.
Please note that after MySQL server restart, the max_connection variable value will again roll back to the previous value.
In order to make the max_connections value persistent, modify the value in the configuration file.
- Stop the MySQL database server:
service mysql stop
- Edit the configuration file my.cnf
vi /etc/my.cnf
- Find the variable max_connections under the mysqld section.
[mysql]
- Set the following value:
max_connections = 1000
Set the higher value and save the file
- Start the server
service mysqld start
Note: use systemctl manager to stop and start the service if the service command is not working.
Before increasing the max_connections variable value, make sure that the server has adequate memory for new requests and connections.
Consult your MySQL DBA to determine if enough resources are available.
MySQL pre-allocates memory for each connection and de-allocates only when the connection is closed. When new connections are querying, the system should have enough resources such as memory, network and computation power to satisfy the user requests.
Also, you should consider increasing the open tables limit in MySQL server to accommodate the additional requests.
Additional Information
MySQL Reference:
Feedback
