I have 4 nodes (1 mgm, 1 API and 2 NDB) 5.0.18
-----Original Message-----
From: Jonas Oreland [mailto:jonas@stripped]
Sent: Wednesday, March 08, 2006 4:03 PM
To: Walter Bertot
Cc: cluster@stripped
Subject: Re: Performance / Index issues
Hi,
What version are you using?
/Jonas
Walter Bertot wrote:
> I have been trying to resolve some serious performance issues I have in
> certain queries. At the heart of most of my problems, per the explain
plan
> the cluster is not using index that InnoDB is using. Even in using the
> FORCE index syntax I still can not avoid the full table scans.
>
>
>
> Here is a small example query I am running
>
>
>
> select id from end_user_audit where timestamp >='2005-12-18 02:00:00' and
> timestamp <'2006-01-01 00:00:00';
>
>
>
>
>
> Explain plan on InnoDB
>
>
>
>
+----+-------------+----------------+-------+-------------------------------
>
-------------------+---------------+---------+------+--------+--------------
> ------------+
>
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows | Extra |
>
>
+----+-------------+----------------+-------+-------------------------------
>
-------------------+---------------+---------+------+--------+--------------
> ------------+
>
> | 1 | SIMPLE | end_user_audit | range |
> eua_carrier_id_target_id_timestamp,eua_timestamp | eua_timestamp | 8
|
> NULL | 202752 | Using where; Using index |
>
>
>
>
>
>
> Explain plan on NDB
>
>
>
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows | Extra
|
>
>
+----+-------------+----------------+-------+-------------------------------
>
-------------------+------------------------------------+---------+------+--
> ----+-------------+
>
> | 1 | SIMPLE | end_user_audit | range |
> eua_carrier_id_target_id_timestamp,eua_timestamp |
> eua_carrier_id_target_id_timestamp | 8 | NULL | 10 | Using where |
>
>
+----+-------------+----------------+-------+-------------------------------
>
-------------------+------------------------------------+---------+------+--
> ----+-------------+
>
>
>
>
>
> Using FORCE INDEX
>
>
>
> select id from end_user_audit FORCE INDEX (eua_timestamp) where timestamp
>
>>='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00';
>
>
>
>
> NDB explain plan
>
>
+----+-------------+----------------+-------+---------------+---------------
> +---------+------+------+-------------+
>
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra |
>
>
+----+-------------+----------------+-------+---------------+---------------
> +---------+------+------+-------------+
>
> | 1 | SIMPLE | end_user_audit | range | eua_timestamp |
eua_timestamp
> | 8 | NULL | 10 | Using where |
>
>
+----+-------------+----------------+-------+---------------+---------------
> +---------+------+------+-------------+
>
>
>
>
>
> Here is what the table looks like…
>
>
>
>
>
>
+-----------------------+----------+------+-----+---------------------+-----
> -----------+
>
> | Field | Type | Null | Key | Default |
> Extra |
>
>
+-----------------------+----------+------+-----+---------------------+-----
> -----------+
>
> | id | int(11) | NO | PRI | NULL |
> auto_increment |
>
> | target_id | int(11) | NO | | 0 |
> |
>
> | end_user_operation_id | int(11) | NO | | 0 |
> |
>
> | user_id | int(11) | NO | MUL | 0 |
> |
>
> | carrier_id | int(11) | NO | | 0 |
> |
>
> | timestamp | datetime | NO | MUL | 0000-00-00 00:00:00 |
> |
>
> | target_type_id | int(11) | NO | | 0 |
> |
>
> | status | int(2) | NO | | 0 |
> |
>
> | interface_type | int(2) | NO | | 0 |
> |
>
> | interface_code | int(11) | YES | | NULL |
> |
>
> | la_id | int(11) | YES | | NULL |
> |
>
> | parent_id | int(11) | YES | | NULL |
> |
>
>
+-----------------------+----------+------+-----+---------------------+-----
> -----------+
>
>
>
>
>
> My config.ini
>
>
>
> [NDBD DEFAULT]
>
> NoOfReplicas=2
>
> DataMemory=600M # defaults to 80M
>
> IndexMemory=100M # defaults to 18M
>
> MaxNoOfAttributes=3000 # defaults to 1000
>
> MaxNoOfOrderedIndexes=512 # defaults to 128
>
> MaxNoOfUniqueHashIndexes=350 # defaults to 64
>
>
>
> [MYSQLD DEFAULT]
>
> [NDB_MGMD DEFAULT]
>
> [TCP DEFAULT]
>
>
>
> # Managment Server
>
> [NDB_MGMD]
>
> HostName=10.10.40.1
>
>
>
> # Storage Engines
>
> [NDBD]
>
> HostName=10.10.40.60
>
> DataDir= /var/lib/mysql-cluster
>
>
>
> [NDBD]
>
> HostName=10.10.40.62
>
> DataDir=/var/lib/mysql-cluster
>
>
>
> # 2 MySQL Clients
>
> [MYSQLD]
>
> [MYSQLD]
>
>
>
>
>
> What am I doing wrong?????
>
>
>
> As always any help is greatly appreciated.
>
>
>
> -wb
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.2.0/276 - Release Date: 3/7/2006
>
>
>
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/276 - Release Date: 3/7/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/276 - Release Date: 3/7/2006
