Message-ID: <48916B6A.4070203@archonet.com>
Date: Thu, 31 Jul 2008 08:36:10 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.16 (X11/20080707)
MIME-Version: 1.0
To: Miernik <public@public.miernik.name>
CC: pgsql-performance@postgresql.org
Subject: Re: how to fix problem then when two queries run at the
	same time, it takes longer to complete then if run in sequence
References: <20080723152112.30A4.0.NOFFLE@turbacz.local>
	<dcc563d10807231025s617c6b16neb93c6348a658cdb@mail.gmail.com>
	<20080723203203.31C4.0.NOFFLE@turbacz.local>
	<488840D3.2090808@archonet.com>
	<20080731061522.54FE.0.NOFFLE@turbacz.local>
In-Reply-To: <20080731061522.54FE.0.NOFFLE@turbacz.local>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Miernik wrote:
>> Might be worth turning off autovacuum and running a manual vacuum full
>> overnight if your database is mostly reads.
> 
> I run autovacum, and the database has a lot of updates all the time,
> also TRUNCATING tables and refilling them, usually one or two
> INSERTS/UPDATES per second.

OK

>> Finally, I think it's worth looking at pgpool or pgbouncer (as Alvaro
>> said) and set them to allow only one connection in the pool. I know
>> that pgbouncer offers per-transaction connection sharing which will
>> make this more practical. Even so, it will help if your application
>> can co-operate by closing the connection as soon as possible.
> 
> 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.

> I plan to rewrite it in Python, not sure if it would improve
> performance, but will at least be a "cleaner" implementation.

Careful of introducing any more overheads though. If libraries end up 
using another 2.5MB of RAM then that's 10% of your disk-cache gone.

> In /etc/pgpool.conf I used:
> 
> # number of pre-forked child process
> num_init_children = 1
> 
> # Number of connection pools allowed for a child process
> max_pool = 1

Might need to increase that to 2 or 3.

> Wanted to install pgbouncer, but it is broken currently in Debian. And
> why is it in contrib and not in main (speaking of Debian location)?

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

-- 
   Richard Huxton
   Archonet Ltd