hi Jeff.
I'm impressed. I figured that was the optimizer's fault and tried to
change its depth but it did not help. STRAIGHT_JOIN gives the perfect
performance of split seconds!
Unfortunately it might be tricky for me to modify my webapp (i'm more of
a system administrator and am uncomfortable with the idea of modifying
the client's code). Is there any way to make the query optimizer work
correctly?
Thanks a lot, you've saved me already.
Regards, NK
Jeff Sturm wrote:
> 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
>>
>>
>>
