Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.

2007-08-20 Thread Babu, Lokesh 
Hi Folks,

This is the little hack, where you can free up the memory, inside
*btree.h*enable the SQLITE_DEFAULT_AUTOVACUUM to 1, and inside
*btree.c*, make sure to pBt->autoVacuum = SQLITE_DEFAULT_AUTOVACUUM; enable
this statement. The statement is in *if* block, so you need to put outside.

This solution gives perfect memory results even after delete, update,
replace, drop etc has been done.

If *delete *is done it should free up the memory, but it was keeping the
contents, particularly if I'm using In-Memory database I need that memory to
be freed and give room for other records.

BTW thanks for everyone who replied. The requirement is for a small realtime
embedded device. I don't want rollback or anything to happen, as database
will reside on memory and it will not be dumped on disk or anywhere, if the
device is rebooted the database will be created afresh.

If anybody has better solution, please let me know.


On 8/9/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
>
> --- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote:
> > Does anybody knows the exact code, a little hack where can I free up the
> > memory, I don't want it to use it for future requests.
> >
> > Even drop table consumes memory. :-(.
> >
> > If we are doing in-memory database operation, why do we want to maintain
> the
> > free'd memory pages?
>
> On Linux using sqlite 3.4.1:
>
> -- start sqlite3 shell
>
> -- 2m VIRT, 1m RES
>
> pragma temp_store=memory;
> CREATE TABLE abc(a,b,c);
> INSERT INTO "abc" VALUES(5400,'some dumb phrase to fill stuff',-345.7);
> INSERT INTO "abc" VALUES(-1234,'the quick brown fox', 20394203492340.5);
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
>
> -- 167m VIRT, 166m RES
>
> delete from abc where a != b;
>
> -- 200m VIRT, 199m RES peak usage during delete
>
> -- 167m VIRT, 166m RES after delete
>
> vacuum;
>
> -- 18m VIRT, 1m RES
>
> Are you seeing different results?
>
>
>
>
> 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] In-Memory Database: Delete rows on a Table increases the memory usage.

2007-08-09 Thread Joe Wilson 
--- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote:
> Does anybody knows the exact code, a little hack where can I free up the
> memory, I don't want it to use it for future requests.
> 
> Even drop table consumes memory. :-(.
> 
> If we are doing in-memory database operation, why do we want to maintain the
> free'd memory pages?

On Linux using sqlite 3.4.1:

-- start sqlite3 shell

-- 2m VIRT, 1m RES

pragma temp_store=memory;
CREATE TABLE abc(a,b,c);
INSERT INTO "abc" VALUES(5400,'some dumb phrase to fill stuff',-345.7);
INSERT INTO "abc" VALUES(-1234,'the quick brown fox', 20394203492340.5);
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;
insert into abc select * from abc;

-- 167m VIRT, 166m RES

delete from abc where a != b;

-- 200m VIRT, 199m RES peak usage during delete

-- 167m VIRT, 166m RES after delete

vacuum;

-- 18m VIRT, 1m RES

Are you seeing different results?



   
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] In-Memory Database: Delete rows on a Table increases the memory usage.

2007-08-09 Thread Babu, Lokesh 
Does anybody knows the exact code, a little hack where can I free up the
memory, I don't want it to use it for future requests.

Even drop table consumes memory. :-(.

If we are doing in-memory database operation, why do we want to maintain the
free'd memory pages?

I think If Mr. Hipp answers, it will be better... :-)


On 8/3/07, Christian Smith <[EMAIL PROTECTED]> wrote:
>
> Scott Derrick uttered:
>
> > are you saying this is a memory leak?
> > sqlite never gives back the unused memory?
>
>
> No, libc never gives back the memory.
>
> It is not leaked because the malloc implementation keeps a reference to
> all the free'd heap memory in tracking it for future requests.
>
>
> >
> > Christian Smith wrote:
> >> Lokesh Babu uttered:
> >>
> >>> Hello Folks,
> >>>
> >>> When I perform the DELETE operation on a Table using In-Memory
> Database
> >>> (":memory:"), the memory usage increases.
> >>>
> >>> I tried using PRAGMA auto_vacuum=1; /* result - nothing works */
> >>> Even I tried using VACUUM table_name; /* this too isn't work */
> >>>
> >>> if I perform DROP the table operation, memory usage works. Deleting
> the
> >>> rows
> >>> doesn't work.
> >>>
> >>> Anybody please help me in resolving this.
> >>
> >>
> >> Memory usage goes up because SQLite must temporarily store copies of
> the
> >> old pages that store the deleted rows, in order to allow a future
> rollback
> >> if required.
> >>
> >> Once a commit is done, the old pages are free'd, but by that time the
> >> memory footprint has already increased. Not many libc implementations
> >> release heap memory back to the OS once it's allocated.
> >>
> >>
> >>>
> >>> Thanks in advance,
> >>>
> >>> Lokee
> >>>
> >>
> >> --
> >> /"\
> >> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
> >>  X   - AGAINST MS ATTACHMENTS
> >> / \
> >>
> >>
> >>
> -
> >> To unsubscribe, send email to [EMAIL PROTECTED]
> >>
> >>
> -
> >>
> >>
> >>
> >>
> >
> >
>
> --
> /"\
> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
>  X   - AGAINST MS ATTACHMENTS
> / \
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.

2007-08-03 Thread Christian Smith 

Scott Derrick uttered:

are you saying this is a memory leak? 
sqlite never gives back the unused memory?



