From: Dan Harris <fbsd@drivefaster.net>
To: pgsql-performance@postgresql.org
Subject: Re: Slow count(*) again...
Date: Tue, 12 Oct 2010 18:00:11 -0600
Message-ID: <4CB4F68B.1010709@drivefaster.net> (raw)
In-Reply-To: <201010121533.34047.neil.whelchel@gmail.com>
References: <201010091626.18636.neil.whelchel@gmail.com>
<90E30938-1E5E-4DCA-A1FB-AEECEB76A70D@richrelevance.com>
<4CB48127.6090200@drivefaster.net>
<201010121533.34047.neil.whelchel@gmail.com>
On 10/12/10 4:33 PM, Neil Whelchel wrote:
> On Tuesday 12 October 2010 08:39:19 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 lived postgres 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.
>>
>> -Dan
> I would like to know the details of what was going on that caused your
> problem. I have been using XFS for over 9 years, and it has never caused any
> trouble at all in a production environment. Sure, I had many problems with it
> on the test bench, but in most cases the issues were very clear and easy to
> avoid in production. There were some (older) XFS tools that caused some
> problems, but that is in the past, and as time goes on, it seems take less and
> less planning to make it work properly.
> -Neil-
>
There were roughly 50 transactions/sec going on at the time I ran it.
xfs_db reported 99% fragmentation before it ran ( we haven't been
running it via cron ). The operation completed in about 15 minutes (
360GB of used data on the file system ) with no errors. Everything
seemed fine until the next morning when a user went to query a table we
got a message about a "missing" file inside the pg cluster. We were
unable to query the table at all via psql. It was a bit of a panic
situation so we restored that table from backup immediately and the
problem was solved without doing more research.
This database has been running for years with no problem ( and none
since ), that was the first time I tried to do an on-line defrag and
that was the only unusual variable introduced into the system at that
time so it was a strong enough correlation for me to believe that caused
it. Hopefully this was just a corner case..
-Dan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: fbsd@drivefaster.net
Subject: Re: Slow count(*) again...
In-Reply-To: <4CB4F68B.1010709@drivefaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox