From: Tal Ben-Gal Date: October 23 2012 7:00pm Subject: RE: mysql threads too high List-Archive: http://lists.mysql.com/win32/19187 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Thanks Shawn, Sorry no Linux box can be enter to this location, this was the first sugges= tion we made but the NHS reject is. I will check the server tomorrow, I am in UK and it is after working hours. Tal. -----Original Message----- From: Shawn Green [mailto:shawn.l.green@stripped] Sent: Tuesday, October 23, 2012 7:24 PM To: Tal Ben-Gal Cc: win32@stripped Subject: Re: mysql threads too high On 10/23/2012 1:13 PM, Tal Ben-Gal wrote: > If I turn the pooling off the connection going up the roof, none stay sle= ep, 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 l= ots of join and some on 2 different databases. > > Opening one connection at the time (and close it - yes with .close - it b= een 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 backg= round 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 hav= e 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 user= s, 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 appr= oval all over again - it is running fine on 3 locations. > That kind of throughput reduction is nearly always hardware based. As your = code has not changed, your MySQL version is not any different, your query p= atterns are alike, and your configurations are probably very similar, then = that leaves the hardware as the next logical place to look. You asked "Could it be the server too fast??!!" This machine wouldn't happe= n to be using the NUMA architecture, would it? If so, the way it maps our m= emory allocations may be creating a HUGE volume of cross-zone access requir= ements. The solution would be to break your MySQL data down into pieces tha= t require less cross-talk between memory zones or get some hardware that is= not NUMA-based. As a test, put it on a reasonably powerful Linux box and see how that handl= es the load. This post represents the limit of free advice I can give on this topic. Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and = Software, Engineered to Work Together. Office: Blountville, TN 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