I've been experimenting with limiting memory usage in our SQLite-based
app. Ran into an unrelated oddity that I thought I'd ask about:
We're running a couple of massive upgrade steps on over 5 million
quite large (70+ columns) rows.
There are two unrelated steps;
1) DROP COLUMN-replacement where all data is copied to a table with
the new schema, the old table is dropped, and the new table is renamed
2) UPDATE statement of all rows
These are obviously huge transactions, so I was expecting to see the
WAL file grow to about the same size as the original database.
But for some reason, the WAL-index (-shm) file also grows to about
40MiB in size. From the docs, I've got the impression that it would
typically stay at around 32KiB. Does this seem normal? I just ran into
some high-level documentation for the WAL-index  where it indicates
there's a page correlation -- is it growing so much because we're
touching so many pages?
On 4/26/17, Kim Gräsman <[hidden email]> wrote:
> But for some reason, the WAL-index (-shm) file also grows to about
> 40MiB in size. From the docs, I've got the impression that it would
> typically stay at around 32KiB. Does this seem normal?
The -shm file is an in-memory hash table, shared by all processes
accessing the database, used to quickly locate pages that have
previously been written into the WAL file. The size of the -shm
depends on the number of pages in the -wal file.
Each entry requires 8 bytes. So if you take the total number of pages
in the database that are being modified, multiply by 8 and round up to
the next multiple of 32K, that will be the -shm file (approximately -
there is some small adjustment for a header on the first page o the
-shm which we shall ignore here).
40MiB seems like a lot. That would imply you are changing about a
half million pages of your database inside a single transaction.
What is your page size, and how big does the -wal file get?
> On 4/26/17, Richard Hipp <[hidden email]> wrote:
> > That would imply you are changing about a
> > half million pages of your database inside a single transaction.
> Correction: About 5 million pages. Missed a zero. (Time for coffee, I
Always time for coffee.
Great, that means the numbers add up. This is a monster transaction
updating 5M rows, and page size is 512 bytes, so I think we have roughly 1
I never got the sense that the shm size was directly proportional to number
of pages modified, but I can see that now.
On Wed, Apr 26, 2017 at 5:58 PM, Dominique Devienne <[hidden email]> wrote:
> On Wed, Apr 26, 2017 at 5:34 PM, Kim Gräsman <[hidden email]> wrote:
>> Great, that means the numbers add up. This is a monster transaction
>> updating 5M rows, and page size is 512 bytes, so I think we have roughly 1
> Which such a small page size though? What's the native filesystem page size
> in your situation?
> I suspect having a single row per page (or worse, fewer than 1 per page) is
> not ideal perf-wise, no?
Yes, the small page size was chosen for other reasons, I think we have
some work to do to find a better size here.
But that's not really germane to this, I just wanted to understand why
the -shm grew so large, and now I understand the maths better.