From: Jorge Bastos Date: October 23 2012 12:49pm Subject: RE: mysql threads too high List-Archive: http://lists.mysql.com/win32/19182 Message-Id: <013101cdb11c$c967b4f0$5c371ed0$@jorge@decimal.pt> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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