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 queries use 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 queries are 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 can keep 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 probably doesn't help you very much then, you could try reducing that and increase the memory assigned to PG, although I'm left 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 able to speed them up (often significantly).

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

Alban Hertroys

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