keltec Posted February 9, 2011 Share Posted February 9, 2011 Hi all, I'm having an issue where when PHP, IIS and MySQL are all used, I have really bad delay times, especially with First Time to Byte. I know there are a lot of threads on this but none have managed to solve my problem. Here is my current configuration: Server 1: IIS 7.5 / Win2k8 R2, PHP v5.3.5 with the following options - WinCache v1.1 installed and enabled. - Session.handlers set to wincache as well. - maxInstances set to 0 The whole lot was installed by WebPI and is managed via WebSitePanel. Server 2: MySQL is a separate box running v5.1.54 and has had a few config changes. The MySQL Box also has MSSQL 2008R2 Web Edition installed. Here are the tests I've been able to perform. Most of my testing has been via Pingdom. Note that my testing is based on US servers to our colo in Australia, so we expect the connect time to be at least 350-500ms. Our results only look at the initial request. We are ignoring any additional data load times. Test site 1: PHP static site, PHP headers and footers, HTML content no MySQL: LAMP: Connect 500ms, First Byte 1200ms, Last Btye 1300ms. WIMP: Connect 500ms First Byte 1250ms, Last Byte 1400ms. Test site 2: PHP Info Connect: 350ms, FB 2050ms, LB 2100ms Test site 3: PHP Dynamic site, wordpress v3.03 very little content, MySQL: Connect: 700ms, FB 6050ms, LB 6100ms. Test site 4: ASP.NET site, Sitefinity, MSSQL: Connect: 350ms, FB 1900ms, LB 2000ms. As you can see from the results, we are noticing a 4-6 second delay when using IIS / PHP / MySQL. Under these circumstances, we are noticing this on every click response. Some Wordpress sites are running faster than others (so some at 4sec, others at 6 but this is the median range so we don't believe its JS holding things up but may explain the 2 sec difference between various sites). So on to some of the things we have noticed: From these results, we immediately start looking at processing delay between PHP and MySQL. On the wordpress site, we activated a MySQL plugin to look at the time it took to process the request. From what we noticed, the processing delay was 91ms in total - nothing that would explain a jump from 2seconds to 4+ seconds for a response. We also enabled failed request tracing and could see the 4-6second delay, but all it told us was it was somewhere in the fastcgi component; nothing to assist us with troubleshooting the issue. We found a forum thread which mentioned turning off name resolution (skip-name-resolve) in the my.ini file which we have done - we believe this did improve things slightly but only by about 20ms or so. As mentioned above, we have set instance autotuning to on and setup the Wincache extensions. We also looked at a cache plugin for Wordpress but that didn't seem to help either. I know that the issue is when MySQL is engaged so perhaps the problem still lies there but our MySQL tracing suggests that this is not the cause either. We enabled slow queries log and nothing is showing up there either... I'm happy to provide more information on request, but this problem is killing me - customers are starting to notice these lag times as the pages don't render for at least 4 seconds after each click and we may be forced to go with LAMP if we cannot drill down to the cause of the problem. Our LAMP systems seem to be very responsive compared to WIMP. The page render times on WIMP are great, but only after we get the the first byte response. Its that lag that is causing us major grief. If anyone could suggest anything we could do to futher troubleshoot the problem so we can understand what delays are being incurred end-to-end, in particular, PHP, then that would be great. I will be posting this on the PHP forums as well, but I strongly believe that the problem is PHP / IIS related at this stage. All our servers are running W2K8R2 with firewall services turned off. All devices are connected on the same Cisco switch. HELP! Quote Link to comment Share on other sites More sharing options...
ruslany Posted February 9, 2011 Share Posted February 9, 2011 I have also noticed slower response times when using PHP 5.3.5 with MySQL. After I edited wp-config.php to use the IP address instead of the machine name for the MySQL server, the response time has improved for me. Have you tried that? Quote Link to comment Share on other sites More sharing options...
keltec Posted February 10, 2011 Author Share Posted February 10, 2011 Hi Ruslany, thanks for the reply. I've just checked two wordpress sites and they reference the MySQL box via IP already. To give you an example of the sites that were involved in the test you can check: Site1: http://www.keltec.com.au/ Site2: http://www.wieselmann.com.au/ Site3: http://www.allmed.com.au/ One thing you will notice is the small click delays in site 1 and 3. Site 2 incurs a 3-4 second delay before page rendering. Quote Link to comment Share on other sites More sharing options...
ruslany Posted February 10, 2011 Share Posted February 10, 2011 Yes, I can see the delay, which does look abnormal. What about PHP scripts that do not call into MySQL? For example wincache.php script that is included with WinCache extension - does it also take 2 seconds to render a page or is it faster? Another thing to try is to profile the code with xdebug and then use WinCacheGrind to see which functions take most of the time. This is how for example I was able to see that mysql_connect took a lot of time. Quote Link to comment Share on other sites More sharing options...
ruslany Posted February 10, 2011 Share Posted February 10, 2011 Another thing to try just for test is to temporary switch to PHP 5.2 and check if the delay still exist. Quote Link to comment Share on other sites More sharing options...
keltec Posted February 10, 2011 Author Share Posted February 10, 2011 Thanks Ruslany, this was the tool I had been looking for!!! First, I should say, that I was referring to Site 1, 3 and 4. Test site 2 was a call to PHPinfo() on one of the sites and yes that seemed to work fine. OK I've added wincache.php to the Wieselmann website and that seems to provide information that I'm not sure how to interpret right now. Essentially it looks like it's caching a few objects and some objects are returning 54% cache hit ratio (if I'm interpreting that correctly). Xdebug however has helped immensely. There are a number of areas generating minor delays but once I drilled down, it looked like a mysqlconnect() routine. The Wieselmann page was taking 3595ms to load, with the php::mysql_connect function taking 2255ms in wp-db.php. I've also noticed 3 other websites that are not wordpress related and they are also taking between 2250 and 4700ms to connect. I guess now moreso than before, it has to be a mysql problem or a transit delay. I've done a ping test and the replies are 1ms over a 2min period... Quote Link to comment Share on other sites More sharing options...
keltec Posted February 10, 2011 Author Share Posted February 10, 2011 OK problem resolved. Stupid me, it was a reverse lookup problem with MySQL. Everything on the net I found pointed to that but the two are connected to the same AD DNS. When I was on the MySQL box however, I noticed that the reverse lookup didn't work, as it was on a public range. I then tried to understand why skip-name-resolve wasn't working in MySQL's my.ini and I discovered after a little more research that this statement needs to appear under [mysqld], not [mysql] where it was currently located. A restart of MySQL and all is running like it should. Thanks alot for your assistance Rulsany Quote Link to comment Share on other sites More sharing options...
erinonlyone Posted August 9, 2011 Share Posted August 9, 2011 hi , i am having similar issue but for my case. only 1 website is having delay. its only happen when connection to mysql is more than 50++. if less, page load a lot more faster. i already set non persistent connection and i also use skip-name-resolve but still facing the same issue. can someone help me please? -zarina Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.