Xampp 1.8 mysql connect slow

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Xampp 1.8 mysql connect slow

Postby djabailey » 03. August 2012 20:16

I have just done a fresh install of Xampp 1.8 (following all the "do this first" warnings) and it works.
The only problem is that connecting to mysql is soooo slow.
any pages with php arrive quickly when I navigate to localhost in the browser on the server machine.
If there is a mysql connect statement in the php then it is really slow.

I have timed the connection in the php code and it usually takes more than 1 second to connect to mysql, a query then runs in less than 4ms.

I have tried restarting mysql with the skip-name-resolve option as suggested by many others who had the problem (after googleing for a while), this causes warnings to appear:
Warning: mysql_connect(): Access denied for user...
and so I cannot query the database and everything is useless and even slower.

How can I fix this?

Thanks.
djabailey
 
Posts: 3
Joined: 03. August 2012 11:39
Operating System: Windows 7

Re: Xampp 1.8 mysql connect slow

Postby rlaserna » 19. August 2012 04:06

Hi
I have the same problem. Did you find a solution?
rlaserna
 
Posts: 1
Joined: 19. August 2012 04:05
Operating System: Windows 7 64-bit

Re: Xampp 1.8 mysql connect slow

Postby djabailey » 19. August 2012 08:19

No, sorry, I switched to wamp server.
djabailey
 
Posts: 3
Joined: 03. August 2012 11:39
Operating System: Windows 7

Re: Xampp 1.8 mysql connect slow

Postby koala15 » 21. August 2012 13:31

I have the same problem .... vvvvery slow, also when I try to import an "exported" file
from 1.7.7 I get either

1) the file is to large, or
2) a similar message to "file not recognised"

I reverted back to 1.7.7 (clean install) and imported the file. No problems. Also,
1.7.7 runs substantially faster.

Greg.
koala15
 
Posts: 1
Joined: 21. August 2012 13:18
Operating System: Win-7

Re: Xampp 1.8 mysql connect slow

Postby meinemitternacht » 21. August 2012 15:13

Here is a solution:

1. In your MySQL configuration (my.cnf), add a line under [mysqld] that says "skip-name-resolve"
2. Ensure 'bind-address="127.0.0.1"' is not commented out
3. For the MySQL account your PHP application uses, make sure you have the username set to allow connections from '%' and from 'localhost' (I haven't tested which one works, but in my current situation, I have both)
4. In your PHP application, ensure it is connecting to MySQL via 127.0.0.1 instead of 'localhost'. This eliminates the DNS lookup.

This should solve this instance of slow-down with 1.8.0.
meinemitternacht
 
Posts: 4
Joined: 21. August 2012 15:09
Operating System: Windows 7 64-bit

Re: Xampp 1.8 mysql connect slow

Postby JonB » 21. August 2012 16:59

It will be interesting to see what results people have.

1st have you tested this?

I think #1 & #2 may be relevant to the problem - and I agree that it 'might be' a MySQL DNS resolve issue. It might also be due to different parameters used OR the new mysldnd library in PHP 5.4.4.

I think #3 is not correct at all - % is a potential security risk, it means ANY host can connect. They are actually two different users as far as MySQL is concerned AFAIK (they can have different privileges, and have unique rows)

I don't think there is an external lookup on localhost. On Vista and Win 7, localhost resolution is built into the MS TCP/IP stack for IPv6, and we recommend you add a 'localhost 127.0.0.1' to your hosts file. The hosts file always has precedence, and is loaded first in the cache. Thereafter, AFAIK, the MS DNS resolver caches external references(lookups). The hosts file is only re-read if the last-modified timestamp is later than the last-read value in the resolver. I.e it only re-reads the file if it has changed.

I do not know if this is true: (Altrea could better judge) I don't think it makes any difference - but that is a guess.
4. In your PHP application, ensure it is connecting to MySQL via 127.0.0.1 instead of 'localhost'. This eliminates the DNS lookup.

Good Luck
8)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: Xampp 1.8 mysql connect slow

Postby djabailey » 21. August 2012 20:59

Thanks for all this guys, I'll remember this for if I switch back to xampp on the windows machine. (I still use xampp on my linux machines)
djabailey
 
Posts: 3
Joined: 03. August 2012 11:39
Operating System: Windows 7

Re: Xampp 1.8 mysql connect slow

Postby Altrea » 22. August 2012 05:31

Hello everybody,

We have a similar posting on the german part of the board: viewtopic.php?f=4&t=50971 (last 3 entrys)
There are hints, that this speed issue maybe only occurs on 64Bit machines with mysqli. I haven't tested this yet, because i don't have a physical 32Bit Windows 7 machine i can use for.

