Re: 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:
> Firstly, congratulations on providing quite a large database on such a
> limited system. I think most people on such plans have tables with a
> few hundred to a thousand rows in them, not a million. Many of the
> people here are used to budgets a hundred or a thousand times of
> yours, so bear in mind you're as much an expert as them :-)

Well, I proved that it can reasonably well work, and I am finetuning the
system step by step, so it can work better.

> If you're going to get the most out of this, you'll want to set up
> your own Xen virtual machine on a local system so you can test
> changes.

Good idea.

> If you know other small organisations locally in a similar position
> perhaps consider sharing a physical machine and managing Xen
> yourselves - that can be cheaper.

Well, maybe, but its also a lot of hassle, not sure it's worth it, just
looking to get the most out of thje existing system.

> First step is to make sure you're running version 8.3 - there are some
> useful improvements there that reduce the size of shorter text fields,
> as well as the synchronised scans Albert mentions below.

I am running 8.3.3

> Second step is to make turn off any other processes you don't need.
> Tune down the number of consoles, apache processes, mail processes
> etc.  Normally not worth the trouble, but getting another couple of MB
> is worthwhile in your case.

There is no apache, but lighttpd, right now:

root@polica:~# free
             total     used     free   shared  buffers   cached
Mem:         49344    47840     1504        0        4    23924
-/+ buffers/cache:    23912    25432
Swap:       257000     9028   247972
root@polica:~#

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

> 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 ...".
I plan to rewrite it in Python, not sure if it would improve
performance, but will at least be a "cleaner" implementation.

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

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

--
Miernik
http://miernik.name/