Re: [sqlite] page_size: bug with PRAGMA or documentation?

[Rowan Worth]

Thanks Richard, that's much clearer.

Just one thing: "The page_size pragma will only set in the page size if ..."

The "in" looks out of place :)
-Rowan


On 12 September 2016 at 19:43, Richard Hipp  wrote:

> On 9/12/16, Rowan Worth  wrote:
> > The docs for PRAGMA page_size say that it is effective if issued "prior
> to
> > the first CREATE statement".
>
> Fixed at https://www.sqlite.org/draft/pragma.html#pragma_page_size
>
> This will be pushed to the main website at the next release.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] page_size: bug with PRAGMA or documentation?

[Richard Hipp]

On 9/12/16, Rowan Worth  wrote:
> The docs for PRAGMA page_size say that it is effective if issued "prior to
> the first CREATE statement".

Fixed at https://www.sqlite.org/draft/pragma.html#pragma_page_size

This will be pushed to the main website at the next release.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size: bug with PRAGMA or documentation?

[Hick Gunter]

I guess that selecting from sqlite_master issues an implicit create, and 
querying/updateing something from the file header (i.e. several PRAGMAs) would 
also imply writing the first page of the file (with the current setting of 
page_size) too.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Rowan Worth
Gesendet: Montag, 12. September 2016 12:14
An: General Discussion of SQLite Database <sqlite-users@mailinglists.sqlite.org>
Betreff: [sqlite] page_size: bug with PRAGMA or documentation?

Hey guys,

The docs for PRAGMA page_size say that it is effective if issued "prior to the 
first CREATE statement". So imagine my surprise when I found page_size to be 
ineffective without ever issuing a CREATE statement!

The sequence goes like so:

$ rm /tmp/lol.db; sqlite3 /tmp/lol.db
SQLite version 3.8.1 2013-10-17 12:57:35
sqlite> BEGIN;
sqlite> select count(*) from sqlite_master;
0
sqlite> pragma page_size = 8192;
sqlite> pragma page_size;
1024

1024? That's not what I asked for! This variant also fails:

$ rm /tmp/lol.db; sqlite3 /tmp/lol.db
SQLite version 3.8.1 2013-10-17 12:57:35
sqlite> BEGIN;
sqlite> pragma schema_version;
0
sqlite> pragma page_size = 8192;
sqlite> pragma page_size;
1024

No create statements in sight, and the behaviour is also present in 3.13.0.

It's not really a big deal to move the page_size pragma earlier, but at the 
very least I think it at least deserves a hint in the documentation that 
operations other than CREATE statements can render it ineffective.

Cheers,
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] page_size: bug with PRAGMA or documentation?

[Rowan Worth]

Hey guys,

The docs for PRAGMA page_size say that it is effective if issued "prior to
the first CREATE statement". So imagine my surprise when I found page_size
to be ineffective without ever issuing a CREATE statement!

The sequence goes like so:

$ rm /tmp/lol.db; sqlite3 /tmp/lol.db
SQLite version 3.8.1 2013-10-17 12:57:35
sqlite> BEGIN;
sqlite> select count(*) from sqlite_master;
0
sqlite> pragma page_size = 8192;
sqlite> pragma page_size;
1024

1024? That's not what I asked for! This variant also fails:

$ rm /tmp/lol.db; sqlite3 /tmp/lol.db
SQLite version 3.8.1 2013-10-17 12:57:35
sqlite> BEGIN;
sqlite> pragma schema_version;
0
sqlite> pragma page_size = 8192;
sqlite> pragma page_size;
1024

No create statements in sight, and the behaviour is also present in 3.13.0.

It's not really a big deal to move the page_size pragma earlier, but at the
very least I think it at least deserves a hint in the documentation that
operations other than CREATE statements can render it ineffective.

Cheers,
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size on ATTACH-ed databases

[Clemens Ladisch]

Ward WIllats wrote:
> sqlite> attach database '/tmp/RareData.db' as rd;   < ATTACH SECOND DB
> sqlite> pragma page_size=512;   <- SET MAIN DB PAGE SIZE
> ...
> sqlite> pragma journal_mode=WAL;

This sets the journal mode of _both_ databases to WAL.
This requires that both database files are actually created.

> sqlite> pragma rd.page_size=4096; <- SET ATTACHED DB PAGE SIZE TO 
> 4K

And now it's too late.


To execute a PRAGMA only on the main database, use:

  PRAGMA main.journal_mode=WAL;


Regars,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size on ATTACH-ed databases

[Scott Robison]

On Tue, Aug 16, 2016 at 12:24 PM, Ward WIllats 
wrote:

> Good thought. But no. In fact, I should have included the line where I
> deleted it before taking the trace below.
>
> ~# rm /tmp/RareData.db
> ~# /usr/local/bin/sqlite3 /opt/foundation/core_db.db
> SQLite version 3.10.1 2016-01-13 21:41:56
>

In addition to the tmp db, are there any other related files (journals, for
example) that might be influencing the page size? I'm just wondering if the
main database is gone but an old journal is hanging around maybe the page
size will be considered set?

I have no idea if that could possibly be an issue. Just something that
occurred to me as I read the email thread.


