MySQL Bugs: #46382: MySQL reports running out of memory

Bug #46382	MySQL reports running out of memory
Submitted:	25 Jul 2009 7:33	Modified:	27 Aug 2009 7:46
Reporter:	Database Administrator	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.35	OS:	Other (Centos 5)
Assigned to:		CPU Architecture:	Any
Tags:	MySQL 5.1.35

Description:
Recently we have upgraded our database from MySQL 5.1.17 to MySQL 5.1.35-log, after 4 days it starts reporting that it is running out of memory.
Given below is a snapshot from error log

090724 12:00:38 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2064880 bytes)
090724 12:00:47  InnoDB: Error: cannot allocate 1064960 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 2337229786 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
090724 12:00:47 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1682160 bytes)

We have 4 GB box running with Centos 5.Why MySQL is reporting such error? Please find the output of ulimit

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

Our MySQL server is handling nearly 25 queries per second and 80% of the tables are INNODB. We get daily 30-40 queries in slow log and no query will take more than 20 seconds.

I have attached our my.cnf.Please help its urgent.

How to repeat:
Don't know.

Suggested fix:
Sorry,Don't know.

Content of My.cnf

Attachment: my.txt (text/plain), 4.19 KiB.

Thank you for the problem report. Please, send the results of:

uname -a

from your system. I want ot check if you run 32-bit OS.

Dear Valeriy Kravchuk,

Thanks for your prompt reply.Please find the output of uname -a

uname -a
Linux dbloans.gnsaindia.com 2.6.18-8.el5 #1 SMP Thu Mar 15 19:57:35 EDT 2007 i686 i686 i386 GNU/Linux

So this is 32-bit Linux... Please, try to set

innodb_buffer_pool_size = 1800M

in my.cnf and start your server again. You can not get continuous memory allocation of 2+ G on 32-bit Linux.

Hi Valeriy Kravchuk,

Thanks for your reply. I have managed to make the database online by changing the configuration file shipped with the MySQL rpm package. I have attached that config file. Also innodb_buffer_pool_size is set a 2GB in that file and the server is running for nearly 3 days without reporting out of memory. These are all the notable changes made in first my.cnf file while the server reports out of memory.

innodb_data_file_path = ibdata1:50M:autoextend 
innodb_file_per_table=1 
innodb_open_files=600

Are the above parameters can be considered as suspect?

Present My.cnf

Attachment: New My.txt (text/plain), 20.06 KiB.

This one:

innodb_file_per_table=1 

may lead to increased internal InnoDB's "data dictionary" in memory maybe if you have a lot of InnoDB tables. But with this being set and innodb_buffer_pool_size=1800M you should be safe enough anyway. Please, check.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".