High MySQL sleep count

Hiya

I have a server that gets connections / requests from Germany and South
Africa, Johannesburg (my server's based in Cape Town).

All servers are in datacentres. Im not 100% sure if its latency related,
but I see a load or SLEEP when I do mysqladin proc, which appears to be
hogging mysql.

My question is. Would adjusting timeout values address this problem.

I was thinking of using / setting these values.
[mysqld]
set-variable = connect_timeout=20
set-variable = wait_timeout=20
set-variable = interactive_timeout=20
set-variable = net_write_timeout=20

Can anyone share some thought or opinion on this. Or whats the correct
practice.

Kind Regards
Brent Clark





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: High MySQL sleep count

In infinite wisdom Brent Clark wrote:

> All servers are in datacentres. Im not 100% sure if its latency related,
> but I see a load or SLEEP when I do mysqladin proc, which appears to be
> hogging mysql.
>
> My question is. Would adjusting timeout values address this problem.

The main causes of sleeping threads are
- applications that call mysql_pconnect
- apps do not close their mysql connections once they are done with their tasks.
- apps that open a connection, run a query, do some magic or go to sleep
and then run some queries again.

>
> I was thinking of using / setting these values.
> [mysqld]
> set-variable = connect_timeout=20
> set-variable = wait_timeout=20
> set-variable = interactive_timeout=20
> set-variable = net_write_timeout=20

This will be OK if you have only apps that do point [1] and [2] of the
above. If you have apps that do [3], they will throw an error when they
see that the mysql server has closed their connection.


--
Raj Shekhar
-
If there's anything more important than my ego around, I want it
caught and shot now.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org