Is there any other job running while the update is happening. Because, myisam does a table level lock. Please check the "show full processlist".
Also run mysqladmin -uroot -pxxx status. This would write lock information into the machine.err log file. Check in this file also if there is any locking happening. R u sure, this disk is a FASTER disk then the earlier one. On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: > > Hi All, > > > Given a fairly simple table as follows > > CREATE TABLE `common_userx2` ( > `t_proj` char(6) default NULL, > `t_id` int(11) NOT NULL default '0', > `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL > default '', > `t_country` varchar(50) NOT NULL default '', > `t_cpid` varchar(50) NOT NULL default '', > `t_url` varchar(50) default NULL, > `t_create_date` int(11) default NULL, > `t_create_time` bigint(20) NOT NULL, > `t_has_profile` char(1) NOT NULL, > `t_team0` int(11) default NULL, > `t_metric1` double(20,6) NOT NULL default '0.000000', > `t_metric2` double NOT NULL default '0', > `t_metric3` double NOT NULL default '0', > `t_metric4` double default NULL, > `t_active` char(1) NOT NULL default '', > `t_rev_metric1` double(20,6) NOT NULL default '10000000000.000000', > `t_projrank0` int(11) default NULL, > `t_rev_metric2` double(20,6) NOT NULL default '10000000000.000000', > `t_racrank0` int(11) default NULL, > `t_teamrank0` int(11) default NULL, > `t_countryrank0` int(11) default NULL, > `t_createdaterank0` int(11) default NULL, > PRIMARY KEY (`t_id`), > KEY `prank` (`t_rev_metric1`,`t_id`), > KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), > KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), > KEY `createdaterank` (`t_create_date`,`t_rev_metric1`,`t_id`), > KEY `racrank` (`t_rev_metric2`,`t_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > I have a ranking update statement as follows > > set @rank = 0; > update common_userx2 set projrank0 = @rank:[EMAIL PROTECTED] order by > t_rev_metric1,t_id; > > For the largest case this has close to 1M rows. > > For weeks it was taking around 10seconds to do this. Yesterday I replaced > the main data drive in the machine with a faster SATA Raptor drive. No > problems occurred, but since then (and the subsequent reboot of the > machine) > this particular query is taking 45 minutes! > > I can't, for the life of me figure out why performance would be degraded so > much. At first I thought perhaps it might be just disk/mysql caching but > the > performance has not increased any in subsequent runs. > > Any advice on where to look ? > > Phil > > -- > Help build our city at http://free-dc.myminicity.com ! >
