When using the BDB SQL interface, you configure your database page size in
exactly the same way as you do when using
SQLite. That is, use PRAGMA page_size
to report and set the page size. This PRAGMA must be called
before you create your first SQLite table. See the
PRAGMA page_size
documentation for more information.
When you use
PRAGMA cache_size to size your in-memory
cache, you provide the cache size in terms of a number of
pages. Therefore, your database page size influences how large your
cache is, and so determines how much of your
database will fit into memory.
The size of your pages can also affect how efficient your application is at performing disk I/O. It will also determine just how fine-grained the fine-grained locking actually is. This is because Berkeley DB locks database pages when it acquires a lock.
Note that the default value for your page size is probably correct for the physical hardware that you are using. In almost all situations, the default page size value will give your application the best possible I/O performance. For this reason, tuning the page size should rarely, if ever, be attempted.
That said, when using the BDB SQL interface, the page size affects how much of your tables are locked when read and/or write locks are acquired. (See Internal Database Usage for more information.) Increasing your page size will typically improve the bandwidth you get accessing the disk, but it also may increase contention if too many key data pairs are on the same page. Decreasing your page size frequently improves concurrency, but may increase the number of locks you need to acquire and may decrease your disk bandwidth.
When changing your page size, make sure the value you
select is a power of 2 that is greater than 512 and
less than or equal to 64KB. (Note that the standard
SQLite MAX_PAGE_SIZE limit is not
examined for this upper bound.)
Beyond that, there are some additional things that you need to consider when selecting your page size. For a thorough treatment of selecting your page size, see the section on Selecting a page size in the Berkeley DB Programmer's Reference Guide.