Extending Informix Dynamic Server 2000 Improving UDR Performance

A virtual process is a process that the database server uses to execute queries and perform other tasks, such as disk I/O and network management. A small number of virtual processors (VPs) can carry out tasks on behalf of many client applications because the database server breaks the client-application requests into pieces called threads. The VP can schedule the individual threads internally for processing. Therefore, VPs are multithreaded processes because they can run multiple concurrent threads.

Tip: The database server implements its own threads to schedule client-application requests. Therefore, this implementation is consistent across all platforms. These threads are not the same as operating-system threads, which multithreaded operating systems provide.

Virtual processors are grouped into virtual-processor classes, or VP classes. All VPs in a particular VP class handle the same type of processing. The database server supports the following VP classes.

The following section provides information about how to choose a virtual-processing class for a user-defined routine. For general information about virtual processors, see the Administrator's Guide.

The database server supports the following classes of virtual processors for the execution of a UDR.

The database server defines the CPU VP and the JVP classes.

The CPU virtual-processor class is the primary VP class of the database server. It runs the following kinds of threads:

• All session threads

Session threads process requests from the SQL client applications

• Some internal threads

Internal threads perform services internal to the database server.

Tip: For general information on CPU virtual processors, see the "Administrator's Guide."

The CPU VP class is the default VP class for a user-defined routine. You do not need to specify the CLASS routine modifier in the CREATE FUNCTION or CREATE PROCEDURE statement to have the UDR execute in the CPU VP class.

SPL routines must always run in the CPU VP. Therefore, you do not need to specify the CLASS routine modifier for an SPL routine. The following CREATE FUNCTION statement registers the getTotal() SPL routine, which runs in the CPU VP:

CREATE FUNCTION getTotal(order_num, state_code)
	RETURNS MONEY
...
END FUNCTION

You cannot run an SPL routine in a user-defined VP.

By default, a C UDR runs in the CPU VP class. Generally, user-defined routines perform best in the CPU VP class because threads do not have to migrate among operating-system processes during query execution. However, to run in the CPU VP, the C UDR must be well behaved; that is, it must adhere to the following programming requirements:

• Preserves concurrency of the CPU VP
• Yields the CPU VP for intense calculations
• Does not perform blocking operating-system calls
• Is thread safe:
• Does not modify static or global data
• Does not allocate local resources
• Does not modify the global VP state
• Does not make unsafe operating-system calls

You can relax some of these programming requirements if you run your C UDR in a user-defined VP class. For more information, see User-Defined Virtual-Processor Class.

For DataBlade or external routines, you can designate a user-defined class of virtual processors, called user-defined VPs, to run the routine.

Important: Use of user-defined VPs can result in lower performance because queries normally execute in the CPU VP, and the query thread must migrate to the user-defined VP to evaluate external routines. Thus, you should use the user-defined VP with caution.

You cannot run an SPL routine in a user-defined VP. SPL routines always run in the CPU VP.

To run in the CPU VP class, a C UDR must be well behaved; that is, it must adhere to special programming requirements. Running in a user-defined VP relaxes some, but not all, of the programming requirements of a well-behaved routine. For example, these routines can issue direct file-system calls that block further processing by the virtual processor until the I/O is complete. Because virtual processors are not CPU virtual processors, however, the normal processing of user queries is not affected. However, they still cannot perform local resource allocations because they might migrate among the VPs.

1. When you register an external function or procedure, assign it to a class of virtual processors with the CLASS routine modifier of the CREATE FUNCTION or CREATE PROCEDURE statement.

The CLASS routine modifier specifies the virtual-processor class with the following syntax:

CLASS = vpclass_name

In this syntax, vpclass_name is the name of the user-defined VP class that you have configured in the database server.

2. Configure new user-defined virtual-processor classes in the ONCONFIG file with the VPCLASS configuration parameter.

The following sample ONCONFIG entry creates the user-defined VP class newvp:

VPCLASS newvp,num=3 # New VP class for testing

The num option specifies the number of virtual processors that the database server starts. For the newvp virtual-processor class, the database server initially starts three virtual processors.

Important: If you create a new virtual-processor class, you must remove the SINGLE_CPU_VP parameter from the ONCONFIG file.

When you configure a new class of user-defined virtual processors to run user-defined routines, you must ensure that the name of the class agrees with the name that you assigned to the CLASS routine modifier in the CREATE FUNCTION or CREATE PROCEDURE statement. The class name is not case sensitive.

When you register functions or procedures with the CREATE FUNCTION or CREATE PROCEDURE statement, you can reference any user-defined VP class that you like. The VP class need not exist when the external routine is registered. However, if you try to use a routine that refers to a user-defined VP class, the class must exist and have virtual processors assigned to it. If the class does not have any virtual processors, you receive an SQL error.

For more information on how to choose a virtual-processor class for a C UDR, see the DataBlade API Programmer's Manual. For information on the VPCLASS configuration parameter, see the Administrator's Reference.

You cannot run a Java UDR in a user-defined VP. Java routines always run in a Java VP. You must specify the following CLASS routine modifier when you register a Java UDR:

CLASS = jvp

You can use the onmode and onstat utilities to manage virtual processors. For additional information about onmode and onstat, refer to the Administrator's Reference.

You can add or drop virtual processors in a user-defined VP class or in the CPU VP class while the database server is on-line. Use onmode -p to add virtual processors to the class. For example, the following command adds two virtual processors to the newvp class:

onmode -p +2 newvp

You can monitor VPs with the onstat utility. The -g glo option prints information about global multithreading such as CPU use of virtual processors and total number of sessions. A user-defined VP class appears in the onstat -g glo output as a new process.