MySQL database used by UIM has high memory usage

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

End user has below parameters set in my.cnf for MySQL server 5.7:

max_heap_table_size=134217728
query_cache_limit=4194304
query_cache_size=268435456
sort_buffer_size=25165824
join_buffer_size=67108864
max_tmp_tables=64
max_connections = 1000

and the DB server has an issue of high memory usage.

Environment

Release :

Component : UNIFIED INFRASTRUCTURE MGMT

Cause

Variables that determine the per-session buffer size are set to a very large value (in bytes).

Typical variables are :

join_buffer_size

sort_buffer_size

read_buffer_size

read_rnd_buffer_size

bulk_insert_buffer_size

Resolution

Reduce per-session buffer size.

Since most of such variables are dynamic, run in MySQL prompt:

> set global var =  value;

and also concrete these changes in the my.cfg or my.ini file used by MySQL server.

If show process list returns a large number of processes are in sleeping status.

Consider reducing the timeout values: wait_timeout and interactive_timeout and reduce the connection pool size managed by ems, wasp probe.