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