List:General Discussion« Previous MessageNext Message »
From:Phil Date:July 22 2008 4:05am
Subject:Re: Major Performance Degradation after replacing Hard Drive
View as plain text  
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 <mussatto@stripped> 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 <pchapman@stripped> 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 <anandkl@stripped>
> 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 <pchapman@stripped> 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:=@rank+1 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/mysql?unsub=1
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Help build our city at http://free-dc.myminicity.com !

Thread
Major Performance Degradation after replacing Hard DrivePhil21 Jul
  • Re: Major Performance Degradation after replacing Hard DriveAnanda Kumar21 Jul
    • Re: Major Performance Degradation after replacing Hard DrivePhil21 Jul
      • Re: Major Performance Degradation after replacing Hard DriveAnanda Kumar21 Jul
        • Re: Major Performance Degradation after replacing Hard DrivePhil21 Jul
      • Re: Major Performance Degradation after replacing Hard DriveBrent Baisley21 Jul
        • Re: Major Performance Degradation after replacing Hard Drivemos21 Jul
        • Re: Major Performance Degradation after replacing Hard DriveWm Mussatto21 Jul
          • Re: Major Performance Degradation after replacing Hard DrivePhil22 Jul