Re: Slow count(*) again...

Поиск
Список
Период
Сортировка
От Dan Harris
Тема Re: Slow count(*) again...
Дата
Msg-id 4CB495A7.6010700@drivefaster.net
обсуждение исходный текст
Ответ на Re: Slow count(*) again...  (Scott Carey <scott@richrelevance.com>)
Ответы Re: Slow count(*) again...  (Greg Smith <greg@2ndquadrant.com>)
Список pgsql-performance
On 10/12/10 10:44 AM, Scott Carey wrote:
> On Oct 12, 2010, at 8:39 AM, Dan Harris wrote:
>
>>   On 10/11/10 8:02 PM, Scott Carey wrote:
>>> would give you a 1MB read-ahead.  Also, consider XFS and its built-in defragmentation.  I have found that a longer
livedpostgres DB will get extreme 
>>> file fragmentation over time and sequential scans end up mostly random.  On-line file defrag helps tremendously.
>>>
>> We just had a corrupt table caused by an XFS online defrag.  I'm scared
>> to use this again while the db is live.  Has anyone else found this to
>> be safe?  But, I can vouch for the fragmentation issue, it happens very
>> quickly in our system.
>>
> What version?  I'm using the latest CentoOS extras build.
>
> We've been doing online defrag for a while now on a very busy database with>  8TB of data.  Not that that means there
areno bugs... 
>
> It is a relatively simple thing in xfs -- it writes a new file to temp in a way that allocates contiguous space if
available,then if the file has not been modified since it was re-written it is essentially moved on top of the other
one. This should be safe provided the journaling and storage is safe, etc. 
>
I'm not sure how to figure out what version of XFS we're on.. but it's
Linux kernel 2.6.24-24 x86_64 on Ubuntu Server 8.04.3.  Postgres version 8.3

We're due for an upgrade on that server soon so we'll do some more
testing once we upgrade.  Right now we are just living with the
fragmentation.  I'm glad to hear the regular on-line defrag is working
successfully, at least that gives me hope we can rely on it in the future.

-Dan

В списке pgsql-performance по дате отправления:

Предыдущее
От: Chris Browne
Дата:
Сообщение: Re: large dataset with write vs read clients
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Slow count(*) again...