SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-33
Creating Table Partitions
examples suggest possible configurations of storage pools:
•
You can configure a pool containing physical volumes primaried to a particular
processor or set of processors. By partitioning a table across virtual volumes
associated with that pool, you ensure that disk access to the table will be managed
by the specified processors.
SQL/MP provides a complementary mechanism for controlling (limiting) which
processors run parallel queries. For more information about using SMF with this
feature, see Managing Processor Usage in a Distributed Environment on
page 12-11.
•
You can configure a pool for a particular application. Create SQL objects for that
application on virtual volumes associated with the pool; the objects will reside on
the physical volumes in the pool. To maximize performance of parallel queries, you
can balance the number of physical volumes primaried to each processor.
•
As a variation on the preceding configurations, you can configure a pool for each
processor. That is, the physical volumes in each pool are primaried to a particular
processor. You then create partitions on virtual volumes associated with each pool;
be sure to distribute the partitions across all the pools. This configuration allows
SMF to manage the physical storage of files and ensures that the partitions are
distributed across all processors, which enhances the parallel execution of queries.
•
However you partition tables and indexes, you can allow temporary files used
during SQL queries (such as files used for repartitioning or FastSort scratch files)
to be distributed across physical volumes primaried to all processors.
The preceding examples are general suggestions for the use of SMF. You will need to
configure partitions and storage pools according to the requirements of your
environment. For more information about SMF, see the Storage Management
Foundation User’s Guide.
SMF allows multiple virtual volumes to be associated with the same storage pool,
which can cause the storage of two or more partitions of the same file being allocated
to the same physical disk, if that is determined to be the most efficient use of available
space. To ensure that a partition resides on a unique physical volume, you can place it
on a direct volume that is not in any storage pool or use the PHYSVOL option when
you create or alter a table or index.
If a direct volume is also in a storage pool, it becomes more complicated to ensure that
a specified partition is the only one residing on that physical volume. To achieve this
goal, it is generally better to specify a virtual volume for the partition and use the
PHYSVOL option than to specify a direct volume. When you use the PHYSVOL option,
the partition has the benefit of being managed by SMF.
The PHYSVOL option lets you place a partition on a specified physical volume in the
pool. You do not have to use the PHYSVOL option on all partitions in a table. You can
use it for particular partitions that you want to locate on specified physical disks, while
letting SMF manage the physical location of other partitions in the table.