List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:July 21 2008 1:41pm
Subject:Re: Major Performance Degradation after replacing Hard Drive
View as plain text  
when you run this update, what is the IO WAIT  from the top command.

regards
anandkl


On 7/21/08, 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 !
>

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