Re: [sqlite] Memory usage of sqlite3
> 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 TABLE tour_blob (id INTEGER PRIMARY KEY, >n_blob blob); > > DROP TABLE IF EXISTS tour; > CREATE TABLE tour (id integer, >score integer NOT NULL, >cost integer NOT NULL, >last_poi integer, >FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON > DELETE CASACADE, >FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) > ON DELETE CASACADE, >PRIMARY KEY(id)); > > You can replace tour_unsorted_path_idx with a new index too: > >> DROP INDEX IF EXISTS tour_unsorted_path_idx; >> CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path); > > DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx; > CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, > last_poi, cost); > > Take care and make cost the last one, because cost is compared with > inequality. Very interesting, this sounds a very good point! > You use '==' instead '=', take care too. I made the same error in a mail some > weeks ago. Ops... > For this query: > >> >> SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1 >> 8< - >> Query: SELECT id FROM tour ORDER BY id LIMIT ?1 >> Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~100 rows) >> - >8 > > I don't know why it doesn't use the primary index. Perhaps analyze statistics > before solves the problem. It seems Explain does not consider the LIMIT, it should not be something to worry about. Thanks a lot. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 will mess-up the layout, I hope it will be > readable. > > (See here, it seems google does not know the mean of "should") > https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04 > > > a) Does it JOIN multiple big tables (in rows and/or columns)? > No, but I got a sub query is probably the culprit. > > > e) Did you normalize the database? > Should be, yes. > > > In both cases (if you can show us the query/schema or not) what do you > > really want to ask to the database? (not to us but to sqlite3, perhaps the > > query can be reformulated) > > > Here is everything... I think the problem is shown in the: > Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows) > that is part of a sub-query. > > > -- First statement (sets the pragmas): > > PRAGMA foreign_keys = ON; > PRAGMA synchronous = OFF; > PRAGMA journal_mode = MEMORY; > PRAGMA cache_size = -10240; > PRAGMA auto_vacuum = NONE; > PRAGMA locking_mode = EXCLUSIVE; > PRAGMA secure_delete = OFF; > PRAGMA temp_store = 0; > > -- Second statement (creates/clean the schema): > BEGIN TRANSACTION; > DROP TABLE IF EXISTS tour; > CREATE TABLE tour (id integer, > score integer NOT NULL, > cost integer NOT NULL, > last_poi integer, > unsorted_path blob, > unsorted_path_tmp blob, > PRIMARY KEY(id)); > 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: DROP TABLE IF EXISTS tour_blob; CREATE TABLE tour_blob (id INTEGER PRIMARY KEY, n_blob blob); DROP TABLE IF EXISTS tour; CREATE TABLE tour (id integer, score integer NOT NULL, cost integer NOT NULL, last_poi integer, FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON DELETE CASACADE, FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) ON DELETE CASACADE, PRIMARY KEY(id)); You can replace tour_unsorted_path_idx with a new index too: > DROP INDEX IF EXISTS tour_unsorted_path_idx; > CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path); DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx; CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, last_poi, cost); Take care and make cost the last one, because cost is compared with inequality. You use '==' instead '=', take care too. I made the same error in a mail some weeks ago. For this query: > > SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1 > 8< - > Query: SELECT id FROM tour ORDER BY id LIMIT ?1 > Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~100 rows) > - >8 I don't know why it doesn't use the primary index. Perhaps analyze statistics before solves the problem. HTH --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 mean of "should") https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04 > a) Does it JOIN multiple big tables (in rows and/or columns)? No, but I got a sub query is probably the culprit. > e) Did you normalize the database? Should be, yes. > In both cases (if you can show us the query/schema or not) what do you really > want to ask to the database? (not to us but to sqlite3, perhaps the query can > be reformulated) Here is everything... I think the problem is shown in the: Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows) that is part of a sub-query. -- First statement (sets the pragmas): PRAGMA foreign_keys = ON; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY; PRAGMA cache_size = -10240; PRAGMA auto_vacuum = NONE; PRAGMA locking_mode = EXCLUSIVE; PRAGMA secure_delete = OFF; PRAGMA temp_store = 0; -- Second statement (creates/clean the schema): BEGIN TRANSACTION; DROP TABLE IF EXISTS tour; CREATE TABLE tour (id integer, score integer NOT NULL, cost integer NOT NULL, last_poi integer, unsorted_path blob, unsorted_path_tmp blob, PRIMARY KEY(id)); DROP INDEX IF EXISTS tour_unsorted_path_idx; CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path); DROP TABLE IF EXISTS categories; CREATE TABLE categories (tour_id integer NOT NULL, order_idx integer NOT NULL, value integer NOT NULL, FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE); DROP INDEX IF EXISTS cats_tour_id_idx; CREATE INDEX cats_tour_id_idx ON categories (tour_id); DROP TABLE IF EXISTS path; CREATE TABLE path (tour_id integer NOT NULL, order_idx integer NOT NULL, node_id integer NOT NULL, FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE); DROP INDEX IF EXISTS path_tour_id_idx; CREATE INDEX path_tour_id_idx ON path (tour_id); DROP TRIGGER IF EXISTS set_last_poi_trg; CREATE TRIGGER set_last_poi_trg AFTER INSERT ON path BEGIN UPDATE tour SET last_poi = ( SELECT node_id FROM path WHERE tour_id == NEW.tour_id ORDER BY order_idx DESC LIMIT 1 ) WHERE id == NEW.tour_id; END; COMMIT; VACUUM; -- Finally the statement executed in a normal program -- execution with the explain if appliable. -- I prepare all this statements and put in hash table -- and finalize them at the end of the program. -- This output comes from the first time, when I store them. SQL STATEMENT: INSERT INTO path (tour_id, order_idx, node_id) VALUES (?1, ?2, ?3) SQL STATEMENT: INSERT INTO tour (score, cost) VALUES (?1, ?2) SQL STATEMENT: UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2 8< - Query: UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2 Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) - >8 SQL STATEMENT: UPDATE tour SET unsorted_path=unsorted_path_tmp, unsorted_path_tmp = NULL WHERE id = ?1; 8< - Query: UPDATE tour SET unsorted_path=unsorted_path_tmp, unsorted_path_tmp = NULL WHERE id = ?1; Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) - >8 SQL STATEMENT: INSERT INTO categories (tour_id, order_idx, value) VALUES (?1, ?2, ?3) SQL STATEMENT: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) 8< - Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~25 rows) - >8 8< - Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) Explain: 0 0 0 EXECUTE LIST SUBQUERY 0 - >8 8< - Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows) - >8 8< - Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1
Re: [sqlite] Memory usage of sqlite3
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 you show us the query and/or schemas? If not: a) Does it JOIN multiple big tables (in rows and/or columns)? b) If it's joining multiple tables, is the select something like select * .? c) Do you really need '*' or only use some columns? d) Do you use LEFT JOINS? Has the joining key an index? e) Did you normalize the database? In both cases (if you can show us the query/schema or not) what do you really want to ask to the database? (not to us but to sqlite3, perhaps the query can be reformulated) > So maybe it is one complex query? > I would like to avoid excessive swapping on the > production server, maybe I should not worry at all? -- Eduardo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 consider this amount of memory usage to be excessive (which I am not saying is or is not the case) drop your indexes and make up better ones. Remember that the purpose of an index is to be ideal for the clauses of a SELECT or UPDATE statement. There is no point in indexing a column just because that column gets used a lot. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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, maybe I should not worry at all? On Tue, Jul 16, 2013 at 1:11 PM, Paolo Bolzoni wrote: > 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 sqlite3_stmt* handles? > Thanks to RAII the code should not leak (also valgrind > confirm this). I allocate statements, but I deallocate only > at the end. So it cannot explain a peak in memory usage. > >> SQLite has various APIs for querying memory usage: >> >> http://www.sqlite.org/c3ref/memory_highwater.html >> http://www.sqlite.org/c3ref/c_status_malloc_count.html >> >> Or, using the shell tool, the ".stats" command can be used >> to access the same values. > I guess I can see something. > > At the moment I am running the test again using a sqlite3 > version compiled with -g3 and -O0 so I hope I can get more > insight... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 sqlite3_stmt* handles? Thanks to RAII the code should not leak (also valgrind confirm this). I allocate statements, but I deallocate only at the end. So it cannot explain a peak in memory usage. > SQLite has various APIs for querying memory usage: > > http://www.sqlite.org/c3ref/memory_highwater.html > http://www.sqlite.org/c3ref/c_status_malloc_count.html > > Or, using the shell tool, the ".stats" command can be used > to access the same values. I guess I can see something. At the moment I am running the test again using a sqlite3 version compiled with -g3 and -O0 so I hope I can get more insight... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 usage: http://www.sqlite.org/c3ref/memory_highwater.html http://www.sqlite.org/c3ref/c_status_malloc_count.html Or, using the shell tool, the ".stats" command can be used to access the same values. 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 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 consider to be all that high considering what it's capable of doing for me. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 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 show? Set it to 0 and recheck. Did you compile >> with SQLITE_TEMP_STORE set to 3? > This ones, so no... > -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 > -DSQLITE_ENABLE_UNLOCK_NOTIFY -DSQLITE_SECURE_DELETE > "Recheck" needs some time. The test lasted almost 7 hours... > I start it now... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 show? Set it to 0 and recheck. Did you compile > with SQLITE_TEMP_STORE set to 3? This ones, so no... -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY -DSQLITE_SECURE_DELETE "Recheck" needs some time. The test lasted almost 7 hours... I start it now... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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? --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 ? No. > Do you use sqlite3_exec() ? Twice, to activate the pragma and to create the db. > Do you have SELECTs for which there is no good index, forcing sqlite3 to make > up its own ? I checked with EXPLAIN and no. Seems not. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 haven't turned the page cache down, until you top ~3MB there is nothing unusual at all. -j > 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 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 consider to be all that high considering what it's capable of doing > > for me. > > > > -- > > - stephan beal > > http://wanderinghorse.net/home/stephan/ > > http://gplus.to/sgbeal > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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, forcing sqlite3 to make up its own ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
>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 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 consider to be all that high considering what it's capable of doing > for me. > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
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 consider to be all that high considering what it's capable of doing for me. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory usage of sqlite3
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 two cases there is a peak of memory usage from sqlite. So, sorry if the question sounds very vague. But what can cause high memory usage in sqlite? A large transaction maybe? Thanks for any insight. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
