Re: [sqlite] :memory: DB releasing storage

[Rick Keiner]

I'm back. ;) I found out that sqlite3pager_movepage goes in and calls unlinkHashChain which sets the page no to zero but leaves it on the pNextFree/pPrevFree list. On subsequent calls to memoryTruncate the page is never unlinked. But if the deletes are within a transaction sqlite3pager_movepage does not call unlinkHashChain and there is no memory leak. :).

It seems the attached patch should make memoryTruncate work properly by checking for a page number of 0.

This seems like a serious problem for in-memory databases but using a transaction is a work around.

Auto-vacuum is still a problem.

Regards,
Rick Keiner

On 6/21/06, Rick Keiner <[EMAIL PROTECTED]> wrote:

Any thoughts on this problem? I've been running with this patch and it seems to deal with the memory leak but no auto-vacuum. :(.

Thanks,

Rick Keiner

On 6/9/06, Rick Keiner < [EMAIL PROTECTED]> wrote:

There seems to be a bug in the memoryTruncate function in the pager. When it iterates through the pages I saw that there were page numbers of 0 where no action was being taken. As the number of deletes increased, the number of page number 0s increased. By making the following modification I no longer saw the memory leak.

  if( pPg->pgno<=dbSize && pPg->pgno != 0){

Everything seemed to be fine but I really don't understand enough about the pager to know what impact this may have.  I'm only trying to observe what's going on. The auto_vacuum still didn't return storage to the system, though.

Is a page number of 0 valid?

hth,

Rick Keiner

On 6/7/06, Rick Keiner <[EMAIL PROTECTED] > wrote:

Understood. It seems the pager code is more relevant.

However, I am using the pragma. It works fine for a disk database. When the deletes are perfomed the database file returns back to the original size. I don't see any memory increase (just in case it was my code). The identical code is executed against a memory database and the memory continues to increase. After the deletes there is no decrease in storage and then the inserts are performed again and my storage usage increases. Delete and insert again and the storage continues to climb at the identical rate. If I double the number of inserts the storage increase doubles.

This is what I am seeing. The number on the left is storage.

Memory DB - Series of Inserts and deletes. The storage increases with each insert.

57.2M Insert 4000
69.9M Flush
69.9M Insert 4000
72.4M Flush
72.4M Insert 4000
75.7M Flush
75.6M Insert 4000
78.3M Flush
78.4M Insert 4000
80.9M

Double the Records

57.1M Insert 8000
73.6M Flush
73.7M Insert 8000
78.8M Flush
78.9M Insert 8000
84.1M Flush
84.1M Insert 8000
89.4M Flush
89.5M

Disk Database -

File size - 8K
57.2M Insert 4000 File size - 1.9M
67.3M Flush - 9K
67.9M Insert 4000 - 1.9M
67.9M Flush - 9K
67.9M Insert 4000 - 1.9M
67.9M Flush - 9K

Is there a minimum amount of storage that it will use until it starts to release storage?

Thanks,

Rick Keiner

On 6/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Rick Keiner" <[EMAIL PROTECTED]> wrote:
> Yes, apparently. The :memory: DB doesn't show the storage being reclaimed by
> the OS. In fact, after some more analysis, it's not reusing storage already
> allocated. :( Could that be?
>
> After checking the vacuum.c code. It's not doing anything for an in-memory
> DB. Would that be handled elsewhere?
>
>   /* Get the full pathname of the database file and create a
>   ** temporary filename in the same directory as the original file.
>   */
>   pMain = db->aDb[0].pBt;
>   zFilename = sqlite3BtreeGetFilename(pMain);
>   assert( zFilename );
>   if( zFilename[0]=='\0' ){
>     /* The in-memory database. Do nothing. Return directly to avoid causing
>     ** an error trying to DETACH the vacuum_db (which never got attached)
>     ** in the exit-handler.
>     */
>     return SQLITE_OK;
>   }
>

Auto-vacuum and VACUUM, in spite of similar names, are very different
mechanisms.  You enable autovacuum by issuing a pragma:

    PRAGMA auto_vacuum=ON;

prior to creating any tables in your :memory: database.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Index: src/pager.c
===================================================================
RCS file: /sqlite/sqlite/src/pager.c,v
retrieving revision 1.269
diff -w -d -u -r1.269 pager.c
--- src/pager.c	15 Jun 2006 14:31:07 -0000	1.269
+++ src/pager.c	29 Jun 2006 16:23:24 -0000
@@ -1923,7 +1923,7 @@
 
   ppPg = &pPager->pAll;
   while( (pPg = *ppPg)!=0 ){
-    if( pPg->pgno<=dbSize ){
+    if( pPg->pgno<=dbSize && pPg->pgno != 0 ){
       ppPg = &pPg->pNextAll;
     }else if( pPg->nRef>0 ){
       memset(PGHDR_TO_DATA(pPg), 0, pPager->pageSize);