From: Nick Keefen
Date: October 6 2008 9:51pm
Subject: Re: slow selects
List-Archive: http://lists.mysql.com/cluster/6263
Message-Id: <48EA8875.8070505@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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=jeff.sturm@stripped
>>
>>
>>