List:MySQL on Win32« Previous MessageNext Message »
From:Tal Ben-Gal Date:October 23 2012 12:59pm
Subject:RE: mysql threads too high
View as plain text  
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

Tal.
________________________________________
From: Jorge Bastos [mysql.jorge@stripped]
Sent: 23 October 2012 13:49
To: Tal Ben-Gal; 'Shawn Green'; win32@stripped
Subject: RE: mysql threads too high

Tal,

Not a MySQL issue,
Which application is using MySQL? It seems that it's with a problem left'n
unclosed and/or unnecessary connections.

>
> is anyone have some idea?
>
> MySql crash when we getting to 1200 threads!!
>
> PLEASE HELP!
>
> Tal.
> ________________________________________
> From: Tal Ben-Gal [tal@stripped]
> Sent: 12 October 2012 09:07
> To: Shawn Green; win32@stripped
> Subject: RE: mysql threads too high
>
> 1) Reduce the connection to 1500, now no one after 300 connection can
> log in
> 2) Mysql use max of 1GB the server has 12GB
> 3) unfortunately it is not possible at this time to rewrite the
> application that run on 5000 PC and in 5 location, all other location
> working fin I only have this problem in one location with 500 users.
>
> So NO, your suggestions (good once) did not resolve the problem.
>
> Tal.
>
>
> ________________________________________
> From: Shawn Green [shawn.l.green@stripped]
> Sent: 11 October 2012 15:36
> To: win32@stripped
> Subject: Re: mysql threads too high
>
> On 10/11/2012 10:07 AM, Tal Ben-Gal wrote:
> > Thanks, I try to change it but it made no different, after 1250 mysql
> threads, no one can connect to the database.
> >
> > In the new ini:
> > max_connections=3510
> > myisam_max_sort_file_size=25G
> >
>
> You have to realize that once you overload your system with RAM
> requests and start to use the page file, that all those activities that
> used to be operating at RAM speed are now operating at DISK speed.
> That's a performance reduction on the scale of 100x to 10000x slower
> than before.
>
> There are several ways to fix this:
>
> 1) Start refusing connections once you reach a number that causes your
> system to begin using swap (system paging file). You control this by
> configuring a reasonable value for --max-connections (Hint: unless you
> have several hundred gigabytes of RAM in this one machine, 3510 is not
> a reasonable value)
>
> 2) Adjust your memory allocation settings to something more reasonable.
> Remember, some values are allocated once for the entire server (global)
> and some are per thread (local). You must multiply the number of local
> allocations by the number of active threads you allow (max_connections)
> in order to get an upper-limit estimate of RAM usage just for your
> threads.  Add that to your global allocations to get an upper estimate
> of how much RAM you will use in total.
>
> 3) Fix your queries. If they ran faster, you would be able to handle
> more queries every second. For example, if you start a query every .5
> seconds and each of them takes an average of 1s to compute then you
> will need a lot of connections as all of your queries are overlapping.
> If you can reduce your average execution time to something less than .5
> seconds (say in the .1 second range) then you can handle at least 500%
> more queries every second than you had before you tuned your queries.
> You will need at least 10x fewer connections than you did under the
> original scenario.
>
> All of these points (and more) are covered in the MySQL manual:
> http://dev.mysql.com/doc/refman/5.5/en/optimization.html
>
> --
> 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
>
> --
> 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 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@ben-
> gal.net
>
> --
> 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