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

Поиск
Список
Период
Сортировка
От Nikolas Everett
Тема Re: explain analyze reports that my queries are fast but they run very slowly
Дата
Msg-id CAPmjWd3wLRDgJQHJ=qGWqpLjBjm=0ib8MmfGhsVWUk0Oj9k_Nw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: explain analyze reports that my queries are fast but they run very slowly  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: explain analyze reports that my queries are fast but they run very slowly
Список pgsql-performance
Дерево обсуждения



On Thu, Dec 27, 2012 at 4:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nikolas Everett <nik9000@gmail.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

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: explain analyze reports that my queries are fast but they run very slowly
Следующее
От: Tom Lane
Дата:
Сообщение: Re: explain analyze reports that my queries are fast but they run very slowly