Possibly.. top - 07:52:58 up 18:04, 3 users, load average: 4.98, 4.09, 3.20 Tasks: 165 total, 3 running, 162 sleeping, 0 stopped, 0 zombie Cpu0 : 0.0% us, 0.0% sy, 100.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu1 : 0.0% us, 0.3% sy, 0.0% ni, 0.0% id, 96.3% wa, 0.7% hi, 2.7% si Cpu2 : 0.0% us, 0.3% sy, 99.7% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 0.0% id, 100.0% wa, 0.0% hi, 0.0% si Mem: 8168104k total, 7694556k used, 473548k free, 31040k buffers Swap: 2008084k total, 160k used, 2007924k free, 6044284k cached
Phil On Mon, Jul 21, 2008 at 9:41 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > 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 ! > > > -- Help build our city at http://free-dc.myminicity.com !
