Quick way to figure SQLite database block size per table & indexes

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

Quick way to figure SQLite database block size per table & indexes

Eric Grange-3
Hi,

Is there a quick way (as in speed) to obtain the number of database blocks
(or kilobytes) allocated for each table and each index ?

I have been using various manual approaches so far (from using length() to
vacuum and drops), but none of them are really fast
or can practical to automate.

Eric
_______________________________________________
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: Quick way to figure SQLite database block size per table & indexes

David Yip
Hi Eric,

If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you
can query the dbstat virtual table for the number of pages used per table
and index, e.g.

    SELECT name, COUNT(path) AS pages FROM dbstat GROUP BY name ORDER BY
pages DESC;

This runs in ~600 ms on one of my applications, which isn't that fast, but
I've had success placing the read on a background thread.  (Out of
curiosity, how quick do you need the report to be?)

The dbstat virtual table manual (https://www.sqlite.org/dbstat.html) has
some more examples and a longer description of the contents of the dbstat
table.

- David

On Mon, Jul 30, 2018 at 1:46 AM, Eric Grange <[hidden email]> wrote:

> Hi,
>
> Is there a quick way (as in speed) to obtain the number of database blocks
> (or kilobytes) allocated for each table and each index ?
>
> I have been using various manual approaches so far (from using length() to
> vacuum and drops), but none of them are really fast
> or can practical to automate.
>
> Eric
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: Quick way to figure SQLite database block size per table & indexes

David Burgess-2
In reply to this post by Eric Grange-3
download sqlite3_analyzer
from
http://www2.sqlite.org/2018/sqlite-tools-linux-x86-3240000.zip



On Mon, Jul 30, 2018 at 4:46 PM, Eric Grange <[hidden email]> wrote:

> Hi,
>
> Is there a quick way (as in speed) to obtain the number of database blocks
> (or kilobytes) allocated for each table and each index ?
>
> I have been using various manual approaches so far (from using length() to
> vacuum and drops), but none of them are really fast
> or can practical to automate.
>
> Eric
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

On Mon, Jul 30, 2018 at 4:46 PM, Eric Grange <[hidden email]> wrote:

> Hi,
>
> Is there a quick way (as in speed) to obtain the number of database blocks
> (or kilobytes) allocated for each table and each index ?
>
> I have been using various manual approaches so far (from using length() to
> vacuum and drops), but none of them are really fast
> or can practical to automate.
>
> Eric
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
D Burgess
_______________________________________________
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: Quick way to figure SQLite database block size per table & indexes

Eric Grange
@David Yip
> If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you
> can query the dbstat virtual table for the number of pages used per table and
index, e.g.

Thanks, I did not know about that module, however it is a bit slow, on a 7
GB database I have, it takes
almost 5 minutes, and I have larger databases :/
So it's definitely something that would have to run in the background or
night.

Also, I do not know if it's intentional, but the precompiled sqlite3.dll
from www.sqlite.org has the module
built-in for Win64, but not for Win32.


@D Burgess
> download sqlite3_analyzer

Thanks, the text output is interesting with built-in documentation.


On Mon, Jul 30, 2018 at 9:06 AM, D Burgess <[hidden email]> wrote:

> download sqlite3_analyzer
> from
> http://www2.sqlite.org/2018/sqlite-tools-linux-x86-3240000.zip
>
>
>
> On Mon, Jul 30, 2018 at 4:46 PM, Eric Grange <[hidden email]> wrote:
> > Hi,
> >
> > Is there a quick way (as in speed) to obtain the number of database
> blocks
> > (or kilobytes) allocated for each table and each index ?
> >
> > I have been using various manual approaches so far (from using length()
> to
> > vacuum and drops), but none of them are really fast
> > or can practical to automate.
> >
> > Eric
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> On Mon, Jul 30, 2018 at 4:46 PM, Eric Grange <[hidden email]> wrote:
> > Hi,
> >
> > Is there a quick way (as in speed) to obtain the number of database
> blocks
> > (or kilobytes) allocated for each table and each index ?
> >
> > I have been using various manual approaches so far (from using length()
> to
> > vacuum and drops), but none of them are really fast
> > or can practical to automate.
> >
> > Eric
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> D Burgess
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: Quick way to figure SQLite database block size per table & indexes

Dominique Devienne
On Mon, Jul 30, 2018 at 10:11 AM Eric Grange <[hidden email]> wrote:

> @David Yip
> > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you
> > can query the dbstat virtual table for the number of pages used per
> table and index, e.g.
>
> Thanks, I did not know about that module, however it is a bit slow, on a 7
> GB database I have, it takes almost 5 minutes, and I have larger databases
> :/
> So it's definitely something that would have to run in the background or
> night.
>

Well, you probably already know that in SQLite, there's a 100 bytes header,
then N fixed-sized pages,
where the page size is configurable from 512 bytes to 64KB by powers of 2.
The first page contains
the sqlite_master table, which references the "root" page of each table and
index. But that's it. To know
how many pages each table/index uses, SQLite (or any other tool) will need
to read each object's root page,
and traverse the b-tree of pages rooted at that first page (and their
overflow pages). Then repeat for each object.
Thus in most cases, all pages of the DB will need to be "paged", i.e. read,
so that's a lot of IO, so not quite fast.

If your DB has auto or incremental vacuum configured, you've have special
PtrMap pages.


@D Burgess
> > download sqlite3_analyzer
>
> Thanks, the text output is interesting with built-in documentation.


If I recall correctly, sqlite3_analyzer's output is based on the dbstat
vtable, so it's unlikely to be faster I assume. --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: Quick way to figure SQLite database block size per table & indexes

Dominique Devienne
Oops, sent too early...

On Mon, Jul 30, 2018 at 10:29 AM Dominique Devienne <[hidden email]>
wrote:

> On Mon, Jul 30, 2018 at 10:11 AM Eric Grange <[hidden email]> wrote:
>
>> @David Yip
>> > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you
>> > can query the dbstat virtual table for the number of pages used per
>> table and index, e.g.
>>
>> Thanks, I did not know about that module, however it is a bit slow, on a 7
>> GB database I have, it takes almost 5 minutes, and I have larger
>> databases :/
>> So it's definitely something that would have to run in the background or
>> night.
>>
>
> Well, you probably already know that in SQLite, there's a 100 bytes
> header, then N fixed-sized pages,
> where the page size is configurable from 512 bytes to 64KB by powers of 2.
> The first page contains
> the sqlite_master table, which references the "root" page of each table
> and index. But that's it. To know
> how many pages each table/index uses, SQLite (or any other tool) will need
> to read each object's root page,
> and traverse the b-tree of pages rooted at that first page (and their
> overflow pages). Then repeat for each object.
> Thus in most cases, all pages of the DB will need to be "paged", i.e.
> read, so that's a lot of IO, so not quite fast.
>
> If your DB has auto or incremental vacuum configured, you've have special
> PtrMap pages.
>

See https://www.sqlite.org/fileformat.html#pointer_map_or_ptrmap_pages

Perhaps thanks to those, you'd be able to figure how many pages per object
faster,
from just those PtrMap pages, and the root pages from sqlite_master. But
then you'd need
to figure this out yourself. And accept the price of SQLite
maintaining/updating those pages
for you during "normal" DMLs. I don't know what the overhead is exactly, on
average. But it
can't be faster than not maintaining those pages for sure :). FWIW.


>
> @D Burgess
>> > download sqlite3_analyzer
>>
>> Thanks, the text output is interesting with built-in documentation.
>
>
> If I recall correctly, sqlite3_analyzer's output is based on the dbstat
> vtable, so it's unlikely to be faster I assume. --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: Quick way to figure SQLite database block size per table & indexes

Eric Grange
PtrMap pages may be too much overhead in my case, I have occasionally run
vacuum
on same databases to see the effect, and it was not very significant.

This is likely because the databases are heavily skewed towards inserting
(and indexing)
data than about update/delete, and while the tables are quite fragmented,
since I am using SSDs,
the gains from a vacuum defragmentation appears marginal.

Eric



On Mon, Jul 30, 2018 at 10:33 AM, Dominique Devienne <[hidden email]>
wrote:

> Oops, sent too early...
>
> On Mon, Jul 30, 2018 at 10:29 AM Dominique Devienne <[hidden email]>
> wrote:
>
> > On Mon, Jul 30, 2018 at 10:11 AM Eric Grange <[hidden email]>
> wrote:
> >
> >> @David Yip
> >> > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB),
> you
> >> > can query the dbstat virtual table for the number of pages used per
> >> table and index, e.g.
> >>
> >> Thanks, I did not know about that module, however it is a bit slow, on
> a 7
> >> GB database I have, it takes almost 5 minutes, and I have larger
> >> databases :/
> >> So it's definitely something that would have to run in the background or
> >> night.
> >>
> >
> > Well, you probably already know that in SQLite, there's a 100 bytes
> > header, then N fixed-sized pages,
> > where the page size is configurable from 512 bytes to 64KB by powers of
> 2.
> > The first page contains
> > the sqlite_master table, which references the "root" page of each table
> > and index. But that's it. To know
> > how many pages each table/index uses, SQLite (or any other tool) will
> need
> > to read each object's root page,
> > and traverse the b-tree of pages rooted at that first page (and their
> > overflow pages). Then repeat for each object.
> > Thus in most cases, all pages of the DB will need to be "paged", i.e.
> > read, so that's a lot of IO, so not quite fast.
> >
> > If your DB has auto or incremental vacuum configured, you've have special
> > PtrMap pages.
> >
>
> See https://www.sqlite.org/fileformat.html#pointer_map_or_ptrmap_pages
>
> Perhaps thanks to those, you'd be able to figure how many pages per object
> faster,
> from just those PtrMap pages, and the root pages from sqlite_master. But
> then you'd need
> to figure this out yourself. And accept the price of SQLite
> maintaining/updating those pages
> for you during "normal" DMLs. I don't know what the overhead is exactly, on
> average. But it
> can't be faster than not maintaining those pages for sure :). FWIW.
>
>
> >
> > @D Burgess
> >> > download sqlite3_analyzer
> >>
> >> Thanks, the text output is interesting with built-in documentation.
> >
> >
> > If I recall correctly, sqlite3_analyzer's output is based on the dbstat
> > vtable, so it's unlikely to be faster I assume. --DD
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: Quick way to figure SQLite database block size per table & indexes

Dominique Devienne
On Mon, Jul 30, 2018 at 11:42 AM Eric Grange <[hidden email]> wrote:

> PtrMap pages may be too much overhead in my case, I have occasionally run
> vacuum on same databases to see the effect, and it was not very
> significant.
>
> This is likely because the databases are heavily skewed towards inserting
> (and indexing) data than about update/delete, and while the tables are
> quite fragmented,
> since I am using SSDs, the gains from a vacuum defragmentation appears
> marginal.
>

I was merely suggesting that, if the overhead of PtrMap pages (i.e. using
auto-vacuum or incremental-vacumm),
in terms of added disk-space (for those extra-pages) and extra processing,
is acceptable to you,
they likely could lead to a (much?) faster how-many-DB-pages per table or
index than dbstat.
Depends how important it's to you to getting those pages-per-table stats
faster than dbstat gives them to you.
At the expense of you having to code it ourself of course.

Ideally, dbstat would also have be able to use PtrMap as well, to speed up
that query.
But since it does much more than giving you that page-count, maybe it's not
practical. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users