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 >
