Re: [sqlite] LIKE operator and ATTACH databases memory usage
> Change cache sizes using separate cache_size > pragmas for each attached database. Thank you! (It would be nice if there is a hint for this behaviour in http://www.sqlite.org/lang_attach.html.) Is this correct? (At least it does not return an error) PRAGMA job01.cache_size=200 PRAGMA job02.cache_size=200 PRAGMA job03.cache_size=200 ...or should I better use PRAGMA default_cache_size when the database I created? So if the cache_size increases per attached database and my emebedded system has a limited amount of memory, the best solution to control the total amount of memory SQLite uses is to count / limit attached databases? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
On Aug 18, 2008, at 8:34 AM, [EMAIL PROTECTED] wrote: > The example with just one main database does not consume more than > ~300KB > which seems to be pretty close to the specified 'cache_size'. The > second example with three attached database consumes > around 500KB per query and it looks like 'cache_size' setting is not > relevant. The cache_size is specified separately for each ATTACHed database. The default is 2000. So if you attach three databases, your total cache size will be 8000 (2000 for the original plus 2000 for each ATTACHed database). Change cache sizes using separate cache_size pragmas for each attached database. Please also note that in the next release (SQLite version 3.6.2) the cache_size pragma will probably become a no-op. We are working on alternative mechanisms to control the amount of cache memory used. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
> Depending on what you are storing in fs_textid and what your LIKE
> pattern is, you might get much better performance (and lower memory
> usage) if you use GLOB instead of LIKE and if you explicitly code the
> pattern rather than using the wildcard "?", and if you create a new
> index:
> CREATE INDEX newidx1 ON fs_main(fs_itemtype, fs_textid);
Thank you for the optimization hints – I changed the application that it uses
all three suggestions and many queries
are now executed faster. Although, not if the search string begins with a
wildcard char-acter as described in http:
//www.sqlite.org/optoverview.html#like_opt under point 2. I assume this causes
again most of the database to be loaded
into cache as this was the case with the LIKE opera-tor.
> Are you sure that the memory is not freed? Calling free() does not
> normally return memory to the operating system so just because the
> process memory usage went up does *not* mean that the memory is still
> in use. It might just mean that the memory is being held by the
> malloc()/free() for possible reuse later. What does the
> sqlite3_memory_used() interface tell you? What about
> sqlite3_memory_highwater()?
I wrote two small example programs to illustrate the memory usage difference
between working on the main database and
working with multiple ATTACHed database. The example with just one main
database does not consume more than ~300KB
which seems to be pretty close to the specified 'cache_size'. The second
example with three attached database consumes
around 500KB per query and it looks like 'cache_size' setting is not relevant.
All memory seems to be freed correctly.
--- Example A ---
--- One database only, cache size limited to 250 pages ---
long rc = SQLITE_OK;
sqlite3_int64 mem01;
sqlite3_int64 mem02;
// Open database connection
sqlite3* m_pDB;
sqlite3_open("job01.db", &m_pDB); // application memory usage: 1.5 MB
// PRAGMA
sqlite3_stmt* pStmtPragma01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "PRAGMA cache_size=250", -1, &pStmtPragma01,
NULL);
rc = sqlite3_step(pStmtPragma01);
rc = sqlite3_finalize(pStmtPragma01);
// Run queries
sqlite3_stmt* pStmtLike01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "SELECT COUNT(fs_recid) FROM main.fs_main
WHERE fs_itemtype=10 AND fs_textid GLOB
'*1';", -1, &pStmtLike01, NULL);
mem01 = sqlite3_memory_used(); // 17937 ...application memory
usage: 1.8MB
rc = sqlite3_step(pStmtLike01);
mem01 = sqlite3_memory_used(); // 305725 ...application memory
usage: 2.0MB
rc = sqlite3_finalize(pStmtLike01);
mem01 = sqlite3_memory_used(); // 302855 ...application memory
usage: 2.0MB
mem02 = sqlite3_memory_highwater(1);// 305757
// Close database connection
sqlite3_close(m_pDB); // application memory usage: 1.5 MB <- all memory
is freed correctly
--- Example B ---
--- :memory: database and three attached databases, cache size limited to 250
pages ---
long rc = SQLITE_OK;
sqlite3_int64 mem01;
sqlite3_int64 mem02;
// Open database connection
sqlite3* m_pDB;
sqlite3_open(":memory:", &m_pDB); // application memory usage: 1.5 MB
// PRAGMA
sqlite3_stmt* pStmtPragma01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "PRAGMA cache_size=250", -1, &pStmtPragma01,
NULL);
rc = sqlite3_step(pStmtPragma01);
rc = sqlite3_finalize(pStmtPragma01);
// ATTACH job databases
sqlite3_stmt* pStmtAttach01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "ATTACH 'job01.db' AS _job01;", -1,
&pStmtAttach01, NULL);
rc = sqlite3_step(pStmtAttach01);
rc = sqlite3_finalize(pStmtAttach01);
sqlite3_stmt* pStmtAttach02(NULL);
rc = sqlite3_prepare_v2(m_pDB, "ATTACH 'job02.db' AS _job02;", -1,
&pStmtAttach02, NULL);
rc = sqlite3_step(pStmtAttach02);
rc = sqlite3_finalize(pStmtAttach02);
sqlite3_stmt* pStmtAttach03(NULL);
rc = sqlite3_prepare_v2(m_pDB, "ATTACH 'job03.db' AS _job03;", -1,
&pStmtAttach03, NULL);
rc = sqlite3_step(pStmtAttach03);
rc = sqlite3_finalize(pStmtAttach03);
// Run queries
sqlite3_stmt* pStmtLike01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "SELECT COUNT(fs_recid) FROM _job01.fs_main
WHERE fs_itemtype=10 AND fs_textid
GLOB '*1';", -1, &pStmtLike01, NULL);
mem01 = sqlite3_memory_used(); // 42568 ...application memory
usage: 1.8MB
rc = sqlite3_step(pStmtLike01);
mem01 = sqlite3_memory_used(); // 5
Re: [sqlite] LIKE operator and ATTACH databases memory usage
Our e-mails crossed on the way to the list... sorry for the confusion. Thank you for your advices - i'll follow up them to make the mentioned wildcard search working. Basically, I'm looking for a way to have a fast wildcard search on a dataset that is distributed over multiple databases whose are attached to a main database. I hope I can do it with GLOB. At the moment, the question is still unanswered, why LIKE consumes multiple times 2.5MB when applied to attached dabases. Daniel Ursprüngliche Nachricht Von: [EMAIL PROTECTED] Datum: 15.08.2008 16:30 An: "General Discussion of SQLite Database" Betreff: Re: [sqlite] LIKE operator and ATTACH databases memory usage On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: > Hello > > Why does SQLite consume 2.5MB memory every time when running a > statement on a attached database with LIKE operator? > > Example 1: > SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes <50kB RAM > > Example 2: > SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM (Note to mailing list readers: Daniel sent me a sample database by private email) The database is about 3MB in size and the example 2 query is probably doing something close to a full table scan. This causes most of the database to be loaded into cache. That will use about 2.5MB of RAM. The cache will flush itself automatically when you close the database connection or when the cache becomes stale. Depending on what you are storing in fs_textid and what your LIKE pattern is, you might get much better performance (and lower memory usage) if you use GLOB instead of LIKE and if you explicitly code the pattern rather than using the wildcard "?", and if you create a new index: CREATE INDEX newidx1 ON fs_main(fs_itemtype, fs_textid); See http://www.sqlite.org/optoverview.html#like_opt D. Richard Hipp [EMAIL PROTECTED] ___ 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] LIKE operator and ATTACH databases memory usage
I'm going to check sqlite3_memory_used()/sqlite3_memory_highwater() next week as soon as I can. At the moment the following is clear: The application needs to run the same statement with LIKE operator for multiple attached databases. On the embedded side, the device crashes after a few statements because there is no more RAM. On the desktop pc side, all statements are successful, but process viewer shows an increased amount of memory used by the application. I would be happy if the problem is in our application, but I wasn't able to find anything yet. Why does the statement with LIKE (Example 2) consume the memory and the statement without LIKE (Example3) does not consume the memory on a ATTACH'ed database? Database schema: CREATE TABLE fs_main ( 'fs_recid' INTEGER PRIMARY KEY NOT NULL, 'fs_itemtype' INTEGER, 'fs_textid' TEXT, 'fs_flag1' INTEGER, 'fs_object' BLOB ); The size of a single record is typically 100 bytes. Please let me know if you didnt get the example database I sent directly to [EMAIL PROTECTED] Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
> This causes most of the database to be loaded into cache. Is there one cache per database connection or one cache per ATTACH'ed database? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: > Hello > > Why does SQLite consume 2.5MB memory every time when running a > statement on a attached database with LIKE operator? > > Example 1: > SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes <50kB RAM > > Example 2: > SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM (Note to mailing list readers: Daniel sent me a sample database by private email) The database is about 3MB in size and the example 2 query is probably doing something close to a full table scan. This causes most of the database to be loaded into cache. That will use about 2.5MB of RAM. The cache will flush itself automatically when you close the database connection or when the cache becomes stale. Depending on what you are storing in fs_textid and what your LIKE pattern is, you might get much better performance (and lower memory usage) if you use GLOB instead of LIKE and if you explicitly code the pattern rather than using the wildcard "?", and if you create a new index: CREATE INDEX newidx1 ON fs_main(fs_itemtype, fs_textid); See http://www.sqlite.org/optoverview.html#like_opt D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
On Aug 15, 2008, at 9:56 AM, [EMAIL PROTECTED] wrote: > > I don’t dare to use the term “leak” here. It is hard so say at the > moment where the memory is going to. At least, the > memory is not freed when sqlite3_finalize() is called on the > statement. Might it be possible, that this memory is > allocated once per attached database and used for caching reasons? > Memory leaks in SQLite are uncommon. Especially 2.5MB memory leaks. See http://www.sqlite.org/malloc.html#testing Are you sure that the memory is not freed? Calling free() does not normally return memory to the operating system so just because the process memory usage went up does *not* mean that the memory is still in use. It might just mean that the memory is being held by the malloc()/free() for possible reuse later. What does the sqlite3_memory_used() interface tell you? What about sqlite3_memory_highwater()? Why does the first query require 2.5MB of memory? I don't know. That would depend on your schema and the content of the database. Perhaps it is going to cache. Perhaps something else. If one of the rows in your table contains a 2.5MB blob, that would explain where the memory is going, would it not? We will need quite a bit more information from you if we are to help you debug your problem. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
I don’t dare to use the term “leak” here. It is hard so say at the moment where the memory is going to. At least, the memory is not freed when sqlite3_finalize() is called on the statement. Might it be possible, that this memory is allocated once per attached database and used for caching reasons? Attached… oh sorry, I meant: “a few databases are attached with the ATTACH command to the main in-memory database”. Please let me know if you really need the database file. I can see how the memory usage of my application increases by 2.5MB as soon as I call sqlite3_step(). Database and parameters: Records: Typically 20’000 records Values for ?: fs_itemtype is an integer between 0 and 100 Values for ?: fs_textid is a string with length between 0 and 16 characters Indexes: On fs_textid Indexes: There is another index on fs_itemtype but is not used here Environment: SQLite version: 3.5.9 (we will release our product very soon) Operation system: Windows XP and WinCE (problem exists on both platforms) Compiled: Ourselves with Visual Studio 2005 (no changes to source code) Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: > Hello > > Why does SQLite consume 2.5MB memory every time when running a > statement on a attached database with LIKE operator? This mailing list strips off attachments. Please send the database by some other means. How do you know that SQLite is "consuming" 2.5MB? What version of SQLite are you running? What operating system? Did you compile it yourself or are using using a pre-build binary? What values are us using for the "?" parameters in your queries? > > > Example 1: > SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes <50kB RAM > > Example 2: > SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM > SELECT fs_rec FROM _job02.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM > SELECT fs_rec FROM _job03.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM > SELECT fs_rec FROM _job04.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM > > Example 3: > SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND > fs_textid=?; // consumes <1KB RAM > > Memory is consumed immediately after the first sqlite3_step() is > called. > > The PRAGMA cache_size is set to 2000 on the in-memory main database. > > Regards > Daniel > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LIKE operator and ATTACH databases memory usage
Hello Why does SQLite consume 2.5MB memory every time when running a statement on a attached database with LIKE operator? Example 1: SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes <50kB RAM Example 2: SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM SELECT fs_rec FROM _job02.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM SELECT fs_rec FROM _job03.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM SELECT fs_rec FROM _job04.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM Example 3: SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid=?; // consumes <1KB RAM Memory is consumed immediately after the first sqlite3_step() is called. The PRAGMA cache_size is set to 2000 on the in-memory main database. Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
