SQL/MX Release 2.0 Best Practices

Design Guidelines and Considerations 13
Design Guidelines and Considerations
Physical System Configuration Guidelines
Distribute the disk subsystem across all processors to balance more evenly the I/O processing activity and
thereby minimize processor and disk bottlenecks.
Construct a symmetric hardware configuration where all hardware (especially disk devices) is distributed
over all processors as evenly as possible. Some customers might need to obtain additional I/O enclosures
to achieve a balance. The new XP storage system provides better performance than using internal drives.
Balance resource use and queues. It is extremely difficult to achieve the type of system balance normally
seen on OLTP systems, since ad-hoc queries are much more dynamic. The goal is to eliminate the
resource bottlenecks—usually processor and disk—and worry less about overall resource balance. The
resource queues impact response time, and the resource queues result from excessive workloads
concentrated on a few devices.
Balance database activity across the disk and processor resources. For overly busy disk volumes, move or
split the active partitions and distribute them to less-busy devices.
Balancing and tuning complex database applications is a challenging task, made more difficult when
the underlying hardware is unbalanced. Systems that have an uneven number of disk volumes for each
processor, or an uneven number of I/O enclosures, create a natural imbalance in resource use that is
difficult to tune around. Having only a few CPUs to process the workload results in extremely long queue
lengths, degrading performance for any process running in those processors. Bottlenecks such as these
mentioned here degrade any application. However, severely unbalanced processors and disks are
especially bad for parallel queries.
When a parallel query runs, groups of processes known as executor server processes (ESP) each run a
subset of the query. The results are later combined and returned to the user. When one processor is
extremely busy, the ESPs running in it cannot make adequate progress servicing the query plan. Although
all other ESPs may have completed their tasks, the degraded ESP delays the overall completion of the
query. Therefore, a single system bottleneck is extremely important to resolve; use a graphical interface to
monitor system performance, in addition to monitoring EMS events.
When the I/O processing activity is balanced, disk memory cache is used more effectively, which
improves performance.
Logical Database-design Considerations
For a detailed discussion about designing and implementing a database application on the NonStop
server platform, refer to “Database Design Guidelines for Improving OLTP Performance” in the SQL/MX
Installation and Management Guide.
Physical Database-design Considerations
Primary, Partitioning, and Clustering Keys
Definitions:
• The Primary Key is used to uniquely identify rows in a table.
• The Partitioning Key is used to identify the disk volume locations of the rows.
• The Clustering Key is used to physically order rows
Primary and clustering keys have often been thought of as the same key. By default, the clustering key is
the primary key. However, different columns can be used as the primary and clustering keys. Therefore,
these keys should be thought of as separate key items.
SQL/MX table data is physically maintained in a b-tree format. The organization of the b-tree is based on
the clustering key. The clustering key must be unique, in order to support the b-tree architecture. By