List:General Discussion« Previous MessageNext Message »
From:Phil Date:July 21 2008 3:03pm
Subject:Re: Major Performance Degradation after replacing Hard Drive
View as plain text  
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 <anandkl@stripped> wrote:

> 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 !
> >
>



-- 
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