> > On Aug 16, 2016, at 11:17 AM, Scott Hess  wrote:
> >
> > Is there any possibility that the attached db already existed before
> > you ran this?  Because once a db exists (contains pages) the page size
> > is fixed until you run vacuum.
> >
> > On Tue, Aug 16, 2016 at 10:53 AM, Ward WIllats 
> wrote:
> >>
>  On Aug 12, 2016, at 11:44 PM, Dan Kennedy 
> wrote:
> 
>  On 08/13/2016 01:14 AM, Ward WIllats wrote:
> 
>  Can't reproduce this problem here. Are you able to reproduce it with
> the shell tool?
> 
> >>>
> >>>
> >>> Yes, if I use the shell on our embedded system (OpenWRT/Linux). I
> should have added that:
> >>>
> >>> 1. The main DB in on a JFFS filesystem and we've moved the -shm file
> onto /tmp with a marginally-supported sqlite #define.
> >>> 2. The secondary DB is on /tmp in a RAM FS (which is
> larger/faster/volatile).
> >>>
> >>> We actually issue quite a few pragmas on open. I'll post the whole
> sequence when I can get back to our box.
> >>>
> >>
> >> Here we go:
> >>
> >> ~# /usr/local/bin/sqlite3 /opt/foundation/core_db.db
> >> SQLite version 3.10.1 2016-01-13 21:41:56
> >>
> >> Enter ".help" for usage hints.
> >> sqlite> attach database '/tmp/RareData.db' as rd;   < ATTACH SECOND
> DB
> >> sqlite>
> >> sqlite> pragma page_size=512;   <- SET MAIN DB PAGE SIZE
> >> sqlite> pragma cache_size=200;
> >> sqlite> pragma mmap_size=0;
> >> 0
> >> sqlite> pragma busy_timeout=57000;
> >> 57000
> >> sqlite> pragma foreign_keys=ON;
> >> sqlite> pragma synchronous=normal;
> >> sqlite> pragma journal_mode=WAL;
> >> wal
> >> sqlite> pragma temp_store=2;
> >> sqlite> pragma waL_autocheckpoint=10;
> >> 10
> >> sqlite> pragma journal_size_limit=15360;
> >> 15360
> >> sqlite> pragma auto_vacuum=2;
> >> sqlite> pragma max_page_count=16384;
> >> 16384
> >> sqlite>
> >> sqlite> pragma page_size;
> >> 512   < MAIN DB PAGE SIZE OK
> >> sqlite>
> >> sqlite> pragma rd.page_size=4096; <- SET ATTACHED DB PAGE
> SIZE TO 4K
> >> sqlite> pragma rd.cache_size=32;
> >> sqlite> pragma rd.mmap_size=0;
> >> 0
> >> sqlite> pragma rd.busy_timeout=57000;
> >> 57000
> >> sqlite> pragma rd.foreign_keys=ON;
> >> sqlite> pragma rd.synchronous=normal;
> >> sqlite> pragma rd.journal_mode=WAL;
> >> wal
> >> sqlite> pragma rd.temp_store=2;
> >> sqlite> pragma rd.waL_autocheckpoint=2;
> >> 2
> >> sqlite> pragma rd.journal_size_limit=16384;
> >> 16384
> >> sqlite> pragma rd.auto_vacuum=2;
> >> sqlite> pragma rd.max_page_count=5000;
> >> 5000
> >> sqlite>
> >> sqlite> pragma rd.page_size;
> >> 1024   <-- GET 1K DEFAULT PAGE SIZE
> BACK ON ATTACHED DB, *NOT* 4K SET ABOVE
> >> sqlite>
>
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size on ATTACH-ed databases

[Ward WIllats]

Good thought. But no. In fact, I should have included the line where I deleted 
it before taking the trace below.

~# rm /tmp/RareData.db
~# /usr/local/bin/sqlite3 /opt/foundation/core_db.db
SQLite version 3.10.1 2016-01-13 21:41:56


> On Aug 16, 2016, at 11:17 AM, Scott Hess  wrote:
> 
> Is there any possibility that the attached db already existed before
> you ran this?  Because once a db exists (contains pages) the page size
> is fixed until you run vacuum.
> 
> On Tue, Aug 16, 2016 at 10:53 AM, Ward WIllats  
> wrote:
>> 
 On Aug 12, 2016, at 11:44 PM, Dan Kennedy  wrote:
 
 On 08/13/2016 01:14 AM, Ward WIllats wrote:
 
 Can't reproduce this problem here. Are you able to reproduce it with the 
 shell tool?
 
>>> 
>>> 
>>> Yes, if I use the shell on our embedded system (OpenWRT/Linux). I should 
>>> have added that:
>>> 
>>> 1. The main DB in on a JFFS filesystem and we've moved the -shm file onto 
>>> /tmp with a marginally-supported sqlite #define.
>>> 2. The secondary DB is on /tmp in a RAM FS (which is 
>>> larger/faster/volatile).
>>> 
>>> We actually issue quite a few pragmas on open. I'll post the whole sequence 
>>> when I can get back to our box.
>>> 
>> 
>> Here we go:
>> 
>> ~# /usr/local/bin/sqlite3 /opt/foundation/core_db.db
>> SQLite version 3.10.1 2016-01-13 21:41:56
>> 
>> Enter ".help" for usage hints.
>> sqlite> attach database '/tmp/RareData.db' as rd;   < ATTACH SECOND DB
>> sqlite>
>> sqlite> pragma page_size=512;   <- SET MAIN DB PAGE SIZE
>> sqlite> pragma cache_size=200;
>> sqlite> pragma mmap_size=0;
>> 0
>> sqlite> pragma busy_timeout=57000;
>> 57000
>> sqlite> pragma foreign_keys=ON;
>> sqlite> pragma synchronous=normal;
>> sqlite> pragma journal_mode=WAL;
>> wal
>> sqlite> pragma temp_store=2;
>> sqlite> pragma waL_autocheckpoint=10;
>> 10
>> sqlite> pragma journal_size_limit=15360;
>> 15360
>> sqlite> pragma auto_vacuum=2;
>> sqlite> pragma max_page_count=16384;
>> 16384
>> sqlite>
>> sqlite> pragma page_size;
>> 512   < MAIN DB PAGE SIZE OK
>> sqlite>
>> sqlite> pragma rd.page_size=4096; <- SET ATTACHED DB PAGE SIZE 
>> TO 4K
>> sqlite> pragma rd.cache_size=32;
>> sqlite> pragma rd.mmap_size=0;
>> 0
>> sqlite> pragma rd.busy_timeout=57000;
>> 57000
>> sqlite> pragma rd.foreign_keys=ON;
>> sqlite> pragma rd.synchronous=normal;
>> sqlite> pragma rd.journal_mode=WAL;
>> wal
>> sqlite> pragma rd.temp_store=2;
>> sqlite> pragma rd.waL_autocheckpoint=2;
>> 2
>> sqlite> pragma rd.journal_size_limit=16384;
>> 16384
>> sqlite> pragma rd.auto_vacuum=2;
>> sqlite> pragma rd.max_page_count=5000;
>> 5000
>> sqlite>
>> sqlite> pragma rd.page_size;
>> 1024   <-- GET 1K DEFAULT PAGE SIZE BACK 
>> ON ATTACHED DB, *NOT* 4K SET ABOVE
>> sqlite>
>> 
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size on ATTACH-ed databases

