Re: [sqlite] Any tips on reducing memory requirements for small MCU?

[Richard Hipp]

On Mon, Oct 20, 2014 at 5:21 PM, Dennis Field  wrote:

>
> When I initialize SQLite, I am instructing it to take a single heap for
> memsys5 of 60 * 1024.
>

Have you tried using memsys3 instead of memsys5?  Memsys3 can be more
memory-efficient.

You might also want to disable LOOKASIDE memory, and set you default pages
size to 512 bytes.

What does your schema look like?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any tips on reducing memory requirements for small MCU?

[Stephen Chrzanowski]

I have no idea what is 100% required in the database, but I THINK you can
include some compiler directives that REMOVE certain features.  I know FTS
is an optional thing, but I don't know if it is included as part of the
default build or not.  CTE (I think that is it?) might also be something
you can remove.

I've not delved into the avenue of removing functionality from the library
as my work currently always deals with PCs and gigabytes of memory
available for use.  I'm not sure I'm up to the challenge of dealing with a
machine that goes back to the "kilobytes of memory" days. ;)

On Mon, Oct 20, 2014 at 5:21 PM, Dennis Field  wrote:

> I have SQLite compiled for a Cortex M4 with 256 KB of RAM. Currently, as
> other things on the system are taking up a grand total of about 190 KB, I'm
> able to allocate about 60KB to SQLite and I'm running out of memory just
> trying to select a 16-byte blob and a string from a pair of tables.
>
>
8<


> Thanks in advance,
>
> Dennis
> ___
> 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] Any tips on reducing memory requirements for small MCU?

[Gerry Snyder]

One possibility might be to use the long-obsolete SQLite2, which was 
around when PC's had much smaller memories.


I know it is heresy to suggest it, and you would have a lot of recoding 
to do, but it seems that it might be workable.


Gerry Snyder
---
On 10/20/2014 2:21 PM, Dennis Field wrote:

I have SQLite compiled for a Cortex M4 with 256 KB of RAM. Currently, as
other things on the system are taking up a grand total of about 190 KB

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Any tips on reducing memory requirements for small MCU?

[Dennis Field]

I have SQLite compiled for a Cortex M4 with 256 KB of RAM. Currently, as
other things on the system are taking up a grand total of about 190 KB, I'm
able to allocate about 60KB to SQLite and I'm running out of memory just
trying to select a 16-byte blob and a string from a pair of tables.

SQL log code 7: failed to allocate 2048 bytes
SQL log code 7: failed to allocate 2048 bytes
SQL log code 7: statement aborts at 20: [SELECT LinkKey, Name FROM Pairings
LEFT JOIN LinkKeys USING (Address) WHERE Address = ?] out of memory

(Address and LinkKey are blobs, Address is primary key in both tables)

The entire database file is 5 kb at this point.

