Introduction

This document explains how to send notifications by mail regarding events occurring in Denodo Virtual DataPort.

In some situations we need to be alerted if a specific event takes place in Denodo. In order to achieve that, we can use different techniques detailed in this article: log4j appenders, JMX alerts and Denodo Scheduler Handlers.

JMX notifications

When to use 

To register alerts regarding Memory/CPU usage information or any other information available in the MBeans exposed through JMX by the Denodo Platform.

Description  

Java Mission Control (JMC) is an Oracle tool for managing, monitoring, profiling, and troubleshooting Java applications. We will see how to use this tool in order to monitor the Denodo Platform MBeans and to generate alerts based on their status.

Steps to configure

JMC is included as part of the Java distribution, if a JDK is already installed JMC can be executed (otherwise the Java Development Kit needs to be installed).

Note that JMC 9.0 can run on JDK 17 and later, while JMC 9.1 can run on JDK 21 or later. Please see the JMC version 9 documentation for more information.

Additionally, JMC 8.0 and JMC 8.0.1 can run on JDK 8 or later, while JMC 8.1.0 can be run on JDK 11 or later. Please see the JMC version 8 documentation for more information.

However, both JMC 8 and JMC 9 supports Java Management Console (JMX) monitoring and Java Flight Recorder (JFR) profiling of JVMs running on JDK 7u40 or later across all supported platforms.

Please utilize either Java Mission Control version 8 or Java Mission Control version 9 depending on the version of JDK.

The following steps demonstrate how to set the JDK of the JMC tool:

1. Open the following file:

1. <JMC_HOME>/JDK Mission Control/jmc.ini

2. Add a new line under the --launcher.appendVmargs property.
3. Add the following property on this new line:

1. --vm <JDK_PATH>/jre/bin

Once the tool is running, perform the following steps:

1. Execute jmc.exe located at <JDK_PATH>/bin.
2. The “JVM Browser” shows the processes running locally. When the VDP Server is running in a remote machine you can connect with a remote JVM using the option “File” > “Connect” > “Create a new connection”.
3. From the JVM Browser search for the VDP Server process. You can identify the process by the process ID (searching previously for this number) or identifying the element which command line starts as following:

com.denodo.util.launcher.Launcher com.denodo.vdb.vdbinterface.server.VDBManagerImpl...

4. By clicking on the JVM Browser element and double clicking on “MBean Server” JMC will connect to the VDP Server.
5. On the MBeanServer wizard a Triggers tab is available at the bottom. This will open a wizard where trigger rules can be configured (activated and deactivated) for several events like CPU usage or Deadlocked threads. New trigger rules can also be created based on the exposed MBeans.

As an example we are going to create a new Trigger that sends an email every time that the number of active requests exceeds a limit number. The steps will be the following:

1. On the Triggers tab click on “Add…” button.

2. Search for the MBean containing the information and click on “Finish”. In the KB article “Monitoring Denodo servers with JMX” more information about the available MBeans can be found.

3. A new wizard called “Rule Details” will show up. In that wizard the values for triggering the condition can be setted. In this example the “current value” will be the limit that, when reached, the email will be sent.

6. Once the condition has been defined, we need to make sure that JMC will evaluate the values properly so the condition can be checked. This means that we need to “cast” the attribute name indicating how it can be measured.

To do that, go to the MBeans tab and search for the appropriate bean and the attribute to be measured. In this case, the bean is “VDBServerManagementInfo” and the attribute “ActiveRequests”.

Once located, right click on “ActiveRequests” to indicate to JMC how it needs to manage the retrieved value. In this case, the value will be interpreted as a number, in units of 1.

7. When the trigger is configured by clicking on the “Action” tab the “Send e-mail” action can be configured.

8. In addition, the SMTP server has to be configured in order to be able to send the email. For that go to Window > Preferences > Java Mission Control > JMX Console > Communication and set up the SMTP Server configuration.

Log4j Alerts

When to use

To get an e-mail alert of an event logged in the Denodo Virtual DataPort logs.

Description

Virtual DataPort uses the logging library Apache Log4j to log the activity of the Virtual DataPort server and its Administration Tool. Log4j allows defining the log level (TRACE, DEBUG, INFO, WARN, ERROR or FATAL) for different categories. The configuration of Log4j for the Virtual DataPort server is controlled by the file:

<DENODO_HOME>/conf/vdp/log4j2.xml

For the administration tool by the file:

<DENODO_HOME>/conf/vdp-admin/log4j2.xml.

In these files you can find three main components: Appenders, PatternLayouts and Loggers.

A logger defines a log level for a category and they can be assigned to appenders previously configured. For instance:

<RollingFile name="VDBOUT" fileName=".../logs/vdp/vdp-cache.log">

        <PatternLayout pattern="[%t] %d{yyyy-MM-dd'T'HH:mm:ss.SSS}/>

</RollingFile>

<Logger name="com.denodo.vdb" level="error">

      <AppenderRef ref="VDBOUT" />

</Logger>

This configuration indicates that the category “com.denodo.vdb” on an error event will write the output of the log to the file configured in the VDBOUT appender following the defined pattern layout.

The SMTPAppender is an appender which sends an e-mail when a specific logging event occurs, typically on errors or fatal errors. Therefore, instead of writing the output to a file it is possible to configure a destination email address.  

Steps to configure

1. Configure the appender. The appender contains the information related to the SMTP server used for sending the email.

                <SMTP name="EMAILALERT"  

                        subject="VDP Log Alert"

                        to="toUser@mydomain.com"

                        from="fromUser@mydomain.com"

                            smtpHost="mail.mydomain.com"

                        smtpPort="25"

                             smtpProtocol="smtp"

                        smtpUsername="mysmtpusername"

                             smtpPassword="mysmtppassword"

                        smtpDebug="true"

                        bufferSize="512">

                    </SMTP>

