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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
