Performance vs. memory trade-off question

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Performance vs. memory trade-off question

Richard Hipp-3
A new feature on a branch has the following disadvantages:

(1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
normally about 0.5% faster, so enabling this feature is sort of like
going back by one-half of a release cycle.)

(2)  The code space (the size of the library) is between 400 and 500
bytes larger (depending on compiler and optimization settings).

The this one advantage:

(3)  Each database connection uses about 72 KB less heap space.

QUESTION:  Should this feature be default-on or default-off?

What's more important to you?  0.25% fewer CPU cycles or about 72KB
less heap space used per database connection?

The feature can be activated or deactivated at start-time, but you
take the disadvantages (the performance hit and slightly larger
library size) regardless, unless you disable the feature at
compile-time.  If the feature is compile-time disabled, then the
corresponding code is omitted and and it cannot be turned on at
start-time.

If you have opinions, you can reply to this mailing list, or directly to me.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Gary R. Schmidt
On 15/12/2019 00:27, Richard Hipp wrote:

> A new feature on a branch has the following disadvantages:
>
> (1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
> normally about 0.5% faster, so enabling this feature is sort of like
> going back by one-half of a release cycle.)
>
> (2)  The code space (the size of the library) is between 400 and 500
> bytes larger (depending on compiler and optimization settings).
>
> The this one advantage:
>
> (3)  Each database connection uses about 72 KB less heap space.
>
> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>
> The feature can be activated or deactivated at start-time, but you
> take the disadvantages (the performance hit and slightly larger
> library size) regardless, unless you disable the feature at
> compile-time.  If the feature is compile-time disabled, then the
> corresponding code is omitted and and it cannot be turned on at
> start-time.
>
> If you have opinions, you can reply to this mailing list, or directly to me.
>
I'll vote for "default on"
        - 72KB of heap is a fair old whack, particularly for those on bare silicon.
        - the half-a-step back in performance is a "meh," given that only those
who manage to push the latest version to production quickly will
probably even notice, that is, if they look for it.
        - 400-500 bytes is not a dramatic cost, but it still might be too much
for someone's PROM.


        Cheers,
                Gary B-)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Philip Bennefall
In reply to this post by Richard Hipp-3
Hi Richard,


I can see the advantage of both, but personally I prefer the greater
speed with slightly more memory approach (e.g. what we have now).


Kind regards,


Philip Bennefall


On 12/14/2019 2:27 PM, Richard Hipp wrote:

> A new feature on a branch has the following disadvantages:
>
> (1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
> normally about 0.5% faster, so enabling this feature is sort of like
> going back by one-half of a release cycle.)
>
> (2)  The code space (the size of the library) is between 400 and 500
> bytes larger (depending on compiler and optimization settings).
>
> The this one advantage:
>
> (3)  Each database connection uses about 72 KB less heap space.
>
> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>
> The feature can be activated or deactivated at start-time, but you
> take the disadvantages (the performance hit and slightly larger
> library size) regardless, unless you disable the feature at
> compile-time.  If the feature is compile-time disabled, then the
> corresponding code is omitted and and it cannot be turned on at
> start-time.
>
> If you have opinions, you can reply to this mailing list, or directly to me.
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Dominique Pellé
In reply to this post by Richard Hipp-3
Richard Hipp <[hidden email]> wrote:

> A new feature on a branch has the following disadvantages:
>
> (1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
> normally about 0.5% faster, so enabling this feature is sort of like
> going back by one-half of a release cycle.)
>
> (2)  The code space (the size of the library) is between 400 and 500
> bytes larger (depending on compiler and optimization settings).
>
> The this one advantage:
>
> (3)  Each database connection uses about 72 KB less heap space.
>
> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>
> The feature can be activated or deactivated at start-time, but you
> take the disadvantages (the performance hit and slightly larger
> library size) regardless, unless you disable the feature at
> compile-time.  If the feature is compile-time disabled, then the
> corresponding code is omitted and and it cannot be turned on at
> start-time.
>
> If you have opinions, you can reply to this mailing list, or directly to me.

