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

[22 Mar 2010 11:58] Vaali ---
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]
[22 Mar 2010 12:24] Valeriy Kravchuk
Please, upload your my.cnf file content. Is it 32-bit or 64-bit Linux and MySQL server?
[22 Mar 2010 12:39] Vaali ---
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
[22 Mar 2010 13:12] Valeriy Kravchuk
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.
[22 Mar 2010 13:18] Vaali ---
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
[22 Mar 2010 13:50] Vaali ---
Could you plz tell me what values should I change not to get this error?

Regards,
Reddy
[22 Mar 2010 14:50] Valeriy Kravchuk
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.
[21 Sep 2010 17:21] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=56904 marked as duplicate of this one.