Lotsa "sleeping" mysql connections

D

Dixiesys

Verified User
Joined
Aug 2, 2003
Messages
137
Location
The South
On Ensim 3.1 (Mysql 3.2.3) I had to add -O wait_timeout=60 to the /etc/init.d/mysqld line with safe_mysqld on it else I'd have boatloads of sleeping mysql. Well on Directadmin I'm having the same problem.

(here's the processlist for one server):

Code: 
mysql> show processlist;
+-------+-----------+-----------+----------+---------+------+-------+------------------+
| Id    | User      | Host      | db       | Command | Time | State | Info             |
+-------+-----------+-----------+----------+---------+------+-------+------------------+
| 63186 | rskit_usr | localhost | rskit_db | Sleep   | 1064 |       | NULL             |
| 63189 | rskit_usr | localhost | rskit_db | Sleep   | 2476 |       | NULL             |
| 63191 | rskit_usr | localhost | rskit_db | Sleep   | 1572 |       | NULL             |
| 63193 | mconn_usr | localhost | mconn_db | Sleep   | 1826 |       | NULL             |
| 63195 | mconn_usr | localhost | mconn_db | Sleep   | 2123 |       | NULL             |
| 63197 | rskit_usr | localhost | rskit_db | Sleep   | 1091 |       | NULL             |
| 63199 | mconn_usr | localhost | mconn_db | Sleep   | 1880 |       | NULL             |
| 63202 | rskit_usr | localhost | rskit_db | Sleep   | 2515 |       | NULL             |
| 63209 | rskit_usr | localhost | rskit_db | Sleep   | 1056 |       | NULL             |
| 63211 | mconn_usr | localhost | mconn_db | Sleep   | 2217 |       | NULL             |
| 63214 | mconn_usr | localhost | mconn_db | Sleep   | 2293 |       | NULL             |
| 63216 | mconn_usr | localhost | mconn_db | Sleep   | 1943 |       | NULL             |
| 63232 | mconn_usr | localhost | mconn_db | Sleep   | 2897 |       | NULL             |
| 63237 | rskit_usr | localhost | rskit_db | Sleep   | 326  |       | NULL             |
| 63262 | mconn_usr | localhost | mconn_db | Sleep   | 2300 |       | NULL             |
| 63264 | rskit_usr | localhost | rskit_db | Sleep   | 323  |       | NULL             |
| 63276 | mconn_usr | localhost | mconn_db | Sleep   | 2065 |       | NULL             |
| 63296 | mconn_usr | localhost | mconn_db | Sleep   | 1818 |       | NULL             |
| 63302 | rskit_usr | localhost | rskit_db | Sleep   | 890  |       | NULL             |
| 63307 | mconn_usr | localhost | mconn_db | Sleep   | 1965 |       | NULL             |
| 63323 | assmt_usr | localhost | assmt_db | Sleep   | 848  |       | NULL             |
| 63332 | mconn_usr | localhost | mconn_db | Sleep   | 2357 |       | NULL             |
| 63334 | mconn_usr | localhost | mconn_db | Sleep   | 1955 |       | NULL             |
| 63344 | mconn_usr | localhost | mconn_db | Sleep   | 1663 |       | NULL             |
| 63348 | mconn_usr | localhost | mconn_db | Sleep   | 2422 |       | NULL             |
| 63364 | mconn_usr | localhost | mconn_db | Sleep   | 3146 |       | NULL             |
| 63376 | mconn_usr | localhost | mconn_db | Sleep   | 2074 |       | NULL             |
| 63511 | rskit_usr | localhost | rskit_db | Sleep   | 2304 |       | NULL             |
| 63513 | rskit_usr | localhost | rskit_db | Sleep   | 1693 |       | NULL             |
| 63515 | rskit_usr | localhost | rskit_db | Sleep   | 1070 |       | NULL             |
| 63521 | rskit_usr | localhost | rskit_db | Sleep   | 2471 |       | NULL             |
| 63525 | rskit_usr | localhost | rskit_db | Sleep   | 906  |       | NULL             |
| 63574 | rskit_usr | localhost | rskit_db | Sleep   | 320  |       | NULL             |
| 63588 | rskit_usr | localhost | rskit_db | Sleep   | 1129 |       | NULL             |
| 63605 | rskit_usr | localhost | rskit_db | Sleep   | 318  |       | NULL             |
| 63651 | da_admin  | localhost | NULL     | Query   | 0    | NULL  | show processlist |
+-------+-----------+-----------+----------+---------+------+-------+------------------+
36 rows in set (0.01 sec)

35 sleeping mysqld processes

Well so far everything I've tried to get the "wait_timeout" set hasn't worked. I'm surely not the only person with customers who have badly coded php/mysql because I can see at least 4 servers of about 12 DA servers sitting here right now with too many mysqld processes. If anyone knows how I can set the sleep timeout I'd be grateful :)
 
Hello,

After hunting for about 10 minutes on mysql.com, I couldn't find anything that limits the number of processes. You could always restart mysql at which time it it would load up the default number (10 on my system). Or you could set a lower max_connections.. but that might lock people out. If anyone knows something I may be missing, let us know :)

John
 
It's not so much the # of processes or # of connections it is the "wait_timeout" setting, you can find the current setting with the "show variables" command:

| wait_timeout | 28800

That's the defaultnow, and 60 is what I want, on the old 3.23 mysql servers I just added -O wait_timeout=60 to the line in /etc/init.d/mysqld that called "safe_mysqld":

/usr/bin/safe_mysqld $SAFE_MYSQLD_OPTIONS -O wait_timeout=60 >/dev/null 2>&1 &

I found this on mysql.com for setting server parameters but so far I haven't gotten any of it to set the wait_timeout correctly:
http://www.mysql.com/doc/en/Server_parameters.html
 
ok here's my new my.cnf:

[root@www50 mail]# cat /etc/my.cnf
[mysqld]
set-variable = wait_timeout=60

[server]
max_allowed_packet=16M

And a show variables still shows:

| wait_timeout | 28800
 
Not too sure. Might need a forceful restart..

Here's is my full /etc/my.cnf

Code: 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=750
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1280
set-variable = thread_cache_size=256
set-variable = wait_timeout=100
set-variable = connect_timeout=600
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=100

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

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

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
John
 
You know, I think show variables is lying to me, on a particular server I am having a lot of trouble with I put this my.cnf I pasted earlier in, restarted mysql and show variables still says 28800 for wait_timeout, however, I haven't seen one sleep over 60 yet and I have been watching. So this my.cnf I pasted seems to actually work, it's just show variables lying to me :)
 
From the prompt, you could try:

mysqld --help | grep wait_timeout

that will also dump all the values.

John
 
You must log in or register to reply here.
Back
Top