MySQL Bugs: #13426: mysqld stop forking

Bug #13426	mysqld stop forking
Submitted:	23 Sep 2005 13:17	Modified:	29 Sep 2005 10:32
Reporter:	Luc Begault	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.24 (debian package)	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Using mysql-server debian package in production, at some time, the mysql server isn't forking anymore. Still it continue to answer request using existing threads, but seem to be unable to create new ones. It seems that this append after a pid recycling but it is not systematic.
Normally, we have around 80 mysql process running, facing the problem, it drops to less than 20 and the mysqld stop answering progressivly. I don't have any log error, the problem occur silently, nagios plugin is unable to detect it, so this is a big issue for service availability.
Regards,
Luc Begault.

How to repeat:
Don't seems to be really repeatable.

Thank you for a bug report, but we need much more information to figure out the reasons if you can't provide a way to repeat it.

Forst of all, have you tried newer, 4.0.26 version of MySQL? I'd recommend you to download it frm our site and use instead of the bundled one...

Please, send the my.cnf content, top results when forking is stopped, ps -elf (list of all processes with SZ column) results at the same time. The results of ulimit -a command performed by the user running mysqld will be of some use too, as well as a detailed description of your hardware (RAM) and linux kernel version.

We use packaged version of mysql for sarge and it can be a big issue for us to get out of the debian package system.

This probleme occured on two server:
A 2 GB dual P III 1.3 Ghz and a 4 GB dual xeon 3.06 Ghz. The kernel are the same: a 2.4.27 compiled from debian sources package.

ulimit -a give the same results on each server:
core file size        (blocks, -c) 0
data seg size         (kbytes, -d) unlimited
file size             (blocks, -f) unlimited
max locked memory     (kbytes, -l) unlimited
max memory size       (kbytes, -m) unlimited
open files                    (-n) 1024
pipe size          (512 bytes, -p) 8
stack size            (kbytes, -s) 8192
cpu time             (seconds, -t) unlimited
max user processes            (-u) unlimited
virtual memory        (kbytes, -v) unlimited

We are waiting for the next crash in order to gives you more informations.

While waiting for the next crash, please, send the my.cnf content for me to be able to ask additional questions, if needed.

cat /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "/var/lib/mysql/my.cnf" to set server-specific options or
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
[client]
#password       = my_password
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
err-log         = /var/log/mysql/mysql.err
socket          = /var/run/mysqld/mysqld.sock
nice            = -5

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
# Both location gets rotated by the cronjob.
#set-variable    = err-log=/var/log/mysql/mysql.err
#log            = /var/log/mysql.log
log             = /var/log/mysql/mysql.log
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
#
# The skip-networking option will no longer be set via debconf menu.
# You have to manually change it if you want networking i.e. the server
# listening on port 3306. The default is "disable" - for security reasons.
#skip-networking
key_buffer              = 200M
max_allowed_packet      = 16M
thread_stack            = 128K
#
# Query Cache Configuration
#
#query_cache_limit      = 1048576
query_cache_size        = 50M
query_cache_type        = 1
max_connections         = 250

table_cache             = 512
#
# Here you can see queries with especially long duration
log-slow-queries        = /var/log/mysql/mysql-slow.log
#
# The following can be used as easy to replay backup logs or for replication
#server-id              = 1
#log-bin                = /var/log/mysql/mysql-bin.log
#binlog-do-db           = include_database_name
#binlog-ignore-db       = include_database_name
#
# Read the manual if you want to enable InnoDB!
skip-innodb
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# If you want to enable SSL support (recommended) read the manual or my
# HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

cpu graph during crash

Attachment: cpu.png (image/png, text), 7.41 KiB.

network graph during crash

Attachment: network.png (image/png, text), 7.39 KiB.

Hi,
we have experienced yesterday a new crash on the 4GB server. I just attached to this bug the ps -elf output and the top output (just a bit cleaned to remove user names and ip addresses). In addition, you can find cacti graph for this server taken just after the crash if it can help.
Thanks again for the time you take helping us.

Thank you for the additional information. But if it was a crash, is there anything in the error log? I'd appreciate the log content for the appropriate period.

Ok after manual searching in the 700 MB log file (!), I finnaly found a valuable reason of the mysql crash: it was a huge request (around 50 console page). 
You can close the bug and I'am really sorry for the time spent on a non-mysql problem.