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. my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 key_buffer_size=3072M max_allowed_packet=16M thread_stack=128K thread_cache_size=64 thread_concurrency=8 sort_buffer_size=32M join_buffer_size=3M read_buffer_size=16M query_cache_size=64M query_cache_limit=8M table_cache=300 max_connections=500 max_heap_table_size=1024M tmp_table_size=1024M myisam_sort_buffer_size=128M wait_timeout=3000 set-variable=long_query_time=6 log-slow-queries=/var/log/mysql-slow-queries.log 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 <[EMAIL PROTECTED]> 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 <[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 ! >> > > -- Help build our city at http://free-dc.myminicity.com !
