eG Database Auto Indexing Test

An index is an alphabetical listing of the terms found on database records and the number of records on which the terms are found. Indexes support the efficient processing of requests to the database. If an appropriate index exists for a table, the database can use the index to limit the number of records it must inspect. The database automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. To avoid this, administrators remedy index fragmentation by reorganizing or rebuilding an index. Without indexes and index rebuild, database must perform a scan, i.e. scan every row in the database tables, to respond to the request. These scans are time-consuming, resource-intensive operations, which if not contained, can degrade performance of the database. To avoid these extensive scans, each table in the database should be indexed. This is where the eG Database Auto Indexing test helps.

This test reports whether the auto-indexing is currently enabled for the eG database and reveals the statistics related to auto indexing of the database.

Target of the test : The eG Manager

Agent deploying the test : An internal/remote agent

Outputs of the test : One set of results for the eG manager being monitored.

Configurable parameters for the test
Parameter Description

Test period

How often should the test be executed .

Host

The host for which the test is to be configured.

Port

The port number at which the specified host listens.

JMX Remote Port

Here, specify the port at which the JMX listens for requests from remote hosts. In the <EG_MANAGER_INSTALL_DIR>\manager directory (on Windows; on Unix, this will be the /opt/egurkha/manager directory) of the eG manager, you will find a management.properties file. Set the port defined against the com.sun.management.jmxremote.port parameter of the file as the JMX Remote Port.

User, Password, and Confirm Password

By default, JMX requires no authentication or security. Therefore, the User, Password , and Confirm Password parameters are set to none by default.

JNDIName

The JNDIName is a lookup name for connecting to the JMX connector. By default, this is jmxrmi. If you have registered the JMX connector in the RMI registry using a different lookup name, then you can change this default value to reflect the same.

JMX Provider

This test uses a JMX Provider to access the MBean attributes of the eG manager and collect metrics. Specify the package name of this JMX Provider here. By default, this is set to com.sun.jmx.remote.protocol.

Timeout

Specify the duration (in seconds) for which this test should wait for a response from the eG manager. If there is no response from the eG manager beyond the configured duration, the test will timeout. By default, this is set to 240 seconds.

DD Frequency

Refers to the frequency with which detailed diagnosis measures are to be generated for this test. The default is 6:1. This indicates that, by default, detailed measures will be generated every time this test runs, and also every time the test detects a problem. You can modify this frequency, if you so desire. Also, if you intend to disable the detailed diagnosis capability for this test, you can do so by specifying none against DD frequency.

Detailed Diagnosis

To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option.

The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled:

  • The eG manager license should allow the detailed diagnosis capability
  • Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Is auto indexing enabled?

Indicates whether/not the auto indexing is enabled for the eG database.

 

The numeric values that correspond to the measure values are as follows:

Measure Value Numeric Value
Yes 1
No 0

Note:

By default, this measure reports the Measure Values listed in the table above to indicate whether/not auto indexing has run today. The graph of this measure however, represents the same using the numeric equivalents only.

Auto indexing rebuild frequency

Indicates how often the auto index should be rebuilt.

Days

 

Maximum rebuild time

Indicates the maximum time duration set for performing auto indexing.

Hours

 

Rebuild secondary indexes only

Indicates the number of secondary indexes that should be rebuilt.

Number

 

Number of indexes available

Indicates the total number of indexes available.

Number

 

Number of primary key indexes available

Indicates the number of primary key indexes available in the eG database.

Number

 

Number of idx indexes available

Indicates the number of idx indexes available in the eG database.

Number

 

Number of tables available

Indicates the total number of tables available in the eG database.

Number

 

Auto indexing status

Indicates the current status of auto indexing.

 

The numeric values that correspond to the current status of auto indexing are as follows:

Measure Value Numeric Value
Running 0
Sleeping 1
Error 2
Not started 3
Interrupted 4

Note:

By default, this measure reports the Measure Values listed in the table above to indicate whether/not cleanup has run today. The graph of this measure however, represents the same using the numeric equivalents only.

Rebuild completed for indexes

Indicates the total number of indexes that are rebuilt.

Number

 

Rebuild completed for primary key indexes

Indicates the number of primary key indexes that are rebuilt.

Number

 

Rebuild completed for idx indexes

Indicates the number of idx indexes that are rebuilt.

Number

 

Rebuild completed for other indexes

Indicates the number of other indexes that are rebuilt.

Number

 

Number of auto indexes failed

Indicates how many times that the auto indexing failed.

Number

 

Total duration of auto indexing

Indicates the time taken for auto indexing.

Minutes

A very high value for this measure could indicate issues auto indexing process.

Has auto indexing run today?

Indicates whether auto indexing has run today or not.

 

The numeric values that correspond to the measure values are as follows:

Measure Value Numeric Value
Yes 1
No 0

Note:

By default, this measure reports the Measure Values listed in the table above to indicate whether/not auto indexing has run today. The graph of this measure however, represents the same using the numeric equivalents only.

Time since recent auto indexing

Indicates the elapsed time duration since the last auto indexing process.

 

With the help of this measure, you can quickly detect whether a scheduled auto indexing occurred on the eG database or not.