It depends of course, but for my project, saving 72KB of memory
per connection is more important as many databases are opened,
each consuming memory. It may be an unusual scenario though.

Furthermore, since I set a heap soft limit for SQLite, saving
memory per connection should leave more memory for page
caching, hence more SQLite page cache hits, hence less I/Os.

In any case, if user can choose at compile time or runtime,
the default behavior does not matter for me.

I'm curious about what kind of change reduces memory per
connection.

Regards
Dominique
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Richard Hipp-3
On 12/14/19, Dominique Pellé <[hidden email]> wrote:
>
> I'm curious about what kind of change reduces memory per
> connection.
>

The branch is here: https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2

I'll try to add more description later - busy with other things right
this moment.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Richard Hipp-3
On 12/14/19, Richard Hipp <[hidden email]> wrote:

> On 12/14/19, Dominique Pellé <[hidden email]> wrote:
>>
>> I'm curious about what kind of change reduces memory per
>> connection.
>>
>
> The branch is here:
> https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2
>
> I'll try to add more description later - busy with other things right
> this moment.

Background information on the lookaside memory allocator:
https://sqlite.org/malloc.html#_lookaside_memory_allocator


> --
> D. Richard Hipp
> [hidden email]
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Esdras Mayrink
In reply to this post by Dominique Pellé
I think it should be turned on by default. Saving 72KB per connection is
worth the performance penalty.

On Sat, Dec 14, 2019 at 11:22 AM Dominique Pellé <[hidden email]>
wrote:

> Richard Hipp <[hidden email]> wrote:
>
> > A new feature on a branch has the following disadvantages:
> >
> > (1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
> > normally about 0.5% faster, so enabling this feature is sort of like
> > going back by one-half of a release cycle.)
> >
> > (2)  The code space (the size of the library) is between 400 and 500
> > bytes larger (depending on compiler and optimization settings).
> >
> > The this one advantage:
> >
> > (3)  Each database connection uses about 72 KB less heap space.
> >
> > QUESTION:  Should this feature be default-on or default-off?
> >
> > What's more important to you?  0.25% fewer CPU cycles or about 72KB
> > less heap space used per database connection?
> >
> > The feature can be activated or deactivated at start-time, but you
> > take the disadvantages (the performance hit and slightly larger
> > library size) regardless, unless you disable the feature at
> > compile-time.  If the feature is compile-time disabled, then the
> > corresponding code is omitted and and it cannot be turned on at
> > start-time.
> >
> > If you have opinions, you can reply to this mailing list, or directly to
> me.
>
> It depends of course, but for my project, saving 72KB of memory
> per connection is more important as many databases are opened,
> each consuming memory. It may be an unusual scenario though.
>
> Furthermore, since I set a heap soft limit for SQLite, saving
> memory per connection should leave more memory for page
> caching, hence more SQLite page cache hits, hence less I/Os.
>
> In any case, if user can choose at compile time or runtime,
> the default behavior does not matter for me.
>
> I'm curious about what kind of change reduces memory per
> connection.
>
> Regards
> Dominique
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Dominique Pellé
In reply to this post by Richard Hipp-3
Richard Hipp <[hidden email]> wrote:

> On 12/14/19, Dominique Pellé <[hidden email]> wrote:
> >
> > I'm curious about what kind of change reduces memory per
> > connection.
> >
>
> The branch is here: https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2
>
> I'll try to add more description later - busy with other things right
> this moment.

Ah, it about lookaside. I build with SQLITE_OMIT_LOOKASIDE
so I suppose the mini-lookaside-take-2 branch will do nothing then
in this case.

Regards
Dominique
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Donald Shepherd
On Sun, 15 Dec 2019 at 1:40 am, Dominique Pellé <[hidden email]>
wrote:

