Re: explain analyze reports that my queries are fast but they run very slowly

On Thu, Dec 27, 2012 at 4:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Nikolas Everett <nik9000(at)gmail(dot)com> writes:
> > We straced the backend during the explain and it looked like the open
> > commands were taking several seconds each.
>
> Kind of makes me wonder if you have a whole lot of tables ("whole lot"
> in this context probably means tens of thousands) and are storing the
> database on a filesystem that doesn't scale well to lots of files in one
> directory. If that's the explanation, the reason the 8.3 installation
> was okay was likely that it was stored on a more modern filesystem.
>

We have 1897 files for our largest database which really isn't a whole lot.
The old servers were EXT3 over FC to a NetApp running RHEL5 PPC. The new
servers are on NFS to the same NetApp running RHEL5 Intel. We've failed
from our physical primary to a virtual secondary both of which seem to have
the same problem. We're in the process of rebuilding the a hot slave on
EXT3 over iSCSI. We'll fail over to it as soon as we can.

We never tried stracing the PPC infrastructure but it obviously didn't have
this problem.

We also have another cluster running with an identical setup which doesn't
seem to have the problem. In fact, the problem never came up durring
correctness testing for this problem either - it specifically required load
before it came up.

>
> BTW, please keep the list cc'd on replies.
>

Itchy reply finger.

>
> regards, tom lane
>