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


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