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