[pgpool-general: 918] Re: pgpool generated queries and version 3.2

[anais.virt at googlemail.com]

Hi

Thank you for your help.

We have set up the 3.2 version with your recommended settings (increased
relcache_size) and it worked beautifully. It also stopped crashing which is
also great!

Thanks again.
Regards A.

On 21 August 2012 16:03, Tatsuo Ishii <ishii at postgresql.org> wrote:

> > Hello all,
> >
> > We are considering using pgpool in our small production server
> > environment. So far so good, we have successfully set up streaming
> > replication and put pgpool on top of it.
> >
> > All the tests seems to be ok however when viewing postgres log files
> > (set to logging 'all') we have noticed that pgpool constantly creates
> > lots of queries of the following syntax:
> >
> > SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid =
> > pgpool_regclass('table_name') AND c.relpersistence = 'u'
> > SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid =
> > pgpool_regclass('some_other_table_name') AND c.relpersistence = 'u'
> > SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.relname
> > = 'another_table_name' AND c.relnamespace = n.oid AND n.nspname =
> > 'pg_catalog'
> >
> > With every "our" application query there is more of the above. We also
> > noticed that out application is slightly (300ms-400ms) slower with
> > pgpool compared to the application going directly to the database
> > server.
> >
> > Is the above behavior of pgpool normal or do we need to change
> > anything in our configuration?
>
> Yes, normal. pgpool needs to obtain catalog information from
> PostgreSQL.  Those query results are cached per connection cache. So
> as long as the connection cache remains, the quries will not repeat
> again, thus performance will be getting better in the long run.
>
> BTW, if you use lots of tables, you might want to increate
> "relcache_size".  Also if you are sure that you do not use temp
> tables, you can turn off:check_temp_table and you can skip the query
> to check if the table is temporary or not.
>
> > We also have wierd problem with pgpool 3.2. It crashes after
> > succesfull first query. We belive the reason for it is that pgpool
> > creates another main process of pgpool. Did anybody have similiar
> > issue? Version 3.1.3 is absolutely fine...
>
> We have found two crash cases with 3.2.0 and fixed them at this
> point. I cannot tell if your crash is one of them or not without
> knowing details, such as back trace or self contained test case
> though.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120821/260dc829/attachment.html>