Mysql memory consumption is constantly growing

Question

Help me figure out what my mistake is. I'm trying to set up a mysql 5.7 server in which 90% of requests are very large requests (2-10 MB) in which inner join, union all, case then, etc. From the main one - there are 3 tables for 50k records. One contains 280 columns, the other 2 by 20-30 - all double fields except two fields (id int and foreign key to the table) Requests are executed in a loop and can be up to 100 requests per second.

The server has 3.7 memory, of which 2.5 is free. You need to count on this amount. But within a few hours mysql starts to consume more and more memory and runs into the server memory limits and crashes with an out memory error

KiB Mem 3880296	total 133132	free 2993828	used, 753336	buff/cache
KiB Swap	0	total 0	free 0	used 462208

PID	USER	PR	NI	VIRT	RES	SHR	S	%CPU	%MEM	TIME+	COMMAND
4667	mysql	20	0	3441964	2.1g	6980	S	130.0	55.8	132:33.69	mysqld
4696	apache	20	0	12.2g	681312	13492	S	61.3	17.6	125:27.81	dotnet

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
nice = 0
[mysqld]
datadir=/var/lib/mysql
tmpdir  = /tmp
lc-messages-dir = /usr/share/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv = "/var/www/html/xg"
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
low-priority-updates
log-error=/var/log/mysqld_error.log
pid-file=/var/run/mysqld/mysqld.pid
bind-address=127.0.0.1
lower_case_table_names=1
explicit_defaults_for_timestamp = 1
innodb_buffer_pool_size = 650M
innodb_log_file_size = 32M
key_buffer_size = 256M
query_cache_size = 0
query_cache_limit = 0
thread_cache_size = 8
max_connections = 100
innodb_lock_wait_timeout = 100
performance_schema = 0
table_cache = 0
table_definition_cache = 0

score 0 · Answer 1 · answered Aug 05 '21 at 11:47

Consider these changes to your my.cnf [mysqld] section to improve performance

innodb_buffer_pool_size=1G  # from 650M to reduce innodb_buffer_pool_reads count
thread_cache_size=32  # from 8 to reduce threads_created count (conserve CPU cycles)
table_cache=200  # from 0 to reduce opened_tables count
innodb_open_files=200  # from ? - should always match table_cache value in cfg
table_definition_cache=100  # from 0 to reduce opened_table_definitions count

Provide comments, please after implementation.

Mysql memory consumption is constantly growing

Disabling symbolic-links is recommended to prevent assorted security risks

1 Answers1