Re: [sqlite] Quick way to determine optimal page size?
On 2 Aug 2019, at 8:55pm, Jen Pollock wrote: > Compressing the backups would likely save you a lot more space, and I > suspect it wouldn't be affected that much by page size; presumably empty > space at the ends of pages will compress very well. It might be an interesting exersize to compare the sizes of these files: A) size of .sqlite file B) use the shell tool to dump SQL commands as .sql C) compress (A) using some standard compression tool D) compress (B) using the same tool Results could differ depending on the proportion of the data which is numeric. It's the sort of thing I'm sure I would have tried years ago but I don't remember what results I got. And I no longer have access to big SQLite databases. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quick way to determine optimal page size?
Compressing the backups would likely save you a lot more space, and I
suspect it wouldn't be affected that much by page size; presumably empty
space at the ends of pages will compress very well.
Jen Pollock
On Thu, Aug 01, 2019 at 12:48:27AM +0300, Tony Papadimitriou wrote:
> Tens of databases (from a few MBs to almost GB), so it's good to keep them
> at their minimum size (for disk and backup savings).
>
> I often save several megabytes by going to the 'right' size, eg., just today
> I went from ~110MB down to ~80MB in one of them ('vacuum'ed before and after
> so it's just the page size making this difference). Sometimes, very small
> page sizes give best results, sometimes the other way around.
>
> Some databases do well in the same page size as new data is added, but for
> some others you need to recalculate as their content changes.
> Still, you can't know in advance which ones can do better unless you
> actually try it. And, that's the main problem.
> I have to try with ~100 DBs to get a significant benefit in just a few of
> them (about 5-10), until next time.
>
> Anyway, I thought I'd ask.
>
> -Original Message- From: David Raymond
> Sent: Wednesday, July 31, 2019 10:48 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Quick way to determine optimal page size?
>
> Not that I'm aware of no. How much of a difference are you seeing for your
> database size depending on the page size you try?
>
> -Original Message-
> From: sqlite-users On Behalf
> Of Tony Papadimitriou
> Sent: Wednesday, July 31, 2019 3:29 PM
> To: General Discussion of SQLite Database
>
> Subject: [sqlite] Quick way to determine optimal page size?
>
> Instead of brute force “pragma page_size=xxx; vacuum;” for each page size
> and each database to determine which one produces the smallest file, is
> there some quicker way?
>
> Thanks.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quick way to determine optimal page size?
Tens of databases (from a few MBs to almost GB), so it's good to keep them
at their minimum size (for disk and backup savings).
I often save several megabytes by going to the 'right' size, eg., just today
I went from ~110MB down to ~80MB in one of them ('vacuum'ed before and after
so it's just the page size making this difference). Sometimes, very small
page sizes give best results, sometimes the other way around.
Some databases do well in the same page size as new data is added, but for
some others you need to recalculate as their content changes.
Still, you can't know in advance which ones can do better unless you
actually try it. And, that's the main problem.
I have to try with ~100 DBs to get a significant benefit in just a few of
them (about 5-10), until next time.
Anyway, I thought I'd ask.
-Original Message-
From: David Raymond
Sent: Wednesday, July 31, 2019 10:48 PM
To: SQLite mailing list
Subject: Re: [sqlite] Quick way to determine optimal page size?
Not that I'm aware of no. How much of a difference are you seeing for your
database size depending on the page size you try?
-Original Message-
From: sqlite-users On Behalf
Of Tony Papadimitriou
Sent: Wednesday, July 31, 2019 3:29 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Quick way to determine optimal page size?
Instead of brute force “pragma page_size=xxx; vacuum;” for each page size
and each database to determine which one produces the smallest file, is
there some quicker way?
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quick way to determine optimal page size?
On 31 Jul 2019, at 8:28pm, Tony Papadimitriou wrote: > Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and > each database to determine which one produces the smallest file, is there > some quicker way? It might be faster to make a new file, set page size, ATTACH the old file, and use the INSERT INTO ... (SELECT * FROM) syntax to make new database files rather than repeatedly use VACUUM to reorganise the old one. Once you know which pagesize gave the best result, you can delete the old one. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quick way to determine optimal page size?
Not that I'm aware of no. How much of a difference are you seeing for your database size depending on the page size you try? -Original Message- From: sqlite-users On Behalf Of Tony Papadimitriou Sent: Wednesday, July 31, 2019 3:29 PM To: General Discussion of SQLite Database Subject: [sqlite] Quick way to determine optimal page size? Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and each database to determine which one produces the smallest file, is there some quicker way? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Quick way to determine optimal page size?
Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and each database to determine which one produces the smallest file, is there some quicker way? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
