Specifying Database Page Size

Go Up to Using CREATE DATABASE

You can override the default page size of 4,096 bytes for database pages by specifying a different PAGE_SIZE. PAGE_SIZE can be 1024, 2048, 4096, 8192, or 16384. The next statement creates a single-file database with a page size of 2048 bytes:

CREATE DATABASE 'employee.ib' PAGE_SIZE 2048;

When to increase page size

Increasing page size can improve performance for several reasons:

• Indexes work faster because the depth of the index is kept to a minimum.
• Keeping large rows on a single page is more efficient. (A row that is too large to fit on a single page requires more than one page fetch to read or write to it.)

BLOB data is stored and retrieved more efficiently when it fits on a single page. If most transactions involve only a few rows of data, a smaller page size might be appropriate, since less data needs to be passed back and forth and less memory is used by the disk cache.

Changing page size for an existing database

To change a page size of an existing database, follow these steps:

1. Back up the database.

2. Restore the database using the PAGE_SIZE option to specify a new page size.

For more detailed information on backing up the database, see the Operations Guide.