MySQL Forums
Forum List  »  MyISAM

Performance problem migrating from NDB to MyISAM
Posted by: Giuseppe Proietti
Date: January 23, 2015 01:45AM

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"



Options: ReplyQuote


Subject
Views
Written By
Posted
Performance problem migrating from NDB to MyISAM
2899
January 23, 2015 01:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.