Out of Memory

MySQL & MariaDB Other MySQL® Questions
#1

Hello,

This is my first post here, after I have tried to search existing posts for an answer to my problem, and could not find it.

I have recently moved my database from a single 2GB RAM machine (that ran the entire LAMP), to a cluster configuration, in which the database is on its own machine with 4GB RAM, and also being replicated.

Since then, I started receiving a lot of “Out of Memory” errors in mysql error log. They come in bulks, and at random times. Here is a sample:

[B]Quote:[/B]

080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1042860 bytes)
080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:02 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 5:20:04 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:04 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:33 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:33 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:42 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:20:01 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:20:01 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:27:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 15:52:20 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 15:52:20 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 16:01:19 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 16:01:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 16:01:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 16:01:23 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)

One time they also caused the server to crash and failover to the slave. Note, that it seems the process only needed 1M of allocation before it terminated…

At first I thought it had to do with the OS being a 32-bit (rhel4), and the mysqld process larger than 2GB - I had innodb_buffer_pool_size of 2000M - so I reduced it to 1000M, and I see that mysqld is under 2GB (at the time of writing: VIRT - 1855m, RES - 1.4g), but I am still getting these errors.

I also tried to isolate a specific query that does it, so I turned on query logging with error messages, and I see that several queries cause this.

Here is my.cnf:

[B]Quote:[/B]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking

max_connections = 530
default-character-set = utf8
max_allowed_packet = 16M

query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

key_buffer_size = 256M
max_seeks_for_key = 500

Try number of CPU’s*2 for thread_concurrency

thread_concurrency = 4
thread_cache_size = 16

table_cache = 2048

tmp_table_size = 256M
max_heap_table_size = 257M

ft_min_word_len = 3

sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 2M

innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 20M

innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

log-slow-queries=slow-log
long_query_time=1

log-bin=db-bin-log
server-id = 1
slave-skip-errors=126,1062

Any help would be greatly appreciated.
Or

#2

Is /proc/sys/vm/overcommit_memory set to 0?

If not, I believe it’s a setting in /etc/sysctl.conf on RedHat (I’m not a Redhat user). Make sure it’s set to 0 if it’s not. You can also change the value until next reboot by running “echo 0 > /proc/sys/vm/overcommit_memory” and see if it solves the problem.

#3

It is set to 0:

cat /proc/sys/vm/overcommit_memory0

I forgot to mention, this is mysql 5.0.45.

Also, now the server from time to time gets huge loads (over 100!), mysqld is burning cpu on close to 200%, and I get over 95% IO waits, and temporary tables start to pile up in /var/lib/mysql… I am not sure if this problem is connected, or an entirely new problem.

Or

#4

The change in the machine was from OEM server with SATA drives to a HP with RAID10.

The problems could be connected as you say, however the memory problems have been going on for a month (since the machine switching), but the IO problems are brand new (started today).

More info that might help, is when this IO load occured, I restarted mysql, and upon restart I get this:

[B]Quote:[/B]

080115 14:26:34 mysqld started
080115 14:26:35 InnoDB: Started; log sequence number 8 1135678335
080115 14:26:35 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.0.45-community-log’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Edition (GPL)
080115 14:27:42 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:43 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:44 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:45 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:46 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:47 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:47 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:51 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:53 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:28:02 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080115 14:28:05 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)

Memory errors as soon as it starts up? Weird no?

Or

#5

That is indeed.

Can you paste in my.cnf and the output of “show status;”?

I’m not an expert, but another set of eyes never hurts.

#6
[B]orensol wrote on Tue, 15 January 2008 09:01[/B]
[B]Quote:[/B]

080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1042860 bytes)

[snip]

080113 16:01:19 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 16:01:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 16:01:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 16:01:23 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)

One time they also caused the server to crash and failover to the slave. Note, that it seems the process only needed 1M of allocation before it terminated…

This looks like a new connection, but you have no memory left. As you are running an 32bit OS you AFAIK cant use all the 4GB. Anyway habe a look at your system also.
Something like top or vmstat look into /var/log/messages.
Type into your mysql-client “show global status like ‘Threads_connected’” next time it happens. “Show status like ‘Max_used_connections’” should give you a good clue.
etc.

#7
[B]Quote:[/B]

USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
mysql 15 0 196 404:07.09 34.7 1811m 1.4g 4688 S mysqld

mysqld is burning the cpu (196%!!)… How can I reduce cpu usage? Maybe I should reduce number of threads?

Or

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.