-shm grows with large transaction

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

-shm grows with large transaction

Kim Gräsman
Hi again,

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
to replace
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 [1] where it indicates
there's a page correlation -- is it growing so much because we're
touching so many pages?

SQLite v.3.14.1.

Thanks,
- Kim

[1] https://www.sqlite.org/fileformat2.html#walindexformat
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: -shm grows with large transaction

Richard Hipp-3
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?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: -shm grows with large transaction

Richard Hipp-3
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 guess)

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: -shm grows with large transaction

Kim Gräsman
Den 26 apr. 2017 3:45 em skrev "Richard Hipp" <[hidden email]>:

> 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
> guess)
>

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
row/page.

I never got the sense that the shm size was directly proportional to number
of pages modified, but I can see that now.

Thanks,
- Kim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: -shm grows with large transaction

Dominique Devienne
On Wed, Apr 26, 2017 at 5:34 PM, Kim Gräsman <[hidden email]> wrote:

> Den 26 apr. 2017 3:45 em skrev "Richard Hipp" <[hidden email]>:
>
> > On 4/26/17, Richard Hipp <[hidden email]> wrote:
> > >  That would imply you are changing about 5 million pages.
>


> 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
> row/page.
>

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? --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: -shm grows with large transaction

Kim Gräsman
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
>> row/page.
>
> 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.

Thanks,
- Kim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users