MySQL Bugs: #13555: running out of memory, freezing

Bug #13555	running out of memory, freezing
Submitted:	28 Sep 2005 9:13	Modified:	28 Sep 2005 9:26
Reporter:	Paul Hussein	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	mysql-server-4.1.10a-2.RHEL4.1	OS:	Linux (centos 4.1)
Assigned to:		CPU Architecture:	Any

Description:
I am using mysql as a backend database for jakarta slide. From the default installation I have only modified the location of the database, and added 

default-character-set=utf8
max_allowed_packet=1000M

to my.cnf

When some files are accessed in jakarta slide, and this items accessed in the database, sometimes the virtal and actual memory for mysqld starts to rise in a very short time from 101MB or tereabouts to 1.2GB. Then the mysql.log shows InnoDB failing to allocate memory and the database slows to a grind failing some queries.

If I restart the database all is fine.

Also if I issue a mysqldump
 mysqldump  --quick --user=root --password=valeria --all-databases --socket=/home/mysql/mysql.sock -O max_allowed_packet=1000M > /data/alldatabases.sql

I see the same behaviour with mysqldump, where the memory grows for the mysqldump process, it locks for a while. Then the mysqldump process dies, and everything returns to normal.

The machine is a dual processor xeon with 1GB memory and 2GB swap.
==================================================================
[root@devserver ~]# 
The Kernel is a custom 2.6.12 kernel based on the centos original kernel.
uname -a
Linux devserver 2.6.12 #1 SMP Mon Aug 1 17:31:30 CEST 2005 i686 i686 i386 GNU/Linux
=============================================================
/etc/my.cnf
[mysqld]
datadir=/home/mysql
socket=/home/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-character-set=utf8
max_allowed_packet=1000M

[mysql.server]
user=mysql
basedir=/home

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
==============================================================
[root@devserver ~]# su - mysql
-bash-3.00$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
pending signals                 (-i) 8191
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
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 8191
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
-bash-3.00$

How to repeat:
Install jakarta slide with mysql support

Upload some large documents, e.g. 200MB and more

run mysqldump

> max_allowed_packet=1000M
...
> The machine is a dual processor xeon with 1GB memory and 2GB swap.

well, what do you expect when allowing the packet buffer to take *all*
your RAM when large BLOB fields are transfered?

configuration problem, not a bug

Well, if thats so I dont undestand this behaviour

I changed /etc/my.cnf to be 

[mysqld]
datadir=/home/mysql
socket=/home/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-character-set=utf8
max_allowed_packet=300M

And issued the command 
 mysqldump -q --quick --user=root --password=xxx slide --socket=/home/mysql/mysql.sock -O max_allowed_packet=300M >slide.sql

top - 14:17:56 up 7 days,  3:23,  4 users,  load average: 1.93, 2.22, 1.78
Tasks: 138 total,   1 running, 136 sleeping,   1 stopped,   0 zombie
Cpu(s):  0.2% us,  0.3% sy,  0.0% ni, 70.7% id, 28.7% wa,  0.2% hi,  0.0% si
Mem:   1034572k total,   683852k used,   350720k free,    31960k buffers
Swap:  2048276k total,  1437928k used,   610348k free,    24036k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
24247 mysql     16   0 1331m 496m 1532 S  1.3 49.2   1:40.02 mysqld
 6800 powordoc  17   0 1310m 8164 1356 S  0.0  0.8  26:29.86 java
 3832 ntp       16   0  4572 4572 3528 S  0.0  0.4   0:00.26 ntpd
 8940 root      25  10 32744 4552 2660 S  0.0  0.4   0:35.64 rhn-applet-gu

The virtal memory for mysqld is 1331M ? Is it normal for mysql to run in 1GB when dumping the database when normally it runs in 110MB?

Cheers

Paul.