MySQL queries in my production environment are taking much longer than I would expect them too. The site in question is a fairly large Drupal site, with many modules installed. The webserver (Nginx) and database server (mysql) are hosted on separated machines, connected by a 100mbps LAN connection (hosted by Rackspace).
I have the exact same site running on my laptop for development. Obviously, on my laptop, the webserver and database server are on the same box.
Here are the results of my database query times:
Production:
Executed 291 queries in 320.33 milliseconds. (homepage)
Executed 517 queries in 999.81 milliseconds. (content page)
Development:
Executed 316 queries in 46.28 milliseconds. (homepage)
Executed 586 queries in 79.09 milliseconds. (content page)
As can clearly be seen from these results, the time involved with querying the MySQL database is much shorter on my laptop, where the MySQL server is running on the same database as the web server.
Why is this?!
One factor must be the network latency. On average, a round trip from from the webserver to the database server takes 0.16ms (shown by ping). That must be added to every singe MySQL query. So, taking the content page example above, where there are 517 queries executed. Network latency alone will add 82ms to the total query time. However, that doesn't account for the difference I am seeing (79ms on my laptop vs 999ms on the production boxes).
What other factors should I be looking at? I had thought about upgrading the NIC to a gigabit connection, but clearly there is something else involved.
I have run the MySQL performance tuning script from http://www.day32.com/MySQL/ and it tells me that my database server is configured well (better than my laptop apparently). The only problem reported is "Of 4394 temp tables, 48% were created on disk". This is true in both environments and in the production environment I have even tried increasing max_heap_table_size and Current tmp_table_size to 1GB, with no change (I think this is because I have some BLOB and TEXT columns).
