SQLite Database Maintenance

WA
Question ...

Does the SQLite database ever need compaction to recover space once used by deleted items? If so, how is compaction done?

(Dan Stillman requested that a new discussion be started, hence this discussion.)
  • WA
    I would appreciate some insight about managing Zotero.

    Yesterday, I noticed something unexpected when using Zotero and monitoring memory usage via "Process Explorer (www.sysinternals.com). I ran "out of memory" on a 3 GB system and 300 MB allocated to Firefox cache. The system had been idle a few hours.

    Today,
    Firefox initially had a "working set" of 126 MB.
    Loading the Zotero panel brought the working set to 133 MB.
    After using Zotero for about a dozen references, the working set rose to 221 MB.

    zotero.sqlite is 95 MB.
    Zotero storage is 1.67 GB (1,608 folders; 64,211 files)
  • adamsmith
    you don't really need to manage Zotero and certainly shouldn't be doing anything to the SQlite db.
    Firefox is well known to use up a good amount of memory and memory usage in excess of 300MB is quite common, especially when you're using a lot of js - with 3G ram I don't really see the problem, though.
  • WA
    Adamsmith,
    Thank you for your insight that this is normal. I had been concerned that there might have been a memory leak somewhere. I had run into an "Out Of Memory" case many years ago while parsing Cobol data divisions using a MS product.

    I'm really looking forward to using the stable Zotero 2 on Firefox 3.6. You have an amazing application in Zotero. Thank you all!

    (P.S. I should have mentioned before that my page file is 4 GB.)
  • dstillman
    edited February 16, 2010
    Just to be clear, adamsmith isn't saying that an "Out of memory" error is normal. It's clearly not. What's normal is Firefox using 300MB of RAM, whether or not it has Zotero installed. So the cause of your memory issues may lie elsewhere.
  • WA
    Dan Stillman: Sorry I wasn't clear. I should have written "normal for Firefox".
    In support of memory issues lying elsewhere, I cited the experience with the MS product. The underlying cause was a stack overflow, not the MS product capability or parsing logic.
  • WA
    My new question is: Is there any risk to Zotero data if the PC is defragmented (not compacted)? Fragmentation will slow down a PC.

    It is taking 2 to 3 minutes to capture certain simple web pages. This, on an Optiplex 3010 with 8GB memory and a 3.2 Ghz processor. The PC is direct wired to the router.

    Restore of Zotero in February took about 8 hours. Zotero.sqlite is around 0.6 GB. "Storage" is somewhat over 18 GB on disc. In "storage", there were about 6935 folders and 772,940 files recently.

    The date of the folders was reset to the date they were restored; a binary search on the index might work.

    Thoughts?
  • noksagt
    Rather than answer your explicit question, I'll attempt to address what seems to be the issue. Your reason for asking this seems to be concern over performance. There are several threads on this.

    See, e.g. https://forums.zotero.org/discussion/39671/zotero-best-practices-and-pitfalls-to-avoid-for-best-performance/

    Consider collapsing the tag selector and disabling full-text indexing to see if you can exclude common reasons for performance issues of adding a new reference.
  • WA
    Thanks. Will do. 8-)
  • WA
    However, I would still like to know whether defragmentation is "safe" with respect to the sqlite database. I assume that it would be safe to "storage".
  • adamsmith
    defragmentation is safe for any file. It just affects where things are stored on your physical harddisk.
  • WA
    Thanks!! I asked because I have only a surface understanding of Zotero's design, apart from sqlite carrying a reference to items in "Storage".

    Long ago, I learned that a fragmented drive has a performance penalty.
Sign In or Register to comment.