List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:July 21 2008 11:08am
Subject:Re: Major Performance Degradation after replacing Hard Drive
View as plain text  
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 <pchapman@stripped> 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:=@rank+1 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 !
>

Thread
Major Performance Degradation after replacing Hard DrivePhil21 Jul
  • Re: Major Performance Degradation after replacing Hard DriveAnanda Kumar21 Jul
    • Re: Major Performance Degradation after replacing Hard DrivePhil21 Jul
      • Re: Major Performance Degradation after replacing Hard DriveAnanda Kumar21 Jul
        • Re: Major Performance Degradation after replacing Hard DrivePhil21 Jul
      • Re: Major Performance Degradation after replacing Hard DriveBrent Baisley21 Jul
        • Re: Major Performance Degradation after replacing Hard Drivemos21 Jul
        • Re: Major Performance Degradation after replacing Hard DriveWm Mussatto21 Jul
          • Re: Major Performance Degradation after replacing Hard DrivePhil22 Jul