Re: how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux