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 (about 140). The webserver (Nginx) and database server (mysql) are hosted on separated machines, connected by a 100mbps LAN connection (hosted by Rackspace). Both machines are Dual Core AMD Opteron 2216HE 2.4 GHz with 4GB RAM and are running Red Hat Enterprise.
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 as reported with the devel module:
Executed 291 queries in 320.33 milliseconds. (homepage)
Executed 517 queries in 999.81 milliseconds. (content page)
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). I guess that must be added to every single 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).
key_buffer = 512M
max_allowed_packet = 16M
table_cache = 2560
sort_buffer_size = 3M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
thread_concurrency = 8
query_cache_size = 128M
open-files = 20000
interactive_timeout = 600
wait_timeout = 600
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M