2. Configure the loggers. Once the appender is configured, any logger with a log level for a category can be configured indicating the email appender as output:  

        <Logger name="com.denodo.vdp" level="FATAL">

<AppenderRef ref="EMAILALERT" />

</Logger>

Denodo Scheduler Handlers

When to use

To send an email alert about the failure of a particular Job in Denodo Scheduler.

Description

When configuring Jobs in Scheduler it is possible to configure handlers. A handler is an action that is executed as the last step before completing the execution of a job (if the conditions are met). Denodo Scheduler includes as a built-in handler the “Mail” handler. This handler allows to send an email with the result report on the job execution. It requires a list of destination email addresses to be specified and allows several conditions to be set (determining in which cases the mail has to be sent).

Steps to configure

In order to be able to send the email Scheduler requires a SMTP server to be configured. This configuration has to be set in “Administration” > “Server” > “Mail Settings”.

Once the mail server is set you have to follow this steps:

1. Select the Job (or create a new one).
2. Click on the “Handlers section” of the Job configuration.
3. Select “Add Handler” > “mail”.
4. Add the target mail addresses and the configuration “When to send”.
5. Save the Job.

Next time the Job is executed, before finishing the execution, Scheduler will check if the “When to send” conditions are met and, if it is the case, it will send the Job report to the configured target emails.

Performance Monitoring Alerts in Denodo Scheduler

When to Use

To configure email alerts for detecting performance issues on the Denodo server. These alerts can help in proactive management by notifying administrators when certain performance thresholds are exceeded.

Description

The Denodo Platform includes capabilities to monitor system metrics such as CPU usage, which are visualized through the Denodo Dashboard. This functionality allows the creation of interactive dashboards for visualizing and analyzing logged information, including system performance metrics such as CPU usage. Alerts based on these metrics can be configured to notify administrators when certain thresholds are exceeded, ensuring timely intervention to address potential issues.

Steps to Configure

1. Configure the SMTP Settings:

• Follow the Denodo documentation to set up the SMTP server that will allow the Denodo Scheduler to send email alerts. For detailed SMTP configuration, refer to the Denodo Scheduler Mail Settings Documentation.

2. Create and Configure Monitoring Jobs in Denodo Scheduler:

• Create new jobs that will execute queries to monitor different performance metrics based on your needs. Each job will be linked to a specific performance alert.

3. Configure an Email Alert Handler:

• For enhanced email alert functionalities in Denodo Scheduler, consider using the Denodo Email Exported Files Custom Handler. This handler allows for greater customization of email alerts, enabling the inclusion of job execution reports and exported data directly in the email body or as attachments. It supports HTML content for better formatting and provides options to include comprehensive job details, enhancing the immediate relevance and readability of alerts.

Example Alerts

High CPU Usage Alert

Query to Monitor CPU Usage:

This query calculates the average CPU usage over the last 15 minutes and triggers an alert if the average exceeds 40%. Monitoring the average, rather than the peak usage of a single query, helps in identifying sustained high usage. This approach is more indicative of underlying issues than transient spikes and is crucial for understanding when the system is consistently operating above normal operational thresholds.The use of a 40% threshold in this alert is set to detect significant, levels of high CPU usage, providing a balanced sensitivity for early detection without being overly reactive to minor fluctuations.

The ‘cpupercentage’ metric used in the query for the High CPU Usage Alert is part of the Denodo Performance Reports dashboard. This dashboard consolidates various performance metrics captured by the Denodo Monitor Tool, organized into distinct tabs for easy navigation and analysis. By leveraging the Denodo Dashboard, users can visually explore these data points.

Denodo Performance Reports section into Denodo Dashboard.

Slow Query Detection Alert

Query to Monitor Slow Queries:

This query identifies and lists all queries that have taken more than 1 minute to execute within the last 15 minutes. Each result includes details such as the server name, query ID, start and end times, query content, duration, and request type, which provide comprehensive information for diagnosing performance issues.

To configure the alert in the Denodo Scheduler, use the option 'The number of exported documents is more (or equals) than: 5' to set the threshold for slow queries before triggering an email alert through the mail handler.

The Denodo Scheduler will export the results of the querie into a CSV file. This CSV file provides detailed information on the slow queries that triggered the alert. By reviewing the CSV file, administrators can identify specific queries that are causing performance issues, analyze their characteristics, and take appropriate actions to optimize or troubleshoot the problematic queries.

Concurrent Query Detection Alert

Query to Monitor Concurrent Queries

This is a more complex query that identifies and lists all queries that are running concurrently within the last 15 minutes. Each result includes details such as the server name, query ID, start and end times, query content, duration, and request type, providing comprehensive information for diagnosing potential performance bottlenecks.

In this example, we use a self-join on the bv_request_notification view to determine the number of concurrent queries. The query joins the bv_request_notification view with itself (q1 and q2). The join condition checks for overlapping time periods where the starttime of one query (q1) is before the endtime of another (q2) and the endtime of q1 is after the starttime of q2. This identifies all queries that overlap in time with each other.

The HAVING COUNT(*) >= 10 clause sets a threshold of 10 concurrent queries. This threshold helps in detecting significant levels of query concurrency.

In this case, due to the nature of the query, we need to set the threshold for exported tuples to 1 in the Denodo Scheduler. The query checks for concurrency by comparing each query's start and end times with others, which means that queries can be concurrent with a query in the current time window while having ended before it. As a result, a single tuple in the output indicates that there are at least 10 concurrent queries within the relevant time window, even if not all of them started or ended within the last 15 minutes.