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. @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
