Solved

MySQL server running out of memory, could be a bug?

Posted on 2010-11-12
4
1,081 Views
Last Modified: 2012-05-10
I need to resolve this problem ASAP. Recently I started receiving these error messages (as recorded in /var/log/mysqld.log):

101112 17:48:04 /usr/sbin/mysqld: Out of memory (Needed 41 bytes)
101112 17:58:04 /usr/sbin/mysqld: Out of memory (Needed 8160 bytes)

The problem is, I am quite sure there is enough memory.

my.cnf:

-------
[mysqld]
max_connections = 100
safe-show-database
#log-bin = /var/log/mysql/mysql-bin.log
#binlog-do-db = icloaker_maintenance
server-id=1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 1
key_buffer_size = 128M
table_cache = 2048
sort_buffer_size = 1M
read_buffer_size = 1M
max_allowed_packet = 16M
tmp_table_size = 300M
max_heap_table_size=64M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
thread_cache_size = 16
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 32M
query_cache_type = 1
innodb_buffer_pool_size = 128M
#innodb_log_file_size = 64M
innodb_additional_mem_pool_size = 20M
innodb_flush_log_at_trx_commit = 2


[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
--------

Using an application called mysqltuner, I found that mysql is allowed to use roughly 823MB now, while the standard useage is just 460M and there do not seem to be spikes. There are very few concurrent connections according to it too.

The server has enough memory, it is the only VPS on a dedicated server with 2GB RAM.

Memory stats on the hardware node, as reported by "top -M":

Mem:  1975.121M total, 1794.250M used,  180.871M free,   73.145M buffers
Swap: 4094.617M total,  152.000k used, 4094.469M free, 1117.539M cached

Memory stats on the VPS, as reported by "top -M":

Mem:  1975.121M total,  809.543M used, 1165.578M free,    0.000k buffers
Swap:    0.000k total,    0.000k used,    0.000k free,    0.000k cached

As you can see, the VPS is allowed to use 2GB RAM as it is the only running VPS on the dedicated server, it can get it easily without fighting for resources, although it could take a small part from the HW node swap if necessary too.

This server has been running OK for a long time although recently I optimized my.cnf to resolve a similar problem but yesterday it got back.

Both HW node and VPS server are running on CentOS 5 64-bit.

Any ideas are more than welcome!


0
Comment
Question by:tomfra
  • 3
4 Comments
 

Author Comment

by:tomfra
ID: 34122597
I suspect a problem here, result of "ulimit -a":

ulimit -a

-------
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 16126
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) 524288
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16126
virtual memory          (kbytes, -v) 524288
file locks                      (-x) unlimited
-------

The max memory size and virtual memory look too low to me. What do you think? And how to increase those values?
0
 

Author Comment

by:tomfra
ID: 34123053
OK, I think I need to increase the "max memory size" and "virtual memory" for the mysql user so I need to put it into sysctl.conf. What are the names of the switches? I need the names for the "ulimit -m" and "ulimit -j" switches, but cannot find them.
0
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 34128227
You can add the ulimit -m 1048576 in the mysql start script.

Do you have phpmyadmin installed on the web server?  It can suggest the remedy as well based on query and mysql statistics.

Your
sort_buffer_size = 1M
read_buffer_size = 1M
max_allowed_packet = 16M
tmp_table_size = 300M
max_heap_table_size=64M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
thread_cache_size = 16

Might be too low double each parameter and see if the issue is pushed on.

Look in the various rc scripts for the settings:
/etc/rc /etc/rc.sysinit /etc/init.d/functions the settings you have are not set by default at installation but were configured.

see if any of these have the ulimit -m settings. when you elevate your rights sudo/su and run ulimit -a is the max memory size remains the same or changes to being unlimited?
0
 

Author Comment

by:tomfra
ID: 34236267
It seems that after I set the ulimit for the root user and restarted the server, it has become active for the mysql user too. So it seems to be OK now, although I will be monitoring the situation.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
move records one table to another 14 63
MySQL - Limit or Top Records 15 49
MYSQL/PHP inserting 1969-12-31 instead of NULL 12 49
mysql date time 14 27
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now