MySQL memory issue

rameshvs02 · December 30, 2020, 4:20pm

Hi Team,

One of my MySQL server process is using more memory (around 75% resident memory) from RAM.

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7622 mysql 15 0 32.8g 27g 5510 S 10.0 76.6 55493:27 mysqld

Server configuration

36GB RAM, 64 bit Linux OS, 16 CPU.

MySQL configuration and status

innodb_buffer_pool_size = 6 GB
innodb_log_buffer_size=8MB
join_buffer_size=126KB
key_buffer_size=8MB
read_buffer_size=126KB
read_rnd_buffer_size=256KB
sort_buffer_size= 2MB
Connections at a time = 500 (approx)
Query per Second = 4500 (approx)
Data size = 32 GB (approx)

Most of the tables are in InnoDB engine and the threads are accessing InnoDB tables. When I was looking Innodb status there is lack of free pages in buffer pool.

Is that the reason for using more memory from for MySQL Daemon ?
If I re-size innodb_buffer_pool_size to 16GB, do I get performance improvement ?

Total memory allocated 6593445888; in additional pool allocated 0
Dictionary memory allocated 2092044
Buffer pool size 393215
Free buffers 1
Database pages 388677
Old database pages 143456
Modified db pages 42657

Please suggest how to resolve this memory issue…

Let me know if there is any extra info required.

regards
ramesh

niljoshi · December 30, 2020, 4:29pm

Hi Ramesh,

I don’t see exactly a memory problem there. InnoDB is using all the available innodb buffer pool and that’s good. buffer pool is used for caching pages, adaptative hash, change buffer and so on. so, if you give mysql X gb of ram it will try to use it all, that variable and the innodb log file size are the two most important parameters for innodb. The usual advice for the buffer pool is to use 80% of the memory, but only if the server is dedicated.

Increasing the buffer pool should increase the performance, but you might see a much larger use of memory on the top output but it should be okay. The most important thing is, it should not swap.

If you are searching reason to use more memory by MySQL it can be because of other sessions variables as you have 500 connections at a time. sort_buffer_size= 2MB and Query per Second = 4500. imagine that only 50% of those queries needs the sort buffer. that’s 5GB more of ram needed.

So if possible provide output of pt-mysql-summary utility. http://www.percona.com/doc/percona-toolkit/2.1/pt-mysql-summ ary.html. It can help us to troubleshoot this issue.

rameshvs02 · December 30, 2020, 4:29pm

Hi Nil,

Thanks for the quick update on this issue.

PFA pt-mysql-summary output.

The maximum possible mysql memory usage in this server is 11.4G.

Calculation as follows

Per-thread memory

per_thread_buffers = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size

max_total_per_thread_buffers = per_thread_buffers * Max_used_connections

Server-wide memory

Global memory

max_used_memory = server_buffers + max_total_per_thread_buffers

but in TOP mysqld RES is 27G