Walter,
Set ndb-index-stat-enable = 0 in you my.cnf. This should fix the issue.
JBM
Jonathan Miller
Austin, Texas USA
Senior Quality Assurance Developer
MySQL AB www.mysql.com
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/ www.mysql.com
Jumpstart your cluster!
http://www.mysql.com/consulting/packaged/cluster.html
Get training on clusters
http://www.mysql.com/training/courses/mysql_cluster.html
All-in-one Enterprise-grade Database, Support and Services
http://www.mysql.com/network/
-> -----Original Message-----
-> From: Walter Bertot [mailto:walter.bertot@stripped]
-> Sent: Thursday, March 23, 2006 1:47 PM
-> To: Jonathan Miller
-> Subject: Re: Performance / Index issues
->
-> mysql> select count(*) from end_user_audit;
-> +----------+
-> | count(*) |
-> +----------+
-> | 2205830 |
-> +----------+
-> 1 row in set (1.89 sec)
->
-> Just an FYI...
->
-> I have a much larger query right now that our reporting tool runs that
-> is taking 16 minutes (yes minutes) as oppossed to < 3 seconds on
-> InnoDB. Please let me know what else if anything I can do from my side
-> to help get this resolved.
->
-> Walter
->
->
->
-> Jonathan Miller wrote:
-> > One of the devs wants to know how many rows the real table has.
-> >
-> >
-> > Jonathan Miller
-> > Austin, Texas USA
-> > Senior Quality Assurance Developer
-> > MySQL AB www.mysql.com
-> > __ ___ ___ ____ __
-> > / |/ /_ __/ __/ __ \/ /
-> > / /|_/ / // /\ \/ /_/ / /__
-> > /_/ /_/\_, /___/\___\_\___/
-> > <___/ www.mysql.com
-> >
-> > Jumpstart your cluster!
-> > http://www.mysql.com/consulting/packaged/cluster.html
-> >
-> > Get training on clusters
-> > http://www.mysql.com/training/courses/mysql_cluster.html
-> >
-> > All-in-one Enterprise-grade Database, Support and Services
-> > http://www.mysql.com/network/
-> >
-> > -> -----Original Message-----
-> > -> From: Walter Bertot [mailto:walter.bertot@stripped]
-> > -> Sent: Thursday, March 23, 2006 1:35 PM
-> > -> To: Jonathan Miller
-> > -> Subject: Re: Performance / Index issues
-> > ->
-> > -> Jonathan,
-> > ->
-> > -> I noticed you opened the bug under version 5.1.8. Just for what it
-> is
-> > -> worth I have this issue on 5.0.18.
-> > ->
-> > -> Thanks,
-> > ->
-> > -> Walter
-> > ->
-> > -> Jonathan Miller wrote:
-> > -> > Hi Walter,
-> > -> >
-> > -> > I have filed the following bug report:
-> > -> >
-> > -> > http://bugs.mysql.com/bug.php?id=18468
-> > -> >
-> > -> > You can subscribe to it so you know the out come.
-> > -> >
-> > -> > Best wishes,
-> > -> >
-> > -> > Jonathan Miller
-> > -> > Austin, Texas USA
-> > -> > Senior Quality Assurance Developer
-> > -> > MySQL AB www.mysql.com
-> > -> > __ ___ ___ ____ __
-> > -> > / |/ /_ __/ __/ __ \/ /
-> > -> > / /|_/ / // /\ \/ /_/ / /__
-> > -> > /_/ /_/\_, /___/\___\_\___/
-> > -> > <___/ www.mysql.com
-> > -> >
-> > -> > Jumpstart your cluster!
-> > -> > http://www.mysql.com/consulting/packaged/cluster.html
-> > -> >
-> > -> > Get training on clusters
-> > -> > http://www.mysql.com/training/courses/mysql_cluster.html
-> > -> >
-> > -> > All-in-one Enterprise-grade Database, Support and Services
-> > -> > http://www.mysql.com/network/
-> > -> >
-> > -> > -> -----Original Message-----
-> > -> > -> From: Walter Bertot [mailto:walter.bertot@stripped]
-> > -> > -> Sent: Thursday, March 23, 2006 8:19 AM
-> > -> > -> To: Jonathan Miller
-> > -> > -> Cc: cluster@stripped
-> > -> > -> Subject: Re: Performance / Index issues
-> > -> > ->
-> > -> > -> Hi Jonathan,
-> > -> > ->
-> > -> > -> Thanks for looking at this. Please find attached some sample
-> data
-> > -> along
-> > -> > -> with the SHOW CREATE TABLE result.
-> > -> > ->
-> > -> > -> Walter
-> > -> > ->
-> > -> > -> mysql> show create table end_user_audit;
-> > -> > ->
+----------------+---------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > -> ------------------------+
-> > -> > -> | Table | Create
-> > -> > -> Table
-> > -> > -> |
-> > -> > ->
+----------------+---------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > -> ------------------------+
-> > -> > -> | end_user_audit | CREATE TABLE `end_user_audit` (
-> > -> > -> `id` int(11) NOT NULL auto_increment,
-> > -> > -> `target_id` int(11) NOT NULL default '0',
-> > -> > -> `end_user_operation_id` int(11) NOT NULL default '0',
-> > -> > -> `user_id` int(11) NOT NULL default '0',
-> > -> > -> `carrier_id` int(11) NOT NULL default '0',
-> > -> > -> `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
-> > -> > -> `target_type_id` int(11) NOT NULL default '0',
-> > -> > -> `status` int(2) NOT NULL default '0',
-> > -> > -> `interface_type` int(2) NOT NULL default '0',
-> > -> > -> `interface_code` int(11) default NULL,
-> > -> > -> `la_id` int(11) default NULL,
-> > -> > -> `parent_id` int(11) default NULL,
-> > -> > -> PRIMARY KEY (`id`),
-> > -> > -> KEY `eua_carrier_id_target_id_timestamp`
-> > -> > -> (`timestamp`,`carrier_id`,`target_id`),
-> > -> > -> KEY `eua_user_id_timestamp` (`user_id`,`timestamp`),
-> > -> > -> KEY `eua_timestamp` (`timestamp`)
-> > -> > -> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
-> > -> > ->
+----------------+---------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > ->
---------------------------------------------------------------
-> -----
-> > -> -----
-> > -> > -> ------------------------+
-> > -> > -> 1 row in set (0.05 sec)
-> > -> > ->
-> > -> > ->
-> > -> > -> Jonathan Miller wrote:
-> > -> > -> > Hi Walter,
-> > -> > -> >
-> > -> > -> > Can you send me the SHOW CREATE TABLE and a few rows of
-> sample
-> > -> data?
-> > -> > -> >
-> > -> > -> > Thanks
-> > -> > -> > Jonathan Miller
-> > -> > -> > Austin, Texas USA
-> > -> > -> > Senior Quality Assurance Developer
-> > -> > -> > MySQL AB www.mysql.com
-> > -> > -> > __ ___ ___ ____ __
-> > -> > -> > / |/ /_ __/ __/ __ \/ /
-> > -> > -> > / /|_/ / // /\ \/ /_/ / /__
-> > -> > -> > /_/ /_/\_, /___/\___\_\___/
-> > -> > -> > <___/ www.mysql.com
-> > -> > -> >
-> > -> > -> > Jumpstart your cluster!
-> > -> > -> > http://www.mysql.com/consulting/packaged/cluster.html
-> > -> > -> >
-> > -> > -> > Get training on clusters
-> > -> > -> > http://www.mysql.com/training/courses/mysql_cluster.html
-> > -> > -> >
-> > -> > -> > All-in-one Enterprise-grade Database, Support and
Services
-> > -> > -> > http://www.mysql.com/network/
-> > -> > -> >
-> > -> > -> > -> -----Original Message-----
-> > -> > -> > -> From: Walter Bertot
[mailto:walter.bertot@stripped]
-> > -> > -> > -> Sent: Wednesday, March 08, 2006 1:00 PM
-> > -> > -> > -> To: cluster@stripped
-> > -> > -> > -> Subject: Performance / Index issues
-> > -> > -> > ->
-> > -> > -> > ->
-> > -> > -> > -> 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 outgoing message.
-> > -> > -> > -> Checked by AVG Free Edition.
-> > -> > -> > -> Version: 7.1.375 / Virus Database: 268.2.0/276 -
Release
-> Date:
-> > -> > -> 3/7/2006
-> > -> > -> > ->
-> > -> > -> > ->
-> > -> > -> > ->
-> > -> > -> > -> --
-> > -> > -> > -> MySQL Cluster Mailing List
-> > -> > -> > -> For list archives: http://lists.mysql.com/cluster
-> > -> > -> > -> To unsubscribe:
-> > -> > -> > -> http://lists.mysql.com/cluster?unsub=1
-> > -> > -> >
-> > -> > -> >
-> > -> > -> >
-> > -> >
-> > -> >
-> > -> >
-> > -> >
-> >
-> >
-> >
