List:MySQL on Win32« Previous MessageNext Message »
From:Tal Ben-Gal Date:October 23 2012 5:13pm
Subject:RE: mysql threads too high
View as plain text  
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 to open the connection, some
of the queries are very complex and involve lots of join and some on 2 different
databases.

Opening one connection at the time (and close it - yes with .close - it been 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 background 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 problem on 3 of them
Only one location having this problem. The one we have the problem is the most powerful
server 12GB memory and 12 core CPU.
This application is for the NHS and lots of testing done on it to see the best solution,
and it is running on 2 locations (about 300 users in each) with 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 location.

Could it be the server too fast??!!

No, changing the application now can't be done - it will require NHS approval 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 each time the
> client need connection to Mysql, in this case the threads getting 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 connection count stays
high (many of them in the Sleep state) then your application 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 connections per
page. Transactions should not remain active between page accesses as each request will
use a different connection even if they are coming 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 clients of ben-gal.net may
contain information that is confidential and legally privileged. Please do not read,
copy, forward, or store this message unless you are an intended recipient of it. If you
have received this message in error, please forward it to the sender and delete it
completely from your computer system. For more information please e-mail tal@stripped
Thread
mysql threads too highTal Ben-Gal11 Oct
  • Re: mysql threads too highAndrĂ©s Tello11 Oct
    • RE: mysql threads too highTal Ben-Gal11 Oct
      • Re: mysql threads too highShawn Green11 Oct
        • RE: mysql threads too highTal Ben-Gal12 Oct
          • RE: mysql threads too highTal Ben-Gal23 Oct
            • RE: mysql threads too highJorge Bastos23 Oct
              • RE: mysql threads too highTal Ben-Gal23 Oct
                • Re: mysql threads too highShawn Green23 Oct
                  • RE: mysql threads too highTal Ben-Gal23 Oct
                    • Re: mysql threads too highShawn Green23 Oct
                      • RE: mysql threads too highTal Ben-Gal23 Oct
                        • RE: mysql threads too highTal Ben-Gal24 Oct