JonB wrote:I think #3 is not correct at all - % is a potential security risk, it means ANY host can connect. They are actually two different users as far as MySQL is concerned AFAIK (they can have different privileges, and have unique rows)

I totally agree with this. Any host (%) can be a huge security issue if your mysql-server is accessible from a bigger Network. if you want to switch off the name resolution for mysql, you simply need another mysql user entry for host 127.0.0.1. And AFAIK it is correct, that each mysql user entry with the same name but from different hosts is fully unique and independent from other users with the same name.

JonB wrote:I don't think there is an external lookup on localhost.

unfortunately i can't find any official Microsoft information on this. I have had a conversation with my external Windows Guru some weeks ago if the localhost lookup is a true lookup (The TCP Stack is sending an internal lookup requests and getting an answer on it) or a simple mapping implemented in the TCP Stack. Because of some problems with the IP Stack on Windows with localhost it normally can only be the first opinion.

I guess this will not make any differnece because all the problems with the IP Stack could not be solved with this entry on the HOSTS file, but i really don't know.
It can't hurt, so i think adding this entry in the HOSTS file is worth to test it.

JonB wrote:I do not know if this is true: (Altrea could better judge) I don't think it makes any difference - but that is a guess.
4. In your PHP application, ensure it is connecting to MySQL via 127.0.0.1 instead of 'localhost'. This eliminates the DNS lookup.

I don't think if the lookup will be eliminated, but it definitely makes a difference if you use localhost or 127.0.0.1 in a connection string of that php application. I have had some Joomla problems on one of my client VMs with an early beta of XAMPP 1.7.4 i think where localhost connections wasn't successful but 127.0.0.1 was.

best wishes,
Altrea
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: Xampp 1.8 mysql connect slow

Postby meinemitternacht » 22. August 2012 16:43

JonB wrote:1st have you tested this?


Absolutely. I have a simple microtime function that is run at the beginning of the page and at the footer of the page that measures the time difference in milliseconds. It was 1.4s before I implemented these changes, and 0.03445s after.

Altrea wrote:I totally agree with this. Any host (%) can be a huge security issue if your mysql-server is accessible from a bigger Network. if you want to switch off the name resolution for mysql, you simply need another mysql user entry for host 127.0.0.1. And AFAIK it is correct, that each mysql user entry with the same name but from different hosts is fully unique and independent from other users with the same name.


Sorry for this, the 127.0.0.1 entry should be all that is required. I use % for my local testing purposes (XAMPP install isn't accessible outside of the local machine). I understand about the security hole that could introduce and I apologize :)
meinemitternacht
 
Posts: 4
Joined: 21. August 2012 15:09
Operating System: Windows 7 64-bit

Re: Xampp 1.8 mysql connect slow

Postby JonB » 22. August 2012 17:34

Thanks for posting your results.

Personally, I think it would be very interesting to disable the 'interventions' you made, one at a time, and see how much effect each really has. This may not be a linear function, as one may affect another - but I am betting 'one' of them is the real 'timesaver'. and my bet is on the skip-name-resolve. It 'might' force a cache refresh.

Good Luck and thanks again for posting your research.

8)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: Xampp 1.8 mysql connect slow

Postby meinemitternacht » 22. August 2012 17:56

Results:

Kept both MySQL user accounts (% and 127.0.0.1) for all tests.

Test 1
skip-name-resolve off
bind-address off
PHP MySQL connection localhost

Rendered in: 1.08551s

Test 2
skip-name-resolve on
bind-address off
PHP MySQL connection localhost

Rendered in: 1.06973s

Test 3
skip-name-resolve off
bind-address on
PHP MySQL connection localhost

Rendered in: 1.02549s

Test 4
skip-name-resolve on
bind-address on
PHP MySQL connection localhost

Rendered in: 1.03902s

Test 5
skip-name-resolve on
bind-address on
PHP MySQL connection 127.0.0.1

Rendered in: 0.02877s

Test 6
skip-name-resolve off
bind-address on
PHP MySQL connection 127.0.0.1

Rendered in: 0.02232s

Test 7
skip-name-resolve on
bind-address off
PHP MySQL connection 127.0.0.1

Rendered in: 0.02807s

Test 8
skip-name-resolve off
bind-address off
PHP MySQL connection 127.0.0.1

Rendered in: 0.04301s

I believe it has more to do with either (a) the method PHP is using to connect to MySQL or (b) a DNS issue internal to Windows.

For point A, would have to test with and without mysqli, or with PDO. For point B, would have to test under Mac OS X or Linux.
meinemitternacht
 
