Re: Next steps in debugging database storage problems? - Mailing list pgsql-general
| From | Terry Schmitt |
|---|---|
| Subject | Re: Next steps in debugging database storage problems? |
| Date | |
| Msg-id | CAOOcysyd6ur=sqmNBiWRO9V6yDKSGGNOxESc-MS70tWHuLTUuw@mail.gmail.com Whole thread Raw |
| In response to | Re: Next steps in debugging database storage problems? (Jacob Bunk Nielsen <jacob@bunk.cc>) |
| List | pgsql-general |
I can't offer a whole lot of detail at this point, but I experienced a pretty bad caching issue about 2 years ago using XFS.
We were migrating a 1TB+ Oracle database to EDB's Advanced server 9.1 (Close enough for this discussion). I normally use ext4, but decided to try XFS for this build-out.
This was a Redhat 6.x system using NetApp SAN for storage. We extensively leverage FlexClones for creating production "read-only" instances as well as our development and testing environments. We take the snapshots of the running database storage and create FlexClones. The newly cloned database does a quick recovery on startup and away it goes. This has worked perfectly when using ext4 for years.
The problem I experienced with XFS, was when I started up the new clone for the first time. We would start getting various block read errors when accessing tables and indexes and knew the database was totally unreliable at this point.It was super painful troubleshooting as I could recreate the problem consistently, but it took a couple days of loading data and some creative scripts to recreate.
NetApp snapshots are consistent and reliable. It was clearly obvious that the data on disk did not match the data cached by the OS and/or XFS. We worked with Redhat, but never arrived at a solution. I finally gave up and switched back to ext4 and the problem went away.
T
On Fri, Aug 15, 2014 at 12:23 AM, Jacob Bunk Nielsen <jacob@bunk.cc> wrote:
HiThis time it took 45 days before this happened:
On the 1st of July 2014 Jacob Bunk Nielsen <jacob@bunk.cc> wrote:
> We have a PostgreSQL 9.3.4 running in an LXC container on Debian
> Wheezy on a Linux 3.10.43 kernel on a Dell R620 server. Data are
> stored on a XFS file system. We are seeing problems such as:
>
> unexpected data beyond EOF in block 2 of relation base/805208133/1238511128
>
> and
>
> could not read block 5 in file "base/805208348/1259338118": read only
> 0 of 8192 bytes
>
> This seems to occur every few days after the server has been up for
> 30-40 days. If we reboot the server it'll be another 30-40 days before
> we see any problems again.
>
> The server has been running fine on a Dell R710 for a long time, and was
> upgraded to a Dell R620 last year, when the problems started. We have
> tried switching to a different Dell R620, but that did not make a
> difference. We've seen this with kernels 3.2, 3.4 and 3.10.
LOG: unexpected EOF on standby connection
ERROR: unexpected data beyond EOF in block 140 of relation base/805208885/805209852
HINT: This has been seen to occur with buggy kernels; consider updating your system.
It always happens with small tables with lots of inserts and deletes.
From previous experience we know that it's now going to happen again in
a few days, so we'll probably try to schedule a reboot to give us
another 30-40 days.
Is anyone else seeing problems with PostgreSQL on XFS filesystems?
Any hints on how to debug what goes wrong here would be still be greatly
appreciated.This is still the case.
> We have multiple other PostgreSQL servers running in a similar setup
> without causing any problems, but this server is probably the busiest of
> our PostgreSQL servers.
Best regards
Jacob
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: