Not sure where is the most appropriate post for this, let me know if anybody has suggestion.

I am having performance issues with INNODB. My major issue is that with INNODB I am getting approximately 20 times slower performance than with MyISAM. For the purpose of testing I have used Drupal 7 with devel module installed. Further more I used 2 bare bone servers with default installations so I can do parallel comparisons.

The objective here is not to get Drupal 7 to run on MyISAM but to get Drupal 7 to run on INNODB at optimal performance (or at least close to performance when running on MyISAM)

Below are detailed setup installation instructions:

SERVER 1 - Debian 7 - INNODB

Starting with bare bone installation
aptitude install ssh -vV
aptitude install mysql-server -vV

aptitude install apache2 -vV
aptitude install php5 -vV
aptitude install php5-gd -vV
aptitude install php5-mysql php5-cgi php5-cli php5-curl -vV

No changes in default my.cnf.
No changes in default apache configuration
No changes in phi.ini
In short: no changes are done except for the above installation steps, we are running all defaults.

PACKAGE VERSIONS:

apache2 -v
Server version: Apache/2.2.22 (Debian)
Server built: Dec 23 2014 22:48:29

php5 --version
PHP 5.4.39-0+deb7u2 (cli) (built: Mar 25 2015 08:33:29)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies

mysqld --version
mysqld Ver 5.5.41-0+wheezy1 for debian-linux-gnu on x86_64 ((Debian))

lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 7.8 (wheezy)
Release: 7.8
Codename: wheezy

Setup Drupal 7 with Devel

- MySQL user for Drupal installation created with:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password';
- default Drupal 7.35, minimal profile
- downloaded and installed modules: admin_menu, devel
- create Content Type "Article", turn off: "Promoted to front page" and "Display author and date information"
- Under Development, use Devel's module for generating content (nodes): 3000 nodes, 1 month back in time and max number of words in title 4

SERVER 2 - Debian 7 - MyISAM

Starting with bare bone installation
aptitude install ssh -vV
aptitude install mysql-server -vV

aptitude install apache2 -vV
aptitude install php5 -vV
aptitude install php5-gd -vV
aptitude install php5-mysql php5-cgi php5-cli php5-curl -vV

No changes in default my.cnf.
No changes in default apache configuration
No changes in phi.ini
In short: no changes are done except for the above installation steps, we are running all defaults.

Configure to use MyISAM instead of INNODB
nano my.cnf
skip-innodb
default-storage-engine=myisam

PACKAGE VERSIONS:

apache2 -v
Server version: Apache/2.2.22 (Debian)
Server built: Dec 23 2014 22:48:29

php5 --version
PHP 5.4.39-0+deb7u2 (cli) (built: Mar 25 2015 08:33:29)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies

mysqld --version
mysqld Ver 5.5.41-0+wheezy1 for debian-linux-gnu on x86_64 ((Debian))

lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 7.8 (wheezy)
Release: 7.8
Codename: wheezy

Setup Drupal 7 with Devel

- MySQL user for Drupal installation created with:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password';
- default Drupal 7.35, minimal profile
- downloaded and installed modules: admin_menu, devel
- create Content Type "Article", turn off: "Promoted to front page" and "Display author and date information"
- Under Development, use Devel's module for generating content (nodes): 3000 nodes, 1 month back in time and max number of words in title 4

TESTING RESULTS

TEST 1: SERVER 2 - Debian 7 - MyISAM

Running Drupal 7 Devel module for generating content (nodes): 3000 nodes, 1 month back in time and max number of words in title 4.
Default my.cnf
Default everything else (see above Server and PHP settings)
Yields:
1ST RUN: 821.31 QPS 20sec
2ND RUN: 885.97 QPS 20sec

TEST 2: SERVER 1 - Debian 7 - INNODB

