SQL/MP Installation and Management Guide

Managing a Distributed Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
12-12
Design Examples
Design Examples
These examples suggest the potential benefits of using the _SQL_CMP_CPUS
DEFINE:
•
A development-and-test environment and a production environment share a single
system. By limiting the development and test activity to certain processors, you
enhance the performance of the production queries.
•
You have an existing OLTP environment, possibly supported by batch applications,
and an expanding DSS environment. As (or before) you scale up the DSS
environment, it shares processor resources with the OLTP environment. By
segregating the batch queries and DSS queries into separate processors, you can
improve the performance of both.
The mixed workload feature already allows you to prioritize DP2 requests for
different queries. Limiting processor use for different queries increases your ability
to manage query performance in a multiple-use environment. For a brief
description of the mixed workload feature, see the Introduction to NonStop
SQL/MP.
These considerations apply to the _SQL_CMP_CPUS DEFINE:
•
It only operates on queries that use parallel execution plans. The DEFINE
influences the location of ESPs but not the location of the master executor, so
serial plans are not affected by it.
•
It does not determine the processor locations of multiple sort processes used in a
parallel execution plan. However, the ESPs communicating with the sort processes
are limited to the processors specified in the DEFINE.
•
It does not determine the processor locations of disk processes involved in the
query. Partitions accessed by the query determine the locations. Each partition is
accessed by a disk process in the processor managing that partition’s disk volume,
regardless of whether the processor is specified as usable by the DEFINE.
Consequently, when you use this DEFINE to limit the usable processors, you can still
read tables partitioned across disk volumes primaried to “unusable” processors. This
gives you two basic design options:
•
You can limit the usable processors to a smaller or different set than the set of
processors that manages access to the database. This approach makes a single
database available to different types of queries (or applications), but you should
limit the processors used in each type of query.
•
You can partition your tables so that the same set of processors manages data
access and is specified as usable for parallel queries. This choice lets you
completely segregate the processors for separate uses.