[Scott Hess]

Is there any possibility that the attached db already existed before
you ran this?  Because once a db exists (contains pages) the page size
is fixed until you run vacuum.

On Tue, Aug 16, 2016 at 10:53 AM, Ward WIllats  wrote:
>
>>> On Aug 12, 2016, at 11:44 PM, Dan Kennedy  wrote:
>>>
>>> On 08/13/2016 01:14 AM, Ward WIllats wrote:
>>>
>>> Can't reproduce this problem here. Are you able to reproduce it with the 
>>> shell tool?
>>>
>>
>>
>> Yes, if I use the shell on our embedded system (OpenWRT/Linux). I should 
>> have added that:
>>
>> 1. The main DB in on a JFFS filesystem and we've moved the -shm file onto 
>> /tmp with a marginally-supported sqlite #define.
>> 2. The secondary DB is on /tmp in a RAM FS (which is larger/faster/volatile).
>>
>> We actually issue quite a few pragmas on open. I'll post the whole sequence 
>> when I can get back to our box.
>>
>
> Here we go:
>
> ~# /usr/local/bin/sqlite3 /opt/foundation/core_db.db
> SQLite version 3.10.1 2016-01-13 21:41:56
>
> Enter ".help" for usage hints.
> sqlite> attach database '/tmp/RareData.db' as rd;   < ATTACH SECOND DB
> sqlite>
> sqlite> pragma page_size=512;   <- SET MAIN DB PAGE SIZE
> sqlite> pragma cache_size=200;
> sqlite> pragma mmap_size=0;
> 0
> sqlite> pragma busy_timeout=57000;
> 57000
> sqlite> pragma foreign_keys=ON;
> sqlite> pragma synchronous=normal;
> sqlite> pragma journal_mode=WAL;
> wal
> sqlite> pragma temp_store=2;
> sqlite> pragma waL_autocheckpoint=10;
> 10
> sqlite> pragma journal_size_limit=15360;
> 15360
> sqlite> pragma auto_vacuum=2;
> sqlite> pragma max_page_count=16384;
> 16384
> sqlite>
> sqlite> pragma page_size;
> 512   < MAIN DB PAGE SIZE OK
> sqlite>
> sqlite> pragma rd.page_size=4096; <- SET ATTACHED DB PAGE SIZE TO 
> 4K
> sqlite> pragma rd.cache_size=32;
> sqlite> pragma rd.mmap_size=0;
> 0
> sqlite> pragma rd.busy_timeout=57000;
> 57000
> sqlite> pragma rd.foreign_keys=ON;
> sqlite> pragma rd.synchronous=normal;
> sqlite> pragma rd.journal_mode=WAL;
> wal
> sqlite> pragma rd.temp_store=2;
> sqlite> pragma rd.waL_autocheckpoint=2;
> 2
> sqlite> pragma rd.journal_size_limit=16384;
> 16384
> sqlite> pragma rd.auto_vacuum=2;
> sqlite> pragma rd.max_page_count=5000;
> 5000
> sqlite>
> sqlite> pragma rd.page_size;
> 1024   <-- GET 1K DEFAULT PAGE SIZE BACK 
> ON ATTACHED DB, *NOT* 4K SET ABOVE
> sqlite>
>
> ___
> 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] page_size on ATTACH-ed databases

[Ward WIllats]

>> On Aug 12, 2016, at 11:44 PM, Dan Kennedy  wrote:
>> 
>> On 08/13/2016 01:14 AM, Ward WIllats wrote:
>> 
>> Can't reproduce this problem here. Are you able to reproduce it with the 
>> shell tool?
>> 
> 
> 
> Yes, if I use the shell on our embedded system (OpenWRT/Linux). I should have 
> added that:
> 
> 1. The main DB in on a JFFS filesystem and we've moved the -shm file onto 
> /tmp with a marginally-supported sqlite #define.
> 2. The secondary DB is on /tmp in a RAM FS (which is larger/faster/volatile).
> 
> We actually issue quite a few pragmas on open. I'll post the whole sequence 
> when I can get back to our box.
> 

Here we go:

~# /usr/local/bin/sqlite3 /opt/foundation/core_db.db
SQLite version 3.10.1 2016-01-13 21:41:56

Enter ".help" for usage hints.
sqlite> attach database '/tmp/RareData.db' as rd;   < ATTACH SECOND DB
sqlite> 
sqlite> pragma page_size=512;   <- SET MAIN DB PAGE SIZE
sqlite> pragma cache_size=200;
sqlite> pragma mmap_size=0;
0
sqlite> pragma busy_timeout=57000;
57000
sqlite> pragma foreign_keys=ON;
sqlite> pragma synchronous=normal;
sqlite> pragma journal_mode=WAL;
wal
sqlite> pragma temp_store=2;
sqlite> pragma waL_autocheckpoint=10;
10
sqlite> pragma journal_size_limit=15360;
15360
sqlite> pragma auto_vacuum=2;
sqlite> pragma max_page_count=16384;
16384
sqlite> 
sqlite> pragma page_size;
512   < MAIN DB PAGE SIZE OK
sqlite> 
sqlite> pragma rd.page_size=4096; <- SET ATTACHED DB PAGE SIZE TO 4K
sqlite> pragma rd.cache_size=32;
sqlite> pragma rd.mmap_size=0;
0
sqlite> pragma rd.busy_timeout=57000;
57000
sqlite> pragma rd.foreign_keys=ON;
sqlite> pragma rd.synchronous=normal;
sqlite> pragma rd.journal_mode=WAL;
wal
sqlite> pragma rd.temp_store=2;
sqlite> pragma rd.waL_autocheckpoint=2;
2
sqlite> pragma rd.journal_size_limit=16384;
16384
sqlite> pragma rd.auto_vacuum=2;
sqlite> pragma rd.max_page_count=5000;
5000
sqlite> 
sqlite> pragma rd.page_size;
1024   <-- GET 1K DEFAULT PAGE SIZE BACK ON 
ATTACHED DB, *NOT* 4K SET ABOVE
sqlite> 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size on ATTACH-ed databases

