Performance tuning tips for Drupal 7 testing.

Opcode Caches

Make sure you are running an opcode cache since loading & parsing all the files take a while.

Non-professional, quick benchmarking by deekayen found varying speed improvements, best to worst as follows:

1. APC
2. xcache
3. eAccelerator

Note: even with extensive testing and trials with varying configurations, ALL the opcode cache options have a history of sporadic, varying, infrequent, and unreproducible failures of all kinds. For this reason, PIFR clients are not recommended to run opcode caching for live, production testing.

Installing APC on Debian/Ubuntu


# sudo apt-get install php5-dev pear
# sudo pecl config-set preferred_state beta
# sudo pecl install APC
# sudo echo "extension=apc.so
apc.shm_segments=1
apc.shm_size=32
apc.ttl=7200
apc.user_ttl=7200
apc.enable_cli=1
apc.stat=1
apc.stat_ctime=1" > /etc/php5/conf.d/apc.ini
# /etc/init.d/apache2 restart
apc.stat_ctime normally off by default, but when turned on "verification with ctime will avoid problems caused by programs such as svn..." Since PIFR is constantly doing CVS checkouts, it falls under the category of "programs such as svn". Reference and added documentation for additional APC variables:
http://us2.php.net/manual/en/apc.configuration.php

Installing xcache on Debian/Ubuntu


# sudo apt-get install php5-xcache
# echo "zend_extension = /usr/lib/php5/20060613+lfs/xcache.so
xcache.shm_scheme = "mmap"
xcache.size = 32M
xcache.count = 1
xcache.slots = 8K
xcache.ttl = 7200
xcache.gc_interval = 7200
xcache.test = Off
xcache.mmap_path = "/dev/zero"
xcache.cacher = On
xcache.stat = On
xcache.optimizer = On
xcache.coverager = Off" > /etc/php5/conf.d/xcache.ini
# /etc/init.d/apache2 restart
xcache.stat is on by default, but this configuration reinforces that setting. The testing process is constantly updating and changing files, so xcache needs to make sure when a file gets patched or CVS updated, that it refreshes the version cached in memory. It only knows to do that by looking at the last modification time through the statistics (stat) on the file's last changes. Some demo configurations around the internet will show that disabling xcache.stat will make xcache run faster because it doesn't check the last modification of each file. Ignore that recommendation for testing.

Installing eAccelerator on Debian/Ubuntu


# cd /opt
# wget http://bart.eaccelerator.net/source/0.9.5.3/eaccelerator-0.9.5.3.tar.bz2
# tar -xvjf eaccelerator-0.9.5.3.tar.bz2
# cd eaccelerator-0.9.5.3
# phpize
# ./configure --with-eaccelerator-shared-memory --with-eaccelerator-sessions
# sudo make
# sudo make install
# echo "extension = eaccelerator.so
eaccelerator.shm_size = 32
eaccelerator.cache_dir = /var/cache/eaccelerator
eaccelerator.enable = 1
eaccelerator.optimizer = 1
eaccelerator.check_mtime = 1
eaccelerator.debug = 0
eaccelerator.filter = ""
eaccelerator.shm_max = 0
eaccelerator.shm_ttl = 0
eaccelerator.shm_prune_period = 0
eaccelerator.shm_only = 1
eaccelerator.compress = 1
eaccelerator.compress_level = 9" > /etc/php5/conf.d/eaccelerator.ini
# /etc/init.d/apache2 restart
eaccelerator.check_mtime is on by default, but this configuration reinforces that setting. The testing process is constantly updating and changing files, so eAccelerator needs to make sure when a file gets patched or CVS updated, that it refreshes the version cached in memory. It only knows to do that by looking at the last modification time through check_mtime. Some demo configurations around the internet will show that disabling eaccelerator.check_mtime will make eAccelerator run faster because it doesn't check the last modification of each file. Ignore that recommendation for testing.

MySQL tuning

The basic MySQL install on a lot of servers is completely inadequate. Debian for one installs a very configuration file based on the mysql-tiny configuration. Read this for details: http://amrmostafa.org/benchmarking-mysql-in-memory-using-tmpfs

These are some things you can do to start tuning your MySQL:

