Hi,
I'm moving a DB from a NDBCluster to a MyISAM Mysql server, using mysqldump.
The porting looks ok, but I got serious performances problem on some nested queries.
The same query that on NDB takes 0.03 sec, on MyISAM takes about 17 secs.
Using the explain select command I discovered that on MyISAM the indexes usage differs, on one table they aren't considered, even if they are declared.
So, the same query on the migrated DB tooks significant more time ( 17 secs instead of 0.03 secs , very bad for our application :( ).
How I can solve this issue ?
Below the details of versions, explain select and environment variables for both situations.
---------------------------------------------------------------
MyISAM configuration (the bad performance case)
mysql-server x86_64 5.1.73-3.el6_5
select id,user_login,user_email from wp_users where id in (select user_id from wp_wpsc_purchase_logs where from_unixtime(date) > date('2014-11-30') and processed=3);
773 rows in set (17.24 sec)
| wp_users | CREATE TABLE `wp_users` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_login` varchar(60) NOT NULL DEFAULT '',
`user_pass` varchar(64) NOT NULL DEFAULT '',
`user_nicename` varchar(50) NOT NULL DEFAULT '',
`user_email` varchar(100) NOT NULL DEFAULT '',
`user_url` varchar(100) NOT NULL DEFAULT '',
`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_activation_key` varchar(60) NOT NULL DEFAULT '',
`user_status` int(11) NOT NULL DEFAULT '0',
`display_name` varchar(250) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`),
KEY `user_login_key` (`user_login`),
KEY `user_nicename` (`user_nicename`)
) ENGINE=MyISAM AUTO_INCREMENT=3921 DEFAULT CHARSET=utf8 |
| wp_wpsc_purchase_logs | CREATE TABLE `wp_wpsc_purchase_logs` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`totalprice` decimal(11,2) NOT NULL DEFAULT '0.00',
`statusno` smallint(6) NOT NULL DEFAULT '0',
`sessionid` varchar(255) NOT NULL DEFAULT '',
`transactid` varchar(255) NOT NULL DEFAULT '',
`authcode` varchar(255) NOT NULL DEFAULT '',
`processed` bigint(20) unsigned NOT NULL DEFAULT '1',
`user_ID` bigint(20) unsigned DEFAULT '0',
`date` varchar(255) NOT NULL DEFAULT '',
`gateway` varchar(64) NOT NULL DEFAULT '',
`billing_country` char(6) NOT NULL DEFAULT '',
`shipping_country` char(6) NOT NULL DEFAULT '',
`base_shipping` decimal(11,2) NOT NULL DEFAULT '0.00',
`email_sent` char(1) NOT NULL DEFAULT '0',
`stock_adjusted` char(1) NOT NULL DEFAULT '0',
`discount_value` decimal(11,2) NOT NULL DEFAULT '0.00',
`discount_data` text,
`track_id` varchar(50) DEFAULT '',
`billing_region` char(6) NOT NULL DEFAULT '',
`shipping_region` char(6) NOT NULL DEFAULT '',
`find_us` varchar(255) NOT NULL DEFAULT '',
`engravetext` varchar(255) DEFAULT '',
`shipping_method` varchar(64) DEFAULT NULL,
`shipping_option` varchar(128) DEFAULT NULL,
`affiliate_id` varchar(32) DEFAULT NULL,
`plugin_version` varchar(32) DEFAULT NULL,
`notes` text,
`wpec_taxes_total` decimal(11,2) DEFAULT NULL,
`wpec_taxes_rate` decimal(11,2) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sessionid` (`sessionid`),
KEY `gateway` (`gateway`),
KEY `date` (`date`),
KEY `processed_and_date` (`processed`,`date`)
) ENGINE=MyISAM AUTO_INCREMENT=3927 DEFAULT CHARSET=utf8 |
mysql> explain select id,user_login,user_email from wp_users where id in (select user_id from wp_wpsc_purchase_logs where from_unixtime(date) > date('2014-11-30') and processed=3);
+----+--------------------+-----------------------+------+--------------------+--------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------+------+--------------------+--------------------+---------+-------+------+-------------+
| 1 | PRIMARY | wp_users | ALL | NULL | NULL | NULL | NULL | 3464 | Using where |
| 2 | DEPENDENT SUBQUERY | wp_wpsc_purchase_logs | ref | processed_and_date | processed_and_date | 8 | const | 2262 | Using where |
+----+--------------------+-----------------------+------+--------------------+--------------------+---------+-------+------+-------------+
2 rows in set (0.00 sec)
$ more my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+---------+
12 rows in set (0.00 sec)
---------------------------------------------------------------
NDBCluster configuration (the good performance case)
Version: '5.6.17-ndb-7.3.5-cluster-gpl' socket: '/home/mysqlroot/MySQL_Cluster/54/mysql.socket' port: 3306 MySQL Cluster Community Server (GPL)
select id,user_login,user_email from wp_users where id in (select user_id from wp_wpsc_purchase_logs where from_unixtime(date) > date('2014-11-30') and processed=3);
779 rows in set (0.03 sec)
| wp_users | CREATE TABLE `wp_users` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_login` varchar(60) NOT NULL DEFAULT '',
`user_pass` varchar(64) NOT NULL DEFAULT '',
`user_nicename` varchar(50) NOT NULL DEFAULT '',
`user_email` varchar(100) NOT NULL DEFAULT '',
`user_url` varchar(100) NOT NULL DEFAULT '',
`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_activation_key` varchar(60) NOT NULL DEFAULT '',
`user_status` int(11) NOT NULL DEFAULT '0',
`display_name` varchar(250) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`),
KEY `user_login_key` (`user_login`),
KEY `user_nicename` (`user_nicename`)
) ENGINE=ndbcluster AUTO_INCREMENT=3923 DEFAULT CHARSET=utf8 |
| wp_wpsc_purchase_logs | CREATE TABLE `wp_wpsc_purchase_logs` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`totalprice` decimal(11,2) NOT NULL DEFAULT '0.00',
`statusno` smallint(6) NOT NULL DEFAULT '0',
`sessionid` varchar(255) NOT NULL DEFAULT '',
`transactid` varchar(255) NOT NULL DEFAULT '',
`authcode` varchar(255) NOT NULL DEFAULT '',
`processed` bigint(20) unsigned NOT NULL DEFAULT '1',
`user_ID` bigint(20) unsigned DEFAULT '0',
`date` varchar(255) NOT NULL DEFAULT '',
`gateway` varchar(64) NOT NULL DEFAULT '',
`billing_country` char(6) NOT NULL DEFAULT '',
`shipping_country` char(6) NOT NULL DEFAULT '',
`base_shipping` decimal(11,2) NOT NULL DEFAULT '0.00',
`email_sent` char(1) NOT NULL DEFAULT '0',
`stock_adjusted` char(1) NOT NULL DEFAULT '0',
`discount_value` decimal(11,2) NOT NULL DEFAULT '0.00',
`discount_data` text,
`track_id` varchar(50) DEFAULT '',
`billing_region` char(6) NOT NULL DEFAULT '',
`shipping_region` char(6) NOT NULL DEFAULT '',
`find_us` varchar(255) NOT NULL DEFAULT '',
`engravetext` varchar(255) DEFAULT '',
`shipping_method` varchar(64) DEFAULT NULL,
`shipping_option` varchar(128) DEFAULT NULL,
`affiliate_id` varchar(32) DEFAULT NULL,
`plugin_version` varchar(32) DEFAULT NULL,
`notes` text,
`wpec_taxes_total` decimal(11,2) DEFAULT NULL,
`wpec_taxes_rate` decimal(11,2) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sessionid` (`sessionid`),
KEY `gateway` (`gateway`),
KEY `date` (`date`),
KEY `processed_and_date` (`processed`,`date`)
) ENGINE=ndbcluster AUTO_INCREMENT=3932 DEFAULT CHARSET=utf8 |
explain select id,user_login,user_email from wp_users where id in (select user_id from wp_wpsc_purchase_logs where from_unixtime(date) > date('2014-11-30') and processed=3);
+----+-------------+-----------------------+--------+--------------------+--------------------+---------+---------------------------------------------------+------+------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+--------+--------------------+--------------------+---------+---------------------------------------------------+------+------------------------------------------------------------------+
| 1 | SIMPLE | wp_wpsc_purchase_logs | ref | processed_and_date | processed_and_date | 8 | const | 59 | Parent of 2 pushed join@1; Using where; Start temporary |
| 1 | SIMPLE | wp_users | eq_ref | PRIMARY | PRIMARY | 8 | fondarching_cluster.wp_wpsc_purchase_logs.user_ID | 1 | Child of 'wp_wpsc_purchase_logs' in pushed join@1; End temporary |
+----+-------------+-----------------------+--------+--------------------+--------------------+---------+---------------------------------------------------+------+------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| ndb_eventbuffer_max_alloc | 0 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
23 rows in set (0.01 sec)
[root@MyCluster-D2 usr]# more my.cnf
#
# Configuration file for MyCluster
# Generated by mcc
#
[mysqld]
log-error=mysqld.54.err
datadir="/home/mysqlroot/MySQL_Cluster/54/"
tmpdir="/home/mysqlroot/MySQL_Cluster/54/tmp"
basedir="/usr/"
user="mysqlroot"
port=3306
ndbcluster=on
ndb-nodeid=54
ndb-connectstring=MyCluster-Mgt:1186,
socket="/home/mysqlroot/MySQL_Cluster/54/mysql.socket"