[Ward WIllats]

> On Aug 12, 2016, at 11:44 PM, Dan Kennedy  wrote:
> 
> On 08/13/2016 01:14 AM, Ward WIllats wrote:
> 
> Can't reproduce this problem here. Are you able to reproduce it with the 
> shell tool?
> 


Yes, if I use the shell on our embedded system (OpenWRT/Linux). I should have 
added that:

1. The main DB in on a JFFS filesystem and we've moved the -shm file onto /tmp 
with a marginally-supported sqlite #define.
2. The secondary DB is on /tmp in a RAM FS (which is larger/faster/volatile).

We actually issue quite a few pragmas on open. I'll post the whole sequence 
when I can get back to our box.

Thanks for taking the the time with this.

-- Ward

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size on ATTACH-ed databases

[Dan Kennedy]

On 08/13/2016 01:14 AM, Ward WIllats wrote:

Consider:

1. Create a new database, set the pragma page_size=512

2. Create a new database on the connection with ATTACH DATABASE 
'/tmp/number_two.db' AS second;

3. Issue pragma second.page_size=4096  to try and set the page size on the 
attached DB to 4096.

4. Read back with pragma second.page_size and get the default page size of 
1024. (We are still on 3.10.1)

Is it expected that the 4096 did not "stick?" Is there some relationship 
between page sizes in a main and attached DB?


Can't reproduce this problem here. Are you able to reproduce it with the 
shell tool?



  $ ./sqlite3 x.db
  SQLite version 3.10.1 2016-01-13 21:41:56
  Enter ".help" for usage hints.
  sqlite> PRAGMA main.page_size = 512;
  sqlite> ATTACH '/tmp/two.db' AS second;
  sqlite> PRAGMA second.page_size = 4096;
  sqlite> PRAGMA second.page_size;
  4096
  sqlite> PRAGMA second.journal_mode = wal;
  wal
  sqlite> CREATE TABLE second.t1(a, b);
  sqlite> PRAGMA second.page_size;
  4096
  sqlite> PRAGMA main.page_size;
  512

Dan.



Thanks

-- Ward

(In real life, we also set journal_mode=WAL on both databases AFTER issuing the 
page_size pragma, in case it makes any difference.)





___
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] page_size on ATTACH-ed databases

[Ward WIllats]

Consider:

1. Create a new database, set the pragma page_size=512

2. Create a new database on the connection with ATTACH DATABASE 
'/tmp/number_two.db' AS second;

3. Issue pragma second.page_size=4096  to try and set the page size on the 
attached DB to 4096.

4. Read back with pragma second.page_size and get the default page size of 
1024. (We are still on 3.10.1)

Is it expected that the 4096 did not "stick?" Is there some relationship 
between page sizes in a main and attached DB?

Thanks

-- Ward

(In real life, we also set journal_mode=WAL on both databases AFTER issuing the 
page_size pragma, in case it makes any difference.)





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Page_size

[Olivier Mascia]

> Le 29 f?vr. 2016 ? 12:34, Simon Slavin  a ?crit :
> 
> On 29 Feb 2016, at 9:14am, Olivier Mascia  wrote:
> 
>> we have found the sweet spot (for us, and for now) to be 4K page sizes, 
>> which just happen to be the virtual memory page size of Windows system
> 
> Yes.  That's the idea.  Match the page size of the database to the size of 
> the chunks your operating system is using.  It can be 1K.  It can be 16K.
> 
> It might be useful for other readers to know what experimenting you used to 
> find your 'sweet spot'.  Was it simple an obvious or did you have to play 
> tricks ?
> 
> Simon.

Short story: quite simple and obvious as soon as we tested on real life 
scenarios. Tricks (like playing fool with sync) were out of question anyway.

We test-migrated about 100 real-life databases of our software over these last 
weeks, with sizes ranging from about 200 MB to about 4 GB and measured the time 
for complete migration: creating schema, pumping data, check referential 
integrity after the fact, adding additional required indexes, and running 
analyze on the resulting DBs.  We then also compared some results from 
sqlite3_analyzer. The overall tests was run with various page sizes and cache 
sizes for about 50 of those databases.

Then we checked the results by actually running typical tasks of the software 
to get sure it fitted our usual performance level (or was better) by comparing 
to current results (not using SQLite).

The results (4 K) is of course closely related to our specific datasets and we 
were focused on short-bursts write performance, without using any integrity 
degradation tricks and with the added comfort of writer does not block readers 
that wal provides. The easiest way to tune for pure read performance would be 
to use memory mapping mode (and 4K pages to match the virtual memory physical 
page size), but we don't need that added boost for reads, they are already very 
good as such.

In all test cases, we kept the cache-size x page-size mostly constant (2000 
pages of 1K, 1000 pages of 2 K, 500 pages of 4K), without going below 500 
pages.   Overall, we wanted to 'feel' the real-life behavior of the 
application, within specific goals of memory usage, and not synthetic test 
cases which you can always write to prove whatever you want and its contrary.

4 K and 8 K are very hard to distinguish in our results. 1K and 2K are clearly 
inferior though. Settings above 8 K brought very mixed results on very 
different work-loads. So be it for 4 K as our own default for now.  Indeed out 
of our compilation settings, here are those mostly significant on performance 
(in either way) - again within our specific needs and application architecture 
- don't draw generalizations from our (rather short) experience:

