From: Tal Ben-Gal Date: October 12 2012 8:07am Subject: RE: mysql threads too high List-Archive: http://lists.mysql.com/win32/19179 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 1) Reduce the connection to 1500, now no one after 300 connection can log i= n 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 on= ly 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 thr= eads, no one can connect to the database. > > In the new ini: > max_connections=3D3510 > myisam_max_sort_file_size=3D25G > 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 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