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
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 yields:
1ST RUN: 107.9 QPS 5min & 5sec
2ND RUN: 115.02 QPS 5min & 3sec
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 yields:
1ST RUN: 821.31 QPS 20sec
2ND RUN: 885.97 QPS 20sec
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.
Comments
APDQC
See what the status report page says after installing apdqc and fix the issues that it brings up https://www.drupal.org/project/apdqc
innodb buffer-pool size
Make sure your innodb buffer-pool size is sized correctly based on the ram on your test machines.
mikeytown2 I have installed
mikeytown2 I have installed apdqc module, thank you. To satisfy the status page issues I did following:
OS:
aptitude install php5-mysqlnd -vV
(this removed php5-mysql)
MySQL:
max_allowed_packet=32M
query_cache_type=OFF
PHP:
realpath_cache_size = 128k
realpath_cache_ttl=3600
settings.php:
$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;
Note: we ignored suggestion for parameter innodb_flush_log_at_trx_commit = 2 and left default (1).
Also semaphore table cannot be converted as we use MySQL less than 5.6 (5.5).
Performance increased to 119QPS (from 100QPS) and run time improved by 2 seconds (5min and 2sec). I repeated tests several times and averaged the results. Although there is some improvement it is still way below what I am getting at MyISAM. Any clue what I am terribly missing here.
Write based test
innodb_flush_log_at_trx_commit = 2 would really help the write speed.
http://dba.stackexchange.com/questions/16395/mysql-insert-performance-in...
carsonevans. I put back
carsonevans. I put back innodb buffer-pool to be 3GB. Performance improvement was slight or negligible (~120 QPS and 5min 3sec).
I am still puzzled how to get to MyISAM performance level (20x faster).