Why MySQL is not releasing the memory ?

@florianbrgnr,

The output you attached doesn’t raise any flags for me so the next thing I would recommend is that you check out the SQL load on the system. One thing that could be happening is expensive queries are utilising memory to complete their tasks and this memory is not reflected as returned to the system however it’s still available for processes to use should it be required. The problem of OOM killer is that the (expensive) SQL queries will continue to arrive and push the server towards using swap or the OOM Killer will kill the process using the most memory which will almost always be MySQL.

There’s another utility you can use to analyse the MySQL Slow Log file that will give you some insight into your expensive queries. My advice is to spend some time tuning the worst offenders so you can reduce the memory needed to handle them.

$> wget percona.com/get/pt-query-digest
$> chmod +x ./pt-query-digest
$> ./pt-query-digest {mysql slow log file path} > pt-query-digest.report.txt

The result of which will have a summary of queries ranked by load that you can analyse by using the EXPLAIN command.

e.g.

mysql> EXPLAIN select * from user ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |  474 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Good luck!

Hello @florianbrgnr,

I’d like to offer some further advice here if I may. It’s hard to diagnose things exactly based on the detail provided but I hope I can add some value here.

MySQL thrives on caching data in memory but there are also some per-thread buffers that can cause some issues if your SQL is not optimal. Due to the fact your memory is shared between 2 critical processes we might experience OOM killer caused by normal activity on the host.

You can get a better picture of what’s going on with the server by using a utility to summarise resource utilisation.

Percona’s pt-summary and pt-mysql-summary are a couple of programs that you can use to check what’s going on.

$> wget percona.com/get/pt-summary
$> chmod +x ./pt-summary
$> ./pt-summary > pt-summary.report.txt

$> wget percona.com/get/pt-summary
$> chmod +x ./pt-mysql-summary
$> ./pt-summary > pt-mysql-summary.report.txt

Are your crashes predictable? Are there any imports or long running jobs ongoing when your instance is killed by the OS? If you can capture the output of SHOW PROCESS LIST on the instance when the symptoms are in the 11th hour and post here.

If you run the two utilities (they are open source Perl scripts so feel free to check them out prior to running on your server) and post the results here I’ll check them out.

BR

Andmoo

Hi @florianbrgnr,

It’s possible your thread or query cache size actually cause the problems.

Can you try and add the following to your my.cnf file

thread_cache_size = 10
query_cache_size = 50M

Once these values have been configured, go ahead and restart MySQL.

One last thing, which MySQL version are you using?

Kind regards, KDSys