Monitoring Performance of Configuration Environment

The performance of a configuration environment depends on multiple factors, including the total number of configuration objects, the number of objects of a particular type, and the size of data associated with each object. Configuration Server and Configuration Server Proxy startup performance depends on the total number of all objects and all configuration options that need to be read and processed. Startup time of a client depends on the number and size of objects of particular types this client is loading.

Configuration Database Performance Monitoring Scripts

Configuration Database performance monitoring is based on a set of scripts to retrieve statistics from the Configuration Database that, together, provide reports on overall usage (number of objects of each type and the amount of data attached to each object), the ten largest objects (in terms of the number of configuration options) per type, and objects larger than 20 kb (in terms of the size of the associated configuration options) per type. With these statistics in hand, it is possible to trace the configuration areas that consume most of the Configuration Server resources, both overall and for particular types of clients (when client behavior and configuration dependencies are known).

The following scripts are available:

• csv_objtype_sizes.sql: Calculates data usage for 37 major types of configuration objects.
• csv_largest10_objbytype.sql: Provides object names and associated sizes of their configuration options, in characters, of the ten largest objects for every major type.
• csv_biggerthan_objbytype.sql: Provides object names and associated sizes of their configuration options, in characters, of all objects of a particular type for which the size of its options exceeds a given upper limit.

Limitations

The scripts do not support the following:

• Configuration Database older than version 8.5 with a non-English locale.
• PostgreSQL older than version 9.0

Running the Performance Monitoring Scripts

The scripts can be executed using DBMS utilities or from the command line.

Using DBMS Utilities

Run the script using the corresponding DBMS utility, as follows:

• MSSQL scripts: Microsoft SQL Server Management Studio 2005 or newer
• Oracle scripts: Oracle SQL Developer 1.5 or newer
• PostgreSQL scripts: pgAdmin III 1.12 that supports Postgre 9.0 or newer

The script output appears on the console. Capture the output and save it as a .csv file, suitable for further analysis.

From the Command Line

The Configuration Database Maintenance Scripts IP also contains command-line scripts for Windows (MS SQL, Oracle, and Postgre databases) and Linux (Oracle and Postgre databases) operating systems. Each command-line script runs the three SQL scripts, and writes the output to a .csv file in a specified results directory. This directory must exist before running the command-line script.

Each command-line script generates three .csv report files, as follows:

• csv_biggerthan_objbytype_<DBMS_TYPE>.csv
• csv_largest10_objbytype_<DBMS_TYPE>.csv
• csv_objtype_sizes_<DBMS_TYPE>.csv

The command-line scripts are listed in the following table.

DBMS	Operating System
	Windows	Linux
Windows MS SQL Server	\mssql\monitor_MSSQL_DB.bat	Not available
Oracle	\oracle\monitor_ORACLE_DB.bat	/oracle/monitor_ORACLE_DB.sh
Postgre a	\postgre\monitor_POSTGRE_DB.bat	/postgre/monitor_POSTGRE_DB.sh
a For Postgre scripts, configure the PostgreSQL server to allow a client connection with the host on which the scripts are run.

Command Syntax

Run the script from the folder in which it is located, using the following syntax:

<script> <USER> <PASSWORD> [<HOST> <PORT> or <HOST[\INSTANCE]>] [<SERVICE_NAME> or <DB_NAME>] <CSV_RESULT_DIR>

where:

• <script> is the name of the script, as provided in the table above.
• <USER> is the name of a user authorized to access the database.
• <PASSWORD> is the password required for the authorized user to access the database.
• <HOST> is the name of the host on which the database is located.
• <PORT> is the number of the port on that host that is used by the DBMS.
• <HOST\INSTANCE> is the database host and instance, if not the default.
• <SERVICE_NAME> is the Oracle Instance Service Name, that is, the SERVICE_NAME value in TNSNAMES.ORA.
• <DB_NAME> is the name of the Database.
• <CSV_RESULT_DIR> is the path to the directory in which the output .csv files will be stored.

Tip

To see the syntax on-line, with no values for parameters, enter the script name and press ENTER.

Examples:

MS SQL Server

.\monitor_MSSQL_DB.bat sa password localhost\SQLEXPRESS TestDB D:\MF_mon\results\mssql
.\monitor_MSSQL_DB.bat sa password somehost SomeTestDB D:\MF_mon\results\mssql

Oracle

./monitor_ORACLE_DB.sh sourcedb sourcedb localhost 1521 orcl.genesys.com '/home/genesys/results/oracle'

Postgre

./monitor_POSTGRE_DB.sh postgres password localhost 5432 TestDB '/home/genesys/results/postgre'

csv_objtype_sizes.sql

This script calculates usage for major types of configuration objects, including:

• The total number of objects of this type.
• The number of objects of this type that have configuration options associated with them.
• The total size, in characters, of all options for all objects of this type.