SQLITE_THREADSAFE=2
SQLITE_DEFAULT_CACHE_SIZE=500
SQLITE_DEFAULT_PAGE_SIZE=4096
SQLITE_DIRECT_OVERFLOW_READ
SQLITE_SECURE_DELETE
SQLITE_DEFAULT_FOREIGN_KEYS=1
SQLITE_ENABLE_STAT4

--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: 

[sqlite] Page_size

[Jim Wang]

hi,all

 Could different page_size do impact on the speed of retrieving record?
 Is page_size 8192 faster than page_size 1024 or page_szie 4096?  or The 
pagesize has nothing to do with the retrieving speed.

 Best reagard!
 Jim Wang.

[sqlite] Page_size

[Simon Slavin]

On 29 Feb 2016, at 9:14am, Olivier Mascia  wrote:

> we have found the sweet spot (for us, and for now) to be 4K page sizes, which 
> just happen to be the virtual memory page size of Windows system

Yes.  That's the idea.  Match the page size of the database to the size of the 
chunks your operating system is using.  It can be 1K.  It can be 16K.

It might be useful for other readers to know what experimenting you used to 
find your 'sweet spot'.  Was it simple an obvious or did you have to play 
tricks ?

Simon.

[sqlite] Page_size

[Olivier Mascia]

> Le 29 f?vr. 2016 ? 09:22, Jim Wang <2004wqg2008 at 163.com> a ?crit :
> 
> hi,all
> 
> Could different page_size do impact on the speed of retrieving record?
> Is page_size 8192 faster than page_size 1024 or page_szie 4096?  or The 
> pagesize has nothing to do with the retrieving speed.
> 
> Best reagard!
> Jim Wang.

I'm eager to read the comments of seasoned users on this.

On my side, having started using SQLite very recently (December 2015), after a 
lot of tests using real data, we have found the sweet spot (for us, and for 
now) to be 4K page sizes, which just happen to be the virtual memory page size 
of Windows system (except older Itanium versions).  Though we use a slightly 
smaller cache size (between 500 and max 1000 pages) instead of the default 2000 
pages.  We use private caches per connection only, and use only wal mode.

You'll obviously pack more record and index cells per page when the page size 
is larger, but you will have larger I/O too to read or write any of those 
pages.  Your cache memory usage will obviously be higher with larger page size, 
unless you cache fewer pages (which we chose to do).

I can't bring any comment regarding other OS.
But there is probably not ONE answer to your question.

--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: 

[sqlite] Page_size

[Keith Medcalf]

The NTFS cluster size is usually 4K, unless you have changed it (you can use 
fsutil or chkdsk to see what the cluster size is).  Windows does I/O in units 
of a cluster.  Therefore the smallest (and largest) I/O which Windows will do 
is 1 Cluster, or 4K.  While you may set the page size larger or smaller at the 
application level, Windows will always do I/O in cluster units.  So if you set 
the page size to 1K, it will still I/O 4K for each page read/write, and if you 
set it larger than the cluster size, then it will scatter-gather the number of 
clusters required to fill your request.

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Olivier Mascia
> Sent: Monday, 29 February, 2016 02:15
> To: SQLite mailing list
> Subject: Re: [sqlite] Page_size
> 
> 
> > Le 29 f?vr. 2016 ? 09:22, Jim Wang <2004wqg2008 at 163.com> a ?crit :
> >
> > hi,all
> >
> > Could different page_size do impact on the speed of retrieving
> record?
> > Is page_size 8192 faster than page_size 1024 or page_szie 4096?  or
> The pagesize has nothing to do with the retrieving speed.
> >
> > Best reagard!
> > Jim Wang.
> 
> I'm eager to read the comments of seasoned users on this.
> 
> On my side, having started using SQLite very recently (December 2015),
> after a lot of tests using real data, we have found the sweet spot (for
> us, and for now) to be 4K page sizes, which just happen to be the virtual
> memory page size of Windows system (except older Itanium versions).
> Though we use a slightly smaller cache size (between 500 and max 1000
> pages) instead of the default 2000 pages.  We use private caches per
> connection only, and use only wal mode.
> 
> You'll obviously pack more record and index cells per page when the page
> size is larger, but you will have larger I/O too to read or write any of
> those pages.  Your cache memory usage will obviously be higher with larger
> page size, unless you cache fewer pages (which we chose to do).
> 
> I can't bring any comment regarding other OS.
> But there is probably not ONE answer to your question.
> 
> --
> Meilleures salutations, Met vriendelijke groeten, Best Regards,
> Olivier Mascia, integral.be/om

[sqlite] Page_size

[Scott Hess]

Also note that almost all current storage you can purchase uses 4k basic
blocks.  So it's not just some weird Windows thing.

In addition to performance advantages of getting the block size right,
there is also the advantage that most storage systems strive hard to make
sure block operations are atomic in the face of power losses.  This is
easier to accomplish when writing a full block, as opposed to doing a
read-modify-write required to write a smaller block.

Circling back to testing page_size, I have noticed that it is easy to
mistake cache effects for improvements due to page_size.  If your pages are
2x as big, your cache is 2x as big (unless you modify it).  You can
convince yourself to use huge pages, only to later realize that actually
the improvement was from using a huge cache or from preloading the cache.
So when experimenting, do try to determine _why_ the improvement happens,
because there may be other ways to accomplish the improvement.

-scott


On Mon, Feb 29, 2016 at 7:28 AM, Keith Medcalf  wrote:

