Performance issues with INNODB vs MyISAM
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.