Am I crazy for wishing to use SQLite on a tiny MCU, or is there something I
can do to get SQLite to shrink in terms of how much memory it needs? I
could perhaps restructure this particular part of the database to a single
table if that would help, but in my application, I'm eventually hoping to
use SQLite as a place to store an index for music files, which would
involve several table joins along the same line (a track entry, for
example, may contain an integer pointing to an artist so I only have to
store one copy of an artist's name). If I can't even store/retrieve some
Bluetooth pairings, I probably can't do the more advanced things either.

Here are my SQLite definitions - everything else is undefined / default

#define SQLITE_OS_OTHER 1
#define SQLITE_THREADSAFE 1  // worker thread and low speed thread may
access DB
#define SQLITE_DISABLE_DIRSYNC
#define SQLITE_DISABLE_LFS  // no large file system
#define SQLITE_ZERO_MALLOC
#define SQLITE_ENABLE_MEMSYS5
#define SQLITE_ENABLE_MEMORY_MANAGEMENT // Release unused memory upon
request
#define SQLITE_DEFAULT_CACHE_SIZE 10
#define SQLITE_DEFAULT_LOCKING_MODE 1 // exclusive
#define SQLITE_DEFAULT_PAGE_SIZE 1024
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 50
#define SQLITE_MUTEX_APPDEF
#define YYSTACKDEPTH 30
#define SQLITE_OMIT_AUTHORIZATION
#define SQLITE_OMIT_AUTOINIT
#define SQLITE_OMIT_AUTORESET
#define SQLITE_OMIT_BUILTIN_TEST
#define SQLITE_OMIT_COMPILEOPTION_DIAGS
#define SQLITE_OMIT_COMPLETE
#define SQLITE_OMIT_DATETIME_FUNCS
#define SQLITE_OMIT_DECLTYPE
#define SQLITE_OMIT_DEPRECATED
#define SQLITE_OMIT_GET_TABLE
#define SQLITE_OMIT_LOAD_EXTENSION
#define SQLITE_OMIT_LOCALTIME
#define SQLITE_OMIT_SHARED_CACHE
#define SQLITE_OMIT_TCL_VARIABLE
#define SQLITE_OMIT_UTF16

When I initialize SQLite, I am instructing it to take a single heap for
memsys5 of 60 * 1024. I am also setting a "soft heap limit" of 4KB under
the heap size, because I figured if it proactively throws away some old
cache it would help to prevent this kind of thing.

If I increase its initial heap size to 87 * 1024, the above query works,
but I don't know that I can make the rest of the system work while giving
SQLite that much memory. (That, and I can't imagine why it would need
between 60 KB and 87 KB to do this, but that's because I'm new to this and
don't know how much stuff it actually does to get data from a table)

I have also tried a heap of 48 KB with a separate scratch of 12 KB, and a
separate lookaside buffer of 50*100. This did not change the situation
unfortunately.

I'm doing all sorts of audio buffering/encoding/decoding on the processor,
and transmitting/receiving said audio via Bluetooth, so memory requirements
for that stuff is pretty hefty and I don't anticipate being able to trim
much, if anything, off of that.

I have implemented mutexes since I have two threads in the system that may
need to access the database (one doing Bluetooth functionality, another
doing main UI operations, but never both at the same time). Yes, threads
are evil. I haven't found a way around this with some of the third-party
stuff I'm using. Blocking calls all over the place, and I can't block for
very long without a hiccup in the audio processing.

I'm using the default amalgamation source from SQLite 3.8.6.0. I will check
with 3.8.7.0 but it looks like more of a speed enhancement/bug fix release.

Apologies if I left any information out that might help point me in the
right direction. I appreciate your time.

Thanks in advance,

Dennis
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug 993556 - SQLite crash in walIndexTryHdr due to Windows EXCEPTION_IN_PAGE_ERROR exception

[Dan Kennedy]

On 10/18/2014 05:45 AM, Deon Brewis wrote:

I'm trying to follow Richard's advise to work around this issue, which is:

"Is that database ever used by more than a single process.  (Use by multiple
threads using separate connections does not count - I mean really used by
multiple processes with their own address space.)  If not (and I think the
answer is "no") then FF could set "PRAGMA locking_mode=EXCLUSIVE"
immediately after opening the database and before doing anything else.  If
that is done, then SQLite will use heap memory for the WAL-index, instead of
mmapped shared memory, and this problem will never come up."


However, I'm unable to do so. I'm using multiple threads using separate
connections, like mentioned, but when I try to use PRAGMA
locking_mode=EXCLUSIVE, the next thread that tries to open a connection will
block indefinitely on the open.

So how can I go about using PRAGMA locking_mode=EXCLUSIVE while still using
multiple threads with connections?


I think the quoted paragraph above assumes that the application is using 
shared-cache mode, which is probably not what you want to do.


If you're on unix, using the VFS "unix-excl" causes a similar effect. 
Second section here:


  http://www.sqlite.org/vfs.html

Dan.









--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Bug-993556-SQLite-crash-in-walIndexTryHdr-due-to-Windows-EXCEPTION-IN-PAGE-ERROR-exception-tp78695.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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