Working With Dbspaces
This section discusses:
The following are some dbspace strategies to consider:
Separate the root dbspace, physical logs, logical logs, and the temporary dbspace from one another and from the application dbspaces.
Place the root dbspace, logical log, and physical log in separate dbspaces on separate disks.
Separate certain high volume application tables to optimize performance.
A minimum configuration for production systems is four physical drives (at least one each for database files, physical logs, logical logs, and temporary area).
Separate the dbspace for data on one drive and the dbspace for indexes on a separate drive.
This section provides an overview and discusses:
Setting Buffer Pools.
Setting the PHYSBUFF Parameter.
Understanding Dbspace Size
Informix Dynamic Server enables you to configure the page size of standard or temporary dbspace a with non-default page size. By default, PeopleTools DDL scripts set dbspaces to 4KB regardless of the default page size of the operating system.
Advantages of having larger page sizes include:
Dbspaces of larger page sizes can support larger index key sizes.
More items can fit in a larger index page and there will be a decreased number of levels in a btree index, which reduces index scan times.
Increased access efficiency because with a larger page, larger numbers of rows get transferred to/from shared memory/disk, which means fewer I/O operations for data and indices.
Longer rows can fit into a single page reduces data access times due to the decreased number of pages read per row.
Pages large enough to fit "oversized" rows eliminate the overhead of access time for remainder pages.
Checkpoint time is typically reduced with larger pages.
PeopleTools delivers two scripts, ptddl.sh and createtblspace.sh, located in <PS_HOME>/scripts/unix directory. The ptddl.sh script is executed by the Database Creation Wizard to create dbspaces required by a PeopleTools Demo database. The createtblspace.sh script is executed when the database needs to be created using only two dbspaces, as in pstable1 and psindex1.
The –k <page_size> option of the onspaces command sets the dbspace size. The default page size is set to 4K, however, you can set the dbspace to a custom value for your site's requirements (6K, 8K , 16K, and so on).
... onspaces -c -d <DBSPACE> -k 4 -p </path>/<DBSPACE>.dbf -o 0 -s 30000 ... onspaces -c -d PSIMAGE -k 4 -p </path>/PSIMAGE.dbf -o 0 -s 150000 ... onspaces -c -d PSIMGR -k 4 -p </path>/PSIMGR.dbf -o 0 -s 5000 ... onspaces -c -d PTWORK -k 4 -p </path>/PTWORK.dbf -o 0 -s 5000
... echo "Creating dbspace pstable1..." $INFORMIXDIR/bin/onspaces -c -d pstable1 -k 4 -p $1/pstable1.dbf -o 0 -s $2 echo "Creating dbspace psindex1..." $INFORMIXDIR/bin/onspaces -c -d psindex1 -k 4 -p $1/psindex1.dbf -o 0 -s $3 echo "Running a Level 0 Archive..." $INFORMIXDIR/bin/ontape -s -L 0
Note: You can define a different page size for temporary tables, so that they have a separate buffer pool.
Note: Rootdbs and other critical dbspaces, logdbs and physdbs, are created with the page size specified by the operating system.
Setting Buffer Pools
When creating a dbspace of non-default page size it requires a corresponding BUFFERPOOL configuration parameter in the ONCONFIG file. If it is not defined, the database server automatically adds an entry in the ONCONFIG file, which would be based on default value of BUFFERPOOL.
For example, if the onspaces command creates a new dbspace with a 4 KB page size, the database server takes the values of buffers, lrus, lru_min_dirty, and lru_max_dirty from the BUFFERPOOL default line, unless there already is a BUFFERPOOL entry for that page size.
The following example shows how the database server would automatically add a BUFFERPOOL entry for 4 KB based on the default values:
BUFFERPOOL default,buffers=1000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.000000 BUFFERPOOL size=2K,buffers=5000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.000000 BUFFERPOOL size=4K,buffers=1000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.000000
Buffer pools can also be added using the onparams command. For example:
onparams -b -g 4 -n 3000 -r 2 -x 2.0 -m 1.0
This example adds 3000 buffers of 4KB each with 2 LRUS with a maximum dirty of 2% and minimum dirty of 1%. When using the onparams utility to add a buffer pool or when adding a dbspace with a different page size with the onspaces utility, the information specified is automatically appended to the ONCONFIG file and new values are specified using the BUFFERPOOL keyword.
You cannot change the values by editing the onconfig file. If you need to resize or delete an existing buffer pool, you must restart the database server and then run onparams again.
Buffer pools that are added while the database server is running go into virtual memory, not into resident memory. Only those buffer pool entries that are specified in the ONCONFIG file at startup go into resident memory, depending on the availability of the memory you are using.
Setting the PHYSBUFF Parameter
It is recommended to set the PHYSBUFF configuration parameter to at least 128 kilobytes. If the database server is configured to use RTO_SERVER_RESTART, set the PHYSBUFF configuration parameter to at least 512 kilobytes. Setting PHYSBUFF to a lower value could impact transaction performance and will result in a performance warning during server initialization.