Re: Optimise PostgreSQL for fast testing

[Dmytrii Nagirniak <dnagir@xxxxxxxxx>]

Hi all,

Just a follow-up.

I found the biggest bottleneck and now my specs run as fast as the SQLite ones.

TL;DR - the issue was the database cleanup that did the truncation. Apparently SQLite is way too fast there.

To "fix" it I open a transaction before each test and roll it back at the end.

Some numbers for ~700 tests.

- Truncation: SQLite - 34s, PG - 76s.

- Transaction: SQLite - 17s, PG - 18s.

2x speed increase for SQLite.

4x speed increase for PG.

Hope that'll help some of you.

Cheers,

Dmytrii

http://ApproachE.com

On 27/02/2012, at 10:57 AM, Dmytrii Nagirniak wrote:

Hi Guys,

Sorry for the late reply.

Thanks to all of you for the help. Appreciate all your suggestions.

So far (with my pretty limited knowledge of PG) I could speed it up a little bit (~20% or so comparing to the original installation) only by "tweaking" the settings.

I think it is relatively good keeping in mind that no single line of code has been changed.

Just my quick summary. Not interested in query tuning for now, just the DB tweaking:

• Best perf optimisation - `fsync=off`.
• Paralelisation should be considered as the 2nd option after `fsync=off`.
• All further optimisations might not be worth the effort unless you know PG well.
• RAM Disk didn't improve perf much at all.
• As Craig Ringer replied to my question at SO, the PostgreSQL 9.0 High Performance is worth the read.
• PG has awesome documentation, including Perf related: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

So far this is my approach:

• Since SQLite has basic FTS support (which I totally missed; thanks for pointing that out!) I can go a long way with it and probably won't need PG soon. But when I do:
• Run most of the specs agains SQLite. Only run specs that rely on PG features against PG (which should be minority).
• Run full acceptance tests (Cucumber) against a production DB (be it SQLite or PG).
• Will parallelise both unit and acceptance tests in the future.

Thanks a lot to all of you guys.

Your suggestions, criticism and discussion was really healthy, helpful and to the point.

Cheers,

Dmytrii

http://www.ApproachE.com

On 24/02/2012, at 9:25 PM, Simon Riggs wrote:

On Fri, Feb 24, 2012 at 12:16 AM, Dmytrii Nagirniak <dnagir@xxxxxxxxx> wrote:

That's totally fine if PG can't beat SQLite on speed in **this particular

case**.

The point is that PG can beat SQLite in this test *easily* if you
choose to use the main architectural difference as an advantage:
running tests concurrently.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services