Skip site navigation (1) Skip section navigation (2)

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

From: Richard Huxton <dev(at)archonet(dot)com>
To: Miernik <public(at)public(dot)miernik(dot)name>
Cc: pgsql-performance(at)postgresql(dot)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
Date: 2008-07-24 08:44:03
Message-ID: 488840D3.2090808@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
Miernik wrote:
> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> 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

In response to

Responses

pgsql-performance by date

Next:From: Gauri KanekarDate: 2008-07-24 10:15:41
Subject: Partitioned Tables Foreign Key Constraints Problem
Previous:From: Albert Cervera ArenyDate: 2008-07-24 07:38:28
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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group