Bug #5745 mysqld leaves zombie threads ( mysqld <defunct> )
Submitted: 25 Sep 2004 10:42 Modified: 5 Jan 2005 16:34
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.4a-gamma-log OS:Linux (slackware-linux)
Assigned to: CPU Architecture:Any

[25 Sep 2004 10:42] [ name withheld ]
Description:
If mysqld has been restarted since boot (using /etc/rc.mysqld), every new connection leaves a dead zombie thread. (ps -A: mysqld <defunct>).

This relates to both PHP-connections and mysql monitor-connections.

If 25 connections has been made, there are 25 dead zombie threads.

In long run (40-50 dead threads?) mysqld cries about memory problems, 
and mysqld must be restarted.

Kernel is 2.4.22.
Using ReiserFS.

How to repeat:
1. See variables and processes below for setup. Use same OS and mysql version.
2. Restart mysqld, using /etc/rc.d/rc.mysqld - stop and start it.
3. Make a connection using mysql command from console.
4. Select a database, do a SELECT and then exit the monitor.
5. Do ps -A. 
6. Now you should see mysqld <defunct> in list.

I'm attaching "ps -lax", "ps -A" and SHOW VARIABLES, 

ps -lax:
4     0  2786     1   9   0  2068  936 wait4  S    pts/0      0:02 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/run/mysql/mysql.pid -
4    27  2814  2786   9   0 43524 8816 do_sel S    pts/0      0:14 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/ru
1    27  2825  2815   9   0     0    0 do_exi Z    pts/0      0:00 [mysqld <defunct>]
1    27  2828  2815   9   0     0    0 do_exi Z    pts/0      0:00 [mysqld <defunct>]
1    27  2830  2815   9   0     0    0 do_exi Z    pts/0      0:00 [mysqld <defunct>]
1    27  2833  2815   9   0     0    0 do_exi Z    pts/0      0:00 [mysqld <defunct>]

ps -A:
  PID TTY          TIME CMD
    1 ?        00:00:08 init
    2 ?        00:00:00 keventd
    3 ?        00:01:00 ksoftirqd_CPU0
    4 ?        00:01:17 kswapd
    5 ?        00:00:00 bdflush
    6 ?        00:00:47 kupdated
   10 ?        00:00:00 mdrecoveryd
   11 ?        00:00:00 kreiserfsd
   38 ?        00:00:01 kapmd
   95 ?        00:00:00 eth0
  113 ?        00:00:00 eth1
  172 ?        00:00:00 khubd
  188 ?        00:00:02 syslogd
  191 ?        00:00:00 klogd
  244 ?        00:00:00 inetd
  247 ?        00:00:15 sshd
  250 ?        00:00:04 dhcpd
  256 ?        00:00:02 crond
  258 ?        00:00:00 atd
  262 ?        00:00:00 apmd
  304 ?        00:00:05 httpd
  306 ?        00:00:00 httpd
  307 ?        00:00:03 httpd
  308 ?        00:00:05 httpd
  309 ?        00:00:01 httpd
  310 ?        00:00:00 httpd
  311 ?        00:00:00 smbd
  313 ?        00:01:36 nmbd
  326 tty1     00:00:00 agetty
  327 tty2     00:00:00 agetty
  328 tty3     00:00:00 agetty
  329 tty4     00:00:00 agetty
  330 tty5     00:00:00 agetty
  331 tty6     00:00:00 agetty
  360 ?        00:00:00 mount.smbfs
  461 ?        00:00:00 httpd
  462 ?        00:00:00 httpd
 2554 ?        00:01:09 noflushd
 2756 ?        00:00:15 sshd
 2758 pts/0    00:00:04 bash
 2786 pts/0    00:00:02 mysqld_safe
 2814 pts/0    00:00:15 mysqld
 2825 pts/0    00:00:00 mysqld <defunct>
 2828 pts/0    00:00:00 mysqld <defunct>
 2830 pts/0    00:00:00 mysqld <defunct>
 2833 pts/0    00:00:00 mysqld <defunct>
 2838 pts/0    00:00:05 jed
 2842 pts/0    00:00:01 ps