>
> The NTFS cluster size is usually 4K, unless you have changed it (you can
> use fsutil or chkdsk to see what the cluster size is).  Windows does I/O in
> units of a cluster.  Therefore the smallest (and largest) I/O which Windows
> will do is 1 Cluster, or 4K.  While you may set the page size larger or
> smaller at the application level, Windows will always do I/O in cluster
> units.  So if you set the page size to 1K, it will still I/O 4K for each
> page read/write, and if you set it larger than the cluster size, then it
> will scatter-gather the number of clusters required to fill your request.
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Olivier Mascia
> > Sent: Monday, 29 February, 2016 02:15
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Page_size
> >
> >
> > > Le 29 f?vr. 2016 ? 09:22, Jim Wang <2004wqg2008 at 163.com> a ?crit :
> > >
> > > hi,all
> > >
> > > Could different page_size do impact on the speed of retrieving
> > record?
> > > Is page_size 8192 faster than page_size 1024 or page_szie 4096?  or
> > The pagesize has nothing to do with the retrieving speed.
> > >
> > > Best reagard!
> > > Jim Wang.
> >
> > I'm eager to read the comments of seasoned users on this.
> >
> > On my side, having started using SQLite very recently (December 2015),
> > after a lot of tests using real data, we have found the sweet spot (for
> > us, and for now) to be 4K page sizes, which just happen to be the virtual
> > memory page size of Windows system (except older Itanium versions).
> > Though we use a slightly smaller cache size (between 500 and max 1000
> > pages) instead of the default 2000 pages.  We use private caches per
> > connection only, and use only wal mode.
> >
> > You'll obviously pack more record and index cells per page when the page
> > size is larger, but you will have larger I/O too to read or write any of
> > those pages.  Your cache memory usage will obviously be higher with
> larger
> > page size, unless you cache fewer pages (which we chose to do).
> >
> > I can't bring any comment regarding other OS.
> > But there is probably not ONE answer to your question.
> >
> > --
> > Meilleures salutations, Met vriendelijke groeten, Best Regards,
> > Olivier Mascia, integral.be/om
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Page_size increase from 1k to 4k made my "REPLACE INTO" slower. why?

[Hick Gunter]

From what little you reveal I assume this is some kind of datalogging 
application. I also assume there will be a primary key (call ?) and also 
suspect that there are a number of secondary indices for data retrieval. Since 
you make no mention of transactions, I must infer that you are using 
autocommit, i.e. the data is committed to disk for each and every row.

For the primary key, only the "rightmost" page of the b-tree will be affected; 
for other indices, it is quite likely that random pages (different for each 
row) will be hit.

In this scenario, increasing the page size means that 4 times as many  sectors 
need to hit the disk surface for each statement.

To increase speed, consider committing the inserts at regular intervals (1 per 
second maybe?), so that disk updates (which is where the time is spent) happen 
only once every "quite a few" records. Larger pages then have a chance of 
getting filled and may also improve the locality of writes, i.e. fewer seeks 
and more consecutively written sectors.

