MySQL Performance: MySQL => Sleeping Queries

Members

Help

Home

Home » Performance » MySQL » Sleeping Queries

Show: Today's Messages :: Show Polls :: Message Navigator

Sleeping Queries [message #119]

Wed, 30 August 2006 15:58

sbazemore
Messages: 4
Registered: August 2006
Location: Charlotte, North Carolina

Junior Member

Why would mysql not clear sleeping queries properly?

In order for our site to run, we have to set our wait_timeout to 5 in to properly clear the connections or we reach our max connections in a matter of seconds.

If we set the wait_timeout to the default of 28800, most of the sleeping connections never clear out until they hit that limit which causes the connection count to max out constantly.

Any pointers would be highly appreciated.

Thanks

Stephen

Report message to a moderator

Re: Sleeping Queries [message #120 is a reply to message #119 ]

Wed, 30 August 2006 16:54

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Hi,

What operation system are you using ? And what kind of language ?

Are you closing connection properly in your application by calling mysql_close() ?

Typically if application does not close connection it is automatically closed when connection is aborted, ie process terminates.

In some cases however it may take a while for remote size to discover connection was aborted which may show up as sleeping connection for rather long time.

Also how does your processlist looks ?

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Sleeping Queries [message #121 is a reply to message #120 ]

Wed, 30 August 2006 17:02

sbazemore
Messages: 4
Registered: August 2006
Location: Charlotte, North Carolina

Junior Member

We are running Windows Server 2003, MySQL 5.0.24, and PHP5. Also, I am explicitly closing the connection and the sleeps are still there.

The process list looks good with the exception of it being filled with sleeps from 1 second to 5 seconds, which is where they get killed by the wait_timeout.

Report message to a moderator

Re: Sleeping Queries [message #122 is a reply to message #121 ]

Wed, 30 August 2006 17:14

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

OK I see.

There is the bug filed on this:

http://bugs.mysql.com/bug.php?id=20237

Unfortunately it is in No Feedback state.

MySQL Support team tried to repeat it but failed so this seems to be something very specific to envinronment.

Could you please check if this bugs shows up on your system and if yes reopen this bug report.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Sleeping Queries [message #134 is a reply to message #121 ]

Fri, 01 September 2006 05:31

toasty
Messages: 19
Registered: August 2006
Location: UK

Junior Member

Hi there,

Maybe an obvious one I don't know. Worth checking just in case I think Smile

Are you using mysql_pconnect to open your connection? If so then mysql_close just gets ignored when you call it. (mysql_close only works for connections opened by mysql_connect, i.e. non-persistent ones)

Not sure about IIS but if you're using Apache and have max requests per child quite high the process that has the pconnect open will hang probably around for a while.

You shouldn't really see many more than ((number of apache processes) * (number of different mysql username/password combinations used)) sleeping threads on mysql as a result of this though.

Toasty

[Updated on: Fri, 01 September 2006 05:35]

Report message to a moderator

Re: Sleeping Queries [message #135 is a reply to message #134 ]

Fri, 01 September 2006 09:08

sbazemore
Messages: 4
Registered: August 2006
Location: Charlotte, North Carolina

Junior Member

Thanks for the reply. We aren't using mysql_pconnect. We are actually using the mysqli extension which doesn't support persistant connections.

I think the bug peter posted is probably the cause. I'm sure the bug applies to mysqli_connect as well.

We are looking into the bug report and testing the sample code on our servers. We will keep you updated just for reference.

Report message to a moderator

Re: Sleeping Queries [message #136 is a reply to message #135 ]

Fri, 01 September 2006 09:15

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Good to know.

mysql_pconnect together with large number of apache children is often cause for large number of connections

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	General query log performance
Next Topic:	help

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 10 00:31:13 EDT 2009

Total time taken to generate the page: 0.01346 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.