Thursday, July 31, 2008

Re: [PERFORM] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence

Richard Huxton <dev@archonet.com> wrote:
>> I just installed pgpool2 and whoaaa! Everything its like about 3 times
>> faster! My application are bash scripts using psql -c "UPDATE ...".
>
> Probably spending most of their time setting up a new connection, then
> clearing it down again.

If I do it in Python it could do all queries in the same connection, so
should be faster? Besides that 'psql' is written in perl, so its also
heavy, by not using psql I get rid of perl library in RAM. Also the
script uses wget to poll some external data sources a lot, also
needlessly opening new connection to the webserver, so I want to make
the script save the http connection, which means I must get rid of wget.
Maybe I should write some parts in C?

BTW, doesn't there exist any tool does what "psql -c" does, but is
written in plain C, not perl? I was looking for such psql replacement,
but couldn't find any.

>> # Number of connection pools allowed for a child process
>> max_pool = 1
>
> Might need to increase that to 2 or 3.

Why? The website says:

max_pool

The maximum number of cached connections in pgpool-II children
processes. pgpool-II reuses the cached connection if an incoming
connection is connecting to the same database by the same username.

But all my connections are to the same database and the same username,
and I only ever want my application to do 1 connection to the database
at a time, so why would I want/need 2 or 3 in max_pool?

> Not well known enough on the Debian side of the fence? It's simple
> enough to install from source though. Takes about one minute.

But is there any advantage for me compared to pgpool2, which works
really nice? In some parts, like doing some count(*) stuff, it now does
things in about one second, which took a few minutes to finish before (if
the other part of the scripts where doing something else on the database
at the same time).

--
Miernik
http://miernik.name/


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

No comments: