[elephant-devel] sqite3 - always increase database size

[Aaron Feng]

Has anyone experienced the following:

*** Original question was sent to Ian Eslick as follows:

I noticed when I add and remove an item from a btree, the size of database
always increases.  Is this the expected behavior?  I wrote a simple example
below to illustrate what I'm seeing:

(in-package :cl-user)

(defpackage :test-elephant
  (:use :common-lisp))

(in-package :test-elephant)

(require :elephant)

(defvar *store-spec* '(:clsql (:sqlite3 "/Users/afeng/tmp/test.db")))

(ele:defpclass test ()
  ((name :initarg :name
     :Accessor name)))

(ele:with-open-store (*store-spec*)
  (loop for i from 1 to 100 do
       (ele:add-to-root "aaron" (make-instance 'test :name "aaron"))
       (ele:remove-from-root "aaron")
       (ele:root-existsp "aaron")))

*** end of question

*** Ian's reply:

Databases are allocated in pages.  When a page becomes free, the disk space
for that page isn't reclaimed, but instead reused in future page
allocations.  The only way to recover all the empty disk space is to migrate
to a new store and delete the old one.  This effectively compacts the DB.

Elephant does not actually manage the page allocation - that's all being
done by the underlying data store (i.e. SQLITE).

You might look for more information on SQLITE's allocation policy.  One
thing to consider is that each add/delete operation is stored in a
transaction log file.  Until the DB is checkpointed, the transaction history
is maintained on disk for future recovery, so there is some linear growth in
storage.  BDB separates DB storage from log files, I think SQLite puts them
all in the same file.

**** end of reply

If theory is correct, executing the loop more than once should cause the
database size to stay relatively the same after the first run.  However,
each time I re-execute the loop, the size always increase by a good factor.
I do not think it's the transaction log file because of the increment amount
seemed large.  I also tried to insert and delete directly in the database,
this method does seem to re-use already allocated pages.  Am I doing
something wrong?

Thanks,

Aaron
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.common-lisp.net/pipermail/elephant-devel/attachments/20080408/ba26fa3c/attachment.html>