Azure MySQL Flexible Server Test

Azure Database for MySQL - Flexible Server is a fully managed production-ready database service designed for more granular control and flexibility over database management functions and configuration settings. The flexible server architecture allows users to opt for high availability within single availability zone and across multiple availability zones. For workloads that don’t need full-compute capacity continuously, they provide better cost optimization controls with the ability to stop/start server and burstable compute tier. And for production workloads with predictable compute capacity requirements, Flexible Server also supports reserved instances allowing you to save up to 63% cost.

Even with all the above mentioned advantages, if you fail to maintain the performance, reliability, and availability of your server and applications, it can cause bitter user experiences. Therefore, it is very essential to keep track of the day-to-day performance of the database. This is exactly where Azure MySQL Flexible Server Test helps administrators. This test continuously monitors the resource usage and database operations, thereby ensuring that the performance is not compromised upon data size growth. This can inturn help you to promptly troubleshoot and optimize your workload.

This test auto-discovers all the MySQL database servers on Azure and reports the CPU usage, memory utilization, network traffic, database connections, and storage of the target server. These measures helps in the proactive identification of high workload on the target server, that caused high resource utilization and more number of failed connections. Additionally, this test also reveals low data read and write rate, which serves as an indication of read and write latency that can lead to slow request processing. Hence, using this test, administrators can identify CPU, memory and I/O resource contentions and any latency issues or connection failures in the MySQL database server.

Target of the Test: A MySQL database server on Azure

Agent deploying the test: A remote agent

Output of the test: One set of results for the target server being monitored.

Configurable parameters for the test
Parameters Description

Test Period

How often should the test be executed.

Host

The host for which the test is to be configured.

