Re: MySQL Cluster performance question

From: Al <broohaha(at)gmail.com>
Date: Mon Aug 20 2007 - 16:53:01 EDT

So, we made some changes. And now with more data nodes were added and with the configuration changes recommended by Mr. Bond made, the following were the results of the query:

mysql> select distinct gv_symbol from past30_ndb force index(`gvsymtime`) where 1;
...
90 rows in set (2 min 26.34 sec)
90 rows in set (2 min 25.46 sec)

mysql> select distinct gv_symbol from past30_MYISAM force index(`gvsymtime`) where 1;
...
90 rows in set (0.01 sec)
90 rows in set (0.01 sec)

It seems better without the force index:

mysql> select distinct gv_symbol from past30_ndb where 1; ...
90 rows in set (1 min 10.99 sec)
90 rows in set (1 min 10.33 sec)

mysql> select distinct gv_symbol from past30_MYISAM where 1; ...
90 rows in set (0.00 sec)
90 rows in set (0.00 sec)

And here are some EXPLAINs:

mysql> explain select distinct gv_symbol from past30_ndb where 1;

----+-------------+------------+------+---------------+------+---------+------+----------+-----------------+
| id | select_type | table      | type | possible_keys | key  | key_len |
ref  | rows     | Extra           |
+----+-------------+------------+------+---------------+------+---------+------+----------+-----------------+
|  1 | SIMPLE      | past30_ndb | ALL  | NULL          | NULL | NULL    |

NULL | 10000000 | Using temporary |

+----+-------------+------------+------+---------------+------+---------+------+----------+-----------------+

1 row in set (0.00 sec)

mysql> explain select distinct gv_symbol from past30_ndb force index (`gvsymtime`) where 1;
+----+-------------+------------+-------+---------------+-----------+---------+------+----------+-------+

| id | select_type | table      | type  | possible_keys | key       |
key_len | ref  | rows     | Extra |
+----+-------------+------------+-------+---------------+-----------+---------+------+----------+-------+
|  1 | SIMPLE      | past30_ndb | index | NULL          | gvsymtime |
56      | NULL | 10000000 |       |

+----+-------------+------------+-------+---------------+-----------+---------+------+----------+-------+ 1 row in set (0.00 sec)

mysql> explain select sql_buffer_result distinct gv_symbol from past30_ndb force index (`gvsymtime`) where 1;

+----+-------------+------------+-------+---------------+-----------+---------+------+----------+-----------------+
| id | select_type | table      | type  | possible_keys | key       |
key_len | ref  | rows     | Extra           |
+----+-------------+------------+-------+---------------+-----------+---------+------+----------+-----------------+
|  1 | SIMPLE      | past30_ndb | index | NULL          | gvsymtime |
56      | NULL | 10000000 | Using temporary |
+----+-------------+------------+-------+---------------+-----------+---------+------+----------+-----------------+

1 row in set (0.00 sec)

The create tables on both tables are:

CREATE TABLE `past30_ndb` (
`symbol` char(32) NOT NULL,
`gv_symbol` char(32) NOT NULL,
`exchange` enum('E1','E2','E3','E4','E5','E6','E7','E8') DEFAULT NULL,
`type` enum('BID','ASK','LAST') NOT NULL,
`price` decimal(16,8) NOT NULL,
`quantity` bigint(20) NOT NULL,
`orders` int(11) NOT NULL,
`exchange_ts` char(32) NOT NULL,
`server_date` date NOT NULL,
`server_time` time NOT NULL,
`server_ms` int(6) unsigned zerofill NOT NULL,
  KEY `servertime` (`server_date`,`server_time`,`server_ms`),   KEY `symtime`
(`symbol`,`exchange`,`server_date`,`server_time`,`server_ms`,`quantity`,`orders`),   KEY `gvsymtime`
(`gv_symbol`,`exchange`,`server_date`,`server_time`,`server_ms`,`quantity`,`orders`) ) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1;

CREATE TABLE `past30_MYISAM` (
`symbol` char(32) NOT NULL,
`gv_symbol` char(32) NOT NULL,
`exchange` enum('E1','E2','E3','E4','E5','E6','E7','E8') DEFAULT NULL,
`type` enum('BID','ASK','LAST') NOT NULL,
`price` decimal(16,8) NOT NULL,
`quantity` bigint(20) NOT NULL,
`orders` int(11) NOT NULL,
`exchange_ts` char(32) NOT NULL,
`server_date` date NOT NULL,
`server_time` time NOT NULL,
`server_ms` int(6) unsigned zerofill NOT NULL,
  KEY `servertime` (`server_date`,`server_time`,`server_ms`),   KEY `symtime`
(`symbol`,`exchange`,`server_date`,`server_time`,`server_ms`,`quantity`,`orders`),   KEY `gvsymtime`
(`gv_symbol`,`exchange`,`server_date`,`server_time`,`server_ms`,`quantity`,`orders`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

On 8/16/07, Stewart Smith <stewart@mysql.com> wrote:

> On Thu, 2007-08-09 at 13:31 -0500, Brian Moon wrote:
> > > mysql> SELECT DISTINCT gv_symbol FROM `past30_ndb_disk` FORCE INDEX
> > > (`gvsymtime`) WHERE 1;
> > > 90 rows in set (4 min 44.90 sec)
> > >
> > > mysql> SELECT DISTINCT gv_symbol FROM `past30_MYISAM` FORCE INDEX
> > > (`gvsymtime`) WHERE 1;
> > > 90 rows in set (1.54 sec)
> >
> > Can we see the create table on these?  Frankly, the 1.54 seconds on the
Do you need more help?
In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software:
Learn more about Pantek's Technical Support offerings
Place an order for 24/7/365 Technical Support here
Learn more about Pantek, who we are, and what we do
Chat with a Live Operator
Related lists.mysql.com Links
announce
benchmarks
bugs
certification
cluster
commits
dotnet
gui-tools
internals
java
maxdb
myodbc
mysql
mysqldoc
packagers
perl
plusplus
replication
win32
Request more information about Pantek
> > myisam seems low to me if a good key is used.
>
> and EXPLAIN
> --
> Stewart Smith, Senior Software Engineer
> MySQL AB, www.mysql.com
> Office: +14082136540 Ext: 6616
> VoIP: 6616@sip.us.mysql.com
> Mobile: +61 4 3 8844 332
>
> Jumpstart your cluster:
> 
http://www.mysql.com/consulting/packaged/cluster.html
>
>

Received on Mon Aug 20 16:53:22 2007