Threads are raising and sleeping. They not die!

MySQL & MariaDB Other MySQL® Questions
1

Hi

Just new here.
Using the Percona Server for some time and all worked, but my SEO was good and the traffic raised.

With the raising traffic, I have some problems know with the percona server.

Server is a Athlonx X2 Maschine with 8 GB RAM, RAID1 System.
OS: Ubuntu Lucid
SQL Server is Percona Server 5.1 latest version.

Here is my config:

client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1
init_connect=‘SET autocommit=0’
max_connections = 60
connect_timeout=10
key_buffer = 256M
myisam_sort_buffer_size = 8M
join_buffer_size = 2M
read_buffer_size = 8M
sort_buffer_size = 8M

table_cache = 4000
table_definition_cache = 4000

max_allowed_packet = 32M

query_cache_size=0M
query_cache_limit = 4M
query_cache_type = 0
tmp_table_size = 256M
max_heap_table_size = 256M
open_files_limit = 24576
thread_cache = 16
thread_stack=128k
thread_concurrency=2
#thread_cache_size=64
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 32M
innodb_lock_wait_timeout = 300
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency=2
innodb_flush_method = O_DIRECT
innodb_write_io_threads=8
innodb_read_io_threads=8
innodb_io_capacity=2000
innodb_max_dirty_pages_pct=90

innodb_ibuf_active_contract = 1
innodb_adaptive_flushing = true
skip-name-resolve

myisam-recover = BACKUP
#thread_concurrency = 0
#long_query_time = 2
#log-slow-queries = /var/log/mysql/drupal-slow.log
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = include_database_name
binlog_ignore_db = include_database_name
default-storage-engine = InnoDB
[mysqldump]
[mysql]
[isamchk]
!includedir /etc/mysql/conf.d/

And now my problem.
MySQL Threads are sleeping and they not die.
From mytop:

758 root localhost ap24_v3 0 Sleep
1083 root localhost ap24_v3 1 Sleep
634 root localhost ap24_v3 2 Sleep
908 root localhost ap24_v3 4 Sleep
1085 root localhost ap24_v3 4 Sleep
1087 root localhost ap24_v3 193 Sleep
1086 root localhost ap24_v3 194 Sleep
910 root localhost ap24_v3 493 Sleep
760 root localhost ap24_v3 786 Sleep
638 root localhost ap24_v3 1079 Sleep

This is after 5 minutes of a mysql restart. It raised to 100 threads there are sleeping and Mysql runs out of memory.

Any Tipp or help would be great.
thx
bennos

2

bennos wrote on Fri, 21 January 2011 10:49

I’m guessing that you have some sort of PHP site or something?

The common problem here is that the old db connections are not closed or reused properly. Instead the application continues to open new ones.

A common fix for this is to lower the mysql timeout values to something like:

interactive_timeout = 300wait_timeout = 300

That way MySQL will instead kill the connections when there hasn’t been any activity on it for 300 seconds.

Then the application will usually just open new connections and not notice anything.

3

do you use mysql_pconnect?