Nick,
Looks like the query optimizer is putting the tables in the wrong order.
(This isn't uncommon in my experience, as NDB seems to have less
information than other engines to optimize queries at runtime.)
Try modifying your query with STRAIGHT_JOIN syntax, i.e.:
SELECT v.vid_id, v.vid_title, u.user FROM phpfox_videos AS v
STRAIGHT_JOIN phpfox_user AS u ON v.vid_userid = u.id ORDER BY
v.vid_time DESC LIMIT 3;
Note: I think you may also be able to eliminate the filesort if you add
a key on vid_time.
Jeff
> -----Original Message-----
> From: Nick Keefen [mailto:nk.nightkid@stripped]
> Sent: Sunday, October 05, 2008 9:33 PM
> To: cluster@stripped
> Subject: slow selects
>
> Hi everyone.
>
> Raising the same old subject in hope i've missed something.
>
> My setup: 2 hardware nodes, each housing both an ndbd and
> mysql instance. 2 replicas.
> The cluster is not in use, the only load applied to the
> machines is my test query.
> Each node has two quad-core Xeons and 16GB of RAM; the
> connection between them is 1G.
>
> An example of my test query on an old weak machine with
> MyISAM and running under production load:
>
> mysql> SELECT v.vid_id, v.vid_title, u.user FROM phpfox_user AS u JOIN
> phpfox_videos AS v ON(v.vid_userid = u.id) limit 3;
> 3 rows in set (0.32 sec)
>
> ... it's nearly instant, like it should be.
>
> phpfox_user is about 100000 rows and phpfox_videos is 170000
> rows large.
>
> Trying to run the same query on my cluster machine, i get
> 3 rows in set (20.47 sec)
>
> Disabling one of the data nodes doubles the speed, but it's
> still taking 10 seconds.
>
>
>
> What's been bugging me is the different behavior of the
> engines during
> query analysis:
>
> MyISAM explain:
> mysql> explain SELECT v.vid_id, v.vid_title, u.user FROM
> phpfox_user AS
> u JOIN phpfox_videos AS v ON(v.vid_userid = u.id) ORDER BY
> v.vid_time
> DESC LIMIT 3;
> +----+-------------+-------+--------+---------------+---------
> +---------+---------------------+--------+----------------+
> | id | select_type | table | type | possible_keys | key
> | key_len
> | ref | rows | Extra |
> +----+-------------+-------+--------+---------------+---------
> +---------+---------------------+--------+----------------+
> | 1 | SIMPLE | v | ALL | vid_userid | NULL
> | NULL
> | NULL | 135025 | Using filesort |
> | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY
> | 4
> | nktest.v.vid_userid | 1 | |
> +----+-------------+-------+--------+---------------+---------
> +---------+---------------------+--------+----------------+
>
>
>
> NDB explain:
> mysql> explain SELECT v.vid_id, v.vid_title, u.user FROM
> phpfox_user AS
> u JOIN phpfox_videos AS v ON(v.vid_userid = u.id) ORDER BY
> v.vid_time
> DESC LIMIT 3;
> +----+-------------+-------+------+---------------+-----------
> -+---------+-----------------+-------+------------------------
> ---------+
> | id | select_type | table | type | possible_keys | key
> | key_len
> | ref | rows | Extra |
> +----+-------------+-------+------+---------------+-----------
> -+---------+-----------------+-------+------------------------
> ---------+
> | 1 | SIMPLE | u | ALL | PRIMARY | NULL
> | NULL
> | NULL | 82124 | Using temporary; Using filesort |
> | 1 | SIMPLE | v | ref | vid_userid |
> vid_userid | 4
> | bb2_phpfox.u.id | 1 | |
> +----+-------------+-------+------+---------------+-----------
> -+---------+-----------------+-------+------------------------
> ---------+
>
>
>
>
> Note the additional 'Using temporary' clause for NDB. Nearly
> all of the
> 20 seconds taken by the query fall at 'copying to temporary table'.
>
>
>
> Three questions arise:
> 1. Why does NDB need a temporary table when MyISAM doesn't?
> 2. Can i force it to use only filesort? I don't think i'm hitting any
> memory limits that might require a tmp table.
> 3. If the temporary table cannot be disabled, how can i debug
> its poor
> performance?
>
>
> Server version: 5.1.23-ndb-6.2.15-cluster-gpl
>
> Performance relevant sections of config.ini:
>
> [NDBD DEFAULT]
> NoOfReplicas=2
> DataMemory=10000M
> IndexMemory=1000M
> RedoBuffer=32M
> MaxNoOfAttributes=100000
> MaxNoOfTables=1000
> TransactionDeadlockDetectionTimeout=10000
> MaxNoOfOrderedIndexes=10000
> MaxNoOfConcurrentOperations=500000
> NoOfFragmentLogFiles=32
> DiskCheckpointSpeed=35M
>
> [MYSQLD DEFAULT]
> [NDB_MGMD DEFAULT]
> [TCP DEFAULT]
> SendBufferMemory=16M
>
> i've been playing with these a lot, no changes.
>
> Tables structure (NDB tables are completely similar except
> for the engine):
>
> Table: phpfox_user
> Create Table: CREATE TABLE `phpfox_user` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `type` tinyint(4) NOT NULL DEFAULT '3',
> `user` varchar(25) NOT NULL DEFAULT '',
> `password` varchar(35) NOT NULL DEFAULT '',
> `email` varchar(50) NOT NULL DEFAULT '',
> `gender` mediumint(9) NOT NULL,
> `age` char(2) NOT NULL DEFAULT '',
> `day` char(2) NOT NULL DEFAULT '',
> `month` char(2) NOT NULL DEFAULT '',
> `year` varchar(4) NOT NULL DEFAULT '',
> `location` mediumint(9) NOT NULL,
> `signup` int(11) NOT NULL DEFAULT '0',
> `signup_ip` varchar(20) NOT NULL DEFAULT '',
> `login` int(11) NOT NULL DEFAULT '0',
> `login_ip` varchar(20) NOT NULL DEFAULT '',
> `rec` varchar(20) NOT NULL DEFAULT '',
> `update` varchar(20) NOT NULL DEFAULT '',
> `headline` text NOT NULL,
> `views` int(11) NOT NULL DEFAULT '0',
> `css` int(11) NOT NULL,
> `rateon` char(1) NOT NULL DEFAULT '',
> `verify` tinyint(1) unsigned NOT NULL,
> `verifycode` varchar(20) NOT NULL DEFAULT '',
> `videon` char(1) NOT NULL DEFAULT '',
> `p_update` varchar(10) NOT NULL DEFAULT '0',
> `music_video` varchar(8) NOT NULL DEFAULT '',
> `not_1` char(1) NOT NULL DEFAULT '',
> `not_2` char(1) NOT NULL DEFAULT '',
> `not_3` char(1) NOT NULL DEFAULT '',
> `not_4` char(1) NOT NULL DEFAULT '',
> `not_5` char(1) NOT NULL DEFAULT '',
> `feature` tinyint(1) NOT NULL,
> `state` varchar(20) NOT NULL DEFAULT '',
> `zip` varchar(20) NOT NULL DEFAULT '0',
> `city` varchar(30) NOT NULL DEFAULT '',
> `user_online` tinyint(4) NOT NULL DEFAULT '0',
> `user_rating` decimal(4,2) NOT NULL DEFAULT '0.00',
> `friends_only` tinyint(4) NOT NULL DEFAULT '0',
> `friends_comment` tinyint(4) NOT NULL DEFAULT '0',
> `setting_gb_html` tinyint(4) NOT NULL DEFAULT '0',
> `remember_me` char(1) NOT NULL DEFAULT '',
> `new_pass` varchar(25) NOT NULL DEFAULT '',
> `is_banned` tinyint(1) unsigned NOT NULL DEFAULT '0',
> `last_payment` datetime DEFAULT '0000-00-00 00:00:00',
> `total_points` int(11) NOT NULL DEFAULT '0',
> `actual_song_id` int(10) unsigned NOT NULL DEFAULT '0',
> `lang_file` tinyint(1) NOT NULL,
> `c_friends` int(11) NOT NULL,
> `c_comments` int(11) NOT NULL,
> `im_status` tinyint(3) unsigned NOT NULL,
> `plugin_style` int(10) unsigned NOT NULL,
> `msg_event` tinyint(1) unsigned NOT NULL,
> `total_friends` tinyint(2) unsigned NOT NULL,
> `dignews_votesgiven` int(11) NOT NULL DEFAULT '0',
> `dignews_votesreceived` int(11) NOT NULL DEFAULT '0',
> `dignews_posted` int(11) NOT NULL DEFAULT '0',
> `lwf_rows` tinyint(2) NOT NULL DEFAULT '5',
> `lwf_display` tinyint(1) NOT NULL DEFAULT '1',
> `lwf_permission` tinyint(1) NOT NULL DEFAULT '2',
> `lwf_left_or_right` char(5) NOT NULL DEFAULT 'left',
> `lwf_signature` text NOT NULL,
> `lwf_total_topics` int(11) NOT NULL DEFAULT '0',
> `lwf_visitor_topics` int(11) NOT NULL DEFAULT '0',
> `feature_time` int(100) NOT NULL DEFAULT '0',
> `feature_end` int(100) NOT NULL DEFAULT '0',
> `reactive` text,
> `arcade_champs` int(12) NOT NULL,
> `arcade_fav` int(12) NOT NULL,
> `dblon` text NOT NULL,
> `dblat` text NOT NULL,
> `mini` text NOT NULL,
> `fpreferrals` int(11) NOT NULL,
> `mood` text,
> `status` text,
> `sm_time` int(12) NOT NULL,
> `cj_phantom` tinyint(1) DEFAULT '0',
> `cj_myspace_friend` varchar(15) DEFAULT NULL,
> PRIMARY KEY (`id`),
> UNIQUE KEY `user` (`user`),
> KEY `login` (`login`),
> KEY `verify` (`verify`,`feature`),
> KEY `email` (`email`),
> KEY `gender` (`gender`,`user_rating`),
> KEY `css` (`css`)
> ) ENGINE=MyISAM AUTO_INCREMENT=82428 DEFAULT CHARSET=utf8
>
>
> Table: phpfox_videos
> Create Table: CREATE TABLE `phpfox_videos` (
> `vid_id` int(11) NOT NULL AUTO_INCREMENT,
> `vid_list_id` int(11) NOT NULL,
> `vid_userid` int(11) NOT NULL,
> `vid_title` varchar(50) NOT NULL,
> `vid_info` text NOT NULL,
> `vid_type` varchar(6) NOT NULL,
> `vid_time` int(11) NOT NULL,
> `vid_total` int(11) NOT NULL,
> `vid_rating` decimal(4,2) NOT NULL,
> `vid_rating_count` int(11) NOT NULL,
> `vid_url` varchar(300) NOT NULL,
> `stream_id` tinyint(4) NOT NULL,
> `vid_tags` varchar(100) NOT NULL,
> `reported` int(1) NOT NULL,
> `featured` int(11) NOT NULL,
> `comments` int(11) NOT NULL,
> `duration` int(11) NOT NULL,
> `filesize` int(11) NOT NULL,
> PRIMARY KEY (`vid_id`),
> KEY `vid_title` (`vid_title`),
> KEY `reported` (`reported`),
> KEY `featured` (`featured`),
> KEY `vid_userid` (`vid_userid`),
> KEY `vid_tags` (`vid_tags`)
> ) ENGINE=MyISAM AUTO_INCREMENT=135059 DEFAULT CHARSET=utf8
>
>
>
> Looking forward to receiving any advice.
>
> Best regards, Nick Keefen, nksupport.com .
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:
> http://lists.mysql.com/cluster?unsub=1
>
>
>
