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

Miernik wrote:
> 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.

Nope - "C" all through.

> 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

Well ECPG lets you embed SQL directly in your "C".

>>> # 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?

From the subject line of your question: "how to fix problem then when
two queries run at the same time..."

Of course if you don't actually want to run two simultaneous queries,
then max_pool=1 is what you want.

>> 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?

Can't say. Given your limited RAM, it's probably worth looking at both
and seeing which leaves you more memory. Your main concern has got to be
to reduce wasted RAM.

> 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).

That will be because you're only running one query, I'd have thought.
Two queries might be sending you into swap.

--
Richard Huxton
Archonet Ltd

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