mysql> SHOW VARIABLES;
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| back_log                        | 50                         |
| basedir                         | /usr/                      |
| binlog_cache_size               | 32768                      |
| bulk_insert_buffer_size         | 8388608                    |
| character_set_client            | latin1                     |
| character_set_connection        | latin1                     |
| character_set_database          | latin1                     |
| character_set_results           | latin1                     |
| character_set_server            | latin1                     |
| character_set_system            | utf8                       |
| character_sets_dir              | /usr/share/mysql/charsets/ |
| collation_connection            | latin1_swedish_ci          |
| collation_database              | latin1_swedish_ci          |
| collation_server                | latin1_swedish_ci          |
| concurrent_insert               | ON                         |
| connect_timeout                 | 5                          |
| datadir                         | /var/lib/mysql/            |
| date_format                     | %Y-%m-%d                   |
| datetime_format                 | %Y-%m-%d %H:%i:%s          |
| default_week_format             | 0                          |
| delay_key_write                 | ON                         |
| delayed_insert_limit            | 100                        |
| delayed_insert_timeout          | 300                        |
| delayed_queue_size              | 1000                       |
| expire_logs_days                | 0                          |
| flush                           | OFF                        |
| flush_time                      | 0                          |
| ft_boolean_syntax               | + -><()~*:""&|             |
| ft_max_word_len                 | 84                         |
| ft_min_word_len                 | 4                          |
| ft_query_expansion_limit        | 20                         |
| ft_stopword_file                | (built-in)                 |
| group_concat_max_len            | 1024                       |
| have_archive                    | NO                         |
| have_bdb                        | NO                         |
| have_compress                   | YES                        |
| have_crypt                      | YES                        |
| have_csv                        | NO                         |
| have_example_engine             | NO                         |
| have_geometry                   | YES                        |
| have_innodb                     | YES                        |
| have_isam                       | NO                         |
| have_ndbcluster                 | NO                         |
| have_openssl                    | NO                         |
| have_query_cache                | YES                        |
| have_raid                       | NO                         |
| have_rtree_keys                 | YES                        |
| have_symlink                    | YES                        |
| init_connect                    |                            |
| init_file                       |                            |
| init_slave                      |                            |
| innodb_additional_mem_pool_size | 1048576                    |
| innodb_buffer_pool_awe_mem_mb   | 0                          |
| innodb_buffer_pool_size         | 8388608                    |
| innodb_data_file_path           | ibdata1:10M:autoextend     |
| innodb_data_home_dir            |                            |
| innodb_fast_shutdown            | ON                         |
| innodb_file_io_threads          | 4                          |
| innodb_file_per_table           | OFF                        |
| innodb_locks_unsafe_for_binlog  | OFF                        |
| innodb_flush_log_at_trx_commit  | 1                          |
| innodb_flush_method             |                            |
| innodb_force_recovery           | 0                          |
| innodb_lock_wait_timeout        | 50                         |
| innodb_log_arch_dir             |                            |
| innodb_log_archive              | OFF                        |
| innodb_log_buffer_size          | 1048576                    |
| innodb_log_file_size            | 5242880                    |
| innodb_log_files_in_group       | 2                          |
| innodb_log_group_home_dir       | ./                         |
| innodb_max_dirty_pages_pct      | 90                         |
| innodb_mirrored_log_groups      | 1                          |
| innodb_open_files               | 300                        |
| innodb_thread_concurrency       | 8                          |
| interactive_timeout             | 28800                      |
| join_buffer_size                | 131072                     |
| key_buffer_size                 | 8388600                    |
| key_cache_age_threshold         | 300                        |
| key_cache_block_size            | 1024                       |
| key_cache_division_limit        | 100                        |
| language                        | /usr/share/mysql/english/  |
| large_files_support             | ON                         |
| license                         | GPL                        |
| local_infile                    | ON                         |
| locked_in_memory                | ON                         |
| log                             | OFF                        |
| log_bin                         | ON                         |
| log_error                       |                            |
| log_slave_updates               | OFF                        |
| log_slow_queries                | OFF                        |
| log_update                      | OFF                        |
| log_warnings                    | 1                          |
| long_query_time                 | 10                         |
| low_priority_updates            | OFF                        |
| lower_case_file_system          | OFF                        |
| lower_case_table_names          | 0                          |
| max_allowed_packet              | 1048576                    |
| max_binlog_cache_size           | 4294967295                 |
| max_binlog_size                 | 1073741824                 |
| max_connect_errors              | 10                         |
| max_connections                 | 100                        |
| max_delayed_threads             | 20                         |
| max_error_count                 | 64                         |
| max_heap_table_size             | 16777216                   |
| max_insert_delayed_threads      | 20                         |
| max_join_size                   | 4294967295                 |
| max_length_for_sort_data        | 1024                       |
| max_relay_log_size              | 0                          |
| max_seeks_for_key               | 4294967295                 |
| max_sort_length                 | 1024                       |
| max_tmp_tables                  | 32                         |
| max_user_connections            | 0                          |
| max_write_lock_count            | 4294967295                 |
| myisam_data_pointer_size        | 4                          |
| myisam_max_extra_sort_file_size | 2147483648                 |
| myisam_max_sort_file_size       | 2147483647                 |
| myisam_recover_options          | OFF                        |
| myisam_repair_threads           | 1                          |
| myisam_sort_buffer_size         | 8388608                    |
| net_buffer_length               | 16384                      |
| net_read_timeout                | 30                         |
| net_retry_count                 | 10                         |
| net_write_timeout               | 60                         |
| new                             | OFF                        |
| old_passwords                   | OFF                        |
| open_files_limit                | 1024                       |
| pid_file                        | /var/run/mysql/mysql.pid   |
| port                            | 3306                       |
| preload_buffer_size             | 32768                      |
| protocol_version                | 10                         |
| query_alloc_block_size          | 8192                       |
| query_cache_limit               | 1048576                    |
| query_cache_min_res_unit        | 4096                       |
| query_cache_size                | 0                          |
| query_cache_type                | ON                         |
| query_cache_wlock_invalidate    | OFF                        |
| query_prealloc_size             | 8192                       |
| range_alloc_block_size          | 2048                       |
| read_buffer_size                | 131072                     |
| read_only                       | OFF                        |
| read_rnd_buffer_size            | 262144                     |
| relay_log_purge                 | ON                         |
| rpl_recovery_rank               | 0                          |
| secure_auth                     | OFF                        |
| server_id                       | 1                          |
| skip_external_locking           | ON                         |
| skip_networking                 | OFF                        |
| skip_show_database              | OFF                        |
| slave_net_timeout               | 3600                       |
| slow_launch_time                | 2                          |
| socket                          | /var/run/mysql/mysql.sock  |
| sort_buffer_size                | 2097144                    |
| sql_mode                        |                            |
| storage_engine                  | MyISAM                     |
| sync_binlog                     | 0                          |
| sync_frm                        | ON                         |
| system_time_zone                | CEST                       |
| table_cache                     | 64                         |
| table_type                      | MyISAM                     |
| thread_cache_size               | 0                          |
| thread_stack                    | 196608                     |
| time_format                     | %H:%i:%s                   |
| time_zone                       | SYSTEM                     |
| tmp_table_size                  | 33554432                   |
| tmpdir                          |                            |
| transaction_alloc_block_size    | 8192                       |
| transaction_prealloc_size       | 4096                       |
| tx_isolation                    | REPEATABLE-READ            |
| version                         | 4.1.4a-gamma-log           |
| version_comment                 | Source distribution        |
| version_compile_machine         | i586                       |
| version_compile_os              | slackware-linux            |
| wait_timeout                    | 28800                      |
+---------------------------------+----------------------------+