Port
  • The port on which the server is listening.
    		•

    Subscription ID

    Specify the GUID which uniquely identifies the Microsoft Azure Subscription to be monitored. To know the ID that maps to the target subscription, do the following:

    1. Login to the Microsoft Azure Portal.

    2. When the portal opens, click on the Subscriptions option (as indicated by Figure 1).

      Figure 1 : Clicking on the Subscriptions option

    3. Figure 2 that appears next will list all the subscriptions that have been configured for the target Azure AD tenant. Locate the subscription that is being monitored in the list, and check the value displayed for that subscription in the Subscription ID column.

      Figure 2 : Determining the Subscription ID

    4. Copy the Subscription ID in Figure 2 to the text box corresponding to the SUBSCRIPTION ID parameter in the test configuration page.

    Tenant ID

    Specify the Directory ID of the Azure AD tenant to which the target subscription belongs. To know how to determine the Directory ID, refer to Configuring the eG Agent to Monitor a Microsoft Azure Subscription Using Azure ARM REST API

    Client ID, Client Password, and Confirm Password

    To connect to the target subscription, the eG agent requires an Access token in the form of an Application ID and the client secret value. For this purpose, you should register a new application with the Azure AD tenant. To know how to create such an application and determine its Application ID and client secret, refer to Configuring the eG Agent to Monitor a Microsoft Azure Subscription Using Azure ARM REST API. Specify the Application ID of the created Application in the Client ID text box and the client secret value in the Client Password text box. Confirm the Client Password by retyping it in the Confirm Password text box.

    Database Name

    Specify the name of a database on the target MySQL database server on Azure being monitored in the Database text box.

    Resource Groups Name

    Specify the name of the resource group the target server belongs to in this text box.

    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.
    Measures made by the test:
    Measurement Description Measurement Unit Interpretation

    Server status

    Indicates the current status of the target database server.

     

    The values reported by this measure and its numeric equivalents are mentioned in the table below:

    Numeric Value Measure Value
    1 Ready
    2 Starting
    3 Stopping
    4 Updating
    5 Disabled
    6 Dropping
    7 Stopped
    0 Unknown

    Note:

    By default, this measure reports the Measure Values listed in the table above to indicate the current status of the target database server.

    The detailed diagnosis of this measure, lists the location of the database server, Backup retention days, Geo redundant backup, Storage Autogrow, Public network access, SKU name, and SKU Tier.

    Total databases

    Indicates the total number of databases on the target server.

    Number

    The detailed diagnosis of this measure shows the database name.

    CPU utilization

    Indicates the percentage of CPU utilized to process all the tasks on the target server.

    Percent

    This measure shed light on to the workload of your Azure Database for MySQL - Flexible Server and Azure MySQL process. High CPU percent indicates that the database server has more workload than it can handle.

    CPU credits remaining

    Indicates the number of CPU credits still unused or credits available to burst on the target server.

    Number

     

    CPU credits consumed

    Indicates the number of CPU credits consumed by the target server.

    Number

     

    Memory utilization

    Indicates the percentage of memory utilized by the target server.

    Percent

    This measure indicates the memory utilization from both database workload and other Azure MySQL processes.

    Network traffic out

    Indicates the total amount of outgoing network traffic on the target server.

    MB

    This metric includes traffic from your database and from Azure MySQL features like monitoring, logs etc.

    Network traffic in

    Indicates the total amount of incoming network traffic on the target server.

    MB

    This metric includes traffic to your database and to Azure MySQL features like monitoring, logs etc.

    Active connections

    Indicates the number of active connections on the target server.

    Number

    Active connections are the total number of threads connected to your server, which also includes threads from azure_superuser.

    Total connections

    Indicates the total number of client connections on the target server.

    Number

    Total Connections is sum of connections by clients using TCP/IP protocol over a selected period.

    Aborted connections

    Indicates the number of failed/aborted connections to the target server.

    Number

    If a client is unable even to connect, the server increments the Aborted_connects status variable. Unsuccessful connection attempts can occur for the following reasons:

    • A client attempts to access a database but has no privileges for it.

    • A client uses an incorrect password.

    • A connection packet does not contain the right information.

    • It takes more than connect_timeout seconds to obtain a connect packet.

    IO utilization

    Indicates the percentage of I/O utilized on the target server.

    Percent

    I/O utilization takes both read and write IOPS into consideration.

    Number of I/O consumed

    Indicates total number of I/O operations (both read and write) utilized by the target server.

    Number

     

    Storage utilization

    Indicates the percentage of storage utilized by the target server.

    Percent

    A value close to 100 is an indication of storage bottleneck.

    Storage used

    Indicates the amount of storage currently in use on the target server.

    MB

    The storage used by the service may include the database files, transaction logs, and the server logs. If the value of this measure is near to Total storage measure then it implies that the server is running out of storage space.

    Storage free

    Indicates the amount of storage free on the target server.

    MB

    A high value is desired for this measure.

    Total storage

    Indicates the maximum amount of storage available for this server.

    MB

     

    Storage throttle count

    Indicates the number of Storage I/O to this server that were throttled as the workload exceeded the predetermined IOPS limit.

    Number

    During peak periods, the workload might exceed the predetermined IOPS limit. As a result, the storage I/O could throttle, impacting performance and potentially causing delays or timeouts for your users.

    Server log storage utilization

    Indicates the percentage of server log storage utilized by the target server.

    Percent

    If the percentage utilization is high then it is an indication of bottleneck condition.

    Server log storage used

    Indicates the amount of server log storage used by the target server.

    MB

    If the value of this measure is near to the maximum storage limit, then it implies that the server is experiencing resource contention.

    Server log storage limit

    Indicates the maximum amount of server log storage available for the target server.

    MB

     

    Backup storage used

    Indicates the amount of backup storage used by this server.

    MB

     

    Replica lag time

    Indicates the number of seconds the replica is behind in replaying the transactions received from the source server.

    Seconds

    This metric is calculated from "Seconds_behind_Master" from the command "SHOW SLAVE STATUS" and is available for replica servers only.

    Replica io thread running

    Indicates the state if replication I/O threads running on this server. (or) Indicates whether the I/O (receiver) thread for reading the source's binary log is running on the target server.

    Number

    When a START REPLICA statement is issued on a replica server, the replica creates an I/O (receiver) thread, which connects to the source and asks it to send the updates recorded in its binary logs.

    The replication receiver thread reads the updates that the source's Binlog Dump thread sends and copies them to local files that comprise the replica's relay log.

    This measure reports the value 1 if the I/O thread is running and 0 if not.

    Replica sql thread running

    Indicates the state of SQL I/O threads running on the target server (or) Indicates whether the SQL thread for executing events in the relay log is running on the target server.

    Number

    The replica creates an SQL (applier) thread to read the relay log that is written by the replication receiver thread and execute the transactions contained in it.

    This measure reports the 1 if the SQL thread is running and 0 if not.

    HA io status

    Indicates the status of HA replication on the target server.

    Number

    This measure reports the value 1 if the I/O thread is running and 0 if not.

    HA sql status

    Indicates the status of HA replication on the target server.

    Number

    This measure reports the 1 if the SQL thread is running and 0 if not.

    Queries executed

    Indicates the number of queries executed on the target server.

    Number

     

    Slow queries executed

    Indicates the number of slow queries that have been executing for more time on the target server.

    Number

    This value should ideally be 0. If it remains consistently high, the administrator should look to identify and optimize the slow queries.

    SELECT statements executed

    Indicates the number of SELECT statements that have been executed on the target server.

    Number

     

    UPDATE statements executed

    Indicates the number of UPDATE statements that have been executed on the target server.

    Number

     

    INSERT statements executed

    Indicates the number of INSERT statements that have been executed on the target server.

    Number

     

    DELETE statements executed

    Indicates the number of DELETE statements that have been executed on the target server.

    Number

     

    CREATE DB statements executed

    Indicates the number of CREATE DB statements that have been executed on the target server.

    Number

     

    DROP DB statements executed

    Indicates the number of DROP DB statements that have been executed on the target server.

    Number

     

    ALTER TABLE statements executed

    Indicates the number of ALTER TABLE statements that have been executed on the target server.

    Number

     

    CREATE TABLE statements executed

    Indicates the number of CREATE TABLE statements that have been executed on the target server.

    Number

     

    DROP TABLE statements executed

    Indicates the number of DROP TABLE statements that have been executed on the target server.

    Number

     

    InnoDB buffer pool reads

    Indicates the number of logical reads that InnoDB engine couldn't satisfy from the Innodb buffer pool, and had to be fetched from the disk.

    Number

     

    InnoDB buffer pool read requests

    Indicates the number of logical read requests to read from the Innodb Buffer pool.

    Number

     

    InnoDB buffer pool pages free

    Indicates the number of free pages in InnoDB buffer pool.

    Number

     

    InnoDB buffer pool pages data

    Indicates the number of pages in the InnoDB buffer pool that contains data.

    Number

    This measure value includes both dirty and clean pages.

    InnoDB buffer pool pages dirty

    Indicates the number of dirty pages in the InnoDB buffer pool.

    Number

    This measure indicates the number of InnoDB buffer pool data pages that have been changed in memory, but the changes are not yet written (flushed) to the InnoDB data files.