Thread: Optimise PostgreSQL for fast testing

Optimise PostgreSQL for fast testing

From
Dmytrii Nagirniak
Date:
Hi guys,

I wonder if you can suggest me how to speed-up PG when running specs.
I asked it at SO here:


But briefly. PG specs are >2x slower than SQLite.
I want it to be on par (don't care about reliability or anything, just need fast specs).

Would appreciate some suggestions.

Cheers,
Dmytrii Nagirniak
http://ApproachE.com

Re: Optimise PostgreSQL for fast testing

From
Jan Kesten
Date:
Hi Dmytrii,

just as short idea, put "fsync = off" in your postgres.conf. That turns off that after a commit data is forcilby written to disk - if the database crashes there might be dataloss.

Von meinem iPhone gesendet

Am 23.02.2012 um 06:13 schrieb Dmytrii Nagirniak <dnagir@gmail.com>:

Hi guys,

I wonder if you can suggest me how to speed-up PG when running specs.
I asked it at SO here:


But briefly. PG specs are >2x slower than SQLite.
I want it to be on par (don't care about reliability or anything, just need fast specs).

Would appreciate some suggestions.

Cheers,
Dmytrii Nagirniak
http://ApproachE.com

Re: Optimise PostgreSQL for fast testing

From
Dmytrii Nagirniak
Date:


On 23/02/2012, at 4:38 PM, Jan Kesten wrote:

Hi Dmytrii,

just as short idea, put "fsync = off" in your postgres.conf. That turns off that after a commit data is forcilby written to disk - if the database crashes there might be dataloss.

Thanks. So far I tried:

fsync = off
full_page_writes = off

It seems it got a *little* faster (down to ~65 seconds from ~76) but is till too far from my target of ~34 secs.


Re: Optimise PostgreSQL for fast testing

From
Pavel Stehule
Date:
Hello

SQLite should be faster in single user test - it is optimized for this
purpose. So you cannot to get same speed from PostgreSQL

Pavel



2012/2/23 Dmytrii Nagirniak <dnagir@gmail.com>:
>
>
> On 23/02/2012, at 4:38 PM, Jan Kesten wrote:
>
> Hi Dmytrii,
>
> just as short idea, put "fsync = off" in your postgres.conf. That turns off
> that after a commit data is forcilby written to disk - if the database
> crashes there might be dataloss.
>
>
> Thanks. So far I tried:
>
> fsync = off
> full_page_writes = off
>
> It seems it got a *little* faster (down to ~65 seconds from ~76) but is till
> too far from my target of ~34 secs.
>
>

Re: Optimise PostgreSQL for fast testing

From
Dmytrii Nagirniak
Date:
On 23/02/2012, at 5:05 PM, Pavel Stehule wrote:

> SQLite should be faster in single user test - it is optimized for this
> purpose. So you cannot to get same speed from PostgreSQL

That's unfortunate to hear.
But hoped with a bit of tuning to get PG close to SQLite by the fact that I can change the settings in such a way so it
runsmore like a single user DB. 



Re: Optimise PostgreSQL for fast testing

From
Pavel Stehule
Date:
2012/2/23 Dmytrii Nagirniak <dnagir@gmail.com>:
> On 23/02/2012, at 5:05 PM, Pavel Stehule wrote:
>
>> SQLite should be faster in single user test - it is optimized for this
>> purpose. So you cannot to get same speed from PostgreSQL
>
> That's unfortunate to hear.
> But hoped with a bit of tuning to get PG close to SQLite by the fact that I can change the settings in such a way so
itruns more like a single user DB. 

It  depends on test queries and data set size - but with simple
queries  and small dataset SQLite should be 2x - 10x faster than
PostgreSQL.

PostgreSQL is optimized for complex queries and multi user environment

Pavel

>
>

Re: Optimise PostgreSQL for fast testing

From
Marti Raudsepp
Date:
On Thu, Feb 23, 2012 at 08:02, Dmytrii Nagirniak <dnagir@gmail.com> wrote:
> Thanks. So far I tried:
>
> fsync = off
> full_page_writes = off
>
> It seems it got a *little* faster (down to ~65 seconds from ~76) but is till
> too far from my target of ~34 secs.

If you have lots of very simple queries, then usually much of the
overhead is in query planning. There are a few tricks to dumb down the
planner to make it faster -- although that may come at the cost of
slowing down execution.

* If your queries use joins at all, you can reduce planning overhead
by setting join_collapse_limit=1 and from_collapse_limit=1 or some
other low number.
* Set default_statistics_target=5 or another low number and run
ANALYZE on the whole database.
* Set enable_bitmapscan=off, enable_material=off,
enable_mergejoin=off, enable_hashjoin=off -- this will prevent the
planner for trying certain kinds of plans in the first place.

Just for the heck of it, you might gain a bit by setting
track_activities=off, update_process_title=off

Regards,
Marti

Re: Optimise PostgreSQL for fast testing

From
Adrian Klaver
Date:
On Wednesday, February 22, 2012 9:13:06 pm Dmytrii Nagirniak wrote:
> Hi guys,
>
> I wonder if you can suggest me how to speed-up PG when running specs.
> I asked it at SO here:
>
> http://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-tes
> ting
>
> But briefly. PG specs are >2x slower than SQLite.
> I want it to be on par (don't care about reliability or anything, just need
> fast specs).
>
> Would appreciate some suggestions.

Not enough information to make suggestions.
Why are you switching databases?
What are the specs?
What is the application?
What is the use case?
     Single user?
        Networked, multiple user?
Do you see the application/database growing?

At this point you are comparing apples and oranges. Sqlite is basically a single
user embedded database, Postgres a multi user, networked database.  They both
work well for the use they are designed for, it is a matter of determining which
is a better fit for your anticipated use.

>
> Cheers,
> Dmytrii Nagirniak
> http://ApproachE.com <http://www.ApproachE.com>

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Optimise PostgreSQL for fast testing

From
Simon Riggs
Date:
On Thu, Feb 23, 2012 at 5:13 AM, Dmytrii Nagirniak <dnagir@gmail.com> wrote:

> I wonder if you can suggest me how to speed-up PG when running specs.
> I asked it at SO here:
>
> http://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing
>
> But briefly. PG specs are >2x slower than SQLite.
> I want it to be on par (don't care about reliability or anything, just need
> fast specs).
>
> Would appreciate some suggestions.

You really need to explain why this matters...

You mention a "typical Ruby on Rails app" and then discuss SQLite.
Well, typical web apps have more than 1 user, so fairly obviously
using SQLite isn't appropriate. If SQLite isn't appropriate, why are
you testing with it? How does a test run on a database you aren't
using in production tell you anything about the success or otherwise
of your program. It doesn't, so saying it runs quicker is irrelevant,
surely?

Perhaps just run half the test, that would make it twice as quick and
still just as valid.

If Postgres tests run in ~1 minute, what benefit have you gained from
saving 30 seconds? How often are you running tests?

So please explain a little more.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Optimise PostgreSQL for fast testing

From
Jack Christensen
Date:
On 2/23/2012 9:22 AM, Simon Riggs wrote:
> On Thu, Feb 23, 2012 at 5:13 AM, Dmytrii Nagirniak<dnagir@gmail.com>  wrote:
>
>> I wonder if you can suggest me how to speed-up PG when running specs.
>> I asked it at SO here:
>>
>> http://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing
>>
>> But briefly. PG specs are>2x slower than SQLite.
>> I want it to be on par (don't care about reliability or anything, just need
>> fast specs).
>>
>> Would appreciate some suggestions.
> You really need to explain why this matters...
>
> You mention a "typical Ruby on Rails app" and then discuss SQLite.
> Well, typical web apps have more than 1 user, so fairly obviously
> using SQLite isn't appropriate. If SQLite isn't appropriate, why are
> you testing with it? How does a test run on a database you aren't
> using in production tell you anything about the success or otherwise
> of your program. It doesn't, so saying it runs quicker is irrelevant,
> surely?
>
> Perhaps just run half the test, that would make it twice as quick and
> still just as valid.
>
> If Postgres tests run in ~1 minute, what benefit have you gained from
> saving 30 seconds? How often are you running tests?
>
> So please explain a little more.
>
As another Rails developer using PostgreSQL I think I can explain the
use case. In standard Rails usage, the ORM handles all SQL query
generation and thus the application is database agnostic. It is typical
to use SQLite in development and testing and MySQL or PostgreSQL in
production. However, if any PostgreSQL specific functionality is used
then obviously PostgreSQL must also be used in development and testing.

Another common practice is test-driven development. So the test suite
for the application may run scores or hundreds of times per day per
developer. So the speed of the test suite is of vital importance to
developers. A 30 second difference 100's of times per day really can add
up.

--
Jack Christensen
jackc@hylesanderson.edu


Re: Optimise PostgreSQL for fast testing

From
David Salisbury
Date:

On 2/23/12 9:06 AM, Jack Christensen wrote:
> As another Rails developer using PostgreSQL I think I can explain the
> use case. In standard Rails usage, the ORM handles all SQL query
> generation and thus the application is database agnostic. It is typical
> to use SQLite in development and testing and MySQL or PostgreSQL in
> production. However, if any PostgreSQL specific functionality is used
> then obviously PostgreSQL must also be used in development and testing.
>
> Another common practice is test-driven development. So the test suite
> for the application may run scores or hundreds of times per day per
> developer. So the speed of the test suite is of vital importance to
> developers. A 30 second difference 100's of times per day really can add
> up.

Perhaps the emphasis should be on the tests themselves, and not PG cycles.
Is he using Factory or Factory.build?.. that sort of thing.  Is he running
the entire test suite, when in fact just running one test would do until
final checkin?

And I'm curious as to why anyone would need to run tests 100s of times a day.
How much code can ya write, or is he simply writing tests themselves all day?

-ds


Re: Optimise PostgreSQL for fast testing

From
Scott Marlowe
Date:
On Thu, Feb 23, 2012 at 10:05 AM, David Salisbury <salisbury@globe.gov> wrote:
>
>
> On 2/23/12 9:06 AM, Jack Christensen wrote:
>>
>> As another Rails developer using PostgreSQL I think I can explain the
>> use case. In standard Rails usage, the ORM handles all SQL query
>> generation and thus the application is database agnostic. It is typical
>> to use SQLite in development and testing and MySQL or PostgreSQL in
>> production. However, if any PostgreSQL specific functionality is used
>> then obviously PostgreSQL must also be used in development and testing.
>>
>> Another common practice is test-driven development. So the test suite
>> for the application may run scores or hundreds of times per day per
>> developer. So the speed of the test suite is of vital importance to
>> developers. A 30 second difference 100's of times per day really can add
>> up.
>
>
> Perhaps the emphasis should be on the tests themselves, and not PG cycles.
> Is he using Factory or Factory.build?.. that sort of thing.  Is he running
> the entire test suite, when in fact just running one test would do until
> final checkin?
>
> And I'm curious as to why anyone would need to run tests 100s of times a
> day.
> How much code can ya write, or is he simply writing tests themselves all
> day?

He's probably doing automated continuous integration testing.  Two
jobs ago we had a setup to do that and had 40k tests.  The whole test
suite took about 30 minutes to runm and kicked off automatically when
the last one finished and anyone touched any code.

Re: Optimise PostgreSQL for fast testing

From
Simon Riggs
Date:
On Thu, Feb 23, 2012 at 5:20 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

> He's probably doing automated continuous integration testing.  Two
> jobs ago we had a setup to do that and had 40k tests.  The whole test
> suite took about 30 minutes to runm and kicked off automatically when
> the last one finished and anyone touched any code.

Having lots of tests is a good thing. Bring 'em on.

If you use SQLite for that, then it all runs in a single thread and it
could easily take 30 minutes or longer.

Now all you have to do is parallelise the tests and everything can
work 10 times quicker and it would be much faster than the time SQLite
produced.

So using PostgreSQL for testing would be both quicker and more
accurate, if you set the tests up right.

The PostgreSQL regression tests are parallelised - if they weren't
we'd produce a lot less work

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Optimise PostgreSQL for fast testing

From
Scott Marlowe
Date:
On Thu, Feb 23, 2012 at 11:15 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Thu, Feb 23, 2012 at 5:20 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
>> He's probably doing automated continuous integration testing.  Two
>> jobs ago we had a setup to do that and had 40k tests.  The whole test
>> suite took about 30 minutes to runm and kicked off automatically when
>> the last one finished and anyone touched any code.
>
> Having lots of tests is a good thing. Bring 'em on.
>
> If you use SQLite for that, then it all runs in a single thread and it
> could easily take 30 minutes or longer.
>
> Now all you have to do is parallelise the tests and everything can
> work 10 times quicker and it would be much faster than the time SQLite
> produced.

It's funny how once you start thinking of how to optimize to run 8 or
16 or more concurrent tests, you sometimes forget that doing that same
thing to some simpler tools might result in very poor performance til
you have to run the tests on the old system and start wondering why
you ever thought it was fast.

Re: Optimise PostgreSQL for fast testing

From
Dmytrii Nagirniak
Date:
On 23/02/2012, at 7:35 PM, Marti Raudsepp wrote:

> If you have lots of very simple queries, then usually much of the
> overhead is in query planning. There are a few tricks to dumb down the
> planner to make it faster -- although that may come at the cost of
> slowing down execution.
>
> * If your queries use joins at all, you can reduce planning overhead
> by setting join_collapse_limit=1 and from_collapse_limit=1 or some
> other low number.
> * Set default_statistics_target=5 or another low number and run
> ANALYZE on the whole database.
> * Set enable_bitmapscan=off, enable_material=off,
> enable_mergejoin=off, enable_hashjoin=off -- this will prevent the
> planner for trying certain kinds of plans in the first place.
>
> Just for the heck of it, you might gain a bit by setting
> track_activities=off, update_process_title=off

Thanks a lot Marti.
After I've applied these settings I couldn't see major improvement over fsync=off. It was pretty much within the error
margin.

But you're right that most of the queries are simple and I hope this will work a little bit faster on small runs (like
asingle test suite). 
Will definitely keep an eye on these settings.

So far fsync=off is the best I could get.

Thanks one more time for the help.

Cheers.

Re: Optimise PostgreSQL for fast testing

From
Dmytrii Nagirniak
Date:

On 24/02/2012, at 2:06 AM, Adrian Klaver wrote:


Would appreciate some suggestions.

Not enough information to make suggestions.

Jack Christensen pretty nailed it very well. But I'll answer the particulars here too:

Why are you switching databases?
Need FTS from PG. No other reasons yet.

What are the specs?
A typical DB spec (test) does the following:
1. Creates a number of records (commonly about 5-ish, but may vary from 1 to ~40 across all tables).
2. Executes some queries against the dataset (**MOST** of them are pretty simple, with only 1-2 joins; only some queries use 5-ish joins, sorting, distinct etc).
3. May update couple of records too (we are talking about a couple only, so it in the range of 1-5, very rarely ~20-30).
4. At the end a spec truncates all the tables (uses truncate, not delete).

This repeats on every spec/test (hundreds of those).

What is the application?

As I said, it is a typical Rails web application.
But for the purpose of this thread it is irrelevant since we're basically talking about single user, development/test environment where the only user of the app is the "spec/test" and no concurrency.

What is the use case?
Single user?
       Networked, multiple user?
See above.

Do you see the application/database growing?
No. As I said before the database is used ONLY for running tests and can be recreated at any time.
Generally it will never have any data in it (except when specs are running).

Cheers.

Re: Optimise PostgreSQL for fast testing

From
Dmytrii Nagirniak
Date:
On 24/02/2012, at 2:22 AM, Simon Riggs wrote:

Would appreciate some suggestions.

You really need to explain why this matters...

I've just replied to Adrian with more details. I suppose you don't mind to take a look there so I won't copy-paste it :)


You mention a "typical Ruby on Rails app" and then discuss SQLite.
Well, typical web apps have more than 1 user, so fairly obviously
using SQLite isn't appropriate.
This is rally irrelevant to this thread, but I believe you are not right here: http://www.sqlite.org/whentouse.html

Websites

SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites).



