DX Infrastructure Management

 View Only

Tech Tip: How to configure the data_engine probe thread_count_insert setting for MySQL 

Mar 21, 2018 10:36 AM

Here are some important notes on the data_engine thread_count_insert setting when using MySQL as the backend DB for UIM

 

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 (er 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 connections reach the maximum limit as defined in the configuration file. The variable holding this value is 'max_connections.'

 

- UIM 8.5x or higher

- MySQL supported versions for UIM
    
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 server:
    Service mysql stop
    Edit the configuration file my.cnf
    vi /etc/my.cnf
    Find the variable max_connections under mysqld section.
    [mysql]
    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.

 

Other references:
https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

Statistics
0 Favorited
3 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Related Entries and Links

No Related Resource entered.