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

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