List:Cluster« Previous MessageNext Message »
From:Nick Keefen Date:October 6 2008 1:33am
Subject:slow selects
View as plain text  
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 .
Thread
slow selectsNick Keefen6 Oct
  • RE: slow selectsJeff Sturm6 Oct
    • Re: slow selectsNick Keefen6 Oct
      • Re: slow selectsStewart Smith8 Oct
        • Re: slow selectsMark Callaghan10 Oct
          • Re: slow selectsMikael Ronstrom11 Oct
      • Re: slow selectsSteve Kurzeja10 Oct