Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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     |
+-------------------+-------+
share|improve this question
    
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. –  Michael - sqlbot Sep 24 '13 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. –  Abe Miessler Sep 24 '13 at 18:33
    
How much memory was it using after the reboot, and how much, now, after running for a few hours? –  Michael - sqlbot Sep 24 '13 at 21:26
    
Immediately after it started: 191 used/1811 free. Now: 514 used/1488 free. –  Abe Miessler Sep 24 '13 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.

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/

share|improve this answer
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.

share|improve this answer
    
Based on the output from SHOW ENGINE INNODB STATUS -- "Total memory allocated 22114338" -- the buffer pool is already very small. –  Michael - sqlbot Sep 24 '13 at 21:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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