Nothing else running and no queries go against that table, it's effectively
created just for this, so I would expect the table lock.
Show (full) processlist has nothing but this running..
Confirmed the faster disks by copying 5Gb files between two of the same type
of disk (I installed two of them). 2xfaster than previous disks.
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
8Gb Ram on this machine which is an intel quad core.
Anything else I'm missing? It's *possible* a colleague had changed the
my.cnf in the last few weeks and mysqld hadn't been restarted, but I don't
see anything obvious in there and he can't remember.
On Mon, Jul 21, 2008 at 7:08 AM, Ananda Kumar <anandkl@stripped> wrote:
> 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
>> 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
>> this particular query is taking 45 minutes!
>> I can't, for the life of me figure out why performance would be degraded
>> much. At first I thought perhaps it might be just disk/mysql caching but
>> performance has not increased any in subsequent runs.
>> Any advice on where to look ?
>> Help build our city at http://free-dc.myminicity.com !
Help build our city at http://free-dc.myminicity.com !