RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?
Hi John, My main memory is very limited but I have large disk to keep the database. I need to serialize the data when the device is in switch off mode or in a different application mode where database is not required. I need to take care of power failure, data corruption etc., I consider your advice but how extensible and flexible it is for future modifications? Regards Kalyani -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 9:25 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? In your case we would not use Sqlite and instead use a much simpler storage method. Since your storage appears to be RAM resident that approach is indicated a fortiori. We have had success with using storage based on AVL trees. It is very fast and remains so despite repeated insertions and deletions. The code footprint is tiny (10K) and there is no heap usage so memory leakage can never be a problem. You do not have SQL in that environment but it would appear that you are not using it anyway. Since your data is memory resident ACID compliance and logging are not an issue. Even with quite detailed data manipulation you would be hard pressed to have a footprint greater than 30K. You could cut that down by defining code like VDBE with a high information density and using a simple engine to interpret that metacode. We have successfully used that approach at times. Kalyani Tummala wrote: Hi John, I could not understand your query properly. Let me tell you my application scenario. I am planning to use sqlite as a database for storing and retrieving media data of about 5-10k records in a device whose main memory is extremely small. A sequence of insert statements increasing the heap usage to nearly 70K(almost saturating point) which is crashing my application. I want to restrict this to 30K. I tried closing database and reopen after some inserts but of no use. I have observed that, when I open the database with about 1K to 2K records in it, inserts and updates take more heap and also gradually increase than a a database with less than 1k records in it. My objective is to reduce the peak heap usage during inserts, updates and also deletes with little or no performance degradation. Please suggest me if I can do anything to do so. Thank you in advance Kalyani -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 6:51 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? Since you are only using part of Sqlite have you considered using a much smaller footprint storage system which only implements the functions you are using? Kalyani Tummala wrote: Hi joe, Thanks for your response. In order to reduce the footprint size, I have bypassed parser completely and using byte codes directly as my schema and queries are almost compile time fixed. Hence I am not using sqlite3_prepare(). The following is the schema and inserts I am using. CREATE TABLE OBJECT( PUOI INTEGER PRIMARY KEY, Storage_IdINTEGER, Object_Format INTEGER, Protection_Status INTEGER, Object_Size INTEGER, Parent_Object INTEGER, Non_ConsumableINTEGER, Object_file_name TEXT, Name TEXT, File_Path TEXT ); CREATE TABLE AUDIO( PUOI INTEGER PRIMARY KEY, Use_Count INTEGER, Audio_Bit_RateINTEGER, Sample_Rate INTEGER, Audio_Codec_Type INTEGER, Number_of_ChannelsINTEGER, Track INTEGER, ArtistTEXT, Title TEXT, Genre TEXT, Album_NameTEXT, File_Path TEXT ); INSERT INTO OBJECT VALUES ( 7, 65537, 12297, 0, 475805, 6, 0, 'ANJANEYASTOTRAM.mp3', NULL, 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3' ); INSERT INTO AUDIO VALUES ( 7, 6, 144100, 0, 0, 0, 6, NULL, NULL, NULL, NULL, 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3' ); INSERT INTO OBJECT VALUES ( 8, 65537, 12297, 0, 387406, 6, 0, 'BHADRAM.mp3', NULL, 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3' ); INSERT INTO AUDIO VALUES ( 8, 6, 144100, 0, 0, 0, 6, NULL, NULL, NULL, NULL, 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3' ); Warm regards Kalyani -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 9:42 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? I am working at porting sqlite ( ver 3.3.8 ) on an embedded device with extremely low main memory. I tried running select queries on the tables( with about 2k records each having
RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?
Hi Joe, Yes, SDRAM is normal volatile RAM. Instead of BEGIN ... COMMIT, I have opened database before insert statement and closed db after that, with 2500 records already stored in the database, temp_store set to 0(file always), sqlite is taking 48K heap to open the database, 55K for first 5 inserts and increases for every other insert and goes to 68K for next 5 inserts. With temp_store set to 3(memory always), it is reaching 78K. Increasing PAGE size increases STACK consumption, which is again a problem with little main memory. So, I think, as the database grows, the initial heap required by an insert or update statement grows. Can we have control over max heap usage by sqlite? Did anyone experience before this growth in heap usage? Are my numbers correct? This is going to be a bottleneck for my application. Thank you Kalyani -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 9:40 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? The default is auto-commit, so unless you've opened an explicit transaction with BEGIN and do a number of inserts, the COMMIT suggestion is not useful in reducing memory footprint. (apologies in advance if this is obvious...) SDRAM is the normal volatile RAM, right? You know that temp_store is competing with sqlite for your RAM. If you point your temp_store to disk to use non-volatile storage (disk or flash) then you'd have more RAM available for sqlite. Or do you lack such non-volatile storage? You can run the tests on Windows by installing either Cygwin or MinGW/MSYS, installing Tcl 8.4, running configure, and then running make test. You can change the generated Makefile to define various sqlite compile-time flags for memory debugging. Just scan the ifdef's in the source code for ideas what to enable. It's much easier to do this under Linux than under Windows, in my opinion. --- Kalyani Tummala [EMAIL PROTECTED] wrote: My temp_store is SDRAM. Thanks for your suggestion of using COMMIT. I have not used it. Any other pointers? Best Regards Kalyani -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 10:27 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? --- Joe Wilson [EMAIL PROTECTED] wrote: I am working at porting sqlite ( ver 3.3.8 ) on an embedded device with extremely low main memory. I tried running select queries on the tables( with about 2k records each having about 5 strings) and they do well within 20kB of runtime heap usage. But, when I try new insertions, the heap usage grows tremendously (about 70 kB at peak). Perhaps preparing the statements (sqlite3_prepare) might decrease RAM use somewhat. Can you post an example of your schema and these insert statements? Is your temp_store in memory or flash ram or other? Another thought... are you performing a COMMIT after each INSERT? (or at least every X inserts, where X100)? It might help to reduce memory (at the cost of reduced speed). Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php - To unsubscribe, send email to [EMAIL PROTECTED] - ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify [EMAIL PROTECTED] ** - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: CAST
SQLite's typelessness is an asset if you work only with SQLite but in any application that uses multiple database engines of which SQLite is only one supported engine, the non-standard typelessness is something that has to be worked around. I for one would be in favor of an option to enforce strict typing (compile time option). Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 6:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: CAST You are looking for a fit to one particular restrictive, proprietary environment. Our approach has been to work with the spirit of Sqlite and to its strengths and to that end we designed out environment accordingly. Sqlite's typing has become a major asset, not a difficulty. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: CAST
Samuel R. Neff schrieb: SQLite's typelessness is an asset if you work only with SQLite but in any application that uses multiple database engines of which SQLite is only one supported engine, the non-standard typelessness is something that has to be worked around. I for one would be in favor of an option to enforce strict typing (compile time option). Quite true for many cases. Strict typing would help in many cases when using or maintaining wrappers, especially concerning the results of expressions like CAST where there is no meaningful defined type. But your wrong about the 'if working with multiple database engines' generalization. Its more of a language feature, e.g. in Tcl you nearly always have typeless interfaces and it works fine, even when accessing multiple databases (oracle, postgres, mysql + sqlite for example). (the typed interfaces break more often, e.g. Windows COM scripting with VARIANTS). So as a compile time option it would surely be a great addition for all those that have to maintain wrappers. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?
It may not be possible to get peak heap usage down to 30K, but here's
some random ideas:
I imagine you've already tried defining SQLITE_OMIT_* for the features
that you don't need.
Verify that your embedded OS has a space-efficient malloc implementation.
Try to find a realtime graphical heap allocation analyzer for C programs.
I'm just guessing it exists for C, as there are several for Java.
2500 records already stored in the database, temp_store set to 0(file
always), sqlite is taking 48K heap to open the database, 55K for first 5
inserts and increases for every other insert and goes to 68K for next 5
inserts.
Based on these findings, perhaps btree recursion is using that extra
memory on inserts on larger tables?
btree.c:
/*
** This routine redistributes Cells on pPage and up to NN*2 siblings
** of pPage so that all pages have about the same amount of free space.
** Usually NN siblings on either side of pPage is used in the balancing,
** though more siblings might come from one side if pPage is the first
** or last child of its parent. If pPage has fewer than 2*NN siblings
** (something which can only happen if pPage is the root page or a
** child of root) then all available siblings participate in the balancing.
**
** The number of siblings of pPage might be increased or decreased by one or
** two in an effort to keep pages nearly full but not over full. The root page
** is special and is allowed to be nearly empty. If pPage is
** the root page, then the depth of the tree might be increased
** or decreased by one, as necessary, to keep the root page from being
** overfull or completely empty.
**
** Note that when this routine is called, some of the Cells on pPage
** might not actually be stored in pPage-aData[]. This can happen
** if the page is overfull. Part of the job of this routine is to
** make sure all Cells for pPage once again fit in pPage-aData[].
**
** In the course of balancing the siblings of pPage, the parent of pPage
** might become overfull or underfull. If that happens, then this routine
** is called recursively on the parent.
**
** If this routine fails for any reason, it might leave the database
** in a corrupted state. So if this routine fails, the database should
** be rolled back.
*/
static int balance_nonroot(MemPage *pPage){
...
#ifndef SQLITE_OMIT_INTEGRITY_CHECK
/*
** Do various sanity checks on a single page of a tree. Return
** the tree depth. Root pages return 0. Parents of root pages
** return 1, and so forth.
**
** These checks are done:
**
** 1. Make sure that cells and freeblocks do not overlap
** but combine to completely cover the page.
** NO 2. Make sure cell keys are in order.
** NO 3. Make sure no key is less than or equal to zLowerBound.
** NO 4. Make sure no key is greater than or equal to zUpperBound.
** 5. Check the integrity of overflow pages.
** 6. Recursively call checkTreePage on all children.
** 7. Verify that the depth of all children is the same.
** 8. Make sure this page is at least 33% full or else it is
** the root of the tree.
*/
static int checkTreePage(
With temp_store set to 3(memory always), it is reaching 78K.
Increasing PAGE size increases STACK consumption, which is again a
problem with little main memory.
So, I think, as the database grows, the initial heap required by an
insert or update statement grows. Can we have control over max heap
usage by sqlite?
Get your own web address.
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] sqlite internal structs don't make use of C bitfields?
You could save a few bytes in some sqlite internal structs if you'd
use C bitfields for boolean flags:
For example:
struct MemPage {
u8 isInit; /* True if previously initialized. MUST BE FIRST! */
u8 idxShift; /* True if Cell indices have changed */
u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
u8 intKey; /* True if intkey flag is set */
u8 leaf; /* True if leaf flag is set */
u8 zeroData; /* True if table stores keys only */
u8 leafData; /* True if tables stores data on leaves only */
u8 hasData; /* True if this page stores data */
u8 hdrOffset;/* 100 for page 1. 0 otherwise */
u8 childPtrSize; /* 0 if leaf==1. 4 if leaf==0 */
u16 maxLocal;/* Copy of Btree.maxLocal or Btree.maxLeaf */
u16 minLocal;/* Copy of Btree.minLocal or Btree.minLeaf */
u16 cellOffset; /* Index in aData of first cell pointer */
u16 idxParent; /* Index in parent of this node */
u16 nFree; /* Number of free bytes on the page */
u16 nCell; /* Number of cells on this page, local and ovfl */
struct _OvflCell { /* Cells that will not fit on aData[] */
u8 *pCell; /* Pointers to the body of the overflow cell */
u16 idx;/* Insert this cell before idx-th non-overflow cell */
} aOvfl[5];
BtShared *pBt; /* Pointer back to BTree structure */
u8 *aData; /* Pointer back to the start of the page */
DbPage *pDbPage; /* Pager page handle */
Pgno pgno; /* Page number for this page */
MemPage *pParent;/* The parent of this page. NULL for root */
};
struct MemPage2 {
u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
u8 childPtrSize; /* 0 if leaf==1. 4 if leaf==0 */
u8 hdrOffset;/* 100 for page 1. 0 otherwise */
u8 isInit:1; /* True if previously initialized. MUST BE FIRST! */
u8 idxShift:1; /* True if Cell indices have changed */
u8 intKey:1; /* True if intkey flag is set */
u8 leaf:1; /* True if leaf flag is set */
u8 zeroData:1; /* True if table stores keys only */
u8 leafData:1; /* True if tables stores data on leaves only */
u8 hasData:1; /* True if this page stores data */
u16 maxLocal;/* Copy of Btree.maxLocal or Btree.maxLeaf */
u16 minLocal;/* Copy of Btree.minLocal or Btree.minLeaf */
u16 cellOffset; /* Index in aData of first cell pointer */
u16 idxParent; /* Index in parent of this node */
u16 nFree; /* Number of free bytes on the page */
u16 nCell; /* Number of cells on this page, local and ovfl */
struct _OvflCell { /* Cells that will not fit on aData[] */
u8 *pCell; /* Pointers to the body of the overflow cell */
u16 idx;/* Insert this cell before idx-th non-overflow cell */
} aOvfl[5];
BtShared *pBt; /* Pointer back to BTree structure */
u8 *aData; /* Pointer back to the start of the page */
DbPage *pDbPage; /* Pager page handle */
Pgno pgno; /* Page number for this page */
MemPage *pParent;/* The parent of this page. NULL for root */
};
sizeof(struct MemPage) = 84
sizeof(struct MemPage2) = 76
Or is there a C portability issue?
Park yourself in front of a world of choices in alternative vehicles. Visit the
Yahoo! Auto Green Center.
http://autos.yahoo.com/green_center/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
struct MemPage2 {
u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
u8 childPtrSize; /* 0 if leaf==1. 4 if leaf==0 */
u8 hdrOffset;/* 100 for page 1. 0 otherwise */
u8 isInit:1; /* True if previously initialized. MUST BE FIRST! */
Okay, maybe not this MUST BE FIRST field...
:-)
Ready
for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
Joe Wilson wrote:
You could save a few bytes in some sqlite internal structs if you'd
use C bitfields for boolean flags:
For example:
struct MemPage {
u8 isInit; /* True if previously initialized. MUST BE FIRST! */
u8 idxShift; /* True if Cell indices have changed */
u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
u8 intKey; /* True if intkey flag is set */
u8 leaf; /* True if leaf flag is set */
u8 zeroData; /* True if table stores keys only */
u8 leafData; /* True if tables stores data on leaves only */
u8 hasData; /* True if this page stores data */
u8 hdrOffset;/* 100 for page 1. 0 otherwise */
u8 childPtrSize; /* 0 if leaf==1. 4 if leaf==0 */
u16 maxLocal;/* Copy of Btree.maxLocal or Btree.maxLeaf */
u16 minLocal;/* Copy of Btree.minLocal or Btree.minLeaf */
u16 cellOffset; /* Index in aData of first cell pointer */
u16 idxParent; /* Index in parent of this node */
u16 nFree; /* Number of free bytes on the page */
u16 nCell; /* Number of cells on this page, local and ovfl */
struct _OvflCell { /* Cells that will not fit on aData[] */
u8 *pCell; /* Pointers to the body of the overflow cell */
u16 idx;/* Insert this cell before idx-th non-overflow cell */
} aOvfl[5];
BtShared *pBt; /* Pointer back to BTree structure */
u8 *aData; /* Pointer back to the start of the page */
DbPage *pDbPage; /* Pager page handle */
Pgno pgno; /* Page number for this page */
MemPage *pParent;/* The parent of this page. NULL for root */
};
struct MemPage2 {
u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
u8 childPtrSize; /* 0 if leaf==1. 4 if leaf==0 */
u8 hdrOffset;/* 100 for page 1. 0 otherwise */
u8 isInit:1; /* True if previously initialized. MUST BE FIRST! */
u8 idxShift:1; /* True if Cell indices have changed */
u8 intKey:1; /* True if intkey flag is set */
u8 leaf:1; /* True if leaf flag is set */
u8 zeroData:1; /* True if table stores keys only */
u8 leafData:1; /* True if tables stores data on leaves only */
u8 hasData:1; /* True if this page stores data */
u16 maxLocal;/* Copy of Btree.maxLocal or Btree.maxLeaf */
u16 minLocal;/* Copy of Btree.minLocal or Btree.minLeaf */
u16 cellOffset; /* Index in aData of first cell pointer */
u16 idxParent; /* Index in parent of this node */
u16 nFree; /* Number of free bytes on the page */
u16 nCell; /* Number of cells on this page, local and ovfl */
struct _OvflCell { /* Cells that will not fit on aData[] */
u8 *pCell; /* Pointers to the body of the overflow cell */
u16 idx;/* Insert this cell before idx-th non-overflow cell */
} aOvfl[5];
BtShared *pBt; /* Pointer back to BTree structure */
u8 *aData; /* Pointer back to the start of the page */
DbPage *pDbPage; /* Pager page handle */
Pgno pgno; /* Page number for this page */
MemPage *pParent;/* The parent of this page. NULL for root */
};
sizeof(struct MemPage) = 84
sizeof(struct MemPage2) = 76
Or is there a C portability issue?
Joe,
You must have missed the comment on this line:
u8 isInit; /* True if previously initialized. MUST BE FIRST! */
There must be some code that uses char pointers to check the first byte
of these structures. So this can't be combined as easily.
Bit fields can offer some space savings, but they are slower since the
generated code must do masking and shifting when reading and setting
them. There are also some issues with regard to the ordering and layout
of bitifleds in cross platform applications. I suspect that is the
reason they aren't used.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Re: CAST
I for one would be in favor of an option to enforce strict typing (compile time option). SQLite version 3 will feature two other affinity modes, as follows: Strict affinity mode. In this mode if a conversion between storage classes is ever required, the database engine returns an error and the current statement is rolled back. I hope it means that SQLite will behave like any other database, and errors during insert will be detected while they are made, not in some unspecified time in the future. This is probably not a problem with machine generated SQLs, but if a human is allowed to enter SQLs, working with a database may be difficult. -- Wyjatkowo niegrzeczne kartki na Dzien Dziecka http://link.interia.pl/f1a96 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: CAST
afaik strict affininity mode hasn't been implemented. From http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq Q) How can the strict affinity mode be used which is claimed to exist on http://www.sqlite.org/datatype3.html A) This has not been implemented as of version 3.3.13. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 30, 2007 12:04 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Re: CAST I for one would be in favor of an option to enforce strict typing (compile time option). SQLite version 3 will feature two other affinity modes, as follows: Strict affinity mode. In this mode if a conversion between storage classes is ever required, the database engine returns an error and the current statement is rolled back. I hope it means that SQLite will behave like any other database, and errors during insert will be detected while they are made, not in some unspecified time in the future. This is probably not a problem with machine generated SQLs, but if a human is allowed to enter SQLs, working with a database may be difficult. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?
I have. I placed software hooks into the SQLite memory heap manager and determined that SQLite allocates memory for most operations and does not give the memory back until it closes. I didn't look any further as we are out of time. Ray Kalyani Tummala [EMAIL PROTECTED] wrote: Hi Joe, Yes, SDRAM is normal volatile RAM. Instead of BEGIN ... COMMIT, I have opened database before insert statement and closed db after that, with 2500 records already stored in the database, temp_store set to 0(file always), sqlite is taking 48K heap to open the database, 55K for first 5 inserts and increases for every other insert and goes to 68K for next 5 inserts. With temp_store set to 3(memory always), it is reaching 78K. Increasing PAGE size increases STACK consumption, which is again a problem with little main memory. So, I think, as the database grows, the initial heap required by an insert or update statement grows. Can we have control over max heap usage by sqlite? Did anyone experience before this growth in heap usage? Are my numbers correct? This is going to be a bottleneck for my application. Thank you Kalyani -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 9:40 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? The default is auto-commit, so unless you've opened an explicit transaction with BEGIN and do a number of inserts, the COMMIT suggestion is not useful in reducing memory footprint. (apologies in advance if this is obvious...) SDRAM is the normal volatile RAM, right? You know that temp_store is competing with sqlite for your RAM. If you point your temp_store to disk to use non-volatile storage (disk or flash) then you'd have more RAM available for sqlite. Or do you lack such non-volatile storage? You can run the tests on Windows by installing either Cygwin or MinGW/MSYS, installing Tcl 8.4, running configure, and then running make test. You can change the generated Makefile to define various sqlite compile-time flags for memory debugging. Just scan the ifdef's in the source code for ideas what to enable. It's much easier to do this under Linux than under Windows, in my opinion. --- Kalyani Tummala [EMAIL PROTECTED] wrote: My temp_store is SDRAM. Thanks for your suggestion of using COMMIT. I have not used it. Any other pointers? Best Regards Kalyani -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 10:27 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? --- Joe Wilson [EMAIL PROTECTED] wrote: I am working at porting sqlite ( ver 3.3.8 ) on an embedded device with extremely low main memory. I tried running select queries on the tables( with about 2k records each having about 5 strings) and they do well within 20kB of runtime heap usage. But, when I try new insertions, the heap usage grows tremendously (about 70 kB at peak). Perhaps preparing the statements (sqlite3_prepare) might decrease RAM use somewhat. Can you post an example of your schema and these insert statements? Is your temp_store in memory or flash ram or other? Another thought... are you performing a COMMIT after each INSERT? (or at least every X inserts, where X100)? It might help to reduce memory (at the cost of reduced speed). Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php - To unsubscribe, send email to [EMAIL PROTECTED] - ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify [EMAIL PROTECTED] ** - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
MemPage bitfield patch below.
sizeof(MemPage) on Linux:
original: 84
patched: 76
Patched make test runs without regressions on Linux and Windows.
Timings for make test (elapsed):
original: 1:20.74
patched: 1:20.22
Size of sqlite3.o when compiled from almalogmation with all
sqlite features enabled with gcc flags -O3 -fomit-frame-pointer:
original: 586976 bytes
patched: 587880 bytes
Patched sqlite3.o is 904 bytes larger.
Break-even for memory is 904/8 = 113 MemPage structs allocated.
Index: src/btreeInt.h
===
RCS file: /sqlite/sqlite/src/btreeInt.h,v
retrieving revision 1.4
diff -u -3 -p -r1.4 btreeInt.h
--- src/btreeInt.h 16 May 2007 17:28:43 - 1.4
+++ src/btreeInt.h 30 May 2007 16:26:03 -
@@ -269,15 +269,15 @@ typedef struct BtLock BtLock;
*/
struct MemPage {
u8 isInit; /* True if previously initialized. MUST BE FIRST! */
- u8 idxShift; /* True if Cell indices have changed */
u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
- u8 intKey; /* True if intkey flag is set */
- u8 leaf; /* True if leaf flag is set */
- u8 zeroData; /* True if table stores keys only */
- u8 leafData; /* True if tables stores data on leaves only */
- u8 hasData; /* True if this page stores data */
- u8 hdrOffset;/* 100 for page 1. 0 otherwise */
- u8 childPtrSize; /* 0 if leaf==1. 4 if leaf==0 */
+ u8 hdrOffset:7;/* 100 for page 1. 0 otherwise */
+ u8 zeroData:1; /* True if table stores keys only */
+ u8 childPtrSize:3; /* 0 if leaf==1. 4 if leaf==0 */
+ u8 leaf:1; /* True if leaf flag is set */
+ u8 idxShift:1; /* True if Cell indices have changed */
+ u8 intKey:1; /* True if intkey flag is set */
+ u8 leafData:1; /* True if tables stores data on leaves only */
+ u8 hasData:1; /* True if this page stores data */
u16 maxLocal;/* Copy of Btree.maxLocal or Btree.maxLeaf */
u16 minLocal;/* Copy of Btree.minLocal or Btree.minLeaf */
u16 cellOffset; /* Index in aData of first cell pointer */
Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.
http://videogames.yahoo.com/platform?platform=120121
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
--- Dennis Cote [EMAIL PROTECTED] wrote: There are also some issues with regard to the ordering and layout of bitifleds in cross platform applications. I suspect that is the reason they aren't used. If an external interface changed, sure. But these internal structs change constantly from (minor) release to release. The struct in question is used solely by btree.c, so the ordering and layout for bit fields on different compilers or different platforms do not matter. Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mailp=summer+activities+for+kidscs=bz - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
- Original Message MemPage bitfield patch below. sizeof(MemPage) on Linux: original: 84 patched: 76 ... Break-even for memory is 904/8 = 113 MemPage structs allocated. I didn't look at the code, so mind me :) If the MemPage are malloced individually (instead of being put in arrays), then they are 16 byte aligned on most platforms, making the allocated block effectively the same size (well, that depends on how many bytes are used by malloc before the user block in memory). If on the other hand those structs are packed in arrays then there can be a benefit. But there, I would think that a good experiment would be to split the fields into different arrays (the same old optimizations on chunky vs planar for those coming from computer graphics) and group data by frequency of use and/or locality for the caches. An example I remember from back in the days was a struct containing data for each pixel that we split into two structs (puting the data used less frequently in a separate struct), and with this change we got over 500% speed improvement on the typical workload just because the processor was doing less cache miss and could prefetch much more efficiently when iterating over data. Also, my take on bitfields is that they are not thread/multi processor friendly (there is no atomic set bit), and also compilers typically don't optimize well with that (so before applying this patch, I would test on other platforms than gcc linux x86). Nicolas
[sqlite] Equal distribution from random rows
Hello, I'm working on a project that requires random images to be chosen from a live database for a dynamic homepage. I found this link from Dr. Hipp that details a very fast approach to selecting random rows: http://www.mail-archive.com/sqlite-users@sqlite.org/msg14652.html Unfortunately, it heavily weights the distribution of the randomly chosen rows - to the extreme that the feeling of randomness is almost lost due to the same images appearing so frequently. After some research I concluded that it must be caused by breaks in the ROWID values. However, after VACUUMing the database to re-order the ROWIDs I find I am still having the same problem - weighted distribution. Is there a way I can modify my query to attain a much more equal distribution? It doesn't have to be perfect, but right now it is too noticiably weighted. I have posted a stripped down version of the database, and a small snippet of perl to display the problem here: http://www.acatysmoof.com/posting/problems/sqlite/dist.tgz Many thanks to anyone with a moment to share some ideas on how to equalize this distribution. Thanks, Alex - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
Joe Wilson wrote: If an external interface changed, sure. But these internal structs change constantly from (minor) release to release. The struct in question is used solely by btree.c, so the ordering and layout for bit fields on different compilers or different platforms do not matter. Joe, Yeah, for strictly internal memory based structures, I can't see a problem. I wasn't sure if these were ever written to disk. One other issue that might have been a problem is that you can't get the address of a bitfield. Since you have successfully compiled the code using bitfields, there must not be any code that tries to get a pointer to any of these fields. Your test seems to show these changes don't have any adverse performance impact either. You may want to look at how the isInited field is used. You may be able to combine it with the others as long as it stays in the first byte and the code only checks for zero vs nonzero values on that byte (then again that may not be safe if other combined bitfield are set nonzero before the isInited field is set). If its safe, you could save another byte per structure. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equal distribution from random rows
Alex Teslik wrote: Hello, I'm working on a project that requires random images to be chosen from a live database for a dynamic homepage. I found this link from Dr. Hipp that details a very fast approach to selecting random rows: http://www.mail-archive.com/sqlite-users@sqlite.org/msg14652.html Unfortunately, it heavily weights the distribution of the randomly chosen rows - to the extreme that the feeling of randomness is almost lost due to the same images appearing so frequently. After some research I concluded that it must be caused by breaks in the ROWID values. However, after VACUUMing the database to re-order the ROWIDs I find I am still having the same problem - weighted distribution. Is there a way I can modify my query to attain a much more equal distribution? It doesn't have to be perfect, but right now it is too noticiably weighted. After running a simple test, I confirmed a suspicion. VACUUM doesn't reorder the ROWIDs, so you still have breaks. Two suggestions: 1. If the image data are fairly stable, copy the table to a new table so as to eliminate the breaks in the ROWIDs. 2. If the table is relatively small, load all the potential ids into memory as a list and select randomly from the list. Assuming the ROWIDs are actually sequential, without any breaks, it would seem to me the distribution problem would lie in the pseudo-random generator rather than in the query. John - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: CAST
* Samuel R. Neff [EMAIL PROTECTED] [2007-05-30 14:55]: SQLite's typelessness is an asset if you work only with SQLite but in any application that uses multiple database engines of which SQLite is only one supported engine, the non-standard typelessness is something that has to be worked around. Can you give an example of such a case? I work with several different DBMSs, myself, and I have yet to run into trouble with SQLite’s approach. Can you give a reallife example? Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
- Original Message From: Dennis Cote [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, May 30, 2007 12:09:25 PM Subject: Re: [sqlite] sqlite internal structs don't make use of C bitfields? You may want to look at how the isInited field is used. You may be able to combine it with the others as long as it stays in the first byte and the code only checks for zero vs nonzero values on that byte (then again that may not be safe if other combined bitfield are set nonzero before the isInited field is set). If its safe, you could save another byte per structure. There seems to be some other removal of redundant fields: u8 childPtrSize; /* 0 if leaf==1. 4 if leaf==0 */ implies that leaf can be replaced by !childPtrSize, right? Well, on the bitfield version, it's only saving 1 bit (we can go the other way and replace childPtrSize by something like leaf?0:4 and save more space). hdrOffset seems to be an other interesting subject as it seems to be the same kind of deal. Nicolas
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
--- Dennis Cote [EMAIL PROTECTED] wrote: Joe Wilson wrote: If an external interface changed, sure. But these internal structs change constantly from (minor) release to release. The struct in question is used solely by btree.c, so the ordering and layout for bit fields on different compilers or different platforms do not matter. Joe, Yeah, for strictly internal memory based structures, I can't see a problem. I wasn't sure if these were ever written to disk. Writing structs' memory directory to disk is not portable, whether bit fields are used or not. One other issue that might have been a problem is that you can't get the address of a bitfield. Since you have successfully compiled the code using bitfields, there must not be any code that tries to get a pointer to any of these fields. Your test seems to show these changes don't have any adverse performance impact either. You may want to look at how the isInited field is used. You may be able to combine it with the others as long as it stays in the first byte and the code only checks for zero vs nonzero values on that byte (then again that may not be safe if other combined bitfield are set nonzero before the isInited field is set). If its safe, you could save another byte per structure. Generally structs are aligned on 8-byte boundaries, so making isInited a bitfield wouldn't save any additional space in this particular case. Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equal distribution from random rows
Is there a way I can modify my query to attain a much more equal distribution? It doesn't have to be perfect, but right now it is too noticiably weighted. What about this: SELECT * FROM Table LIMIT 1 OFFSET round((CAST(random(*) as float)/(9223372036854775807)+1)/2*(SELECT COUNT(*) FROM Table)) It possibly looks a little weird, but it seems to me to be a logical way of how to get Uniform distributed samples from a table. Jiri - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
Joe Wilson wrote: Generally structs are aligned on 8-byte boundaries, so making isInited a bitfield wouldn't save any additional space in this particular case. You can combine the other bitfields with isInited at the beginning of the structure and save a byte later in the structure. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
If the MemPage are malloced individually (instead of being put in arrays), then they are 16 byte aligned on most platforms, making the allocated block effectively the same size (well, that depends on how many bytes are used by malloc before the user block in memory). This patch does indeed save memory - on Linux at least. Linux has a malloc mimimum resolution of 8 bytes, not 16. I mis-wrote: Linux's minimum malloc, malloc(1), will occupy 16 bytes of heap as you stated. Malloc(n) on Linux will allocate the next multiple of 8 bytes for (n+4), minimum 16 bytes total. Observed malloc behavior on Linux - Malloc'd bytes on the left, heap occupied by that malloc on right: 1 16 2 16 3 16 4 16 5 16 6 16 7 16 8 16 9 16 10 16 11 16 12 16 13 24 14 24 15 24 16 24 17 24 18 24 19 24 20 24 21 32 22 32 23 32 24 32 25 32 26 32 27 32 28 32 29 40 30 40 31 40 32 40 33 40 34 40 35 40 36 40 37 48 38 48 39 48 40 48 41 48 42 48 43 48 44 48 45 56 46 56 47 56 48 56 49 56 50 56 51 56 52 56 53 64 54 64 55 64 56 64 57 64 58 64 59 64 60 64 61 72 62 72 63 72 64 72 65 72 etc... Get your own web address. Have a HUGE year through Yahoo! Small Business. http://smallbusiness.yahoo.com/domains/?p=BESTDEAL - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equal distribution from random rows
On Wed, 30 May 2007 15:18:18 -0400, John Elrick wrote
snip
After running a simple test, I confirmed a suspicion. VACUUM
doesn't reorder the ROWIDs, so you still have breaks.
My tests show otherwise:
[alex]# cat 01_vacuum_table_test.sql
CREATE TABLE foo (
string varchar(1) not null
);
INSERT INTO foo (string) VALUES ('a');
INSERT INTO foo (string) VALUES ('b');
INSERT INTO foo (string) VALUES ('c');
INSERT INTO foo (string) VALUES ('d');
[alex]# sqlite3 foo.db 01_vacuum_table_test.sql
[alex]# sqlite3 --header --column foo.db SELECT rowid,* FROM foo
rowid string
-- --
1 a
2 b
3 c
4 d
[alex]# sqlite3 --header --column foo.db DELETE FROM foo WHERE rowid = 2
[alex]# sqlite3 --header --column foo.db DELETE FROM foo WHERE rowid = 4
[alex]# sqlite3 --header --column foo.db SELECT rowid,* FROM foo
rowid string
-- --
1 a
3 c
[alex]# sqlite3 --header --column foo.db VACUUM
[alex]# sqlite3 --header --column foo.db SELECT rowid,* FROM foo
rowid string
-- --
1 a
2 c
did I do something incorrectly?
Two suggestions:
1. If the image data are fairly stable, copy the table to a new
table so as to eliminate the breaks in the ROWIDs.
2. If the table is relatively small, load all the potential ids
into memory as a list and select randomly from the list.
The table is small now, but expected to grow considerably to over a million
rows. I coded a random selector from an array at the application level, and it
works, but it will degrade dramatically over time.
Thanks,
Alex
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Equal distribution from random rows
Here's a naive solution, which requires some programming language
around the SQL:
BEGIN TRANSACTION
n = (SELECT count(*) from table)
i = RandBetween(0, n)
row = (SELECT * from table LIMIT 1 OFFSET i)
END TRANSACTION
I'm posting this because I suspect that this naive solution isn't
correct, but I don't know why, and want to find out.
On May 30, 2007 3:55 PM, Alex Teslik [EMAIL PROTECTED] wrote:
On Wed, 30 May 2007 15:18:18 -0400, John Elrick wrote
snip
After running a simple test, I confirmed a suspicion. VACUUM
doesn't reorder the ROWIDs, so you still have breaks.
My tests show otherwise:
[alex]# cat 01_vacuum_table_test.sql
CREATE TABLE foo (
string varchar(1) not null
);
INSERT INTO foo (string) VALUES ('a');
INSERT INTO foo (string) VALUES ('b');
INSERT INTO foo (string) VALUES ('c');
INSERT INTO foo (string) VALUES ('d');
[alex]# sqlite3 foo.db 01_vacuum_table_test.sql
[alex]# sqlite3 --header --column foo.db SELECT rowid,* FROM foo
rowid string
-- --
1 a
2 b
3 c
4 d
[alex]# sqlite3 --header --column foo.db DELETE FROM foo WHERE rowid = 2
[alex]# sqlite3 --header --column foo.db DELETE FROM foo WHERE rowid = 4
[alex]# sqlite3 --header --column foo.db SELECT rowid,* FROM foo
rowid string
-- --
1 a
3 c
[alex]# sqlite3 --header --column foo.db VACUUM
[alex]# sqlite3 --header --column foo.db SELECT rowid,* FROM foo
rowid string
-- --
1 a
2 c
did I do something incorrectly?
Two suggestions:
1. If the image data are fairly stable, copy the table to a new
table so as to eliminate the breaks in the ROWIDs.
2. If the table is relatively small, load all the potential ids
into memory as a list and select randomly from the list.
The table is small now, but expected to grow considerably to over a million
rows. I coded a random selector from an array at the application level, and it
works, but it will degrade dramatically over time.
Thanks,
Alex
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite internal structs don't make use of C bitfields?
At 23:25 30/05/2007, you wrote:
--- [EMAIL PROTECTED] wrote:
- Original Message
MemPage bitfield patch below.
sizeof(MemPage) on Linux:
original: 84
patched: 76
...
Break-even for memory is 904/8 = 113 MemPage structs allocated.
I didn't look at the code, so mind me :)
If the MemPage are malloced individually (instead of being put in arrays),
then they are 16 byte
aligned on most platforms, making the allocated block effectively the same
size (well, that
depends on how many bytes are used by malloc before the user block in
memory).
This patch does indeed save memory - on Linux at least.
Linux has a malloc mimimum resolution of 8 bytes, not 16.
No, for example, under powerpc and linux with altivec enabled in kernel it's 16
bytes (or use www.freevec.org library). I think under other simd capable
processors it's 16 bytes too.
Also, my take on bitfields is that they are not thread/multi processor
friendly (there is no
atomic set bit), and also compilers typically don't optimize well with
that (so before
applying this patch, I would test on other platforms than gcc linux x86).
Setting and reading individual bytes (u8 in sqlite-speak) are not
threadsafe either. Only reading/setting entire entire words
are threadsafe on most architectures.
Using a uint32 for store the flags is threadsafe. There are less than 32
true/false values and read/set is simple. I see no difference doing
if (uint8==0){ // read/test bit
uint8=1; // set bit
whatever more
}
and
if (uint32MASK){ // read/test bit
uint32=MASK;// set bit
whatever
}
in speed, and a compiler should not make worse code on last one. So say
Also, my take on bitfields is that they are not thread/multi processor
friendly (there is no
atomic set bit), and also compilers typically don't optimize well with
that (so before
applying this patch, I would test on other platforms than gcc linux x86).
is not true.
--
Dios es real, a no ser que esté declarado como entero...
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