Running this script periodically reveals an increase in object count and an associated increase in options size (if any) because of new objects being added. This provides overall statistics that can be further analyzed using other monitoring scripts and tools.

Genesys recommends that you run this script daily. The audit trail for a given day can be used to determine the actual objects added on that day.

This script produces CSV-formatted output on the console if run in the DBMS utility, or in the csv_objtype_sizes_<DBMS_TYPE>.csv file if run from the command-line. There is one header row, and 37 data rows in the output. Each data row represents the data for one major object type. 37 major types of configuration options are analyzed. The header row identifies each column in the data rows, as follows:

• type: The type of object, as visible in Genesys Administrator. (The one exception is that a User or Agent object type is named Person.)
• object_count: The total number of objects of this type.
• objects_with_opts: The total number of objects of this type that have associated configuration options.
• opt_size_chrs: The total size of all configuration options (including the total number of characters in strings in key-value pairs) of all objects of this type, expressed as the total number of characters or bytes.

Sample Output, in CSV format

type,object_count,objects_with_opts,opt_size_chrs 
Switch,9,4,480 
DN,54604,41982,12373545 
Person,23656,22349,357163117
...

csv_largest10_objbytype.sql

This script generates a drill down report enabling you to determine if any particular object of a given type is significantly bigger than the others, possibly requiring further review or follow up with the object's owner or its consumers.

Running this script daily can reveal the addition of a significant number of options to particular objects. The audit trail for a given day can be used to determine who actually made the changes.

This script produces CSV-formatted output on the console if run in the DBMS utility, or in the csv_largest10_objbytype_<DBMS_TYPE>.csv file if run from the command-line. There is one header row, and up to 370 data rows in the output. Each data row represents the data for each object that falls in the 10-largest category for the given object type. The rows are grouped by object type, with a maximum of ten rows for each type (the actual number will be less if fewer objects of the given type existed or had no associated configuration options). The rows are also sorted by size, in descending order, within each group. The header row identifies each column in the data rows, as follows:

• type: The type of object, as visible in Genesys Administrator. (The one exception is that a User or Agent object type is named Person.)
• object_name: The name of the object as stored in the Configuration Database, enabling a trace of the object in Genesys Administrator.
• tenant_name: The name of the Tenant object to which this object belongs. This information is not provided for Folder object types.
• object_dbid: The DBID of the object, enabling a trace of the object in the database and the audit trail.
• opt_size_chrs: The total size of all options (including the total number of characters in strings in key-value pairs) for the object.

Sample Output, in CSV format

type,object_name,tenant_name,object_dbid,opt_size_chrs
Switch,MMK_FID1_SIPSwitch,Resources,101,120
Switch,MMK_FID2_SIPSwitch,Resources,102,120
Switch,RTP_FID1_SIPSwitch,Resources,103,120
Switch,RTP_FID2_SIPSwitch,Resources,104,120 
Person,a274533,Resources,29032,59407
Person,a270369,Resources,29256,54627 
Person,a360983,Resources,28542,54335 
Person,a545582,Resources,28380,50052 
Person,a335660,Resources,28526,49707 
Person,a505259,Resources,28486,49111 
Person,a023647,Resources,29113,48669 
Person,a395158,Resources,28501,48259 
Person,a536690,Resources,28379,45615 
Person,a495796,Resources,29150,44869

csv_biggerthan_objbytype.sql

This script generates a drill down report in which you can identify the configuration objects per object type that impact performance the most, and/or are out of the range of expected size for this type. Audit logs can be used to trace details of a particular change.

This script produces CSV-formatted output on the console if run in the DBMS utility, or in the csv_biggerthan_objbytype_<DBMS_TYPE>.csv file if run from the command-line. It provides data for each object where the total size of the configuration options is greater than a threshold (currently 20,000 characters or bytes). The output contains one header row, and a variable number of data rows. The number of data rows can be large (providing data for all objects of every major type, if the threshold is set too low). Rows are grouped by object type and ordered by the size of options, in descending order, within each type. The header row identifies each column in the data rows, as follows:

• type: The type of object, as visible in Genesys Administrator. (The one exception is that a User or Agent object type is named Person.)
• tenant_name: The name of the Tenant object to which this object belongs. This information is not provided for Folder object types.
• object_dbid: The DBID of the object, enabling a trace of the object in the database and the audit trail.
• opt_size_chrs: The total size of all options (including the total number of characters in strings in key-value pairs) for the object.

Sample Output, in CSV format)

type,object_name,tenant_name,object_dbid,opt_size_chrs
Person,a274533,Resources,29032,59407
Person,a270369,Resources,29256,54627
Person,a360983,Resources,28542,54335
Person,a545582,Resources,28380,50052
Person,a335660,Resources,28526,49707
....