No, libc never gives back the memory.

It is not leaked because the malloc implementation keeps a reference to 
all the free'd heap memory in tracking it for future requests.





Christian Smith wrote:

Lokesh Babu uttered:


Hello Folks,

When I perform the DELETE operation on a Table using In-Memory Database
(":memory:"), the memory usage increases.

I tried using PRAGMA auto_vacuum=1; /* result - nothing works */
Even I tried using VACUUM table_name; /* this too isn't work */

if I perform DROP the table operation, memory usage works. Deleting the 
rows

doesn't work.

Anybody please help me in resolving this.



Memory usage goes up because SQLite must temporarily store copies of the 
old pages that store the deleted rows, in order to allow a future rollback 
if required.


Once a commit is done, the old pages are free'd, but by that time the 
memory footprint has already increased. Not many libc implementations 
release heap memory back to the OS once it's allocated.





Thanks in advance,

Lokee



--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \


- 
To unsubscribe, send email to [EMAIL PROTECTED]


- 










--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.

2007-08-02 Thread Christian Smith 

Lokesh Babu uttered:


Hi Smith,

Is there any way where I can free the old pages and without using rollback
feature.
Because I'm much concerned about memory usage.

As soon as I delete some records, It should free up the memory.



Use a libc that has a malloc implementation that releases excess memory 
back to the operating system. I don't know of specific instances of libc 
that do this, so I can't help further, sorry.





Thanks


On 8/1/07, Christian Smith <[EMAIL PROTECTED]> wrote:


Lokesh Babu uttered:


Hello Folks,

When I perform the DELETE operation on a Table using In-Memory Database
(":memory:"), the memory usage increases.

I tried using PRAGMA auto_vacuum=1; /* result - nothing works */
Even I tried using VACUUM table_name; /* this too isn't work */

if I perform DROP the table operation, memory usage works. Deleting the

rows

doesn't work.

Anybody please help me in resolving this.



Memory usage goes up because SQLite must temporarily store copies of the
old pages that store the deleted rows, in order to allow a future rollback
if required.

Once a commit is done, the old pages are free'd, but by that time the
memory footprint has already increased. Not many libc implementations
release heap memory back to the OS once it's allocated.

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \


-
To unsubscribe, send email to [EMAIL PROTECTED]

-






--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.

2007-08-02 Thread Scott Derrick 
are you saying this is a memory leak? 


sqlite never gives back the unused memory?

Christian Smith wrote:

Lokesh Babu uttered:


Hello Folks,

When I perform the DELETE operation on a Table using In-Memory Database
(":memory:"), the memory usage increases.

I tried using PRAGMA auto_vacuum=1; /* result - nothing works */
Even I tried using VACUUM table_name; /* this too isn't work */

if I perform DROP the table operation, memory usage works. Deleting 
the rows

doesn't work.

Anybody please help me in resolving this.



Memory usage goes up because SQLite must temporarily store copies of 
the old pages that store the deleted rows, in order to allow a future 
rollback if required.


Once a commit is done, the old pages are free'd, but by that time the 
memory footprint has already increased. Not many libc implementations 
release heap memory back to the OS once it's allocated.





Thanks in advance,

Lokee



--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 








--

-
   As nightfall does not come at once, neither does oppression. In both 
instances, there is a twilight when everything remains seemingly unchanged. And 
it is in such twilight that we all must be most aware of change in the air 
however slight lest we become unwitting victims of the darkness.

   William O. Douglas, Justice of the U.S. Supreme Court 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.

2007-08-02 Thread Lokesh Babu 
Hi Smith,

Is there any way where I can free the old pages and without using rollback
feature.
Because I'm much concerned about memory usage.

As soon as I delete some records, It should free up the memory.

Thanks


On 8/1/07, Christian Smith <[EMAIL PROTECTED]> wrote:
>
> Lokesh Babu uttered:
>
> > Hello Folks,
> >
> > When I perform the DELETE operation on a Table using In-Memory Database
> > (":memory:"), the memory usage increases.
> >
> > I tried using PRAGMA auto_vacuum=1; /* result - nothing works */
> > Even I tried using VACUUM table_name; /* this too isn't work */
> >
> > if I perform DROP the table operation, memory usage works. Deleting the
> rows
> > doesn't work.
> >
> > Anybody please help me in resolving this.
>
>
> Memory usage goes up because SQLite must temporarily store copies of the
> old pages that store the deleted rows, in order to allow a future rollback
> if required.
>
> Once a commit is done, the old pages are free'd, but by that time the
> memory footprint has already increased. Not many libc implementations
> release heap memory back to the OS once it's allocated.
>
> --
> /"\
> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
>  X   - AGAINST MS ATTACHMENTS
> / \
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.

2007-08-01 Thread Christian Smith 

Lokesh Babu uttered:


Hello Folks,

When I perform the DELETE operation on a Table using In-Memory Database
(":memory:"), the memory usage increases.

I tried using PRAGMA auto_vacuum=1; /* result - nothing works */
Even I tried using VACUUM table_name; /* this too isn't work */

if I perform DROP the table operation, memory usage works. Deleting the rows
doesn't work.

Anybody please help me in resolving this.



Memory usage goes up because SQLite must temporarily store copies of the 
old pages that store the deleted rows, in order to allow a future rollback 
if required.


Once a commit is done, the old pages are free'd, but by that time the 
memory footprint has already increased. Not many libc implementations 
release heap memory back to the OS once it's allocated.





Thanks in advance,

Lokee



--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-