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 .
