Re: [sqlite] Performance vs. memory trade-off question
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
Dominique Devienne wrote on Monday, December 16, 2019 7:46 AM >On Sat, Dec 14, 2019 at 2:27 PM Richard Hipp 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, 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
Give me speed anytime and twice on Sundays... From: sqlite-users on behalf of Eric Grange Sent: Saturday, December 14, 2019 04:11 PM To: SQLite mailing list 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 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 > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
On Sat, Dec 14, 2019 at 2:27 PM Richard Hipp 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
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?)? andy...@ashurst.eu.org http://www.ashurst.eu.org/ http://www.gonumber.com/andyjpb 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
On 14 Dec 2019, at 8:49pm, Donald Shepherd 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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 > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
On Sun, 15 Dec 2019 at 1:40 am, Dominique Pellé wrote: > Richard Hipp wrote: > > > On 12/14/19, Dominique Pellé 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
Richard Hipp wrote: > On 12/14/19, Dominique Pellé 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
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é wrote: > 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. > > 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
On 12/14/19, Richard Hipp wrote: > On 12/14/19, Dominique Pellé 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 > d...@sqlite.org > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
On 12/14/19, Dominique Pellé 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
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. 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
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, GaryB-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance vs. memory trade-off question
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance vs. memory trade-off question
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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
