Have you ever used database monitoring?

When it comes to traditional database monitoring, monitoring is performed using a method of querying data through an SQL query. However, as the amount of data increases, the load on the DB server increases, which can sometimes degrade system performance. In order to overcome these limitations, recently it is faster and more efficient DMA (Direct Memory Access) methodThis is getting attention.

In this article, I'll compare the differences between the existing SQL query method and the DMA method, and take time to explore the pros and cons of each method.

1. Features of traditional query methods

1) Query-based data collection

In the traditional approach, the monitoring agent queries data by making direct requests to the database via an SQL query. In this process, the agent must constantly communicate with the database server, and the process of requesting and responding to data uses a large amount of resources.

2) Treatment process

1. Send an SQL query: The monitoring agent creates an SQL query and sends it to the database server.

2. Run queries and generate results: The database server runs the query and produces results.

3. Send results: Send generated results to agents over the network.

4. data processing: The monitoring server processes the data received from the agent and provides it to the user.

3) Limitations

Querying large amounts of data puts a significant load on the database server, which is likely to degrade system performance. Also, data collection during a system failure or hang situation can be difficult.

2. Features of the DMA (Direct Memory Access) method

1) Overview of the DMA method

The DMA (Direct Memory Access) method is a method of extracting necessary data by directly accessing the database server's memory. This approach is particularly useful in environments where large amounts of data must be processed quickly.‍

2) Treatment process

1. memory access: The DMA method directly accesses the database server's memory to extract the required data.

2. data transmission: Sends the extracted data to the agent over the network.

3. data processing: The monitoring server processes the data received from the agent and provides it to the user.

3) Advantages

• Ideal for processing large amounts of data: Effective for large-scale data analysis and real-time streaming processing
• Can be collected even in the event of a failure: Data can be collected even in a system failure or hang situation

3. Differences between traditional query methods and DMA methods

‍‍

🧐 Why does the query-based approach consume more network or CPU resources?

The main reason why the query-based approach consumes a lot of network and CPU resources Persistent execution of database querieset Results delivery Because of the process.

• Execute an SQL query: The agent must write and execute an SQL query to gather data. At this time, the database consumes CPU resources to process queries and generate results.

• network traffic: The results of the SQL query are delivered over the network to the agent. More data means more network traffic, which consumes more network resources to send results.

• Frequently executed queries: If the monitoring cycle is fast, the CPU and network resources will continue to be used while repeatedly executing queries. In particular, the database server must process a large number of queries, which can cause significant load.

Eventually, multiple database operations are required to query data and return results, and queries that are executed periodically consume a lot of resources.

‍

🧐 If the DMA method is good, shouldn't everyone use the DMA method? Why do I need to write a query?

The DMA method is definitely in terms of performance Fast and efficientHowever, in every situation Not the best choice. There are also cases where the query method is still necessary or advantageous.

1) The query method is good for simple queries

• The DMA method, which directly accesses the database's memory, requires complex implementation and is not supported by all databases. SQL queries, on the other hand, are supported by most databases. Simple queryme Small-scale data processingThe query method may be more appropriate for

2) Limitations of supported databases

• The DMA method is often only supported by certain databases, such as Oracle. Since not all databases support the DMA method, it is often necessary to use the query method in environments using various databases.

3) When resource consumption is low

• If the database is small or if you monitor data that does not change frequently, you can monitor reliably enough even with the query method without having to bear the complexity of the DMA method.

4) When you want accurate query results

• Because SQL queries can be accurately filtered and sorted through conditional clauses, the query method is more appropriate when complex query conditions are required. The DMA method quickly extracts data from memory, but processing complex conditions can be limited.

4. Database monitoring, how do I get started?

Watap supports both the query method and the DMA method.

• DB that supports query methods: MySQL, SQL Server, Tibero, CUBRID, Redis, MongoDB, Oracle
• DMA method support DB: Oracle Pro

Watap database monitoring supports a variety of environments, from open source to cloud-based databases, and helps monitor the status of multiple DB instances on a single screen in real time through key performance indicators collected in seconds.

Since the agent installation location can be flexibly configured, it can be deployed on the DB server without burden, and the root cause of the problem can be quickly and accurately traced through integrated analysis linked to applications, servers, and logs. You can manage database performance intuitively and efficiently through Watap's unique features, such as real-time session tracking, SQL Elapse Map visualization, and integrated log views.

Go to Watab Database Monitoring →

5. corollary

What is the data processing method Key factors that determine the performance and efficiency of an applicationThis is it. The traditional SQL query method is suitable for general data queries, but if you need to process large amounts of data quickly, consider the DMA method.

In order to achieve optimal performance in a specific environment, it is important to understand the characteristics of each method and choose a strategy that suits the environment. Please consider the pros and cons of both methods in a balanced manner and choose the monitoring method that suits your situation.