Maria SQL Workload Test

Nothing can degrade the performance of a Maria database server like a resource-hungry or a long-running query! When such queries execute on the server, they either hog almost all the available CPU, memory, and disk resources or keep the resources locked for long time periods, thus leaving little to no resources for carrying out other critical database operations. This can significantly slowdown the database server and adversely impact user experience with the server. To ensure peak performance of the Maria database server at all times, such queries should be rapidly identified and quickly optimized to minimize resource usage. This is where the Maria SQL Workload Test helps.

At configured intervals, this test compares the usage levels and execution times of all queries that started running on the server in the last measurement period and identifies a ‘top query’ in each of the following categories - CPU usage, memory usage, disk activity, and execution time. The test then reports the resource usage and execution time of the top queries and promptly alerts administrators if any query consumes more resources or takes more time to execute than it should. In such a scenario, administrators can use the detailed diagnosis of this test to view the inefficient queries and proceed to optimize them to enhance server performance.

Target of the test : A Maria Database server

Agent deploying the test : An external agent

Outputs of the test :One set of results for the Maria Database server being monitored.

Configurable parameters for the test

Parameter	Description
Test period	How often should the test be executed
Host	The IP address of the host for which this test is to be configured.
Port	The port on which the server is listening. The default port is 5432.
Database	Specify the name of the database that is to be monitored on the target Maria Database server.
User and Password	The eG agent has to be configured with the credentials of a user who has server-wide PROCESS and SELECT privileges on the monitored Maria Database server. To know how to create such a user, refer to Configuring the eG Agent with Access Privileges section.
Confirm Password	Confirm the password by retyping it here.
SSL	This indicates that the eG agent will communicate with the Maria Database via HTTPS or not. By default, this flag is set to No, as the target Maria database is not SSL-enabled by default. If the target database is SSL-enabled, then set this flag to Yes.
Verify CA	If the eG agent is required to establish an encrypted connection with the target Maria Database server by authenticating the server's identity through verifying the server CA certificate, set Verify CA flag to Yes. By default, this flag is set to No.
DD Frequency	Refers to the frequency with which detailed diagnosis measures are to be generated for this test. The default is 1: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
Recently executed queries	Indicates the number of recently executed queries since the last measurement period.	Number	A consistent rise in the value of this measure is a sign of optimal database usage and minimal direct disk accesses.
Maximum query duration	Indicates the maximum time duration taken by the top query for execution.	Seconds	If the value of this measure is very high, it could indicate that the database is unable to process queries quickly or that one or more queries are taking too long to execute. Common causes for database-level slowdowns include improper indexing and fragmented tables. Additionally, improperly structured queries can also be time-consuming. The longer a query executes on the database, the higher its resource consumption. It is therefore imperative to quickly isolate and fine-tune such resource-intensive queries to prevent performance degradations in the database server. Using the detailed diagnosis, you can rapidly identify resource-intensive database queries.
Average elapsed time	Indicates the average time taken by queries to execute on the database.	Seconds	This measure shows the average elapsed time taken by queries to execute on these databases. If this value is very high, it could either indicate that the database is unable to process the queries quickly or that one/more queries to the databases are taking too long to execute.