Thanks all for the suggestions. I *think* I've finally figured it out. At the end of the day I think it was because the new drives were formatted with ext3. I recreated them in ext2 and performance is back to where it was on those queries..
I hadn't realised that ext3 would give that much of a degradation with journaling! This page gives a rough indication of the time spent in various queries on one of my runs. http://stats.free-dc.org/stats.php?page=statsrun&proj=fah You can see after 12pm on sunday 20th when I installed the drives as ext3 and then 8pm tonight (PST) where I put them to ext2. Users RankRAC and rankinteams was dramatically dfferent. Phil On Mon, Jul 21, 2008 at 12:39 PM, Wm Mussatto <[EMAIL PROTECTED]> wrote: > On Mon, July 21, 2008 09:14, Brent Baisley wrote: > > 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. > Also that its turned ON. > > 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] > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- Help build our city at http://free-dc.myminicity.com !
