Oops, sent too early... On Mon, Jul 30, 2018 at 10:29 AM Dominique Devienne <ddevie...@gmail.com> wrote:
> On Mon, Jul 30, 2018 at 10:11 AM Eric Grange <egra...@glscene.org> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
