[Digikam-devel] [Bug 216895] New: Improve performance and reduce space with some sqlite3 pragma's

[Scott Crosby]

https://bugs.kde.org/show_bug.cgi?id=216895

           Summary: Improve performance and reduce space with some sqlite3
                    pragma's
           Product: digikam
           Version: unspecified
          Platform: Debian testing
        OS/Version: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: general
        AssignedTo: digikam-devel at kde.org
        ReportedBy: scrosby at cs.rice.edu


Version:           1.0.0beta6 (using KDE 4.3.2)
OS:                Linux
Installed from:    Debian testing/unstable Packages

You have an awesome program that I enjoy using. Just a couple of things you can
do to make it better. The default setup of sqlite uses a 1kb page size. This
can lead to a lot of wasted space in the large tuples that digikam uses.
Increasing the pagesize to 4kb reduces the total database size by a third. I
used 4kb because it matches the normal intel page size and larger page sizes
have marginal benefit (<1% space savings). I also suspect this page size comes
with some performance benefits.

For existing databases (and recent versions of sqlite3):
   'pragma page_size = 4096 ; vacuum;'

When creating a new database:
   'pragma page_size = 4096;' before the first table.

Also, in order to run in limited environments, sqlite only lets itself use a
default of at most of 2000 pages worth of data for caching or internal sorts. I
propose increasing this to 8k-16k pages (32-64mb). Modern machines have a lot
of RAM. I've not benchmarked if there is any speed improvement, but I cannot
see how giving sqlite a few more megabytes of memory can hurt.

Can be run on any existing or new database:
   'pragma default_cache_size=16000;'

-- 
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.