How not to turn into a dragonfly if you have many different databases

In the macro photo, the eyes of a dragonfly. They have a faceted structure and consist of approximately 30,000 hexagonal facets, which allows the dragonfly to look at almost 360 degrees (except for the direction “straight back”). Useful skill if you are a dragonfly.

When the organization has a “zoo” of databases, and their unification is not even visible on the horizon, efforts must be made to manage and monitor their work. Look again at the dragonfly.

In this article, we will talk about the monitoring tool Foglight for Databases, which combines monitoring of SQL Server, Oracle, MySQL, PostgreSQL, DB2, SAP ASE, MongoDB and Cassandra in one console. It also has a touch of DevOps in terms of logging changes to the database configuration. First things first. Under the cut a lot of screenshots.

Why did we mention DevOps before kat? One of the favored Foglight features is the ability to manually mark releases on the timeline and generally any changes. Developers can make changes themselves and after a while get feedback here in the form of a decrease or increase in productivity. In automatic mode, tracked changes are also noted: changes in the Execution Plan or database configuration - all this will be present on the chart.



Examples of changes.



From here, you can go to another view and compare the changes in the Execution Plan.



Here are a couple of good ways to audit changes, but on a regular basis, running such reports is not very convenient.





How well established is the communication channel between developers and DBAs? Do all colleagues know about the changes in time? What if the new release generated a sporadic problem that began to appear on a regular basis and slows down the application?

We do not want to say that this tool will finally establish contact between developers and operation, however, it will add developers an additional channel for receiving feedback.

Further in the article we will show monitoring capabilities for each of the supported databases.

Microsoft SQL Server

A key feature of SQL Server monitoring in Foglight for Databases is the presence of the Performance Investigator tool, which performs multivariate analysis of database performance in sections of databases, long queries, sessions, users, executable scripts, workstations and applications.



Another feature is support for SSIS, SSRS and SSAS technologies. For example, below is an overview view with SSIS metrics.



From the view above, you can “fail” to the level with details to packages and identify problematic ones.

Additionally, Foglight has a view with configuration variables, where you can track changes in the value of a particular variable.



Many views duplicate reports that you can build in Management Studio. Monitoring is carried out remotely, and the total overhead does not exceed 2%. The current version of Foglight supports versions of SQL Server from 2005 to 2017.

Oracle

To monitor an Oracle database, Foglight also has a Performance Investigator tool. The concept of its work is largely similar to the approaches for monitoring SQL Server, based on a multidimensional analysis of database performance.





Supported technology is RAC, Exadata, ASM and Data Guard.

My sql

My SQL monitoring covers the main indicators (including replication as one to one, and one to many) and tracks changes in the database configuration similarly to SQL Server and Oracle. You can create your own configuration templates, the compliance with which will also be monitored.

Foglight can control the parameters of query execution in the database. Does this with the explain method. There are special views on the tables of various My SQL databases on a single screen. From a single point, you can control hundreds of databases with a small overhead (no more than 2%).

Postgre sql

In the module for monitoring Postgre SQL there are views a'la helicopter view for an overview of key database performance indicators, an overview of table indicators.



Replication monitoring and WAL (Write-Ahead Logging) execution monitoring are supported on both the sender and receiver sides.

In the Foglight console, you can customize the presentation with performance metrics for critical queries and on one screen compare them with similar metrics on another database. For each such query, you can also perform explain analyze and monitor the relevant metrics.



PostgreSQL database monitoring is also performed remotely, and the overhead does not exceed 2%.

DB2

After monitoring is configured, the basic DB2 metrics can immediately be seen on the aggregate dashboard.



DB2 database monitoring is performed using an agentless method with an overhead that does not exceed 2% on average.

In one of the interfaces, you can control the load on agents by applications that work through these agents.



In the screenshot below, a dashboard with locks. A report can be made from each dashboard. Including on a regular basis.



If necessary, you can track heavy requests.



Using the module for the DB2 DBMS, you can control the configuration and automatically set up monitoring of new databases.

SAP ASE

For each SAP ASE database monitored, there is an aggregate view for viewing basic health metrics.



More detailed data is available for each database engine. Switching between tabs, you can see related processes and compare performance.



In the corresponding view, you can track performance indicators in multidimensional form according to requests, procedures, users, locks, and other related items.



The module for SAP ASE supports remote monitoring. Based on all the data collected, you can create your own dashboards and reports.

Mongodb

As with previous DBMSs, MongoDB monitoring is performed remotely. After setting up monitoring, an aggregate view with basic monitoring metrics will be available.



At the top of the dashboard there are links to additional views, on which you can view metrics for databases, operations, logs, etc. ... For example, below is a view of operations.



When profiling is enabled in the DBMS settings, you can select and display all relevant metrics on the dashboard. If the database has a replica, Foglight will also control things like member status, health and others.

An additional useful functionality is monitoring the configuration of nodes, comparing with the standard one and alert in case of changes.

Cassandra

Installing the agent on the nodes is not required, everything happens remotely and with minimal overhead. After setting up Cassandra monitoring in Foglight, a set of dashboards with database performance indicators appears.



Foglight supports keyspace control for nodes in a cluster, their performance comparison (the number of accesses to objects, read / write metrics, delay). Nodes can also be compared by configuration, number of processed messages, error messages, etc.



In the screenshot below, the Foglight for Cassandra interface and JVM performance view.



From all the collected metrics, you can design your own dashboards and reports.

Conclusion

Thank you for reading and watching to this place. To summarize.

1. Foglight for Databases supports monitoring of SQL Server, Oracle, MySQL, PostgreSQL, DB2, SAP ASE, MongoDB, and Cassandra.
2. In addition to the performance of the databases themselves, Foglight also controls their configuration.
3. Data for different DBMSs can be combined on one dashboard (what if you need to?).
4. Monitoring of all DBMSs is performed remotely with an overhead that does not exceed 2%. From one point you can control all the bases.
5. Foglight for Databases has a REST API from which you can collect metrics or events into external dashboards, reports or alerts. If there is an umbrella monitoring system, it can also be integrated into it.
6. In addition to the DBMS, Foglight can also virtualize (VMware, Hyper-V, Openstack), storage systems, servers, AD, Exchange.

You can take a closer look at Foglight for Databases. You can leave an application for a pilot project or obtaining a distribution kit and test licenses (if you want to do everything yourself) through the feedback form on the website .