Mysql Server 5.6 Mysqld high memory consumption

configurationmemorymysql-5.6windows

I have MySQL Server 5.6 installed in a development machine with these specs:

RAM: 4 GB
Windows 7 Professional 64 bits
Intel Pentium CPU G630 @ 2.70 GHZ

Since I installed the MySQL Server, the process mysqld.exe is consumpting 433 MB.

What I want to achieve is to reduce this consumption as much as possible.

What I have tried

I read some posts about adding some parameters to the my.ini, but inside the MySQL directory I have a file called my-default.ini so I added the following properties inside:

[mysqld]

# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K
performance_schema = off

Also I ran these commands to flush the queries cache:

FLUSH QUERY CACHE;
RESET QUERY CACHE;

All these attempts have not reduced an Megabyte, it stills consumption 433 MB.

Thanks in advance

Best Answer

Which version are you using? 5.6.?

I did however came across this bug, hopefully it helps.

http://bugs.mysql.com/bug.php?id=68287

Related Solutions

MySQL Server > 90% memory consumption

Well, having 10% free of big RAM is still a lot of space, but MySQL memory usage should be fairly predictable. You can check your config with http://www.mysqlcalculator.com/ or similar tool.

You don't want it to get to swap or OOM if swap is disabled, so tune it to use less than total RAM, maybe keep it under 90% for mysql and normal system usage. Then if it gets over 90, you actually know something is off (maybe some OS service memleak or similar stuff), but you still have few GB to delay real trouble.

MySQL connection timeout on connection spike

What caught my eye immediately was the following

sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 32M
join_buffer_size = 128M

The sum of these buffer sizes is 176M.

This would be the maximum amount of RAM used per connection.

If you multiply that by your max_connections (61440) you get

11331331686400 Bytes
11065753600 KB
10806400 MB
10553.125 GB
10.3057861328125 TB

This means that you would need to have 10.5 TB of RAM to accommodate 61440 open connections.

I have mentioned this formula before

May 09, 2013 : Should I increase max_connections in AWS RDS t1-micro for MySQL?
Mar 28, 2014 : MySQL not releasing memory

Here is a query you can run to see these metrics

SELECT REPLACE(var,'Total',CONCAT('Total for max_connections=',maxconn)) var,
LPAD(CONCAT(FORMAT(mcm/POWER(1024,exp),2),' ',
IF(ELT(exp,'KB','MB','GB','TB','PB')='','B',
ELT(exp,'KB','MB','GB','TB','PB'))),21,' ') "Max Connection Memory"
FROM (SELECT var,mcm,FLOOR(LOG(mcm)/LOG(1024)) exp
FROM (SELECT var,buffer_sizes * maxconn mcm
FROM (SELECT IFNULL(LCASE(variable_name),'Total') var,
SUM(variable_value) buffer_sizes
FROM information_schema.global_variables
WHERE variable_name IN
('join_buffer_size','sort_buffer_size',
'read_buffer_size','read_rnd_buffer_size')
GROUP BY variable_name WITH ROLLUP) AAA,
(SELECT variable_value maxconn
FROM information_schema.global_variables
WHERE variable_name IN ('max_connections')) BBB) AA) A,
(SELECT variable_value maxconn
FROM information_schema.global_variables
WHERE variable_name IN ('max_connections')) B;

You can run this one to test out changing max_connections (Example: What if you wanted to lower max_connections to 76):

SET @conn = 76;
SELECT REPLACE(var,'Total',CONCAT('Total for max_connections=',maxconn)) var,
LPAD(CONCAT(FORMAT(mcm/POWER(1024,exp),2),' ',
IF(ELT(exp,'KB','MB','GB','TB','PB')='','B',
ELT(exp,'KB','MB','GB','TB','PB'))),21,' ') "Max Connection Memory"
FROM (SELECT var,mcm,FLOOR(LOG(mcm)/LOG(1024)) exp
FROM (SELECT var,buffer_sizes * maxconn mcm
FROM (SELECT IFNULL(LCASE(variable_name),'Total') var,
SUM(variable_value) buffer_sizes
FROM information_schema.global_variables
WHERE variable_name IN
('join_buffer_size','sort_buffer_size',
'read_buffer_size','read_rnd_buffer_size')
GROUP BY variable_name WITH ROLLUP) AAA,
(SELECT @conn maxconn) BBB) AA) A,
(SELECT @conn maxconn) B;

YOUR CURRENT SETTINGS

Let's compare how much RAM can potentially be consumed given your current settings

Look at your InnoDB Buffer Pool (innodb_buffer_pool_size). It's 160G.
Look at your MyISAM Key Cache (key_buffer_size). It's 2048M or 2G.
256G - 160G - 2G = 94G available to the OS
94G / 176M (per connection) = 546 Connections
You will never reach 546 connections because mysqld is competing with the OS for RAM

SUGGESTIONS

You need to lower your max_connections so that it does not eat the 94G of leftover RAM.
You may need to consider lowering join_buffer_size if your joins are fast.
You could probably lower read_buffer_size and read_rnd_buffer_size if you are not reading from MyISAM tables.