| Subject | Re: [firebird-support] Newbie Question: Page_Size |
|---|---|
| Author | Tim |
| Post date | 2004-07-30T13:28:54Z |
It's in the documentation, in the Interbase Operations guide, under
Database and server performance.
It makes a HUGE difference to performance ... :-)
Hope this helps :
Here's a quote from the docs :
Choosing a database page size
InterBase pages are 1KB by default. A typical production InterBase database
gains 25 to
30 percent performance benefit from using a page size of 4KB. This larger
page size
results in better performance for the following reasons:
g
Fewer record fragments are split across pages
It is common for records to be larger than the default 1KB page size. This
means that
InterBase fragments records and stores them on multiple pages. Querying a given
record requires multiple page reads from the database.
By increasing the size of a page, InterBase can reduce the number of
multiple page
reads and can store record fragments more contiguously.
g
Index B-trees are more shallow
Indexes are B-trees of pointers to data pages containing instances of
specific indexed
values. If the index B-tree is larger than one page, InterBase allocates
additional
database pages for the index tree. If the index pages are larger, InterBase
needs fewer
additional pages to store the pointers. It is easier for the database cache
to store the
entire B-tree in memory, and indexed lookups are much faster.
g
I/O is more contiguous
It is fairly likely for a query to request successive records in a table.
For example, this
is done during a table scan, or query that returns or aggregates all
records in a table.
InterBase stores records on the first page that is unused, rather than
ensuring that they
are stored near each other in the file. Doing a table scan can potentially
require
retrieval of data by seeking all over the database. Seeks take time just as
reading data
takes time.
Any given page can store records from only one table. This indicates that a
larger page
is certain to contain more data from the same table, and therefore reading
that page
returns more relevant data.
g
Default number of cache buffers is a larger amount of memory
InterBase allocates the database cache in number of pages, rather than a fixed
number of bytes. Therefore defining a larger page size increases the cache
size. A
larger cache is more likely to have a better hit rate than a smaller cache.
g
Most operating systems perform low-level I/O in 4096 byte blocks
InterBase performs a page read or write at the OS level by reading in 4096 byte
increments regardless of the size of the database page. Therefore, by
defining the
database with a page size of 4096, the database I/O matches the low-level
I/O and this
results in greater efficiency when reading and writing pages.
Although 4KB seems to be the best page size for most databases, the optimal
size depends
on the structure of the specific metadata and the way in which applications
access the
data. For this reason, you should not consider the 4KB page size guideline
to be a magic
value. Instead, you should perform testing with your application and
database under
several different page sizes to analyze which configuration gives the best
performance.
At 14:54 30/07/2004, you wrote:
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.261 / Virus Database: 264.1.0 - Release Date: 29/07/2004
[Non-text portions of this message have been removed]
Database and server performance.
It makes a HUGE difference to performance ... :-)
Hope this helps :
Here's a quote from the docs :
Choosing a database page size
InterBase pages are 1KB by default. A typical production InterBase database
gains 25 to
30 percent performance benefit from using a page size of 4KB. This larger
page size
results in better performance for the following reasons:
g
Fewer record fragments are split across pages
It is common for records to be larger than the default 1KB page size. This
means that
InterBase fragments records and stores them on multiple pages. Querying a given
record requires multiple page reads from the database.
By increasing the size of a page, InterBase can reduce the number of
multiple page
reads and can store record fragments more contiguously.
g
Index B-trees are more shallow
Indexes are B-trees of pointers to data pages containing instances of
specific indexed
values. If the index B-tree is larger than one page, InterBase allocates
additional
database pages for the index tree. If the index pages are larger, InterBase
needs fewer
additional pages to store the pointers. It is easier for the database cache
to store the
entire B-tree in memory, and indexed lookups are much faster.
g
I/O is more contiguous
It is fairly likely for a query to request successive records in a table.
For example, this
is done during a table scan, or query that returns or aggregates all
records in a table.
InterBase stores records on the first page that is unused, rather than
ensuring that they
are stored near each other in the file. Doing a table scan can potentially
require
retrieval of data by seeking all over the database. Seeks take time just as
reading data
takes time.
Any given page can store records from only one table. This indicates that a
larger page
is certain to contain more data from the same table, and therefore reading
that page
returns more relevant data.
g
Default number of cache buffers is a larger amount of memory
InterBase allocates the database cache in number of pages, rather than a fixed
number of bytes. Therefore defining a larger page size increases the cache
size. A
larger cache is more likely to have a better hit rate than a smaller cache.
g
Most operating systems perform low-level I/O in 4096 byte blocks
InterBase performs a page read or write at the OS level by reading in 4096 byte
increments regardless of the size of the database page. Therefore, by
defining the
database with a page size of 4096, the database I/O matches the low-level
I/O and this
results in greater efficiency when reading and writing pages.
Although 4KB seems to be the best page size for most databases, the optimal
size depends
on the structure of the specific metadata and the way in which applications
access the
data. For this reason, you should not consider the 4KB page size guideline
to be a magic
value. Instead, you should perform testing with your application and
database under
several different page sizes to analyze which configuration gives the best
performance.
At 14:54 30/07/2004, you wrote:
>I apologize if this is a question asked often and well documented but I have----------
>searched the documentation and can't find a concise answer to this question.
>
>My question is, is there any significance to the PAGE_SIZE specified when
>the CREATE DATABASE command is issued? If so, does this limit the physical
>size of the database and therefore affect the amount of data able to be
>stored? Or does this have significance when record locks occur?
>
>Thanks in advance for your help!
>
>Regards,
>Chris - newbie Firebird User
>
>
>
>Yahoo! Groups Sponsor
>ADVERTISEMENT
><http://us.ard.yahoo.com/SIG=129p4ka5a/M=295196.4901138.6071305.3001176/D=groups/S=1705115386:HM/EXP=1091280038/A=2128215/R=0/SIG=10se96mf6/*http://companion.yahoo.com>
>click here
>
>[]
>
>
>
>----------
>Yahoo! Groups Links
> * To visit your group on the web, go to:
> *
> <http://groups.yahoo.com/group/firebird-support/>http://groups.yahoo.com/group/firebird-support/
>
> *
> * To unsubscribe from this group, send an email to:
> *
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>firebird-support-unsubscribe@yahoogroups.com
>
> *
> * Your use of Yahoo! Groups is subject to the
> <http://docs.yahoo.com/info/terms/>Yahoo! Terms of Service.
>
>
>Incoming mail is certified Virus Free.
>Checked by AVG Anti-Virus (http://www.grisoft.com).
>Version: 7.0.261 / Virus Database: 264.1.0 - Release Date: 29/07/2004
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.261 / Virus Database: 264.1.0 - Release Date: 29/07/2004
[Non-text portions of this message have been removed]