You might also consider having a logger thread that writes into alternating 
table(s) (e.g. even and odd seconds' data) with INTEGER PRIMARY KEY and no 
other indices; and a transfer thread that copies the entries over into the 
"real" table in a batch transaction.

-Ursprüngliche Nachricht-
Von: Andy (KU7T) [mailto:k...@ku7t.org]
Gesendet: Montag, 19. Jänner 2015 09:06
An: sqlite-users@sqlite.org
Betreff: [sqlite] Page_size increase from 1k to 4k made my "REPLACE INTO" 
slower. why?

Hi,



I am using System.Data.SQLite, Version=1.0.92.0. I read that increasing the 
page_size to 4k on modern OS is a good thing and should speed things up.
However, I have a particular query that takes substantially longer. I tried to 
make sure that nothing else is changed, so I am a little puzzled. Can anyone 
explain why this is? Query is this, in case this gives  a clue:



REPLACE INTO PacketSpots

(Call, TS, Band, Freq, QSXFreq, Comment, Spotter, Bearing, CountryPrefix, Sect, 
ZN, Exchange1, GridSquare, MiscText, Name, NR, Continent, Sunrise, Sunset, 
Latitude, Longitude, QualityTag)

VALUES

(@Call, @TS, @Band, @Freq, @QSXFreq, @Comment, @Spotter, @Bearing, 
@CountryPrefix, @Sect, @ZN, @Exchange1, @GridSquare, @MiscText, @Name, @NR, 
@Continent, @Sunrise, @Sunset, @Latitude, @Longitude, @QualityTag)



SqliteParameters not show.



The query used to be sub 1 ms, now it is 28 ms. Since I get quite a few calls 
per second, this is a big change.



Anyone any ideas or tips?



Thanks

Andy

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Page_size increase from 1k to 4k made my "REPLACE INTO" slower. why?

[Andy (KU7T)]

Hi,

 

I am using System.Data.SQLite, Version=1.0.92.0. I read that increasing the
page_size to 4k on modern OS is a good thing and should speed things up.
However, I have a particular query that takes substantially longer. I tried
to make sure that nothing else is changed, so I am a little puzzled. Can
anyone explain why this is? Query is this, in case this gives  a clue:

 

REPLACE INTO PacketSpots 

(Call, TS, Band, Freq, QSXFreq, Comment, Spotter, Bearing, CountryPrefix,
Sect, ZN, Exchange1, GridSquare, MiscText, Name, NR, Continent, Sunrise,
Sunset, Latitude, Longitude, QualityTag) 

VALUES

(@Call, @TS, @Band, @Freq, @QSXFreq, @Comment, @Spotter, @Bearing,
@CountryPrefix, @Sect, @ZN, @Exchange1, @GridSquare, @MiscText, @Name, @NR,
@Continent, @Sunrise, @Sunset, @Latitude, @Longitude, @QualityTag)

 

SqliteParameters not show.

 

The query used to be sub 1 ms, now it is 28 ms. Since I get quite a few
calls per second, this is a big change.

 

Anyone any ideas or tips?

 

Thanks

Andy

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size

[Tom Broadbent]

ah - good to know. that is what i was looking for.

thanks
tom

On Apr 25, 2010, at 10:21 PM, Dan Kennedy wrote:

> The page formats use 16-bit unsigned integers to store various offsets
> (in bytes) to cells and free-blocks within a page. So it definitely
> won't work with greater than 64KB pages.
> 
> Not sure if 64KB would work or not. Since it hasn't been tested, the
> answer is probably "No.".


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size

[Dan Kennedy]

On Apr 26, 2010, at 11:09 AM, Tom Broadbent wrote:

>
> thanks for the replies.
>
> understood.  i was informed that our underlying (EMMC??) FS will do  
> this w/ FS pages, i.e. read the entire 256k FS page, modify some  
> small portion of it, and write it out again.  i'm higher in the  
> stack so i don't understand the underlying FS mechanisms; i've  
> simply asked the lower-level devs for advice about maximizing FS IO.
>
> on this particular FS i'm under the impression that writing a 1k  
> SQLite page requires reading a 256k FS page, modifying the 1k  
> portion and then writing the whole 256k FS page out. i'll verify w/  
> the lower-level devs next week.
>
> yes, this sounds _very_ inefficient, but perhaps this is why our  
> commit times are so poor (i.e. writing each DB page requires reading  
> and writing a much larger FS page).
>
> yes - i'm aware that each table and index are in a separate DB page;  
> i saw evidence of this when bumping the page size from 1k to 32k.
>
> this DB has only one table w/ a single pkey index.  the DB will be  
> relatively large (10MB) compared to the page size and will continue  
> to grow over time, so i'm not concerned.
>
> basically i want to know if SQLite will have internal problems  
> running w/ a page_size greater than the recommended (required?) max  
> of 32k.

The page formats use 16-bit unsigned integers to store various offsets
(in bytes) to cells and free-blocks within a page. So it definitely
won't work with greater than 64KB pages.

Not sure if 64KB would work or not. Since it hasn't been tested, the
answer is probably "No.".

Dan.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size

[Tom Broadbent]

thanks for the replies.

understood.  i was informed that our underlying (EMMC??) FS will do this w/ FS 
pages, i.e. read the entire 256k FS page, modify some small portion of it, and 
write it out again.  i'm higher in the stack so i don't understand the 
underlying FS mechanisms; i've simply asked the lower-level devs for advice 
about maximizing FS IO.  

on this particular FS i'm under the impression that writing a 1k SQLite page 
requires reading a 256k FS page, modifying the 1k portion and then writing the 
whole 256k FS page out. i'll verify w/ the lower-level devs next week.

yes, this sounds _very_ inefficient, but perhaps this is why our commit times 
are so poor (i.e. writing each DB page requires reading and writing a much 
larger FS page).

yes - i'm aware that each table and index are in a separate DB page; i saw 
evidence of this when bumping the page size from 1k to 32k.  

this DB has only one table w/ a single pkey index.  the DB will be relatively 
large (10MB) compared to the page size and will continue to grow over time, so 
i'm not concerned.

basically i want to know if SQLite will have internal problems running w/ a 
page_size greater than the recommended (required?) max of 32k.

thanks
tom


On Apr 21, 2010, at 1:39 PM, D. Richard Hipp wrote:

> 
> On Apr 21, 2010, at 4:37 PM, Pavel Ivanov wrote:
> 
>> I don't know anything about internal support of pages bigger than 32k.
>> But I want to warn you: each table and each index in SQLite occupy at
>> least 1 database page. So let's say you have 4 tables with 1
>> additional index each (besides 'integer primary key' one). With 256k
>> pages this schema will result in a database of more than 2 Mb without
>> any data stored. Is your embedded FS okay with this storage amount?
> 
> Furthermore, SQLite changes whole pages at a time.  So in a database  
> with 256kB pages, if you change a single byte, you still have to write  
> 256kB both to the rollback journal and to the database file.
> 
>> 
>> 
>> Pavel
>> 
>> On Tue, Apr 20, 2010 at 4:51 PM, Tom Broadbent
>>  wrote:
>>> i've read in the docs that SQLITE_MAX_PAGE_SIZE can't be > 32k  
>>> (below).  is this limitation still valid?
>>> 
>>> we have an embedded FS that is _very_ slow and performs best w/ a  
>>> write page size of 256k.  will bad things happen if i configure  
>>> SQLite w/ 256k pages?
>>> 
>>> thanks
>>> tom
>>> 
>>> Maximum Database Page Size
>>> 
>>> An SQLite database file is organized as pages. The size of each  
>>> page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The  
>>> default value for SQLITE_MAX_PAGE_SIZE is 32768. The current  
>>> implementation will not support a larger value.
>>> 
>>> It used to be the case that SQLite would allocate some stack  
>>> structures whose size was proportional to the maximum page size.  
>>> For this reason, SQLite would sometimes be compiled with a smaller  
>>> maximum page size on embedded devices with limited stack memory.  
>>> But more recent versions of SQLite put these large structures on  
>>> the heap, not on the stack, so reducing the maximum page size is no  
>>> longer necessary on embedded devices. There is no longer any real  
>>> reason to lower the maximum page size.
>>> 
>>> __
>>> This email has been scanned by the MessageLabs Email Security System.
>>> For more information please visit http://www.messagelabs.com/email
>>> __
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email 
> __


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size

[D. Richard Hipp]

On Apr 21, 2010, at 4:37 PM, Pavel Ivanov wrote:

> I don't know anything about internal support of pages bigger than 32k.
> But I want to warn you: each table and each index in SQLite occupy at
> least 1 database page. So let's say you have 4 tables with 1
> additional index each (besides 'integer primary key' one). With 256k
> pages this schema will result in a database of more than 2 Mb without
> any data stored. Is your embedded FS okay with this storage amount?

Furthermore, SQLite changes whole pages at a time.  So in a database  
with 256kB pages, if you change a single byte, you still have to write  
256kB both to the rollback journal and to the database file.

>
>
> Pavel
>
> On Tue, Apr 20, 2010 at 4:51 PM, Tom Broadbent
>  wrote:
>> i've read in the docs that SQLITE_MAX_PAGE_SIZE can't be > 32k  
>> (below).  is this limitation still valid?
>>
>> we have an embedded FS that is _very_ slow and performs best w/ a  
>> write page size of 256k.  will bad things happen if i configure  
>> SQLite w/ 256k pages?
>>
>> thanks
>> tom
>>
>> Maximum Database Page Size
>>
>> An SQLite database file is organized as pages. The size of each  
>> page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The  
>> default value for SQLITE_MAX_PAGE_SIZE is 32768. The current  
>> implementation will not support a larger value.
>>
>> It used to be the case that SQLite would allocate some stack  
>> structures whose size was proportional to the maximum page size.  
>> For this reason, SQLite would sometimes be compiled with a smaller  
>> maximum page size on embedded devices with limited stack memory.  
>> But more recent versions of SQLite put these large structures on  
>> the heap, not on the stack, so reducing the maximum page size is no  
>> longer necessary on embedded devices. There is no longer any real  
>> reason to lower the maximum page size.
>>
>> __
>> This email has been scanned by the MessageLabs Email Security System.
>> For more information please visit http://www.messagelabs.com/email
>> __
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size

[Pavel Ivanov]

I don't know anything about internal support of pages bigger than 32k.
But I want to warn you: each table and each index in SQLite occupy at
least 1 database page. So let's say you have 4 tables with 1
additional index each (besides 'integer primary key' one). With 256k
pages this schema will result in a database of more than 2 Mb without
any data stored. Is your embedded FS okay with this storage amount?


Pavel

On Tue, Apr 20, 2010 at 4:51 PM, Tom Broadbent
 wrote:
> i've read in the docs that SQLITE_MAX_PAGE_SIZE can't be > 32k (below).  is 
> this limitation still valid?
>
> we have an embedded FS that is _very_ slow and performs best w/ a write page 
> size of 256k.  will bad things happen if i configure SQLite w/ 256k pages?
>
> thanks
> tom
>
> Maximum Database Page Size
>
> An SQLite database file is organized as pages. The size of each page is a 
> power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The default value for 
> SQLITE_MAX_PAGE_SIZE is 32768. The current implementation will not support a 
> larger value.
>
> It used to be the case that SQLite would allocate some stack structures whose 
> size was proportional to the maximum page size. For this reason, SQLite would 
> sometimes be compiled with a smaller maximum page size on embedded devices 
> with limited stack memory. But more recent versions of SQLite put these large 
> structures on the heap, not on the stack, so reducing the maximum page size 
> is no longer necessary on embedded devices. There is no longer any real 
> reason to lower the maximum page size.
>
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] page_size

