Re: [sqlite] Any tips on reducing memory requirements for small MCU?
On Thu, Oct 23, 2014 at 2:25 PM, Dennis Field wrote: > > > Are there any omit/other preprocessor defines that are particularly helpful > for reducing memory usage? > > SQLITE_SMALL_STACK - but that will require regenerating the amalgamation. -- 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?
Thanks, all! Sorry for the delayed response. I ran into an SD card reading issue that I'm still trying to solve (multiple block reading), and I've worked around it for now to keep trying to make progress on the database. I have switched to memsys3 and the 60 KB heap seems sufficient for now to run the stated query as well as open a temporary memory table for inquiries to look for new devices. I had to put my own, global, mutex around every access to SQLite since I don't seem to be having much luck with SQLite's built in mutexing (it's claiming database corruption / malformed schema when I access it from the Bluetooth thread). Once I put a mutex around every call to SQLite I got back in business. I suppose I could alternatively restructure the application so SQLite is always only called from a single thread, but that sounds about as tricky. Here is my current schema: CREATE TABLE `Pairings` ( `Address` blob, `UserID` integer, `ClassOfDevice` blob, `ConnectPriority` integer, `Name` text, PRIMARY KEY(Address) ) CREATE TABLE `LinkKeys` ( `Address` blob, `LinkKey` blob, PRIMARY KEY(Address) ) As per Bluetooth specification, Address is always 6 bytes, LinkKey is always 16, ClassOfDevice always 3. I know, not a whole lot. I could maybe get away with putting LinkKey as another column in Pairings, but this relational setup is a small example of what might end up happening for other types of data I'd like to store. (If Bluetooth pairings were the only thing, this might be overkill and I'd just create a flat structure and store it directly in EEPROM or something like that) As I have attempted to start familiarizing myself with how memsys3 works differently than memsys5, one thing does worry me - memsys3 comes with the caveat that there is no mathematical guarantee against fragmentation. So, I guess the next question would be...how do I know when it's fragmented, as opposed to when it's simply run plumb out as memsys5 did under this condition? Is there a clean way to wipe the memory and start over again when this happens? (e.g. close database connection, reopen, voila heap magically starts over as if first started, no more fragmentation) I have set my default page size to 512 and will rebuild my database and see where I end up next. I've defined about as many SQLITE_OMIT_* things as I can think of that I won't need (except the ones that would require regenerating the amalgamation source). Code space is getting a little tight (770kb of 1mb used so far), but the main thing is memory allocation at the moment (between SQLite and Bluetooth, both of which need dynamic alloc/free, I've got half the RAM dedicated to them). Code space can be reduced in a couple of other ways if I need, and as a last resort I'll regenerate amalgamation source with a few more omit flags. Are there any omit/other preprocessor defines that are particularly helpful for reducing memory usage? Many thanks! I appreciate all the responses! On Mon, Oct 20, 2014 at 7:20 PM, Richard Hipp wrote: > 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 > ___ 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?
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?
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?
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?
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