Running Drupal 7 Devel module for generating content (nodes): 3000 nodes, 1 month back in time and max number of words in title 4.
Default my.cnf
Default everything else (see above Server and PHP settings)
Yields:
1ST RUN: 107.9 QPS 5min & 5sec
2ND RUN: 115.02 QPS 5min & 3sec

TEST 3: SERVER 1 - Debian 7 - INNODB

Running Drupal 7 Devel module for generating content (nodes): 3000 nodes, 1 month back in time and max number of words in title 4.
1)
Added following to my.cnf
max_allowed_packet=32M
concurrent_insert=1
query_cache_type=OFF
innodb_buffer_pool_size=3G
innodb_log_file_size=256M
innodb_thread_concurrency=10

2)
PHP:
realpath_cache_size = 128k
realpath_cache_ttl=3600

3)
OS:
Changed php5-mysql with php5-mysqlnd

4)
Drupal:
added module apdqc and following configuration:
$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";
$conf['cache_backends'][] = 'sites/all/modules/apdqc/apdqc.cache.inc';
$conf['cache_default_class'] = 'APDQCache';
$conf['lock_inc'] = 'sites/all/modules/apdqc/apdqc.lock.inc';
$conf['session_inc'] = 'sites/all/modules/apdqc/apdqc.session.inc';
$databases['default']['default']['unix_socket'] = '/var/run/mysqld/mysqld.sock';
$databases['default']['default']['mysql_db_type'] = '';
$conf['page_cache_invoke_hooks'] = FALSE;

Yields:
1ST RUN: 123.39 QPS 4min & 50sec
2ND RUN: 123.61 QPS 4min & 51sec

TEST 4: SERVER 1 - Debian 7 - INNODB

Running Drupal 7 Devel module for generating content (nodes): 3000 nodes, 1 month back in time and max number of words in title 4.
1)
Added following to my.cnf:
max_allowed_packet=32M
concurrent_insert=1
query_cache_type=OFF
innodb_buffer_pool_size=3G
innodb_log_file_size=256M
innodb_thread_concurrency=10
innodb_flush_log_at_trx_commit=2

2)
PHP:
realpath_cache_size = 128k
realpath_cache_ttl=3600

3)
OS:
Changed php5-mysql with php5-mysqlnd

4)
Drupal:
added module apdqc and following configuration:
$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";
$conf['cache_backends'][] = 'sites/all/modules/apdqc/apdqc.cache.inc';
$conf['cache_default_class'] = 'APDQCache';
$conf['lock_inc'] = 'sites/all/modules/apdqc/apdqc.lock.inc';
$conf['session_inc'] = 'sites/all/modules/apdqc/apdqc.session.inc';
$databases['default']['default']['unix_socket'] = '/var/run/mysqld/mysqld.sock';
$databases['default']['default']['mysql_db_type'] = '';
$conf['page_cache_invoke_hooks'] = FALSE;

Yields:
1ST RUN: 1260 QPS 0min & 17sec
2ND RUN: 1090 QPS 0min & 18sec

QPS measurements were done using innotop.

I have ran many other test configurations based on suggestions from tuner primers and on line guide lines, but have not reached desired performance on INNODB. Therefore I have chosen to deliberately post here testing results of most default configuration and try to compare it side by side.

Does anybody has any idea how to get performance level of INNODB at the level comparable to MyISAM.

Update 4/28/2015: In addition I am posting two additional tests for INNODB with my.cnf settings as attempts to improve performance. Also PHP was changed and also php5-mysqld was replaced with php5-mysqlnd.

Comments

milovan’s picture

I have similar problem.
I managed to speed up Drupal 7 by using parameter innodb_flush_log_at_trx_commit on either 0 or 2. However, downside is that in the case of server / MySQL crash a few seconds of transactions might be lost, which is not acceptable on e-commerce sites. So, I am still looking how to safely improve InnoDB performance.

Stefan Lehmann’s picture

There is a group, which is dedicated to performance related questions. Might help to ask there: https://groups.drupal.org/high-performance.

I like cookies!