MySQL Forums
Forum List  »  NDB clusters

Re: SQL Cluster Performane worse than MyISAM.
Posted by: Mikael Ronström
Date: February 25, 2005 07:52AM

Hi Andrew,
Some notes on your posting.
1) MyISAM has a very good performance, MySQL was designed with MyISAM in mind and
much of the success of MySQL is due to the good performance of MyISAM. So it is not a
surprise that MyISAM delivers good performance.

2) In the particular test you described below, MyISAM works as a main memory database.
If you would look at vmstat or similar during the run of the SELECT query below I am pretty
sure you wouldn't find a single disk access. All the data was loaded into the file system cache
during your ALTER TABLE statements and 16 MByte fits into the file system cache on more
or less any system.

3) Performance of one particular query in MySQL Cluster is very much dependent on how
well the MySQL Server manages to batch operations within the query execution. The reason for
this is that the data is fetched from a separate process and going to another process to fetch
data has a certain turn-around time (around 50-100 microseconds) so the more one can
fetch for each such turn-around the better.

There is a lot of work ongoing to improve these matters. This work is ongoing in the 5.0 tree.
The upcoming 5.0.3 tree will have a number of interesting optimisations that make certain
queries very fast. Essentially there are two new mechanisms that account for most of this
speed up. The first is a possibility to fetch several ranges in one go and second is to push down
some filters in the scan process. Whether your query would benefit from these is difficult
to know, so if you could post an EXPLAIN on the query I could comment on if the query
will be affected positively by those changes. When those optimisations kick in you will find
that MySQL Cluster has a very good performance.

4) If you are only looking for maximum performance and don't look for improved scalability,
reliability or transaction support then certainly the HEAP engine is what you are looking for.

After looking at your query and doing some guess work it looks probable that a full table scan
is performed on the table b and for each record there a lookup is made in table m. This means
that the all records of the table b has to be transported from the data node to the MySQL Server.
The optimisation that will hopefully be available in 5.0.3 will enable a filter to be applied in the
data node instead. This means that with less records that are true to the WHERE
clause this optimisation will benefit more.

The second part of the query execution where fetching records from m is performed should not
be a problem since the MySQL optimiser should perform the ORDER BY on the b table above and
then fetch only one record from m and this is a 0.0002 sec business even in a bad case.

So I think the bad performance comes from the fact that the entire table b (I presume around
5-10 MByte) is sent over the TCP/IP connection. It might be even worse by the fact that your
resulting filesort will need a big sort buffer and thus actually records are fetched one-by-one.

So in order to speed up your query as a test try setting the following session variables before
executing the query.

Set the variable max_length_for_sort_data to a value that is bigger than the record length in
the table b (use max size of fields when calculating this)
Set the sort_buffer_size to a size that fits the entire table m
Assuming that max_length_for_sort_data is set to 2048 and sort_buffer_size to 16 M perform
the following commands before executing the query.

SET SESSION max_length_for_sort_data = 2048;
SET SESSION sort_buffer_size = 16 * 1024 * 1024;

This should already in the version you are currently using and might even have a positive impact
on MyISAM performance.

Rgrds Mikael

Andrew Mcleod wrote:
> Hi,
>
> I am yet another person that is curious with
> MySQL Cluster, and loved the idea of acheiving
> more performance by the NDBCLUSTER storage engine
> loading all tables into memory.
>
> Expecting an increase in performance, I was
> shocked to see things taking even longer. A query
> that would take less than one second using MyISAM
> disk based tables, took up near FIVE seconds using
> NDBCLUSTER. These tables are are mere 16MB in size
> in total (two tables plus indexes) - so this
> behaviour is completely unexpected.
>
> This is how I've measured the performance over and
> over... as follows:
>
> mysql> alter table iBB_members type=ndb;
> Query OK, 15581 rows affected, 1 warning (13.79
> sec)
> Records: 15581 Duplicates: 0 Warnings: 0
>
> mysql> alter table iBB_blog_blogs type=ndb;
> Query OK, 15029 rows affected, 1 warning (20.69
> sec)
> Records: 15029 Duplicates: 0 Warnings: 0
>
> mysql> SELECT b.*, m.name FROM iBB_blog_blogs b
> LEFT JOIN iBB_members m ON (b.member_id=m.id)
> WHERE b.blog_private=0 AND blog_name IS NOT NULL
> ORDER BY b.blog_pinned DESC, blog_last_date desc
> LIMIT 1;
> -----output omitted------
> 1 row in set (4.27 sec)
>
> Over four seconds here, it varies - however, if I
> convert these two tables back to MyISAM:
>
> mysql> alter table iBB_members type=myisam;
> Query OK, 15582 rows affected, 1 warning (7.25
> sec)
> Records: 15582 Duplicates: 0 Warnings: 0
>
> mysql> alter table iBB_blog_blogs type=myisam;
> Query OK, 15030 rows affected, 1 warning (6.79
> sec)
> Records: 15030 Duplicates: 0 Warnings: 0
>
> mysql> SELECT b.*, m.name FROM iBB_blog_blogs b
> LEFT JOIN iBB_members m ON (b.member_id=m.id)
> WHERE b.blog_private=0 AND blog_name IS NOT NULL
> ORDER BY b.blog_pinned DESC, blog_last_date desc
> LIMIT 1;
> -----output omitted-------
> 1 row in set (0.38 sec)
>
> Cluster config.ini as follows for reference:
>
>
> NoOfReplicas= 1
> DataMemory= 1024M
> IndexMemory= 384M
> LockPagesInMainMemory=1
>
>
>
>
> HostName= 192.168.254.1
>
> HostName= 192.168.254.1
> DataDir= /usr/local/mysql/data
>
> HostName= 192.168.254.1
>
> Any help as to what the problem is would be
> greatly appreciated, I've spent the past week
> working on preparing an environment and
> configuring MySQL Cluster and am extremely
> dissapointed with these performance tests!
>
> Thank you

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: SQL Cluster Performane worse than MyISAM.
4114
February 25, 2005 07:52AM


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.