> Richard Hipp <[hidden email]> wrote:
>
> > On 12/14/19, Dominique Pellé <[hidden email]> wrote:
> > >
> > > I'm curious about what kind of change reduces memory per
> > > connection.
> > >
> >
> > The branch is here:
> https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2
> >
> > I'll try to add more description later - busy with other things right
> > this moment.
>
> Ah, it about lookaside. I build with SQLITE_OMIT_LOOKASIDE
> so I suppose the mini-lookaside-take-2 branch will do nothing then
> in this case.
>
> Regards
> Dominique
> _______________________________________________


Gut feel is that the divide will fall such that embedded use cases want the
memory and PC or equivalent use cases want the CPU.  I fall into the latter
so inevitably want the performance since 72k doesn't really move the needle
on our usage but we've found ourselves CPU bound a few times.

If that split on the usefulness is correct, is it reasonable to suggest
that since embedded cases will use switches to exclude everything they
don't need this should fall into that category?

I'm obviously just speculating on that front having not used it there
myself.

Regards,
Donald Shepherd.

>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Eric Grange-3
In reply to this post by Richard Hipp-3
While I am using SQLIite mostly in x86-64 with plentiful RAM, I suspect the
reduced lookaside size will help CPU L1/L2 caches, which is definitely
never plentiful. I would not be surprised if it resulted in a speedup in
practical high-load multi-threaded scenarios.

Also future SQLite code changes may be able to further build upon a new
lookaside configuration by tweaking the structure sizes to more tightly
match with the slot sizes f.i., and reduce the performance hit even in
single-threaded cases.

So "on by default" for me.

Eric

Le sam. 14 déc. 2019 à 14:27, Richard Hipp <[hidden email]> a écrit :

> A new feature on a branch has the following disadvantages:
>
> (1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
> normally about 0.5% faster, so enabling this feature is sort of like
> going back by one-half of a release cycle.)
>
> (2)  The code space (the size of the library) is between 400 and 500
> bytes larger (depending on compiler and optimization settings).
>
> The this one advantage:
>
> (3)  Each database connection uses about 72 KB less heap space.
>
> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>
> The feature can be activated or deactivated at start-time, but you
> take the disadvantages (the performance hit and slightly larger
> library size) regardless, unless you disable the feature at
> compile-time.  If the feature is compile-time disabled, then the
> corresponding code is omitted and and it cannot be turned on at
> start-time.
>
> If you have opinions, you can reply to this mailing list, or directly to
> me.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Simon Slavin-3
In reply to this post by Donald Shepherd
On 14 Dec 2019, at 8:49pm, Donald Shepherd <[hidden email]> wrote:

> Gut feel is that the divide will fall such that embedded use cases want the memory and PC or equivalent use cases want the CPU.

I read here around 18 months ago that more than half of SQLite installations were on mobile phones (presumably including tablets which run iOS or Android).  So a consideration would be which option was of best advantage on a mobile phone.

Unfortunately I don't know what the performance stats or speeds of mobile phones are, or which option would give the best payoff.  The only thing I've seen recently was that iPhone stats and capabilities are roughly equivalent to those of high-end Android phones, so an argument which applies to one should apply to the other.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Andy Bennett
In reply to this post by Richard Hipp-3
Hi,

> (3)  Each database connection uses about 72 KB less heap space.

How much is this as a percentage of a freshly initialised connection and
how much as a percentage of some kind of "typical" connection (is there
even one?)?




[hidden email]
http://www.ashurst.eu.org/
http://www.gonumber.com/andyjpb
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Dominique Devienne
In reply to this post by Richard Hipp-3
On Sat, Dec 14, 2019 at 2:27 PM Richard Hipp <[hidden email]> wrote:

> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>

Backward compatibility. I.e. if I change nothing in my build, and upgrade
the amalgamation,
then I get the behavior closest to the one before this change. Which if
default-off at compile-time.

Embedded (or many connection) scenarios wanting this can always turn it on
explicitly. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Jose Isaias Cabrera-4
In reply to this post by Eric Grange-3

