MySQL memory usage issues?

Question

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:

SHOW ENGINE INNODB STATUS;

----------------------
BUFFER POOL AND MEMORY
----------------------
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?

UPDATE:

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

and

SHOW VARIABLES LIKE 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 8     |
+-------------------+-------+

Answer 1

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

http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html

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.

http://www.mysqlperformanceblog.com/2012/11/02/how-much-memory-do-you-use-to-run-mysql/

Answer 2

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.