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 <dna...@gmail.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
> 

Reply via email to