If SQLite isn't appropriate, why are
you testing with it?
It is appropriate in the first place. But another reason to use SQLite for testing is that is fast and easy to maintain and setup.
Since I need to use PG FTS (and that's the only reason), I have to use PG for testing too.
And this is where this thread comes in.


Perhaps just run half the test, that would make it twice as quick and
still just as valid.

If Postgres tests run in ~1 minute, what benefit have you gained from
saving 30 seconds? How often are you running tests?

I think it is irrelevant to the this thread, but here's my math:

I run full suite aprox every 5 mins. Which is ~ 100 times a day.
Now, 30s*100 = 50mins vs 60s=100mins.
This is another ~hour lost a day.

On top of that I would run a single test file probably every couple of minutes or so.
(So even if it is a couple of seconds slower, it all adds up a lot).

This is just a common TDD style. Watch the https://www.destroyallsoftware.com/ for example to see what I mean.


Don't get me wrong, I realise that PG and SQLite are totally different beasts.

That's totally fine if PG can't beat SQLite on speed in **this particular case**.
I just want to try to tune it to be as fast as it can (for **this particular case**, see my reply to Adrian).

Cheers.


Re: Optimise PostgreSQL for fast testing

From
Dmytrii Nagirniak
Date:
On 24/02/2012, at 5:15 AM, Simon Riggs wrote:

Now all you have to do is parallelise the tests and everything can
work 10 times quicker and it would be much faster than the time SQLite
produced.

So using PostgreSQL for testing would be both quicker and more
accurate, if you set the tests up right.

That is certainly true. And there are number of techniques to make tests faster.
But that is outside of the scope of this thread I believe.

I only want to make it run locally (don't care about CI yet) faster.
So far I could get the most out of with fsync=off (which is ~15% improvement).


Re: Optimise PostgreSQL for fast testing

From
Greg Smith
Date:
On 02/23/2012 07:16 PM, Dmytrii Nagirniak wrote:
> That's totally fine if PG can't beat SQLite on speed in **this
> particular case**.
> I just want to try to tune it to be as fast as it can (for **this
> particular case**, see my reply to Adrian).

You can find all of the big tunable parameters at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server That's as
good of a general "how do I make this faster by tweaking the server"
guide as you'll get.

Once you've hit the big tunables--shared_buffers, checkpoint_segments,
work_mem, effective_cache_size, and tweaking either synchronous_commit
or fsync--there's not too much else you can do except dig into what's
slow in individual queries.  Only other thing that might help is running
ANALYZE against the whole database after any major loading of test data,
just to make sure the queries are being executed with good statistics.

If you can extract the SQL from the test cases so they can be executed
directly with the psql client, you could add "\timing" before them to
see how long each individual query runs, to look for the long running
ones.  It's possible that every statement is a little slower, which
would be unsurprising and not something you can really resolve if so.
It could just be a small number that are being executed poorly though,
in which case specific query tweaking might be possible.  You might get
further insight by posting the EXPLAIN ANALYZE plans of whatever the
slowest single query is.  More on that subject at
http://wiki.postgresql.org/wiki/Slow_Query_Questions

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

Re: Optimise PostgreSQL for fast testing

From
Scott Marlowe
Date:
On Thu, Feb 23, 2012 at 5:22 PM, Dmytrii Nagirniak <dnagir@gmail.com> wrote:
> On 24/02/2012, at 5:15 AM, Simon Riggs wrote:
>
> Now all you have to do is parallelise the tests and everything can
> work 10 times quicker and it would be much faster than the time SQLite
> produced.
>
> So using PostgreSQL for testing would be both quicker and more
> accurate, if you set the tests up right.
>
>
> That is certainly true. And there are number of techniques to make tests
> faster.
> But that is outside of the scope of this thread I believe.

Is there a reaon why you can't parallelize your tests?  If you could
run 10 or so at a time it would be worth benchmarking.

Also, look into automating your testing, so that you don't need to run
the tests all the time, they run in the background, and if something
breaks they send you an alert with the code that broke things etc.

Re: Optimise PostgreSQL for fast testing

From
Adrian Klaver
Date:
On Thursday, February 23, 2012 4:00:08 pm Dmytrii Nagirniak wrote:
> On 24/02/2012, at 2:06 AM, Adrian Klaver wrote:
> >> Would appreciate some suggestions.
> >
> > Not enough information to make suggestions.
>
> Jack Christensen pretty nailed it very well. But I'll answer the particulars
here too:
> > Why are you switching databases?
>
> Need FTS from PG. No other reasons yet.
>

Not sure if you need the specific features of FTS from Postgres, otherwise Sqlite
has FTS also:

http://www.sqlite.org/fts3.html

It is different beast than the Postgres version so I don't know if it would
apply.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Optimise PostgreSQL for fast testing

From
Alban Hertroys
Date:
On 24 Feb 2012, at 1:00, Dmytrii Nagirniak wrote:

>> What are the specs?
> A typical DB spec (test) does the following:
> 1. Creates a number of records (commonly about 5-ish, but may vary from 1 to ~40 across all tables).
> 2. Executes some queries against the dataset (**MOST** of them are pretty simple, with only 1-2 joins; only some
queriesuse 5-ish joins, sorting, distinct etc). 
> 3. May update couple of records too (we are talking about a couple only, so it in the range of 1-5, very rarely
~20-30).
> 4. At the end a spec truncates all the tables (uses truncate, not delete).
>
> This repeats on every spec/test (hundreds of those).

With that few records you probably don't benefit from any indexes on those tables; they take time to update, but the
queriesare not going to make use of them because sequential scans are likely to be faster. 
You still need some for some constraints, of course - the majority of those will probably be primary keys.

Since you truncate those tables anyway, autovacuum probably gets in your way more than it helps and it's unlikely it
cankeep up with the rate of changes. Turning it off and vacuuming between tests probably improves things. 

This also seems a rather pessimistic workload for any caches you have. I think you get very few requests for the same
data?You said you tried a RAM disk for storage and it didn't improve much, which supports that theory. Disk cache
probablydoesn't help you very much then, you could try reducing that and increase the memory assigned to PG, although
I'mleft wondering what it could use the extra memory for with this workload... 

And as others said, use EXPLAIN ANALYSE on the slower queries to see why they are slow. With that knowledge you may be
ableto speed them up (often significantly). 

And look into parallelising that workload. PG was designed for parallel workloads. Using a single process you're still
payingfor that and not benefitting. 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


Re: Optimise PostgreSQL for fast testing

From
Simon Riggs
Date:
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 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

Re: Optimise PostgreSQL for fast testing

From
Dmytrii Nagirniak
Date:
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@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

Re: Optimise PostgreSQL for fast testing

From
Dmytrii Nagirniak
Date:
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@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


Re: Optimise PostgreSQL for fast testing

From
Simon Riggs
Date:
On Fri, Mar 16, 2012 at 4:38 AM, Dmytrii Nagirniak <dnagir@gmail.com> wrote:

> 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.

Did you try this? synchronous_commit = off

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services