Quick way to determine optimal page size?

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

Quick way to determine optimal page size?

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

Re: Quick way to determine optimal page size?

David Raymond
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 <[hidden email]> On Behalf Of Tony Papadimitriou
Sent: Wednesday, July 31, 2019 3:29 PM
To: General Discussion of SQLite Database <[hidden email]>
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
[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 determine optimal page size?

Simon Slavin-3
In reply to this post by Tony Papadimitriou
On 31 Jul 2019, at 8:28pm, Tony Papadimitriou <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quick way to determine optimal page size?

Tony Papadimitriou
In reply to this post by David Raymond
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 <[hidden email]> On Behalf
Of Tony Papadimitriou
Sent: Wednesday, July 31, 2019 3:29 PM
To: General Discussion of SQLite Database
<[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quick way to determine optimal page size?

Jen Pollock
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 <[hidden email]> On Behalf
> Of Tony Papadimitriou
> Sent: Wednesday, July 31, 2019 3:29 PM
> To: General Discussion of SQLite Database
> <[hidden email]>
> 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
> [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 determine optimal page size?

Simon Slavin-3
On 2 Aug 2019, at 8:55pm, Jen Pollock <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users