Re: Optimise PostgreSQL for fast testing

On 24 Feb 2012, at 1:00, Dmytrii Nagirniak wrote:

>> What are the specs?
> A typical DB spec (test) does the following:
> 1. Creates a number of records (commonly about 5-ish, but may vary from 1 to ~40 across all tables).
> 2. Executes some queries against the dataset (**MOST** of them are pretty simple, with only 1-2 joins; only some
queriesuse 5-ish joins, sorting, distinct etc). 
> 3. May update couple of records too (we are talking about a couple only, so it in the range of 1-5, very rarely
~20-30).
> 4. At the end a spec truncates all the tables (uses truncate, not delete).
>
> This repeats on every spec/test (hundreds of those).

With that few records you probably don't benefit from any indexes on those tables; they take time to update, but the
queriesare not going to make use of them because sequential scans are likely to be faster. 
You still need some for some constraints, of course - the majority of those will probably be primary keys.

Since you truncate those tables anyway, autovacuum probably gets in your way more than it helps and it's unlikely it
cankeep up with the rate of changes. Turning it off and vacuuming between tests probably improves things. 

This also seems a rather pessimistic workload for any caches you have. I think you get very few requests for the same
data?You said you tried a RAM disk for storage and it didn't improve much, which supports that theory. Disk cache
probablydoesn't help you very much then, you could try reducing that and increase the memory assigned to PG, although
I'mleft wondering what it could use the extra memory for with this workload... 

And as others said, use EXPLAIN ANALYSE on the slower queries to see why they are slow. With that knowledge you may be
ableto speed them up (often significantly). 

And look into parallelising that workload. PG was designed for parallel workloads. Using a single process you're still
payingfor that and not benefitting. 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.