Re: Optimise PostgreSQL for fast testing - Mailing list pgsql-general
| From | Dmytrii Nagirniak |
|---|---|
| Subject | Re: Optimise PostgreSQL for fast testing |
| Date | |
| Msg-id | 64B8E470-0719-40F3-81BB-0BF609F2B85E@gmail.com Whole thread Raw |
| In response to | Re: Optimise PostgreSQL for fast testing (Dmytrii Nagirniak <dnagir@gmail.com>) |
| Responses |
Re: Optimise PostgreSQL for fast testing
(Simon Riggs <simon@2ndQuadrant.com>)
|
| List | pgsql-general |
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.
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:
- 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@gmail.com> wrote:That's totally fine if PG can't beat SQLite on speed in **this particularcase**.
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
pgsql-general by date: