Page 1 of 1

Xampp 1.8 mysql connect slow

PostPosted: 03. August 2012 20:16
by djabailey
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.

Re: Xampp 1.8 mysql connect slow

PostPosted: 19. August 2012 04:06
by rlaserna
Hi
I have the same problem. Did you find a solution?

Re: Xampp 1.8 mysql connect slow

PostPosted: 19. August 2012 08:19
by djabailey
No, sorry, I switched to wamp server.

Re: Xampp 1.8 mysql connect slow

PostPosted: 21. August 2012 13:31
by koala15
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.

Re: Xampp 1.8 mysql connect slow

PostPosted: 21. August 2012 15:13
by meinemitternacht
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.

Re: Xampp 1.8 mysql connect slow

PostPosted: 21. August 2012 16:59
by JonB
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)

Re: Xampp 1.8 mysql connect slow

PostPosted: 21. August 2012 20:59
by djabailey
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)

Re: Xampp 1.8 mysql connect slow

PostPosted: 22. August 2012 05:31
by Altrea
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

Re: Xampp 1.8 mysql connect slow

PostPosted: 22. August 2012 16:43
by meinemitternacht
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 :)

Re: Xampp 1.8 mysql connect slow

PostPosted: 22. August 2012 17:34
by JonB
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)

Re: Xampp 1.8 mysql connect slow

PostPosted: 22. August 2012 17:56
by meinemitternacht
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.

Re: Xampp 1.8 mysql connect slow

PostPosted: 25. September 2012 14:19
by Cobra427
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

Re: Xampp 1.8 mysql connect slow

PostPosted: 02. October 2012 11:51
by meinemitternacht
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

Re: Xampp 1.8 mysql connect slow

PostPosted: 22. February 2013 11:10
by tburba
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.

Re: Xampp 1.8 mysql connect slow

PostPosted: 10. April 2013 13:56
by Mathias-S
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.