• phpMyAdmin provides a listing with all the configuration and logging variables with some explanation and how your servers is doing. Go through the list and see which are green (good) and which are red (bad) and read how you can influence result. This should give you a basic understanding of the configuration options.
• At http://mysqltuner.pl/mysqltuner.pl there's a nifty script that analyses your configuration and recommends configuration changes. I've found it to be pretty helpful at that. You'll need Perl to run it.
• Make sure you are logging your slow queries and queries without indices and monitor your logs.

Run MySQL in memory

DamZ wrote a modified mysql init.d script for /etc/init.d/mysql on Debian 5 that runs MySQL databases entirely out of tmpfs. It's at
http://drupal.org/files/mysql-tmpfs.txt, attached to http://drupal.org/node/466972.

It allowed the dual quad core machine donated to move from a 50 minute test and huge disk I/O with InnoDB to somewhere under 3 minutes per test. It's live as #32 on PIFR v1 for testing.d.o right now. It is certainly the only way to go.

I have not and won't be trying it on InnoDB anytime soon if anyone wants to omit the step on skip-innodb below and try it on tmpfs.

Brief instructions:

uncomment skip-innodb in /etc/mysql/my.cnf
apt-get install rsync
Backed up /etc/init.d/mysql somewhere.
Drop the script in place.
mkdir /var/lib/.mysql
chown -R mysql:mysql /var/lib/.mysql
/etc/init.d/mysql stop
cd /var/lib/.mysql (to confirm files got copied there)
cd /var/lib/mysql
rm -r *
cd /var/lib
/etc/init.d/mysql start
cd /var/lib/mysql (to verify files moved back in)
df -h (to verify tmpfs is mounted at /var/lib/mysql)

InnoDB tuning

You might be able to optimize INNO for testing operations by creating /etc/mysql/conf.d/innodb.cnf with the following settings:


[mysqld]
innodb_log_file_size = 16M
innodb_log_group_home_dir = /var/lib/mysql
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_files_in_group = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 1M
innodb_thread_concurrency = 4
# If you have more than 512MB ram, you can increase the innodb_buffer_pool_size
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 10M
innodb_file_per_table = 1
# If you have raid5 or raid10 with more disks, you can increase innodb_file_io_threads
innodb_file_io_threads = 1
innodb_flush_log_at_trx_commit=0
innodb_max_dirty_pages_pct = 70
# If you run an OurDelta MySQL server, you can enable adaptive checkpointing.
# innodb_adaptive_checkpoint = 1

And also /etc/mysql/conf/tune.cnf with the following settings:


[mysqld]
default-storage-engine = InnoDB
sql_mode = NO_ENGINE_SUBSTITUTION
read_buffer_size = 512K
read_rnd_buffer_size = 512K
query_cache_limit = 128K
query_cache_size = 16M
sort_buffer_size = 16M
bulk_insert_buffer_size = 4M
tmp_table_size = 32M
max_heap_table_size = 32M
key_buffer = 64M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 8
max-connections = 32
table-cache = 256
thread-concurrency = 2

Because you're changing the size of the innodb transaction logs, you need to make sure these logs are flushed when you next shut MySQL down. Connect to mysql as root and run:


mysql> SET GLOBAL innodb_fast_shutdown=0;

You can now shut down mysql via /etc/init.d/mysql stop and move the existing trasnaction log files out of the way. (Do not delete them yet!)


mv /var/lib/mysql/ib_logfile* /root

Now start the mysql server again via /etc/init.d/mysql start and check that it is happy via tail /var/log/syslog. You should now have ib_logfile0 and ib_logfile1 files in /var/lib/mysql that are both 16MB in size.

If not and MySQL refuses to start, move the /root/ib_logfile* files back to where they came from and change innodb_log_file_size to 5M in innodb.cnf. You can then start mysql and re-try this procedure from the beginning.

Edit /etc/php5/apache2/php.ini and set mysql.allow_pesistent = Off as MySQL is keeping threads ready for clients and is more efficient at doing this than PHP.

Finally, convert the drupal tables to InnoDB format.


mysql_convert_table_format --user=root --password=secretpass --type=InnoDB drupal

Apache tuning

Disable or comment access.log settings. On Debian/Ubuntu, it is
CustomLog /var/log/apache2/access.log combined in /etc/apache2/sites-available/default, or scripted at the command line

perl -pi -e 's/(\s+CustomLog \/var\/log\/apache2\/access\.log combined)$/#\1/g' /etc/apache2/sites-available/default

Running the tests with concurrency

Add the following paramater to pifr.review.inc where it runs php (line 370-380):

--concurrency [number of cores of the server + 1]