[Tom Broadbent]

i've read in the docs that SQLITE_MAX_PAGE_SIZE can't be > 32k (below).  is 
this limitation still valid?

we have an embedded FS that is _very_ slow and performs best w/ a write page 
size of 256k.  will bad things happen if i configure SQLite w/ 256k pages?

thanks
tom

Maximum Database Page Size

An SQLite database file is organized as pages. The size of each page is a power 
of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The default value for 
SQLITE_MAX_PAGE_SIZE is 32768. The current implementation will not support a 
larger value.

It used to be the case that SQLite would allocate some stack structures whose 
size was proportional to the maximum page size. For this reason, SQLite would 
sometimes be compiled with a smaller maximum page size on embedded devices with 
limited stack memory. But more recent versions of SQLite put these large 
structures on the heap, not on the stack, so reducing the maximum page size is 
no longer necessary on embedded devices. There is no longer any real reason to 
lower the maximum page size.

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Page_size

[Martin Pelletier]

[EMAIL PROTECTED] wrote:

Martin Pelletier <[EMAIL PROTECTED]> wrote:
  

Hello,

I am trying to optimise an SQLite database running on Windows. One of 
the things I'm looking at is page size. However, a page on sqlite.org 
(http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows) mentions 
this:


"Just a note that you must also perform this command [PRAGMA 
page_size=4096; ] before you start a transaction (despite the 
optimization hint that every command should be in a transaction)."


Is this still true? Do I need to specify page size before any INSERT or 
SELECT query, even though it is already set from the moment of database 
creation? If I don't, what happens?





You only need to do PRAGMA page_size=4096 once, before your first
CREATE TABLE statement.  Once the database exists, the page size
is fixed and can never change.


--
D. Richard Hipp <[EMAIL PROTECTED]>

  

Perfect. Thank you!

On a side note, that comment on the page 
(http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows) should 
really be clarified to reflect what you just told me. It is clearly told 
that the page_size cannot be changed, but the bit about setting it 
before every transaction is misleading.


Martin

Re: [sqlite] Page_size

[drh]

Martin Pelletier <[EMAIL PROTECTED]> wrote:
> 
> On a side note, that comment on the page 
> (http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows) should 
> really be clarified to reflect what you just told me. It is clearly told 
> that the page_size cannot be changed, but the bit about setting it 
> before every transaction is misleading.
> 

It is a wiki.  You are free to change it to whatever you
think will be clearer.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Page_size

[drh]

Martin Pelletier <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I am trying to optimise an SQLite database running on Windows. One of 
> the things I'm looking at is page size. However, a page on sqlite.org 
> (http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows) mentions 
> this:
> 
> "Just a note that you must also perform this command [PRAGMA 
> page_size=4096; ] before you start a transaction (despite the 
> optimization hint that every command should be in a transaction)."
> 
> Is this still true? Do I need to specify page size before any INSERT or 
> SELECT query, even though it is already set from the moment of database 
> creation? If I don't, what happens?
> 

You only need to do PRAGMA page_size=4096 once, before your first
CREATE TABLE statement.  Once the database exists, the page size
is fixed and can never change.


--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Page_size

[Martin Pelletier]

Hello,

I am trying to optimise an SQLite database running on Windows. One of 
the things I'm looking at is page size. However, a page on sqlite.org 
(http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows) mentions 
this:


"Just a note that you must also perform this command [PRAGMA 
page_size=4096; ] before you start a transaction (despite the 
optimization hint that every command should be in a transaction)."


Is this still true? Do I need to specify page size before any INSERT or 
SELECT query, even though it is already set from the moment of database 
creation? If I don't, what happens?


Regards,

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-