Lotsa "sleeping" mysql connections [Archive]

Dixiesys

03-12-2004, 05:32 PM

DirectAdmin Support

03-13-2004, 01:26 PM

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

Dixiesys

03-13-2004, 02:05 PM

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

DirectAdmin Support

03-13-2004, 02:24 PM

/etc/my.cnf :

[mysqld]
set-variable = wait_timeout=60John

Dixiesys

03-15-2004, 06:30 PM

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

DirectAdmin Support

03-16-2004, 10:29 AM

Not too sure. Might need a forceful restart..

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

[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

Dixiesys

03-16-2004, 10:36 AM

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 :)

DirectAdmin Support

03-16-2004, 10:40 AM

From the prompt, you could try:

mysqld --help | grep wait_timeout

that will also dump all the values.

John