I have a MySQL instance running on my Ubuntu server that is eating up most of the memory on the machine. I currently have 2 GB of ram installed on the machine, and MySQL is using 1983 MB.

Most of my experience is with SQL Development so I'm a little unsure about how to go about freeing up memory. So far I've tried running FLUSH TABLES; but that didn't really do anything. I also ran some diagnostic queries, but am not sure what to make of them:


Total memory allocated 22114338; in additional pool allocated 1048576
Dictionary memory allocated 1061936
Buffer pool size   512
Free buffers       0
Database pages     510
Modified db pages  33
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 12944231, created 26985, written 3625935
2.17 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 976 / 1000

select sum(data_length+index_length) from information_schema.tables where engine='memory';
| sum(data_length+index_length) |
|                             0 |

Can anyone make any suggestions about how to free up memory and prevent MySQL from eating up all memory on the server with the information I have provided? If I haven't provided enough info here, any suggestions on what I should look at next to diagnose the problem?


SHOW STATUS LIKE 'threads%';
| Variable_name     | Value |
| Threads_cached    | 0     |
| Threads_connected | 31    |
| Threads_created   | 38    |
| Threads_running   | 1     |


SHOW VARIABLES LIKE 'thread_cache_size';
| Variable_name     | Value |
| thread_cache_size | 8     |
  • I'd be curious to see the output from SHOW STATUS LIKE 'threads%'; and SHOW VARIABLES LIKE 'thread_cache_size'; and you might want to mention the specific version of MySQL Server. Sep 24, 2013 at 2:56
  • Hey @Michael-sqlbot, I updated my question with the requested information. FYI I rebooted last night to free up memory so these queries were run after that. Sep 24, 2013 at 18:33
  • How much memory was it using after the reboot, and how much, now, after running for a few hours? Sep 24, 2013 at 21:26
  • Immediately after it started: 191 used/1811 free. Now: 514 used/1488 free. Sep 24, 2013 at 21:29

2 Answers 2


MySQL Community Manager Morgan Tocker posted a blog earlier this year on how to reduce memory consumption in MySQL.


Some of the settings he uses are not really realistic for a production instance of MySQL, because the settings are far below what it takes to give good performance. But his blog was an exercise, not a recommendation. Anyway, it can show you many of the settings that do have an effect on memory use.

Your server with only 2GB of RAM is pretty undersized for a MySQL server. Consider this survey of MySQL users, asking how much RAM they have in their servers. The top answers were 16-64 GB and 4-16 GB.


Show variables like 'innodb_buffer_pool_size';

You can tweak this value in your cnf to be a bit lower. Changes to this will require a server restart.

In a perfect world, though, you'd want your buffer pool size to be large enough to hold all your innodb tables in memory for fast performance. Of course this isn't always feasible.

  • Based on the output from SHOW ENGINE INNODB STATUS -- "Total memory allocated 22114338" -- the buffer pool is already very small. Sep 24, 2013 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.