We just received a Acquia Drupal 1.2.11 (Drupal 6.12 core) site to finish/handle/manage/maintain and because we are drupal noobies, we are looking for some guidance and tips with the performance/load problems we are experiencing with the site. The site with just 2/3 developers testing/using the site the performance is really poor and our server would even freezes sometimes for about 2/3 minutes.
A little about the Problem and its context:
We first start looking at this drupal site once it was moved to one of our new developing servers, and our monitoring system (nagios) start sending almost 3/5 incidents/reports per day about the server not being available over http.
Once we look at it we found that we were experiencing high cpu usage (~10 load average) and 20+ apache process going nuts/defunct or even a Apache segmentation fault mostly random(*).
We as drupal noobs, first look at a cron job for our drupal site that was running over wget, we try lowering frequency and even disabling it but we didn't get any performance increase.
So we start doing some more debuging and found out that lots of Apache process where defunct because mod_deflate was being used by the drupal site and it was failing sometimes when compressing the site pages, so we disable it and finally got some performance increase but nothing drastic and apache continues going nuts randomly(*)
So we start looking at any wrong server settings, but we are almost using the defaults one for debian packages, One can argue that the mysql settings are ugly, but because we are not sure its a problem regarding mysql we didn't change it.
we are already working on a new mysql configuration since we migrate drupal DB to InnoDB engine following Drupal standards and guidance.
But, Our site performance continues to be really poor... so here we are :) looking for some guidance from the forums. Hope there is all the information needed for you guys.
Thanks in Advance.
* We belive its not randomly, that it is trigger but some /function/insert/code once in a while and that cowardly fails
The Problem According to numbers :P
Drupal Performance Logging Summary
Average memory per page: 60.8 MB
Average milliseconds per page: 4,126.62
Total number of page accesses: 229
First access: 06/16/2009 - 09:50.
Last access: 06/18/2009 - 12:08
http://img233.imageshack.us/i/screenshotk.png/
Xdebug Output for site/index.php
http://rapidshare.com/files/246298648/cachegrind.out.28102
Current Server Settings
drupal settings.php
$db_url = 'mysqli://XXX:XXX@localhost/XXX';
$db_prefix = '';
$update_free_access = FALSE;
ini_set('arg_separator.output', '&');
ini_set('magic_quotes_runtime', 0);
ini_set('magic_quotes_sybase', 0);
ini_set('session.cache_expire', 200000);
ini_set('session.cache_limiter', 'none');
ini_set('session.cookie_lifetime', 2000000);
ini_set('session.gc_maxlifetime', 200000);
ini_set('session.save_handler', 'user');
ini_set('session.use_only_cookies', 1);
ini_set('session.use_trans_sid', 0);
ini_set('url_rewriter.tags', '');
$conf = array( 'menu_rebuild_needed' => FALSE);
mysql settings
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log = /var/log/mysql/mysql.log
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
expire_logs_days = 10
max_binlog_size = 100M
skip-bdb
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
php.ini settings
[PHP]
engine = On
zend.ze1_compatibility_mode = Off
short_open_tag = On
asp_tags = Off
precision = 12
y2k_compliance = On
output_buffering = Off
zlib.output_compression = Off
implicit_flush = Off
unserialize_callback_func=
serialize_precision = 100
allow_call_time_pass_reference = On
safe_mode = Off
safe_mode_gid = Off
safe_mode_include_dir =
safe_mode_exec_dir =
safe_mode_allowed_env_vars = PHP_
safe_mode_protected_env_vars = LD_LIBRARY_PATH
disable_functions =
disable_classes =
expose_php = On
max_execution_time = 120 ; Maximum execution time of each script, in seconds
max_input_time = 60 ; Maximum amount of time each script may spend parsing request data
memory_limit = 128M ; Maximum amount of memory a script may consume (128MB)
error_reporting = E_ALL & ~E_NOTICE
display_errors = Off
display_startup_errors = Off
log_errors = On
log_errors_max_len = 1024
ignore_repeated_errors = Off
ignore_repeated_source = Off
report_memleaks = On
track_errors = Off
error_log = /var/log/apache2/php.log
variables_order = "EGPCS"
register_globals = Off
register_long_arrays = On
register_argc_argv = On
auto_globals_jit = On
post_max_size = 8M
magic_quotes_gpc = On
magic_quotes_runtime = Off
magic_quotes_sybase = Off
auto_prepend_file =
auto_append_file =
default_mimetype = "text/html"
doc_root =
user_dir =
enable_dl = Off
file_uploads = On
upload_max_filesize = 2M
allow_url_fopen = On
allow_url_include = Off
default_socket_timeout = 60
[Date]
[filter]
[iconv]
[sqlite]
[xmlrpc]
[Pcre]
[Syslog]
define_syslog_variables = Off
[mail function]
SMTP = localhost
smtp_port = 25
[SQL]
sql.safe_mode = Off
[ODBC]
odbc.allow_persistent = On
odbc.check_persistent = On
odbc.max_persistent = -1
odbc.max_links = -1
odbc.defaultlrl = 4096
odbc.defaultbinmode = 1
[MySQL]
mysql.allow_persistent = On
mysql.max_persistent = -1
mysql.max_links = -1
mysql.default_port =
mysql.default_socket =
mysql.default_host =
mysql.default_user =
mysql.default_password =
mysql.connect_timeout = 60
mysql.trace_mode = Off
[MySQLi]
mysqli.max_links = -1
mysqli.default_port = 3306
mysqli.default_socket =
mysqli.default_host =
mysqli.default_user =
mysqli.default_pw =
mysqli.reconnect = Off
[mSQL]
msql.allow_persistent = On
msql.max_persistent = -1
msql.max_links = -1
[OCI8]
[PostgresSQL]
pgsql.allow_persistent = On
pgsql.auto_reset_persistent = Off
pgsql.max_persistent = -1
pgsql.max_links = -1
pgsql.ignore_notice = 0
pgsql.log_notice = 0
[Sybase]
sybase.allow_persistent = On
sybase.max_persistent = -1
sybase.max_links = -1
sybase.min_error_severity = 10
sybase.min_message_severity = 10
sybase.compatability_mode = Off
[Sybase-CT]
sybct.allow_persistent = On
sybct.max_persistent = -1
sybct.max_links = -1
sybct.min_server_severity = 10
sybct.min_client_severity = 10
[bcmath]
bcmath.scale = 0
[browscap]
[Informix]
ifx.default_host =
ifx.default_user =
ifx.default_password =
ifx.allow_persistent = On
ifx.max_persistent = -1
ifx.max_links = -1
ifx.textasvarchar = 0
ifx.byteasvarchar = 0
ifx.charasvarchar = 0
ifx.blobinfile = 0
ifx.nullformat = 0
[Session]
session.save_handler = files
session.use_cookies = 1
session.name = PHPSESSID
session.auto_start = 0
session.cookie_lifetime = 0
session.cookie_path = /
session.cookie_domain =
session.cookie_httponly =
session.serialize_handler = php
session.gc_divisor = 100
session.gc_maxlifetime = 1440
session.bug_compat_42 = 1
session.bug_compat_warn = 1
session.referer_check =
session.entropy_length = 0
session.entropy_file =
session.cache_limiter = nocache
session.cache_expire = 180
session.use_trans_sid = 0
session.hash_function = 0
session.hash_bits_per_character = 4
url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=,fieldset="
[MSSQL]
mssql.allow_persistent = On
mssql.max_persistent = -1
mssql.max_links = -1
mssql.min_error_severity = 10
mssql.min_message_severity = 10
mssql.compatability_mode = Off
mssql.secure_connection = Off
[Assertion]
[COM]
[mbstring]
[FrontBase]
[gd]
[exif]
[Tidy]
tidy.clean_output = Off
[soap]
soap.wsdl_cache_enabled=1
soap.wsdl_cache_dir="/tmp"
soap.wsdl_cache_ttl=86400
Apache2 settings
ServerRoot "/etc/apache2"
LockFile /var/lock/apache2/accept.lock
PidFile ${APACHE_PID_FILE}
Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
User ${APACHE_RUN_USER}
Group ${APACHE_RUN_GROUP}
AccessFileName .htaccess
DefaultType text/plain
HostnameLookups Off
ErrorLog /var/log/apache2/error.log
LogLevel warn
Include /etc/apache2/mods-enabled/*.load
Include /etc/apache2/mods-enabled/*.conf
Include /etc/apache2/httpd.conf
Include /etc/apache2/ports.conf
LogFormat "%v:%p %h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" vhost_combined
LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %b" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent
CustomLog /var/log/apache2/other_vhosts_access.log vhost_combined
Include /etc/apache2/conf.d/
Include /etc/apache2/sites-enabled/
Comments
Re: LF Guidance and tips regarding Drupal Performance.
Very interesting post. To begin with, you might find some luck by tweaking these in my.cnf:
query_cache_limit = 1M
query_cache_size = 16M
http://drupal.org/node/36628 also suggests about these.
The devel module (which you are already using) can tell you which queries are taking how long. You are already logging slow queries at /var/log/mysql/mysql-slow.log. Why not have a look?
Also, the web server error log should tell you why Drupal is crashing sometimes (Usually because it went out of memory).
These guys will be highly interested in this post : http://groups.drupal.org/high-performance :-)
HTH.