MySQL Bugs: #15368: Mysql 5.0.15 and 5.0.16 consuming 99% of the cpu

Bug #15368	Mysql 5.0.15 and 5.0.16 consuming 99% of the cpu
Submitted:	30 Nov 2005 22:49	Modified:	3 Dec 2005 16:31
Reporter:	Dâniel Fraga	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Linux (Linux 2.6.14)
Assigned to:		CPU Architecture:	Any

Description:
I tested Mysql 5.0.15 and 5.0.16 and both versions after a certain amount of time, suddenly starts to consume all the cpu. I activated the log option, but couldn't notice anything unusual. My setup:

Athlon-xp 2400+ (1GB RAM)w
Linux 2.6.14
gcc 4.0.2
glibc 2.3.5
./configure --with-pthread

With mysql 5.0.16 the problem takes hours to happen. With 5.0.15 it happens faster.

How to repeat:
Sorry but I don't know how to repeat this. It's kind of random.

I can add that I use php 5.1.1, apache 2.0.55... I use mysql through perl's DBI interface too.

It happens when mysqld is sleeping (I checked thru top). Maube could it be a race condition?

Thank you for a problem report. Surely more information on how to repeat the problem is needed. We have to estimate the load on your system, at least. But I have one idea, for the beginning. Can you try to build without that --with-pthread option and give new binary a try? I want to check that the problem does not depend on threading implementation used.

Hi, I'm afraid it's impossible to compile without --enable-pthread (in fact it's a redundant option for me), because If I do so, it detects autommaticaly the pthread from glibc 2.3.5 and enable pthread. From config.log:

configure:26441: checking "Linux threads"
configure:26445: result: "starting"
configure:26448: checking "getconf GNU_LIBPTHREAD_VERSION"
configure:26452: result: "NPTL"
configure:26556: result: "no need to check headers"

The interesting thing is that is didn't happen with mysql 4.1.x. Only when I upgraded to mysql 5 this problem started.

I'll try to upgrade to glibc 2.3.6 and see what happens.

Thanks.

Please, try to create a simple simulation of "load" for your server that leads to this 99% of CPU used. It may be one inefficient (in 5.0.x) query, really. And it may be not a bug at all. So, the set of statements (or programs) that gives the repeatable results as you described is really needed.

Please, send the results of SHOW PROCESSLIST when you have this 99% cpu usage. Your my.cnf settings may be useful too.

You may try to upgrade the glibc and inform about the results of your testing, but NPTL is not necessary "bad thing" by itself. By the way, what is the result of the "getconf GNU_LIBPTHREAD_VERSION" command on your system?

Hi! Your suggestions were very good.

mysql> show processlist;
+------+------+-----------+-------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id   | User | Host      | db    | Command | Time | State                | Info                                                                                                 |
+------+------+-----------+-------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 2745 | root | localhost | phpbb | Query   | 6660 | Copying to tmp table | SELECT m.word_id 
                                FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
                                WHERE w.word_text  |
| 2771 | root | localhost | phpbb | Query   | 6148 | Copying to tmp table | SELECT m.word_id 
                                FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
                                WHERE w.word_text  |
| 2917 | root | localhost | NULL  | Query   |    0 | NULL                 | show processlist                                                                                     |
+------+------+-----------+-------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

I think I will send it to phpbb team.

The other requested info:

fraga@abusar ~$ getconf GNU_LIBPTHREAD_VERSION
NPTL 2.3.6

And my.cnf:

# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

# The following options will be passed to all MySQL clients
[client]
#password       = 
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
set-variable    = key_buffer=16M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=100
set-variable    = sort_buffer=512K
set-variable    = net_buffer_length=8K
set-variable    = myisam_sort_buffer_size=8M
set-variable    = max_connections=64
#log-bin
#server-id      = 1

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/         
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#set-variable   = bdb_cache_size=4M
#set-variable   = bdb_max_lock=10000

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:400M
#innodb_data_home_dir = /usr/local/var/
#log-bin
#server-id      = 1

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/         
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#set-variable   = bdb_cache_size=4M
#set-variable   = bdb_max_lock=10000

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:400M
#innodb_data_home_dir = /usr/local/var/
#innodb_log_group_home_dir = /usr/local/var/
#innodb_log_arch_dir = /usr/local/var/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
set-variable    = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable    = key_buffer=20M
set-variable    = sort_buffer=20M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M

[myisamchk]
set-variable    = key_buffer=20M
set-variable    = sort_buffer=20M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

Now, if it is phpbb and hang is on "Copying to temp table", then it is yet another duplicate of well know bug report http://bugs.mysql.com/bug.php?id=14070... You may add you comments (as well as the results of your questions to php bb developers) to that report.