Miernik wrote:
Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
won't ever run into. Why such an incredibly limited virtual machine?
Even my cell phone came with 256 meg built in two years ago.
Because I don't want to spend too much money on the machine rent, and a
48 MB RAM Xen is about all I can get with a budget of 100$ per year.
[snip]
My DB has several tables with like 100000 to 1 million rows each,
running sorts, joins, updates etc on them several times per hour.
About 10000 inserts and selects each hour, the whole DB takes 1.5 GB on
disk now, 500 MB dumped.
If I could shorten the time it takes to run each query by a factor of 3
that's something worth going for.
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 :-)
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.
You'll be trading your time against the budget, so bear that in mind.
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.
Changes
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.
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. Might be worth turning off autovacuum and
running a manual vacuum full overnight if your database is mostly reads.
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.
--
Richard Huxton
Archonet Ltd