Posts: 4
Joined: 21. August 2012 15:09
Operating System: Windows 7 64-bit

Re: Xampp 1.8 mysql connect slow

Postby Cobra427 » 25. September 2012 14:19

Hi!
I am not common to the mysql code, so please excuse my stupid questions!

where do I finde these settings and how do I measure the result? THX
Cobra427
 
Posts: 13
Joined: 22. September 2012 12:25
Operating System: XP

Re: Xampp 1.8 mysql connect slow

Postby meinemitternacht » 02. October 2012 11:51

Your settings are determined by how PHP is connecting to MySQL.

For example, this is my script that I use:

Code: Select all
<?php
$time_start = microtime(true);

//choose one of the following two host values to see the difference in DNS lookup time
$cfg['database']['dbhost'] = "127.0.0.1";
//$cfg['database']['dbhost'] = "localhost";

$cfg['database']['dbname'] = "YOUR_DATABASE_NAME";
$cfg['database']['dbuser'] = "YOUR_USER_NAME";
$cfg['database']['dbpass'] = "YOUR_PASSWORD";

// Connect to the Database
function databaseHandler( $host, $user, $password, $database )
{
   $dbError = false;
   $handle = mysql_connect($host, $user, $password);
   if(!($handle)) { dbError = true; }
   
   if(!(@mysql_select_db($database, $handle))) { $dbError = true; }
   
   if($dbError == true) {
      die('There seems to be a problem with the database server.  We should have everything fixed soon!');
   }
   else
   {
      return 1;
   }
}

$connect = databaseHandler( $cfg['database']['dbhost'], $cfg['database']['dbuser'], $cfg['database']['dbpass'], $cfg['database']['dbname'] );

if(!$connect)
{
   exit();
}

?>
Rendered in:
<?php
$time_end = microtime(true);
$time = $time_end - $time_start;
echo round($time,5);
?>s
meinemitternacht
 
Posts: 4
Joined: 21. August 2012 15:09
Operating System: Windows 7 64-bit

Re: Xampp 1.8 mysql connect slow

Postby tburba » 22. February 2013 11:10

The issue is also discussed in detail here: http://stackoverflow.com/questions/11663860/mysql-connect-localhost-127-0-0-1-on-windows-platform

In my case, configuring a third-party PHP code to connect to "127.0.0.1" instead of "localhost" created other issues. I was forced to instruct MySQL to make an IPv6 binding as well:
Code: Select all
bind-address = ::

(this line is already present in my.ini but commented out initially). That was enough. If DNS wants to resolve localhost to ::1, so be it.

That system was Server 2008 R2, with an interesting hosts file
Code: Select all
# localhost name resolution is handled within DNS itself.
#   127.0.0.1       localhost
#   ::1             localhost

Apparently it's a standard behavior starting from Vista and Server 2008: http://serverfault.com/questions/4689/windows-7-localhost-name-resolution-is-handled-within-dns-itself-why.

By the way, what was changed in XAMPP 1.8 so that this issue surfaced? I attend another system with Windows 7, the same hosts file and XAMPP Lite 1.7.1; it just worked out of the box, with MySQL bound to IPv4 by default. The DisabledComponents registry value which could make a difference in localhost resolution (bitmask 0x20, http://support.microsoft.com/kb/929852) is set to 0x00000001 on both systems. I might need to form a habit to use 0xffffffff instead, as IPv6 support was never required so far.

Hmmm... I finally understand why on some systems the browser also couldn't connect to Apache referenced as "localhost" -- "127.0.0.1" was required instead.

I think XAMPP installer could detect systems like these, then uncomment that line from my.ini and use a corresponding version of Listen directive in httpd.conf. The detection is simple -- check if "localhost" doesn't resolve to "127.0.0.1". Of course, the final choice can be left to the user.
tburba
 
Posts: 2
Joined: 21. February 2013 22:48
Operating System: XP and up

Re: Xampp 1.8 mysql connect slow

Postby Mathias-S » 10. April 2013 13:56

I also had this very same issue on Windows 7 x64, where the following code would be very slow and take a little more than a second to run:
Code: Select all
new mysqli('localhost', 'user', 'password', 'mydb');


I solved it, as others have suggested in this thread, by uncommenting/adding localhost to my hosts file:
Code: Select all
127.0.0.1       localhost

It's possible that connecting to MySQL using 127.0.0.1 instead of localhost would also have solved the problem in my case. I suspect it tried resolving ::1 on my local network, which does not support IPv6.
Mathias-S
 
Posts: 1
Joined: 10. April 2013 13:51
Operating System: Windows 7 x64


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 118 guests