Cache Dbspace for Direct-Attached Storage

Add direct-attached storage devices, such as solid-state disks (SSDs), to a multiplex node or a simplex server by configuring a cache dbspace local to each node. Local direct-attached storage provides an SSD buffer pool extension to your database, which can improve I/O performance in general, and shared SAN device performance in particular. Although multiplex configurations gain the most benefit from direct-attached storage, simplex servers can benefit too.

The cache dbspace is an independent dbspace, like the IQ main store (IQ_SYSTEM_MAIN), IQ temporary store (IQ_SYSTEM_TEMP), and RLV (IQ RLV STOREs) dbspaces. Like all dbspaces, the cache dbspace can contain a single file or device, or multiple files or devices. You can configure a maximum of one cache dbspace per simplex and multiplex node.

After you create and configure the cache dbspace, the server caches copies of locally used read-only pages—both affinitized (preferred) and nonaffinitized pages—in the cache dbspace. In subsequent reads from the IQ main store dbspace files, the server first checks the cache dbspace to see if the page exists. If it does exist, the server reads the page from the high-speed SSD cache dbspace, rather than the shared SAN IQ main store dbspace, thereby improving performance.

You see maximum performance benefits if the cache dbspace is large enough to contain the node's entire working set of pages. If the cache dbspace is smaller than the working set of pages, the performance benefit is more modest.

The cache dbspace works with the data affinity feature to increase performance. Data affinity accesses cached data to boost query performance in multiplex servers. Affinity is automatically enabled for multiplex databases. Affinity tries to store the pages of partitions assigned to a node in the main buffer cache memory for that node. However, if an application's working set of pages exceeds the main buffer cache memory for the node, add direct-attached storage as a secondary fast access layer.

• Creating the Cache Dbspace
  Create a cache dbspace to add direct-attached storage (DAS) devices to a simplex or multiplex node. The cache dbspace increases performance by reducing reads from the storage area network. Each multiplex and simplex node can have one cache dbspace.
• Dropping the Cache Dbspace
  Delete (drop) the cache dbspace from a simplex or multiplex node. Unlike other dbspaces, the cache dbspace does not need to be empty before you drop it.
• Adding Storage to the Cache Dbspace
  Add additional direct-attached storage to the cache dbspace by specifying a file or device path.
• Removing Storage from the Cache Dbspace
  Remove direct-attached storage from the cache dbspace by dropping files or raw devices.
• Disabling and Reenabling the Cache Dbspace
  Change the online/offline status of a cache dbspace to disable a cache dbspace without dropping it, and to reenable a disabled cache dbspace.
• Changing the Readonly/Readwrite Status of a Cache Dbspace Dbfile
  Change a single dbfile in the cache dbspace to read-only or read-write status.
• Monitoring the Cache Dbspace
  Use the sp_iqstatus, sp_iqdbspace, sp_iqfile, and sp_iqcheckdb stored procedures to monitor key performance indicators for cache dbspaces and their dbfiles.