patternsqlMinor
MySQL memory usage issues?
Viewed 0 times
issuesusagemysqlmemory
Problem
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
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:
and
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 |
+-------------------+-------+Solution
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/
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/
Context
StackExchange Database Administrators Q#50375, answer score: 2
Revisions (0)
No revisions yet.