data_engine cannot connect to MySQL server on '<DB_IP_ADDRESS>' (0) (errno:2003)

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)
• Unified Infrastructure Management for Mainframe

Issue/Introduction

The data_engine intermittently loses its connection to the database, sometimes even after 30 days as evidenced by the number of aborted connections in the data_engine.log.

data_engine [main] [main] - Open - Error: [main] thread_id: 0, error: Open: Can't connect to MySQL server on '172.27.153.60' (0) (errno:2003)

Environment

Release : 20.4

Component : UIM - DATA_ENGINE

Cause

- MySQL database configuration

Resolution

Add more RAM, e.g., up to 24 GB, and set MySQL max_connections up to 1000.

 

Restart MySQL server.

 

sudo service mysql restart

 

Then keep an eye on the connections and the physical RAM being utilized.

 

Some parameters were adjusted in order to keep the memory usage at ~12G.

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
character-set-client-handshake = FALSE
bind-address=172.xx.xxx.xx
datadir=/opt/schufa/mysql
explicit_defaults_for_timestamp=true
federated = 1
innodb_force_recovery=0
innodb_log_file_size = 356M
interactive_timeout = 300
join_buffer_size = 8M
log-error=/opt/schufa/mysql_log/mysqld.log
log_timestamps = SYSTEM
##########
lower_case_table_names=1
local_infile=ON
table_definition_cache = 2000
innodb_file_per_table=0
#########
max_allowed_packet = 500M
max_heap_table_size = 134217728
max_tmp_tables = 64
net_read_timeout = 600
net_read_timeout=120
net_write_timeout = 600
pid-file=/var/run/mysqld/mysqld.pid
query_cache_limit = 4194304
socket=/opt/schufa/mysql/mysql.sock
wait_timeout = 3600
#############
key_buffer_size = 64M
query_cache_size = 64M
tmp_table_size = 32M
innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 1M
innodb_log_buffer_size = 4M
max_connections = 1000
sort_buffer_size = 4M
read_buffer_size = 128000
read_rnd_buffer_size = 256000
join_buffer_size = 128000
thread_stack = 256000
binlog_cache_size = 33000