Re: Optimise PostgreSQL for fast testing

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(at)gmail(dot)com> 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
>