In order to optimize our architecture, I tried to run the mysql database on a remote dedicated server.
In consequence, the performance dropped dramaticall.
The remote server (24 GB RAM, 8 cores 3 GHz) pings bteween 0.5 to 0.8 ms from the Drupal server.
Is it normal, and I shall forget this option, or may I had missed something?
Below my.cnf of this server (InnoDb tables)
Thanks for help
[mysql]
CLIENT
port = 3306
socket = /var/lib/mysql/mysql.sock
socket = /var/run/mysqld/mysqld.sock
[mysqld]
GENERAL
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/lib/mysql/mysql.pid
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address=188.165.208.179
MyISAM
key-buffer-size = 32M
key_buffer_size = 64M
myisam-recover = FORCE,BACKUP
from old
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 32M
myisam_sort_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_max_sort_file_size = 5G
SAFETY
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1
DATA STORAGE
datadir = /var/lib/mysql/
BINARY LOGGING
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
CACHES AND LIMITS
tmp-table-size = 32M
tmp_table_size = 512M
max-heap-table-size = 32M
max_heap_table_size = 512M
query-cache-type = 0
query_cache_type = 1
query-cache-size = 0
query_cache_size = 64M
max-connections = 500
thread-cache-size = 50
thread-cache-size = 64
from old
thread_stack = 192K
thread_concurrency = 8
query_cache_min_res_unit = 2K
query_cache_limit = 2M
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
INNODB
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 16G
from old
max_binlog_size = 100M
binlog_cache_size = 1M
LOGGING
log-error = /var/lib/mysql/mysql-error.log
log_error = /var/log/mysql/error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 0.05
Comments
Sometimes hosting the db
Sometimes hosting the db remotely can drastically affect performance. The first thing I would do is find one of your queries that are slow coming from your web server, and then run the exact same query locally on the db server. From there, it'll give you a bit of info on how the distance is affecting your speed.
Premium Drupal Commercial themes
Remote databases
First off, remote databases are generally a bad idea for applications that do lots of queries. By remote, I mean not on your LAN, and have less bandwidth and more latency than a LAN.
Having said that, delays like this are well known, and often caused by reverse DNS not being able to resolve the client.
See a discussion and solution here
http://www.mysqlperformanceblog.com/2008/05/31/dns-achilles-heel-mysql-i...
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
I wonder why
I wonder why skip_name_resolve - reportedly ON in variable status - does not seem to have any effect.
Setting $db_url to either IP address or sever name does not change anything. MySQL seems to always resolve the DNS.
When opening the module page Devel reports
- Local : Executed 1336 queries in 925.45 millisecond - fastest 0.04 ms - slowest 450 ms
- Remote : Executed 1337 queries in 111681.21 milliseconds. - fastest 0,46 - slowest 1530
This matches the Ping latency effect apparently.
I think I have to abandon this bad idea.
How Drupal can be scalable if it cannot be configured this way?
The ping times aren't really
The ping times aren't really all that high, but ping also isn't a good indicator of network latency. A better test it to check the transfer speeds between the 2 servers. There's a lot of ways you can do this(lperf, netcat, etc.), but the simplest is just using RSync and transfer a file from the web to the database and then back again, so you get both an idea of upstream and downstream speed.
As was said in an early comment, you really need to take your slowest query and run that on the database server directly through the MySQL command line. There's a very good chance you have a problem in your database causing the queries to execute slow.
Having a separate DB and Web server is very common in scaling Drupal, but is almost always done with the 2 machines on the same lower network. The further apart the machines are, the more routers and switching stations you have to go through, meaning the more latency you will have, plus you greatly increase you possibility of failures.
But you also have problems in your Drupal outside of this. On your local test "slowest 450 ms". Right there you have a horrible query that is going to affect performance, no matter where the database is. And looking at those numbers, that isn't the only problem query in there. Locally all queries were executed in 925ms. Substract the 450ms for the slowest and you got 375ms left over. Divide that 375ms by the 1335 remaining queries and you got an average of .28ms execution time for the remaining queries. That right there shows you have other slow running queries that need addressed.
Honestly, fix those slow queries and you will probably find out that you don't even need a dedicated DB server. 1336 queries, even on the module page is awfully high. I just checked on a D7 site with about 70 modules enabled. With the cache cleared, the module page is only showing 248 queries. Check on a more common page, like your front page and see how many queries are being executed there and see if you can reduce anything through caching (including different backends) or reworking views.
Just remember this golden rule - you want to make sure that your site is running at optimal performance before throwing more hardware at it. When you try things like switching to a dedicated database, then you aren't really fixing the problem, but rather trying to masquerade it. As your site matures and you get more content, users, taxonomy terms, etc. in the database, then the problems will suddenly return.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
Just remember this golden
This. Over the years I've been following this group, we've seen a lot of threads like this; "My server was slow and I didn't know why, so I threw more expensive hardware at the problem, but it's still slow! Drupal sucks!"
You've got to understand the problem if you want to solve it the best way. Otherwise, you're just wasting time, money, and sanity.
The Boise Drupal Guy!
but ping also isn't a good
Not sure that I agree with this. Most queries return a few hundred bytes at the most. Latency is the important thing when it comes to network performance of DB servers.
Your my.cnf looks like a pretty good starting point. My only recommendation would be to change innodb-flush-log-at-trx-commit to something other than 1, and sync-binlog to 0 (most Drupal sites will be just fine if they loose 2s of transactions during the extremely rare event of a server crash / power outage).
As other people have mentioned your biggest issue appears to be the total number of queries. A page with a primed cache for an authenticated user (assuming that you are using an alternative cache back-end like Memcache) typically has no more than a couple dozen fast queries (or even down to <10 if you've really made efforts to cache things). Even for an uncached page it would be rare to see more than about 500 queries (again, this is assuming that you are using something like Memcache).
In short I agree with the other posters, you seem to have an underlying issue that the remote DB exacerbates. Fix the underlying issues first, you might not even need an external DB (unless you are doing so for added redundancy or greater scalability which are different beasts).
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
There are two good hypotheses
There are two good hypotheses on the table and you have to figure out some tests to eliminate one of them (and ideally a test that positively confirms one of them):
To test the first hypothesis, find a complex query and run it on both environments using the mysql cli. What are the numbers?
To test the second you can do the ping tests, which I agree with dalin are a pretty legitimate test.
I also agree with the other advice about trying to eliminate and/or speed up queries. It seems like you've got the problem of having way too many queries AND that your slowest query(or queries, more likely) are too slow AND that all of your queries (against the remote db, anyway are taking too much time). It's good to separate the problems and work on them all individually.
knaddison blog | Morris Animal Foundation
Some measures - seems good now
First of all, the many queries came from the specfic situation I selected for challenging the remote database: there is nothing I can do to limit the queriies on the module page apart removing active and inactive modules...
In operating conditions, the number of queries for an authenticated user is between 90 to 500 - I've never ran a Drupal site with less than that.
I changed the settings as suggested by Dalin, and look closer at the query reports
1) the short queries are directly affected by the ping. those firing at 0,02ms surge at 0,5 of course.
2) Complex queries perform similarily on both servers. For example, module_rebuild_cache takes 40 ms about in both cases
3) some queries seem faster on remote such as cache_clear_all
4) Opening the complex module page takes
- 1345 queries in 743.4 milliseconds local
- 1346 queries in 1203.94 milliseconds remote
many short queries, so this is normal
Running a Pingdom test 5 times - cache disabled:
Remote : 1.18 1.60 .938 2.18 2.15
Local: .633 1.94 1.89 1.94 .663
So this not much different.
I suspect that the change in database settings had a significant -positive - impact.
Actually, admin tasks running many queries would be affected, though the normal site operations seems acceptable.
Just a last question: the database server seems to respond aven then using the name of the querying server instead of the IP address.
Does that mean that skip_name_resolve is not ON, though it is according the Mysql status
Thank you all for your help.