Copying 5GB files shows you what kind of performance you would get for working with say video, or anything with large contiguous files. Database access tends to be random, so you want a drive with faster random access, not streaming speed. Try copying thousands of small files and compare the speeds.
One odd thing to check is if the "old" drive supports command queueing and the new one does not. I assume that are both SATA drives. All SCSI drives support command queueing and it can make a huge difference depending on access patterns. Brent On Mon, Jul 21, 2008 at 8:42 AM, 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 ! > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
