Re: [sqlite] Memory usage of sqlite3

2013-07-17 Thread Paolo Bolzoni
> Some changes, if blob is bigger than a few bytes, you should normalize them. > If 2 blobs are equal, their id must be equal and you don't waste time > comparing nor memory joining blob content. So you get: They are quite small (max ~70 bytes...) > DROP TABLE IF EXISTS tour_blob; > CREATE

Re: [sqlite] Memory usage of sqlite3

2013-07-17 Thread Eduardo Morras
On Wed, 17 Jul 2013 12:04:52 +0200 Paolo Bolzoni wrote: > On Tue, Jul 16, 2013 at 8:13 PM, Eduardo wrote: > > > Can you show us the query and/or schemas? If not: > Sure, I appended everything in the bottom of this email. > Unfortunately gmail

Re: [sqlite] Memory usage of sqlite3

2013-07-17 Thread Paolo Bolzoni
On Tue, Jul 16, 2013 at 8:13 PM, Eduardo wrote: > Can you show us the query and/or schemas? If not: Sure, I appended everything in the bottom of this email. Unfortunately gmail will mess-up the layout, I hope it will be readable. (See here, it seems google does not know the

Re: [sqlite] Memory usage of sqlite3

2013-07-16 Thread Eduardo
On Tue, 16 Jul 2013 18:17:41 +0200 Paolo Bolzoni wrote: > I tried the experiment again with -g3 -O0, I got less > information than expected (there are still many unknown > symbols in libsqlite3.so), but the function requiring all > this memory is sqlite3_step. Can

Re: [sqlite] Memory usage of sqlite3

2013-07-16 Thread Simon Slavin
On 16 Jul 2013, at 5:17pm, Paolo Bolzoni wrote: > the function requiring all > this memory is sqlite3_step. > > So maybe it is one complex query? Possibly a query for which no good index exists, so SQLite decides to make up its own temporary index. If you

Re: [sqlite] Memory usage of sqlite3

2013-07-16 Thread Paolo Bolzoni
I tried the experiment again with -g3 -O0, I got less information than expected (there are still many unknown symbols in libsqlite3.so), but the function requiring all this memory is sqlite3_step. So maybe it is one complex query? I would like to avoid excessive swapping on the production server,

Re: [sqlite] Memory usage of sqlite3

2013-07-16 Thread Paolo Bolzoni
On Tue, Jul 16, 2013 at 1:00 PM, Dan Kennedy wrote: > On 07/16/2013 01:49 AM, Paolo Bolzoni wrote: > A very large blob or string result? I would exclude this, I do use blobs... but they are at most few dozen of bytes... > Code allocates (or leaks) > tremendous numbers of

Re: [sqlite] Memory usage of sqlite3

2013-07-16 Thread Dan Kennedy
On 07/16/2013 01:49 AM, Paolo Bolzoni wrote: From 35-40MB to 940MB; I would put massif result but I think the list deletes attachments. A very large blob or string result? Code allocates (or leaks) tremendous numbers of sqlite3_stmt* handles? SQLite has various APIs for querying memory

Re: [sqlite] Memory usage of sqlite3

2013-07-16 Thread Paolo Bolzoni
The test ended sometime during the night and setting temp_store to 0 the result is exactly the same. I suspect it was the default anyway. On Mon, Jul 15, 2013 at 9:20 PM, Paolo Bolzoni wrote: > On Mon, Jul 15, 2013 at 9:08 PM, Eduardo Morras

Re: [sqlite] Memory usage of sqlite3

2013-07-15 Thread Paolo Bolzoni
On Mon, Jul 15, 2013 at 9:08 PM, Eduardo Morras wrote: > On Mon, 15 Jul 2013 20:49:52 +0200 > Paolo Bolzoni wrote: > >> From 35-40MB to 940MB; I would put massif result but I think the >> list deletes attachments. > > What does PRAGMA temp_store

Re: [sqlite] Memory usage of sqlite3

2013-07-15 Thread Eduardo Morras
On Mon, 15 Jul 2013 20:49:52 +0200 Paolo Bolzoni wrote: > From 35-40MB to 940MB; I would put massif result but I think the > list deletes attachments. What does PRAGMA temp_store show? Set it to 0 and recheck. Did you compile with SQLITE_TEMP_STORE set to 3?

Re: [sqlite] Memory usage of sqlite3

2013-07-15 Thread Paolo Bolzoni
On Mon, Jul 15, 2013 at 8:59 PM, Simon Slavin wrote: > > On 15 Jul 2013, at 7:49pm, Paolo Bolzoni > wrote: > >> From 35-40MB to 940MB; I would put massif result but I think the >> list deletes attachments. > > Do you have in-memory tables ?

Re: [sqlite] Memory usage of sqlite3

2013-07-15 Thread Jay A. Kreibich
On Mon, Jul 15, 2013 at 08:49:52PM +0200, Paolo Bolzoni scratched on the wall: > >From 35-40MB to 940MB; I would put massif result but I think the > list deletes attachments. By default, the page-cache is 2000. Pages are typically 1KB, but have some minor overhead in the cache. Assuming you

Re: [sqlite] Memory usage of sqlite3

2013-07-15 Thread Simon Slavin
On 15 Jul 2013, at 7:49pm, Paolo Bolzoni wrote: > From 35-40MB to 940MB; I would put massif result but I think the > list deletes attachments. Do you have in-memory tables ? Do you use sqlite3_exec() ? Do you have SELECTs for which there is no good index,

Re: [sqlite] Memory usage of sqlite3

2013-07-15 Thread Paolo Bolzoni
>From 35-40MB to 940MB; I would put massif result but I think the list deletes attachments. On Mon, Jul 15, 2013 at 8:41 PM, Stephan Beal wrote: > On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni < > paolo.bolzoni.br...@gmail.com> wrote: > >> So, sorry if the question sounds

Re: [sqlite] Memory usage of sqlite3

2013-07-15 Thread Stephan Beal
On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni < paolo.bolzoni.br...@gmail.com> wrote: > So, sorry if the question sounds very vague. But what can > cause high memory usage in sqlite? A large transaction > maybe? > What is "high"? In my apps sqlite tends to use 200-400kb or so, which i don't

[sqlite] Memory usage of sqlite3

2013-07-15 Thread Paolo Bolzoni
I wrote an C++ application that uses sqlite3 to save part of the data when it become larger than a known threshold. The idea is to use at most a known quantity of memory; to check if it is working I executed a relevant test using valgrind's massif. It worked fairly well most of the time, but in