Managing mixed query workloads on the XPS database server

This article describes the new features in informix®extended Parallel Server™ version 8.3, which allow you to customize the environment for each user's session for better performance and more efficient use of database server resources. You can help users and control resources by using the new features listed below.

To specify the initial session environment, create the Sysdbopen () procedure that runs when each session opens the database, and merge the SQL and SPL statements to customize the query behavior that is submitted by each session. In addition to using the appropriate SPL statements, you can specify the following new set environment attributes for users each time they open the database.

The Compute_quota parameter helps those who run multiple small or medium special queries.

The IMPLICIT_PDQ parameter allocates memory based on the requirements of each query and each query operator.

The Tmpspace_limit parameter prevents any query from monopolizing the TEMP space.

To adjust the session environment later, use the new Onmode command.

To manage and analyze queries in more detail, use informix®i-spy™ version 2.0.

Combining these features with standard good practices in your database design and database server management will make managing ever-changing workloads easier.

Changing usage of data warehouses and data marts

Previously, designing and adjusting a data warehouse database was used to run large, Easy-to-understand queries, but data Warehouse usage was changing rapidly. Users are now accessing large databases in a variety of ways, using many different tools, including SAS for statistical reporting, brio and Cognos for predictive data mining, and third-party OLAP tools for multiple types of data analysis.

When users run applications that allow them to write special queries, you can no longer assume that you can adjust the queries that run on your Data warehouse database. The scope of a special query is from a simple, specified fiscal year statistic analysis to a complex algorithm designed to predict customer behavior or detect fraud. And these queries may run concurrently with large queries that you design for your database. As the business intelligence definition expands, all these uses of the data Warehouse will be required.

The main problem with DBAs and DBSA in this environment is to ensure that users running a variety of queries can effectively use database server resources. When you adjust a single collection of environment variables and configuration parameters for a large DSS query, the database server cannot efficiently run many other types of queries. The problem raises other questions:

Some queries may get more concurrent processing power than they need, and thus hinder the execution of other queries.

Other queries may get more (or possibly worse, less) memory than they need.

If the query gets less memory than they need, they overflow into TEMP space, which can degrade performance. Also, overflow queries may use too much temp space, and other overflow queries cannot get temp space to complete their execution and therefore terminate abnormally.

Informix Extended Parallel Server version 8.31 introduces several new features that allow you to adjust the environment for each session to properly manage its requirements. New features allow you to manage memory usage, CPU VP use, and temp space usage for a particular session or for all sessions.

In addition, you can use I-spy, a daemon that intercepts queries before they are sent to the database server, to more accurately analyze database server usage and manage users and queries. I-spy collects information about the query and controls the behavior of the query by enforcing the rules that you specify.