Tune the size of database pages

Stick with 4K as the page size (the default, and the size operating systems use) unless:

• You are storing large objects.
• You have very large tables (over 10,000 rows).

  For very large tables, large pages reduces the number of I/Os required.

• For read-only applications, use a large page size (for example, 32K) with a pageReservedSpace of 0.

You might need to experiment with page size to find out what works best for your application and database.

Performance trade-offs of large pages

Using large database pages benefits database performance, notably decreasing I/O time. By default, the database page size is 4096 bytes. You can change the default database page size with the derby.storage.pageSize property. For example:

derby.storage.pageSize=8192

Note:

Large database pages require more memory.

If row size is large, generally page size should be correspondingly large. If row size is small, page size should be small. Another rough guideline is to try to have at least 10 average-sized rows per page (up to 32K).

Use a larger page size for tables with large columns or rows. Maximum page size allowed is 32k.

However, some applications involve rows whose size will vary considerably from user to user. In that situation, it is hard to predict what effect page size will have on performance.

If a table contains one large column along with several small columns, put the large column at the end of the row, so that commonly used columns will not be moved to overflow pages. Do not index large columns.

Large page size for indexes improves performance considerably.

When large page size does not improve performance

• Selective Queries

  If your application's queries are very selective and use an index, large page size does not provide much benefit and potentially degrades performance because a larger page takes longer to read.

When large page size is not desirable

• Limited memory

  Large database pages reduce I/O time because Derby can access more data with fewer I/Os. However, large pages require more memory. Derby allocates a bulk number of database pages in its page cache by default. If the page size is large, the system might run out of memory.

  Here's a rough guideline: If the system is running Windows 95 and has more than 32 MB (or Windows NT and has more than 64 MB), it is probably beneficial to use 8K rather than 4K as the default page size.

  Use the -mx flag as an optional parameter to the JVM to give the JVM more memory upon startup.

  For example:

  java -mx64 myApp
  

• Limited disk space

  If you cannot afford the overhead of the minimum two pages per table, keep your page sizes small.