Search Postgresql Archives

Re: Optimise PostgreSQL for fast testing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






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:


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.




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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux