Re: SQL Cluster Performane worse than MyISAM.

[Mikael Ronström]

Hi Andrew,
It was an interesting query to analyse.
It seems that the manner in which the query is processed is that an
index scan is executed on the blog_private index. It would be interesting to
know the number of records that actually have blog_private = 0 and
how many of those that have blog_name IS NOT NULL
SELECT COUNT(*) from iBB_blog_blobs where blog_private = 0;
SELECT COUNT(*) from iBB_blog_blobs where blog_private = 0 and blog_name IS NOT NULL;

In the version you are using (4.1 I presume) all records with blog_private = 0 will be sent
from the ndbd node to the MySQL Server together with all fields (since query wanted b.*)

(Actually the best method is to only retrieve the primary key and the fields for which the
ORDER BY is applied and then retrieve all fields only for the records to be sent to the user,
I'll look into if it is possible to adapt the code for that optimisation in an easy manner).

Then a filesort is performed and in this case.
If the record length is bigger than 1024 bytes then the filesort will be performed in memory
using the buffer with size sort_buffer_size (in that case only the fields to be used in ORDER BY
and reference to record will be stored in sort buffer.
If the record length is smaller than 1024 then the entire record will be stored in the sort buffer
and most likely that won't fit (it is 2 MByte in size by default which is likely to be smaller than
the table size)

After the filesort there are one primary key lookup in each table for each record requested by
the LIMIT. When those LIMIT's are small it is more efficient to sort only using the sort keys
and reference to the records and then fetch the records in a second step.

It would be very interesting to see the behaviour of your query when setting those
parameters in different ways.

1)
SET SESSION max_length_sort_data = 100;
execute query

2)
SET SESSION max_length_sort_data = 4096;
SET SESSION sort_buffer_size = 32 * 1024 * 1024
execute query

Rgrds Mikael

Andrew Mcleod wrote:
> Thank you for your reply Mikael.
>
> It is good to know that future releases are
> looking at dealing with these types of queries
> better, I'll be hanging out for the version 5
> release.
>
> Below is an explain on the query:
>
> 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
> 0,20
> table type possible_keys key key_len ref
> rows Extra
> b ref blog_private blog_private 1 const
> 10 Using where; Using filesort
> m eq_ref PRIMARY PRIMARY 3
> boostcruising.b.member_id 1
> MySQL time: 5.12795
>
> I've been using replication with a manual switch
> over method if the primary host goes down for a
> significant period of time - and in regards to
> performance, using the HEAP engine is not an
> option as I do not want to loose data when the
> daemon restarts - using the NDB engine seemed like
> a perfect solution...
>
> -----