Ensure that
the storage system used for the database server is configured for optimal
performance. Incorrect configuration of the I/O system can severely degrade the
performance of your system. Sizing and placement of your application database
data files, log files, and the tempdb system database play a major role
in dictating overall performance.
PeopleSoft
applications involve online transaction processing (OLTP), which mainly results
in random data access, as well as batch processing that often results in
sequential access. Consider how much random data access and sequential access
your applications will be making when selecting the disk I/O configuration.
RAID
Type Recommendations
A common
debate when discussing RAID options is the relative performance of RAID 5
versus RAID 10. RAID 10 will outperform a RAID 5 set of the same number of
volumes, for the following reasons:
• Write
performance for RAID 10 is superior. A write operation on RAID 5 requires four
physical I/O operations, whereas RAID 10 requires two.
• Read
performance of RAID 10 is enhanced in most implementations by balancing read
requests across the two drives participating in the mirror.
RAID 0 is
unsuitable for use in a SQL Server environment because the loss of a single
drive will result in the loss of data. Even tempdb should not be placed
on RAID 0 in a production environment because the loss of one drive on RAID 0
would result in an outage on the SQL Server instance. A possible use of RAID 0
could be as the temporary location of disk backups, prior to writing disk
backups to tape or to another location.
RAID 1 is
appropriate for objects such as the SQL Server binaries, the master database,
and the msdb database. I/O requirements for these objects are minimal
and therefore they do not generally benefit from striping, but they require
fault tolerance to remain available. To maintain continuous operation, you must
implement fault tolerance for these objects.
Note. You should isolate the transaction
log from all other I/O activity – no other files should exist on the drives
that contain the log file. This ensures that, with the exception of transaction
log backup and the occasional rollback, nothing disturbs the sequential nature
of transaction log activity.
RAID 10
affords the best overall performance, making it the preferred choice for all
database files.
RAID level recommendations for PeopleSoft applications:
Typical I/O Performance Recommended
Range
For PeopleSoft applications with high performance
requirements, the recommended range for SQL Server data and log files in
milliseconds (ms) per read and milliseconds per write is as follows:
SQL
Server data files:
• Up to 10 ms is good.
• 10 to 20 ms is acceptable.
• 20 to 30 ms is not acceptable.
• Above 30 ms can have an adverse effect on the performance
of the system and it is usually not acceptable, especially for high-throughput
deployments.
SQL
Server transaction log files:
• Up to 10 ms is good.
• 10 to 20 ms is acceptable. A duration that is longer than
20 ms can result in problems using CPU resources.
• Above 20 ms indicates that CPU resources of the database
server are not fully utilized.
Files, Filegroups, and Object
Placement Strategies
The complex
pattern of I/O activity and the large number of tables and indexes in the
PeopleSoft database make attempting strategic placement of objects and object
types (that is, tables and indexes) a difficult task. A better strategy is to
spread a single user-defined filegroup across as many physical drives as
possible, which puts the entire storage system to work completing as many I/O
requests as possible in parallel. It is recommended that you create a
user-defined filegroup and then create secondary data files in it. Mark the
user-defined filegroup as default and place the PeopleSoft objects in it. For
increased manageability, it is also recommended that you create multiple files
in the user-defined filegroup.
A possible
exception to this strategy is the isolation of temporary tables (described
later in this document). Temporary tables store intermediate results during
batch processing; therefore, isolating temporary tables from the rest of the
PeopleSoft database objects has the potential to reduce the interference that
batch processing can have with OLTP processing.
Note. PeopleSoft applications do allow you
to assign tables and indexes to specific filegroups. To do so, update
PeopleTools tables PSDDLMODEL and PSDDLDEFPARMS. When used, each table and
index script is generated with its specific filegroup included.
Tempdb Placement and Tuning
The tempdb
system database is a global resource that is available to all users
connected to the instance of SQL Server. It holds the following:
• Temporary
user objects that are explicitly created, such as: global or local temporary
tables, temporary stored procedures, table variables, and cursors.
• Internal
objects that are created by the SQL Server Database Engine, for example, work
tables to store intermediate results for spools or sorting.
• Row
versions that are generated by data modification transactions in a database
that uses read-committed using row versioning isolation or snapshot isolation
transactions.
• Row
versions that are generated by data modification transactions for features,
such as online index operations.
In SQL
Server, tempdb requires more disk space than in earlier versions of SQL
Server. Configuration of tempdb for best performance is critical for SQL
Server due to the potential of added performance stress on tempdb from
new features such as read-committed snapshot isolation level and online index
operations.
It is
recommended that tempdb be isolated from other database activity on its
own RAID set of physical disks. It is especially important to use RAID 10 for tempdb.
To move tempdb to its own set of RAID disks, use the ALTER DATABASE
statement with the MODIFY FILE clause to specify a new location for the tempdb
data file and log file.
You may
also want to increase the SIZE clause to a larger value, such as 100 MB, and
increase the FILEGROWTH clause to 50 MB. To preallocate space for all tempdb
files, set the file size to a value large enough to accommodate the typical
workload in the environment. This prevents tempdb from expanding too
frequently, which can affect performance. Set the tempdb database to
autogrow, but use this option to increase disk space for unplanned exceptions.
In SQL
Server, pre-sizing tempdb to a sufficiently large size is strongly
recommended.
When the
READ_COMMITTED_SNAPSHOT database option is ON, logical copies are maintained
for all data modifications performed in the database. Every time a row is
modified by a specific transaction, the instance of the Database Engine stores
a version of the previously committed image of the row in tempdb. The tempdb
should have enough capacity to store the row versions and the other objects
stored in the tempdb.
Set the
file growth increment to a reasonable size to avoid the tempdb database
files from growing by too small a value. If the file growth is too small,
compared to the amount of data that is being written to tempdb, tempdb
may have to constantly expand. This will affect performance. See the
following general guidelines for setting the FILEGROWTH increment for tempdb
files.
You may
have to adjust this based on the speed of the I/O subsystem on which the tempdb
files are located. To avoid potential latch time-outs, limit the autogrow
operation to approximately two minutes. For example, if the I/O subsystem can
initialize a file at 50 MB per second, the FILEGROWTH increment should be set
to a maximum of 6 GB, regardless of the tempdb file size. The instant database
file initialization feature can improve the performance of autogrow operations.
Note. Monitor and avoid automatic file
growth, as it impacts performance. Every time SQL Server is started, the tempdb
file is re-created with the default size. While tempdb can grow, it
does take resources to perform this task. To reduce this overhead of tempdb growing,
you may want to permanently increase the default size of tempdb after
carefully monitoring its growth.
Also,
consider adding multiple data files to tempdb rather than maintaining
just one. This can reduce contention on tempdb. To add multiple data
files, use the ALTER DATABASE statement with the ADD FILE clause.
Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. As a general guideline, create one data file
for each CPU processor core on the server (accounting for any affinity mask
settings) and then adjust the number of files up or down as necessary. Note
that a dual-core CPU is considered to be two CPUs for this purpose.
Make each
data file the same size; this allows for optimal proportional-fill performance.
Data and Log File Sizing
For the
PeopleSoft installation, it is critical that you set sizes for the database
data and log files appropriately. Ensure that the data and log files always
have enough capacity to allow data modifications to happen seamlessly without
causing a physical file expansion (autogrow). In other words, the data and log
files should be pre-grown to a sufficiently large size.
It is
recommended that you enable autogrow for the data and log files, however; it is
meant as a fallback mechanism in the event file expansion is required. For
large databases, it is recommended that you enable autogrow by size (MB) rather
than by percent.