Suggested fix:
Make mysqld kill the ended threads properly.
[25 Sep 2004 10:49] [ name withheld ]
[Correction, Description:, the path /etc/rc.mysqld is incorrect, it should be /etc/rc.d/rc.mysqld.]
[5 Jan 2005 16:34] Aleksey Kishkin
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.
[10 Sep 2005 14:48] Johan Nilsson
I have the same problem running both MySQL 4.0.x och 4.1.x with Linux 2.6.11.8 on alpha EV6.

I read the "MySQL Bugs: How to Report a Bug", but what else need to be known by the developers to try to solve it?

Johan
[21 Oct 2005 19:58] Ilya Volynets-Evenbakh
I am running mysql 4.0.25 on alpha EV6 machine, and have exactly same problem.
It does seem to be specific ot alpha architecture. At least I wasn't able to reproduce
it on x86 or amd64.
[21 Oct 2005 20:20] Ilya Volynets-Evenbakh
After playing around a  bit I discovered that setting thread_cache_size to something other then 0 fixes the problem.
[22 Oct 2005 10:44] Johan Nilsson
Your tips seem to work for me as well. I changes the variable (to > 0) and now it works.
[8 Jan 2006 18:36] Johan Nilsson
Isn't this a kind of critical and wouldn't it be kind of silly to ignore this bug? What kind of information do you developers need to look into this? It is pretty self explained to me...
[27 Apr 2006 0:49] Stephen Brownlow
We get this problem on numerous MySQL servers.
Some run RedHat Linux 8.0.
Some run Slackware Linux.

Often we see numerous "defunct mysqld" processes.
The worst effect seems to happen when a process repeatedly calls processes that each connect to MySQL. It is as if the cleanup of used processes is too slow and the priority is given to the creation of new processes. After a few minutes there can be 1000 or so "defunct mysqld" processes and the system is unable to allow new connections for a while, often causing MySQL to seize up.

I am currently trying the suggestion made by Ilya, which at least is having some affect and offering some hope.

I agree with Johan that this is a very serious matter that MySQL-AB should be carefully investigating.
Also, because we cannot always switch quickly to a recent MySQL version, both a fix and a workaround are needed.
[21 Apr 2017 20:35] Howard Freeman
We are experiencing the same symptoms in MySQL 5.6.36 on OEL6.  It has caused many days of debug and root cause analysis that finally pointed towards the mysqld startup not returning and hanging the thread being used...  We can produce this issue very easily and would like to assist with any efforts to resolve the problem ASAP... 

Thank You!!!!