UPDATE: Make sure that your Drupal settings.php connection string uses the IP address of your MySQL server (e.g. 127.0.0.1 for your local Windows machine), not 'localhost'. This change brought the execution time of a barebones Drupal install down from ~800-900ms to ~60-90ms.
Original post follows -----------------------
Fairly new to Drupal, trying to determine what kind of performance we should expect from a stock Drupal install, with the optional modules (not the barebones option).
We have a fresh dedicated Win 2008 server, 16GB RAM, 8 cores, etc. Hardware shouldn't be an issue.
Latest release of Drupal 7.x. FastCGI. WinCache enabled. MySQL with innoDB.
Load times: 1100-1300ms. Seems crazy high for a FRESH Drupal install (Bartik theme), no custom modules. This is authenticated user, so caching isn't really coming into play.
So, I'm curious, what's the fastest perf we should expect from a fresh Drupal install? Please list your stack (IIS, Apache, etc), your load speeds (code processing time/database query time). We're using the devel module (the only extra module) to collect the speed stats.
I don't see how Drupal can be useful without getting down to ~100ms with stock modules, ideally faster. Maybe we have a configuration problem, but this is exactly the same speed we're seeing in the dev environments.
Thanks all!
Comments
On my 4yr old Macbook Pro
On my 4yr old Macbook Pro running Apache for a stock D7 install I get authenticated pages in <50ms (maybe 130ms on a big form like user/[uid]/edit or node/[nid]/edit).
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Hi, Try your tests on Linux
Hi,
Try your tests on Linux with apache+ mod_php + xcache or eaccelerator.
Example:
http://demo.citrine.vps-private.net
Page execution time was 141.14 ms. Executed 137 queries in 19.41 milliseconds.
No cache. + devel module + some blocks and nodes with comments.
LAMP + SSD
Linux / Apache / mySql-innoDB / 2.4Ghz Quad Core / 8GB Ram / SSD
With a fresh D7 install (without APC opcode cache) I get ~200 ms
Enabling APC gives me ~60 ms of which 3ms is 33 database queries.
(I was also surprised that devel module didn't seem to add any significant overhead)
Windows?
These numbers are much more like what I would expect.
I also found this interesting discussion about IIS vs Apache: http://groups.drupal.org/node/234373. The numbers Canadaka is getting are way better than anything I'm seeing. Again, fresh Windows install with what I believe to be a proper PHP/FastCGI config.
One key thing there is that
One key thing there is that they are running MySQL 5.5, which saw some serious performance increases on Windows, especially when it comes to InnoDB:
http://dev.mysql.com/tech-resources/articles/5.5/mysql-55-faster-on-wind...
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
MySQL 5.5.25a
Jamie, thanks for your input!
I'm running MySQL 5.5.25a. I've just rechecked my php.ini. I've run phpinfo() and it appears fastcgi is configured and running. opcode appears to be getting hit.
I'm bewildered by this. If this were one machine, I'd have to doubt my configuration, but it's on multiple machines, and some that are completely fresh. Based on the numbers others are reporting, I can tell this is our problem, but it's strange that we are obviously missing something on each of our machines.
Exact Devel output
Here is my devel output from the homepage of a brand new D7 install:
Executed 33 queries in 7.25 ms. Queries exceeding 5 ms are highlighted. Page execution time was 1112.07 ms. Memory used at: devel_boot()=0.66 MB, devel_shutdown()=2.66 MB, PHP peak=2.75 MB. And this is after refreshing several times, the first load is more like 1900 ms.
simg: Your query number is the same as mine - the perf numbers, not so much. Grrr. If you have the time to check, how much memory is drupal using in your result?
Thanks!
my devel output
Executed 33 queries in 2.72 ms. Queries exceeding 5 ms are highlighted. Page execution time was 61.43 ms. Memory used at: devel_boot()=1.13 MB, devel_shutdown()=5.21 MB, PHP peak=5.5 MB.
Wiki
I have a wiki where there are some patches we run on D7 that do improve performance. Try out the 3 under smaller performance gains.
http://groups.drupal.org/node/210683
Untweaked windows?
Thanks mikeytown. I'll check those out.
Anybody out there with a stock drupal install on a fairly stock IIS/PHP/FastCGI environ that can chime in with stats? It would be much appreciated.
I crossposted this to Drupal
I crossposted this to Drupal on Windows to try to get more feedback from Windows users.
It would be interesting to add the Devel module and see (roughly speaking) whether time is being spent in the DB or php or something else. XHprof would give even more details about where, inside PHP, you are spending time.
knaddison blog | Morris Animal Foundation
WinCacheGrind
Doing some profiling. WinCacheGrind says the cumulative page execution time is 16.11ms. mySQL queries execute in 130ms. I'm starting to think this could be a connection issue/mySQL config.
odd
Usually the total time in cachegrind has the mysql queries included in it. Could upload your cachegrind file somewhere so we could take a look at it?
Hey, I was just about to add
Hey, I was just about to add a link to the WinCacheGrind report when we definitively found the problem: We had to change the Drupal connection string to the IP address of the mySQL server (e.g. 127.0.0.1), instead of using 'localhost'. This change took the PHP execution time down from 900ms to 60-90ms. Thanks!!
hosts
On windows localhost is bound to the ip6 and ip4 address by default so you either have to disable ip6 or add the following to your hosts file (C:\Windows\System32\drivers\etc\hosts): 127.0.0.1 localhost
I you don't do this ip6 will be tried first, if it fails ip4 is tried.
ip4
What is odd is that we had already disabled ip6, while trying to solve this problem, and that didn't do it. Furthermore, the hosts file already had the '127.0.0.1 localhost' entry. So, you still have to specifically use 127.0.0.1 in the connection string (at least this has proved true for the Windows 7 or Server 2008 configurations we have running), in order to fix this perf issue.
Since there are plenty of Windows users hosting Drupal, I think it may be advisable to include a comment about this is in the template settings file that ships with Drupal. 1000% improvement is pretty dramatic, and this change has resulted in similar improvement on the three dev machines we've now tested it on.
I wonder how many Windows Drupal users think ~1 second page execution time, for authenticated users, is par?
dns lookups
This is because of dns lookups. It is a problem for windows and *nix boxes alike.
DNS lookups aren' t that
DNS lookups aren' t that slow.
Actually I remember seeing this problem on Windows before and it seemed to have started around the time of Vistas birth. When I first discovered it on my development machine back then, there was a lot of different blame. It went from PHP to MySQL to Windows IPv6 handling to Windows Firewall. The thing is I did spend some troubleshooting time and it appears to be something in PHP. For example, I could connect to memcache using "localhost" and the connect time was ~1ms. Connecting to MySQL and it jumped up to 500ms-1second. Changing to 127.0.0.1 fixed that. Likewise I tried a MySQL connect via "localhost" in Perl and had no problem.
What's even more interesting is that not long after discovering that, I started using Acrylic DNS proxy so that I could do a wild card TLD to point at my machine (*.dev) and not have to edit hosts every time I started a new project. Using that also fixed the problem and I could use localhost again, with 127.0.0.1 defined for it in Acrylic's host file.
It's a weird problem and if you Google "mysql connect slow php windows" you get tons of results. But like I said, near as I can tell it is actually something inside of PHP and the MySQL drivers.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
This was really confusing for
This was really confusing for us, as we are mostly familiar with MS SQL server/.NET development where we would typically use a named instance of SQL server. Since most connection string examples use localhost, including the Drupal settings template, it would seem this is something that many 'newbies' get caught by.
I recommend that the default settings template be updated, at least with a comment. We spent a LOT of time trying to figure out what was wrong with our PHP configuration, drupal modules, etc. before getting to this cause.
MySQL DNS Lookups
Hi,
DNS lookups might be an issue when you have a lot of remote clients. It doesn't affect local connections.
There's a good explanation on the MySQL docs
To disable DNS lookups you can just add the following line to your my.ini:
skip-name-resolveIn my tests I didn't notice any significant difference.
Alessandro Pilotti
MVP ASP.Net/IIS
Windows Azure Insider
Very interesting. This should
Very interesting. This should be taken out of discussion here and placed on some performance wiki page.
End goal
greggles, thanks for that. I do have Devel installed and the figures I've listed are from that module. The devel module shows the majority of time is not DB, but I don't think that rules out the db/MySQL.
Ultimately, I think (hope!) there is going to be something we're just missing that does improve perf (a lot). Once I do figure this out I will post some IIS/Drupal considerations from the perspective of setting up a new environment, since there must be something I'm just missing.
I come from a .NET/MSSQL background and have lots of experience with perf tuning .NET apps. I (and my team) don't have nearly as much experience with PHP and IIS, so I assume this is something that would be of interest to anyone expanding into PHP/Drupal.
How did you install php? Are
How did you install php? Are you using the php manager plugin for IIS?
edit
Actually, it's worth verifying your installation as a whole:
PHP Version should be -
VC9 x86 Non Thread Safe (2012-Jun-13 22:41:43)
+the latest 2008 runtime
Microsoft Visual C++ 2008 Redistributable Package
Drupal install
Drupal is installed manually (download source, setup IIS site), not via WPI. Yes, using the PHP Manager.
Something odd: I uninstalled the Microsoft Visual C++ Redis Package, but everything continues to run. I assume uninstall didn't remove from the GAC. Not sure. But, I've abandoned that track temporarily while I'm using WinCacheGrind (for the first time). Numbers are kinda interesting. 16.11ms for the same page. I'm starting to think this is mySQL connection related.
CORRECTION: WINCACHEGRIND SAYS 16,111, not 16.11. So still looking at PHP execution issues.
Drupal on IIS
There's an interesting thread about Drupal 7 performance on IIS vs Apache.
I'm a big user of Apache on Linux, but on Windows I personally prefer IIS due to it's better integration with the OS and general better performance due, among other things, to the great http.sys kernel level request processing.
To start with, make sure to enable WinCache, run PHP 5.3 via FastCGI (not ISAPI!) and possibly use a recent version of MySQL.
You can simply use WebPI to install WinCache, PHP 5.3 and enable FastCGI or you can also do it manually if you prefer to do so.
Note: it's recommended to put the MySQL host IP address instead of the host name in the Drupal settings.
If you'd like to go further, compiling the PHP executable with PGO can provide an additional boost.
Memcache and Solr are also great performance boosters in a lot of scenarios and you can install them on Windows as well as on Linux.
BTW We have a full series of free webcasts on how to configure and optimize Drupal 7 on IIS :-)
Alessandro Pilotti
MVP ASP.Net / IIS
Windows Azure Insider
WinCacheGrind
Thanks for your response Alex. I have been watching your screencasts. I'm getting close to exploring compiling a custom PHP exe with PGO.
I've just profiled the app with interesting results (reviewing using WinCacheGrind): The cumulative page processing time is 16.11ms! The Devel module shows the same page "Page execution time was 4528.91 ms". I'm now wondering if the problem is something to do with connection to mySQL server, since the queries execute in 103.4 ms. So there's something wrong with negotiation, I suppose.
The conn string is using the host IP address instead of localhost (mySQL is running on same machine, so using 127.0.0.1).
If you have any other suggestions, would be much appreciated! Thanks again.
CORRECTION: WINCACHEGRIND SAYS 16,111, not 16.11. So still looking at PHP execution issues.
Explanation, proof and solution for MySQL slow connections
Hi guys,
as this is a very common issue with MySQL on Windows, I wrote in my previous post the following line:
Let me show you what happens in practice if you just write "localhost" as your server address.
Create a simple php file (e.g. testconn.php) with the following content:
<?php
function test_connect($addr)
{
$start = microtime(true);
$con = mysql_connect($addr,"root","Passw0rd");
if (!$con)
die('Could not connect: ' . mysql_error());
$time = microtime(true) - $start;
echo "Connection time to $addr: $time seconds\n";
mysql_close($con);
}
test_connect("localhost");
test_connect("127.0.0.1");
test_connect("::1");
?>
Now let's run it:
C:\>php testconn.phpConnection time to localhost: 1.0159001350403 seconds
Connection time to 127.0.0.1: 0.00072979927062988 seconds
Connection time to ::1: 1.0262320041656 seconds
as you can see there's a HUGE difference in the connection time if you explicitly put an IPv4 address.
Here's why.
If you ping localhost on a default setup of Windows Server 2008 / Vista and above, you'll get an IPv6 address:
[::1]
But if you check your MySQL < 5.5.3 listening endpoint with:
netstat -an | findstr 3306you'll get that it's only listening on IPv4 (as IPv6 is supported only by MySQL >= 5.5.3):
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENINGHow do you solve it? You can just put an IPv4 address (or a name that resolves to an IPv4 address only) or upgrade to MySQL >= 5.5.3 AND set bind-address=:: in you my.ini (restarting the MySQL service afterwards).
Let me put the last sentence in bold:
EVEN IF YOU HAVE MYSQL 5.5.3 OR ABOVE, IF YOU DON'T SET bind-address=:: IN YOUR my.ini, IT WILL NOT LISTEN ON IPv6 AND CONNECTIONS TO localhost WILL STILL HAVE HUGE DELAYS!
Here's the result:
Connection time to localhost: 0.0042622089385986 secondsConnection time to 127.0.0.1: 0.001816987991333 seconds
Connection time to ::1: 0.015259027481079 seconds
If you want to read more about MySQL bindings, here's the manual page
Hope this helps! :-)
Alessandro Pilotti
MVP ASP.Net / IIS
Windows Azure Insider
Addtional MySQL tests
I did some interesting tests to see if the delay in the MySQL connection could be related to the PHP driver.
Here's a simple Powershell script that replicates the PHP sample that I wrote in the previous post by using the MySQL .Net connector:
Function TestConnect($addr)
{
$a = [Reflection.Assembly]::Load("MySql.Data, Version=6.3.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d")
$sw = new-object System.Diagnostics.Stopwatch
$sw.Start()
$cn = new-object MySql.Data.MySqlClient.MySqlConnection("server=" + $addr + ";user=root;password=Passw0rd;pooling=false")
$cn.Open()
$sw.Stop()
"Connection time to " + $addr + ": " + $sw.ElapsedTicks / 10000000 + " seconds"
$cn.Close()
}
TestConnect("127.0.0.1")
TestConnect("localhost")
TestConnect("localhost")
TestConnect("127.0.0.1")
By running it with:
powershell .\TestConnect.ps1We get:
Connection time to 127.0.0.1: 0.198912 secondsConnection time to localhost: 1.0116756 seconds
Connection time to localhost: 1.0300329 seconds
Connection time to 127.0.0.1: 0.0206324 seconds
So results are similar to what we obtained in PHP. But by setting "pooling=true" in the connection string, we get:
Connection time to 127.0.0.1: 0.2195582 secondsConnection time to localhost: 1.0156127 seconds
Connection time to localhost: 0.0013516 seconds
Connection time to 127.0.0.1: 0.0003432 seconds
The interesting part here is that connecting to "localhost" is still very slow, but only the first time. The following one is faster due to the pooling.
Alessandro Pilotti
ASP.Net MVP/IIS
Windows Azure Insider
thank you & give me advise
thank you for your solution its like a present, I could not understand why the site is slow, and the configuration file of Drupal in general I would never have touched. I do not think that these things can be a problem. because 127.0.0.1 and loсalhost for us it's always been the same. When i changed only host string its change nothing, but when i also set port : "3306", it gives effect.
In general we have 200ms for speed loading for clear drupal on local mashine. But i'm curious what we have on real hosting? With no cache drupal options, no op cache php extensions. I checked 2 free hostings and they have ~560ms for garland home page.
This is important for me cause i have slow core loading on my general hosting.
And this is very similar to the situation with the local machine where I had unreasonably 1200ms. for pure Drupal is not changed settings.fp. But what to do in such situation on real hosting? When we usually get to setup something like:
array (
'database' => '734564_mysitename',
'username' => '7030564_u1',
'password' => 'wegwehweh',
'host' => 'mysql51-004.wc1.ord1.stabletransit.com',
'port' => '',);
How to detect sql problem in this situation ? and i can't set ip adress of host , cause i dont know it.
How likely is it at all possible that hosting have such a problem as incorrect settings for their my.ini sql server?
Still applies to IIS 8.5 & 2012 R2 server?
Hello, I've been experiencing intense 100% cpu usage on a win 2012 server using MySQL 5.6.x community and PHP 5.3.x.
I'm running MySQL on the same server, using IIS 8.5, as PHP and Drupal so wanted to confirm that I should still add the:
bind-address=127.0.0.1
to the my.ini file?
Currently my ini file has no bind-address explicitly set.
I have it in my settings.php file as well as in the host file.
That works. Windows has
That works. Windows has problems in PHP accessing databases via hostnames. My dev server is Windows and I was getting close to 2 second page times. Changed everything from localhost to 127.0.0.1 and it dropped to under 200ms.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
Agree 100%
Use a socket if possible, if not an IP address is preferred to a hostname.
I created a module that usually helps to speed up the database layer and uses an async db connection for things like the cache: https://www.drupal.org/project/apdqc
Has anyone tried using SQL
Has anyone tried using SQL Server (or PostgreSQL for that matter) and seeing if the problem persists or if it's just an issue with MySQL?
The Boise Drupal Guy!
It's just an issue with
It's just an issue with MySQL. I didn't check with MariaDB but it's on my TODO list.
It's a configuration error
This isn't an issue with mysql, so much as your mysql configuration. If you have IPv6 enabled, then localhost quite correctly maps to the IPv6 interface as well as IPv4. You should arrange for mysql to listen to that interface, which you can presumably do in your config file.
It probably is reasonable to say that there's a bug in the packaging of mysql for windows if the default mysql.ini file gets this wrong.
I am also Experiencing issues with WAMP!
SSD, 8 Gigs Ram, WAMP install on Fresh Windows 7... Basically same issue as original author!
Fix your dns
If You've got problems with your DNS lookup times, then fix your DNS server. Perhaps you have two configured and the first is timing out?
You could just avoid the problem with a hosts file entry. It's recommended practice on a server to always do this, and usually I wouldn't use the real dns name, but rather a name that describes the role within the set of servers. eg it might just be 'db', or '<sitename>-db'.
I try to arrange things so real hostnames and IPs don't appear anywhere in configuration files other than the hosts file, and the hosts file translates the names I use in the config files to IP addresses, and usually it has the real dns name also to make things clear. This makes things a lot easier when you move things around between servers, or redirect queries to a new db server, or whatever.