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

[Tatsuo Ishii]

> 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