From: Tal Ben-Gal Date: October 23 2012 5:13pm Subject: RE: mysql threads too high List-Archive: http://lists.mysql.com/win32/19185 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable If I turn the pooling off the connection going up the roof, none stay sleep= , I have about 40 queries per min for each connection, its take long time t= o open the connection, some of the queries are very complex and involve lot= s of join and some on 2 different databases. Opening one connection at the time (and close it - yes with .close - it bee= n tried.) is not an option, as it is slowing the application drastically. It is windows base application, there is no 'block' of quires, users can't = go any farther until the query finish, furthermore some queries are backgro= und work that need to update the user screen every 5 Min. The application been tested with all the option you suggested and the best = solution is with pooling, the application running in 4 locations, no proble= m on 3 of them Only one location having this problem. The one we have the problem is the m= ost powerful server 12GB memory and 12 core CPU. This application is for the NHS and lots of testing done on it to see the b= est solution, and it is running on 2 locations (about 300 users in each) wi= th no problem. The location that having this problem is actually the one with fewer users,= only 180. The My.ini is identical in all location, the application identical in all l= ocation. Could it be the server too fast??!! No, changing the application now can't be done - it will require NHS approv= al all over again - it is running fine on 3 locations. Thanks, Tal. -----Original Message----- From: Shawn Green [mailto:shawn.l.green@stripped] Sent: Tuesday, October 23, 2012 5:16 PM To: win32@stripped Subject: Re: mysql threads too high On 10/23/2012 8:59 AM, Tal Ben-Gal wrote: > The client application using MySql connector (in VB DOT NET) each client = open 8 connections (in a pool) and sty open as long as the client running, = all connections is closing properly once the client application end. > > The situation is even worth if I try to open and close the connection eac= h time the client need connection to Mysql, in this case the threads gettin= g to 1250 even faster. > > It is only happens with Mysql, the same application running in different = location with MS SQL and no problem there. > > Defiantly Mysql issue > It is not "definitely a MySQL issue". Turn off your pooling. If the connect= ion count stays high (many of them in the Sleep state) then your applicatio= n is NOT closing them properly. This is a coding/usage problem. Also, you do not need to open a new connection for every little thing. Open one connection per block of work to perform. Also, do not leave your = connections or transactions open while waiting for a person to do something= . For example, a web-based application will generally need only one or two co= nnections per page. Transactions should not remain active between page acce= sses as each request will use a different connection even if they are comin= g from a pool. Remember, abandoning a connection object for garbage collection or closing = the TCP/IP socket is not the same as actually calling the .close() method for your connection library. Let us know your results. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and = Software, Engineered to Work Together. Office: Blountville, TN -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 To unsubscribe: http://lists.mysql.com/win32 This e-mail message is intended to be received only by persons entitled to = receive the confidential information it may contain. E-mail messages to cli= ents of ben-gal.net may contain information that is confidential and legall= y privileged. Please do not read, copy, forward, or store this message unle= ss you are an intended recipient of it. If you have received this message i= n error, please forward it to the sender and delete it completely from your= computer system. For more information please e-mail tal@stripped