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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
