when you run this update, what is the IO WAIT from the top command. regards anandkl
On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: > > 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 ! >
