MySQL Bugs: #52272: Unable to execute SQL (Out of memory (Needed XXXXXXX bytes))

Bug #52272	Unable to execute SQL (Out of memory (Needed XXXXXXX bytes))
Submitted:	22 Mar 2010 11:58	Modified:	22 Mar 2010 14:50
Reporter:	Vaali ---	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	1	OS:	Linux (out of memory error)
Assigned to:		CPU Architecture:	Any
Tags:	out of memory

Description:
Hi All,

From past two months, we are getting Unable to execute SQL (Out of memory (Needed XXXXXXX bytes)) on our database server.
[This problem occurs when all(12) processes are using the database at a time]

We have 4 GB RAM and high configuration DB server.

Error message:
TERMINATED: Unable to execute SQL (Out of memory (Needed 1342176 bytes))

I did run the following commands on db.system.com:

[root@db01 ~]# sar 2 100
Linux 2.6.18-92.1.22.el5 (db.system.com)       02/24/2010

09:57:09 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle
09:57:59 AM       all    100.00      0.00      0.00      0.00      0.00      0.00
09:58:01 AM       all     99.75      0.00      0.25      0.00      0.00      0.00
09:59:45 AM       all    100.00      0.00      0.00      0.00      0.00      0.00
09:59:47 AM       all     99.00      0.00      1.00      0.00      0.00      0.00
10:00:31 AM       all    100.00      0.00      0.00      0.00      0.00      0.00
10:00:33 AM       all    100.00      0.00      0.00      0.00      0.00      0.00
10:00:36 AM       all     99.44      0.00      0.37      0.00      0.00      0.19
10:00:38 AM       all     99.76      0.00      0.24      0.00      0.00      0.00

[root@db01 ~]# top
Mem:   3631864k total,  3506384k used,   *125480k* free,    57192k buffers
Swap: 16777208k total,      636k used, 16776572k free,   463312k cached

PID  USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
2306 mysql     15   0 2853m 2.6g 4620 S  *200* 74.6  10714:01 mysqld 

Could you please let me know how to fix this issue?

Thanks very much.

Reagrds,
Reddy

How to repeat:

[This problem occurs when all(12) processes are using the database at a time]

Please, upload your my.cnf file content. Is it 32-bit or 64-bit Linux and MySQL server?

Hi Valirey,

Thanks very much for your prompt response :-)

It's 32 bit and mysql version is 5.1.3

my.cnf:
--------

#BEGIN CONFIG INFO
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries
#TYPE: SYSTEM
#END CONFIG INFO

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]

port            = 3306
socket          = /var/lib/mysql/mysql.sock

back_log = 50
max_connections = 100
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M

max_heap_table_size = 64M
sort_buffer_size = 8M

join_buffer_size = 8M

thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 32M
query_cache_limit = 8M
ft_min_word_len = 4
#default_table_type = MYISAM
thread_stack = 192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 64M
binlog_format=mixed

slow_query_log_file = /apps/mysql/logs/slow_query_log

long_query_time = 2

log_long_format

tmpdir = /tmp

server-id = 1

key_buffer_size = 32M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1

myisam_recover

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table
innodb_data_home_dir      = /apps/mysql/data
innodb_log_group_home_dir = /apps/mysql/logs
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 4096

Regards,
Reddy

32-bit MySQL server on 32-bit OS can use 3G of memory at best. You ask 2G only for InnoDB buffer pool:

innodb_buffer_pool_size = 2G

Your per-thread buffers (and temporary tables that will be created in memory) are too big for this setting, 32-bit environment and up to 100 concurrent connections. So, this problem is more likely related to improper my.cnf settings than to any bug in MySQL server.

Hi Valeriy,

Then could you please let me know what all the parameters do I need to change in my.cnf file to fix this issue?

Regards,
Saidi

Could you plz tell me what values should I change not to get this error?

Regards,
Reddy

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

I'd start with setting:

max_heap_table_size = 8M # was 64M
sort_buffer_size = 1M # was 8M
join_buffer_size = 2M # was 8M
tmp_table_size = 8M # was 64M
read_buffer_size = 1M # was 4M
read_rnd_buffer_size = 2M # was 16M

or even lower and seriously think about using 64-bit OS and MySQL server.

More specific recommendations will require study of your server's typical load and status, and definitely fall into the area of "free support" that I can not provide in frames of bugs system. So, let's stop at this point.

Thank you for your interest in MySQL.

Bug: http://bugs.mysql.com/bug.php?id=56904 marked as duplicate of this one.