Contents
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.
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
....