Give me speed anytime and twice on Sundays...

________________________________
From: sqlite-users <[hidden email]> on behalf of Eric Grange <[hidden email]>
Sent: Saturday, December 14, 2019 04:11 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Performance vs. memory trade-off question

While I am using SQLIite mostly in x86-64 with plentiful RAM, I suspect the
reduced lookaside size will help CPU L1/L2 caches, which is definitely
never plentiful. I would not be surprised if it resulted in a speedup in
practical high-load multi-threaded scenarios.

Also future SQLite code changes may be able to further build upon a new
lookaside configuration by tweaking the structure sizes to more tightly
match with the slot sizes f.i., and reduce the performance hit even in
single-threaded cases.

So "on by default" for me.

Eric

Le sam. 14 déc. 2019 à 14:27, Richard Hipp <[hidden email]> a écrit :

> A new feature on a branch has the following disadvantages:
>
> (1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
> normally about 0.5% faster, so enabling this feature is sort of like
> going back by one-half of a release cycle.)
>
> (2)  The code space (the size of the library) is between 400 and 500
> bytes larger (depending on compiler and optimization settings).
>
> The this one advantage:
>
> (3)  Each database connection uses about 72 KB less heap space.
>
> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>
> The feature can be activated or deactivated at start-time, but you
> take the disadvantages (the performance hit and slightly larger
> library size) regardless, unless you disable the feature at
> compile-time.  If the feature is compile-time disabled, then the
> corresponding code is omitted and and it cannot be turned on at
> start-time.
>
> If you have opinions, you can reply to this mailing list, or directly to
> me.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Nelson, Erik - 2-2
In reply to this post by Dominique Devienne
Dominique Devienne wrote on Monday, December 16, 2019 7:46 AM
>On Sat, Dec 14, 2019 at 2:27 PM Richard Hipp <[hidden email]> wrote:

>> QUESTION:  Should this feature be default-on or default-off?
>>
>> What's more important to you?  0.25% fewer CPU cycles or about 72KB
>> less heap space used per database connection?

>Backward compatibility. I.e. if I change nothing in my build, and upgrade
>the amalgamation,
<then I get the behavior closest to the one before this change. Which if
>default-off at compile-time.

For my own clarification... if this is default-on, and I upgrade the amalgamation, the performance will be improved *and* the connection heap space will be reduced.  Is that correct?

Erik
 

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance vs. memory trade-off question

Jonathan Moules-3
In reply to this post by Richard Hipp-3
I'd lean towards speed being preferable in the default. Reasoning:

* People who use SQLite in low-RAM (i.e. embedded) scenarios compile it
themselves.

* People who use it on PCs (like me) almost never compile it, they just
use whatever the distro/sqlite.org/language-of-choice provides, and that
in turn uses the default.

The default therefore is much more likely to affect the later user than
the former. 74kB of RAM on a machine that has some multiple of
~4,000,000kB of RAM is a rounding error.

Add a note to the docs page about "low RAM use notes" for those who need
to know (I'm guessing there is such a page; never looked for it).

My 2p,

Jonathan

On 2019-12-14 13:27, Richard Hipp wrote:

> A new feature on a branch has the following disadvantages:
>
> (1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
> normally about 0.5% faster, so enabling this feature is sort of like
> going back by one-half of a release cycle.)
>
> (2)  The code space (the size of the library) is between 400 and 500
> bytes larger (depending on compiler and optimization settings).
>
> The this one advantage:
>
> (3)  Each database connection uses about 72 KB less heap space.
>
> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>
> The feature can be activated or deactivated at start-time, but you
> take the disadvantages (the performance hit and slightly larger
> library size) regardless, unless you disable the feature at
> compile-time.  If the feature is compile-time disabled, then the
> corresponding code is omitted and and it cannot be turned on at
> start-time.
>
> If you have opinions, you can reply to this mailing list, or directly to me.
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users