Azure MySQL Database

Introduction

Azure MySQL Database monitoring involves the continuous collection, analysis, and visualization of performance, health, availability, and usage metrics from the Azure Database for MySQL service. This monitoring ensures optimal performance, helps identify potential issues proactively, and supports capacity planning and compliance efforts.

Getting Started

Compatibility

The Azure MySQL O11ySource is designed to work with all versions greater than or equal to 5.7, and it has been tested with Azure MySQL 8.0.

Qcache queries will not work with version 8.x, as the feature has been revoked.

Data Collection Method

The Azure MySQL O11ySource is configured to collect various kinds of metrics related to Azure MySQL Performance & Azure MySQL Engine Performance.

vuSmartMaps uses an internal agent to collect Azure MySQL Performance Metrics, Azure MySQL Engine Performance Metrics

Prerequisites

Dependent Configuration

To configure this O11ySource, create a 'credential' of type 'user' under the 'Definition' tab.

Inputs for Configuring Data Source

• Instance Name: Please enter the name of the AzureMySQL instance. This should be a unique identifier for the specific AzureMySQL deployment you want to monitor.
• DB Details: Please provide the details of DB
• Server Name: Host name needs to be a valid FQDN
• Port: Enter a valid port
• Credential: This field is required
• Secure Transport: This is optional
• Metrics Collection Queries: These queries are utilized for AzureMYSQL server's metrics.
• Query: Choose the Queries
• Polling Interval [seconds]: How frequently data is gathered. interval should be between 180 -.86400 seconds

Firewall Requirement

To collect data from this O11ySource, ensure the following ports are opened:

Source IP	Destination IP	Destination Port	Protocol	Direction
vuSmartMaps IP	IP address of the Azure MySQL server	3306*	TCP	Outbound
IP address of the Azure MySQL server	vuSmartMaps Kafka Broker IP	9092*	TCP	Inbound

*Before providing the firewall requirements, please update the port based on the customer environment.

Configuring the Target

Configure Metrics Collection from Azure MySQL Server

• On each Azure MySQL server, create a database user for the vuSmartMaps Agent.
• *For Azure MySQL 5.6 or Azure MySQL 5.7 create the vunet user with the following command:

CREATE USER 'vunet'@'%' IDENTIFIED BY '<UNIQUEPASSWORD>';

• *or Azure MySQL 8.0 or greater, create the vunet user with the native password hashing method:

CREATE USER 'vunet'@'%' IDENTIFIED WITH Azure MySQL_native_password '<UNIQUEPASSWORD>';

• The vuSmartMaps agent required the below permissions to collect the metrics from Azure MySQL. Grant the vunet user only the following limited privileges:
• *Grant replication client with the following command:

GRANT REPLICATION CLIENT ON *.* TO 'vunet'@'%';

• *Grant replication client with the following command:

GRANT PROCESS ON *.* TO 'vunet'@'%';

• *Grant replication client with the following command:

GRANT SELECT ON performance_schema.* TO 'vunet'@'%';
GRANT SELECT ON sys.schema_unused_indexes TO 'vunet'@'%';
GRANT SELECT ON sys.x$statements_with_full_table_scans TO 'vunet'@'%';

Configuration Steps

• Enable the AzureMYSQL O11ySource.
• Select the Sources tab and press the + button to add AzureMYSQL instance that has to be monitored.
• Set up metrics collection configurations. The O11ySource already provides predefined metric collection queries. Users have the flexibility to adjust query intervals, modify existing queries, and introduce new queries as needed.
• Once all the required details are filled, click on Save and Continue

Metrics Collected

Name	Description	Data Type
timestamp	Timestamp of metric collection	DateTime64
tenant_id	Identifier for the tenant (multi-tenant support)	LowCardinality(String)
bu_id	Business unit identifier	LowCardinality(String)
host	Hostname of the monitored MySQL instance	LowCardinality(String)
type	Metric type or category	LowCardinality(String)
ClusterID	Cluster identifier	LowCardinality(String)
DBName	Name of the database	LowCardinality(String)
uniqueId	Unique identifier for the metric source or query	LowCardinality(String)
Com_delete	Total number of DELETE operations	Float64
Com_select	Total number of SELECT operations	Float64
Com_insert	Total number of INSERT operations	Float64
Com_update	Total number of UPDATE operations	Float64
Com_delete_multi	DELETE operations that delete multiple rows	Float64
Com_insert_select	INSERT ... SELECT operations	Float64
Com_replace_select	REPLACE ... SELECT operations	Float64
Com_update_multi	UPDATE operations that update multiple rows	Float64
Com_replace	REPLACE statement operations	Float64
Com_load	Number of LOAD DATA INFILE operations	Float64
Com_delete_diff	Delta of Com_delete since last interval	Float64
Com_insert_diff	Delta of Com_insert since last interval	Float64
Com_select_diff	Delta of Com_select since last interval	Float64
Com_update_diff	Delta of Com_update since last interval	Float64
Com_delete_multi_diff	Delta of Com_delete_multi since last interval	Float64
Com_insert_select_diff	Delta of Com_insert_select since last interval	Float64
Com_replace_select_diff	Delta of Com_replace_select since last interval	Float64
Com_update_multi_diff	Delta of Com_update_multi since last interval	Float64
Com_replace_diff	Delta of Com_replace since last interval	Float64
Com_load_diff	Delta of Com_load since last interval	Float64
timestamp	Timestamp when the metric was collected	DateTime64
tenant_id	Identifier for the tenant (for multi-tenant architecture support)	LowCardinality(String)
bu_id	Business unit identifier	LowCardinality(String)
host	Hostname of the MySQL instance being monitored	LowCardinality(String)
type	Type/category of the metric	LowCardinality(String)
ClusterID	Cluster identifier	LowCardinality(String)
DBName	Name of the database where the query was executed	LowCardinality(String)
table_scan_query	SQL text or fingerprint of the query performing a full table scan	String
table_scan_db	Database on which the full table scan was detected	String
table_scan_exec_count	Number of times the table scan query was executed	Float64
table_scan_total_latency	Total latency in microseconds across all executions of the table scan query	Float64
table_scan_no_index_used_pct	Percentage of executions where no index was used	String
table_scan_row_sent_avg	Average number of rows returned per execution	String
table_scan_rows_examined	Total number of rows examined across all executions	String
table_scan_first_seen	Timestamp when the table scan query was first seen	DateTime64
table_scan_last_seen	Timestamp when the table scan query was last seen	DateTime64
timestamp	Timestamp when the metric was collected	DateTime64
tenant_id	Identifier for the tenant (multi-tenant architecture support)	LowCardinality(String)
bu_id	Business unit identifier	LowCardinality(String)
host	Hostname of the database server	LowCardinality(String)
type	Type/category of the metric	LowCardinality(String)
ClusterID	Identifier for the database cluster	LowCardinality(String)
DBName	Name of the database to which the object belongs	LowCardinality(String)
total_size_mb	Total size of the object (e.g., table or index) in megabytes	Float64
object_schema	Schema (database) where the object is located	String
object_name	Name of the database object (typically a table name)	String
index_name	Name of the index associated with the object (if applicable)	String
timestamp	Timestamp of when the metric was collected	DateTime64
tenant_id	Identifier for the tenant	LowCardinality(String)
bu_id	Business unit identifier	LowCardinality(String)
host	Hostname or instance name	LowCardinality(String)
type	Type of monitored entity	LowCardinality(String)
ClusterID	Unique identifier for the cluster	LowCardinality(String)
DBName	Database name being monitored	LowCardinality(String)
uniqueId	Unique ID combining various identifying elements	LowCardinality(String)
Max_connections	Maximum permitted simultaneous client connections	Float64
Key_buffer_size	Size of the buffer used for index blocks	Float64
Max_prepared_stmt_count	Limit on the total number of prepared statements	Float64
Table_open_cache	Number of open tables the server can cache	Float64
Thread_cache_size	Number of threads to cache for reuse	Float64
Aborted_clients	Number of connections aborted by the client	Float64
Aborted_connects	Failed attempts to connect to the MySQL server	Float64
Binlog_cache_disk_use	Binary log cache overflowed to disk	Float64
Binlog_cache_use	Number of binary log cache uses	Float64
Bytes_received	Total bytes received from all clients	Float64
Bytes_sent	Total bytes sent to all clients	Float64
Threads_cached	Number of threads in the thread cache	Float64
Threads_connected	Number of currently open connections	Float64
Threads_created	Number of threads created to handle connections	Float64
Threads_running	Number of threads actively executing queries	Float64
Connections	Total connection attempts to the server	Float64
Created_tmp_disk_tables	Temporary tables created on disk	Float64
Created_tmp_files	Number of temporary files created by the server	Float64
Created_tmp_tables	Number of in-memory temporary tables created	Float64
Flush_commands	Number of FLUSH commands executed	Float64
Max_used_connections	Maximum number of connections used at one time	Float64
Open_files	Number of open files	Float64
Open_streams	Number of open streams (mainly used for logging)	Float64
Opened_tables	Number of tables opened	Float64
Open_tables	Number of tables currently open	Float64
Table_locks_waited	Number of times a table lock couldn't be acquired immediately	Float64
Uptime	Server uptime in seconds	Float64
Queries	Number of statements executed by the server	Float64
Questions	Number of client queries received	Float64
Connection_errors_max_connections	Failed connections due to max_connections reached	Float64
Connection_errors_internal	Connection errors caused by internal issues	Float64
Max_used_connections_timestamp	Timestamp when Max_used_connections was last reached	String
Key_reads	Number of physical reads of key blocks	Float64
Key_read_requests	Number of requests to read a key block	Float64
Prepared_stmt_count	Current number of prepared statements	Float64
Slow_queries	Number of queries longer than long_query_time	Float64
Key_write_requests	Requests to write a key block	Float64
Key_writes	Physical writes of key blocks to disk	Float64
Handler_commit	Number of internal COMMITs	Float64
Handler_delete	Number of times rows were deleted from tables	Float64
Handler_prepare	Number of calls to prepare a statement	Float64
Handler_read_first	Number of times the first entry was read from an index	Float64
Handler_read_key	Number of requests to read a row based on a key	Float64
Handler_read_next	Number of requests to read the next row in key order	Float64
Handler_read_prev	Number of requests to read the previous row in key order	Float64
Handler_read_rnd	Number of requests to read a row based on a fixed position	Float64
Handler_read_rnd_next	Number of requests to read the next row in the data file	Float64
Handler_rollback	Number of internal rollbacks performed	Float64
Handler_update	Number of times a row was updated in a table	Float64
Handler_write	Number of times a row was inserted into a table	Float64
SELECT_full_join	Number of joins without indexes (full joins)	Float64
SELECT_full_range_join	Number of joins using a range search on a reference table	Float64
SELECT_range	Number of joins that used ranges on the first table	Float64
SELECT_range_check	Joins without keys where each row is checked	Float64
SELECT_scan	Number of joins that did a full table scan	Float64
Sort_merge_passes	Number of merge passes the sort algorithm had to do	Float64
Sort_range	Number of sorts using ranges	Float64
Sort_rows	Number of sorted rows	Float64
Sort_scan	Number of sorts done by scanning the table	Float64
Table_locks_immediate	Number of table locks acquired immediately	Float64
Table_open_cache_hits	Number of hits in the table open cache	Float64
Table_open_cache_misses	Number of misses in the table open cache	Float64
Aborted_clients_diff	Delta of aborted clients between collection intervals	Float64
Aborted_connects_diff	Delta of aborted connects between collection intervals	Float64
Binlog_cache_use_diff	Delta of binlog cache uses	Float64
Bytes_received_diff	Delta of bytes received	Float64
Bytes_sent_diff	Delta of bytes sent	Float64
Connections_diff	Delta of connection count	Float64
Created_tmp_disk_tables_diff	Delta of temporary tables created on disk	Float64
Created_tmp_files_diff	Delta of temporary files created	Float64
Created_tmp_tables_diff	Delta of in-memory temporary tables created	Float64
Flush_commands_diff	Delta of FLUSH commands executed	Float64
Queries_diff	Delta of total queries	Float64
Table_locks_waited_diff	Delta of table locks waited	Float64
Questions_diff	Delta of client queries	Float64
Connection_errors_max_connections_diff	Delta of max_connections errors	Float64
Connection_errors_internal_diff	Delta of internal connection errors	Float64
Slow_queries_diff	Delta of slow queries	Float64
Handler_commit_diff	Delta of internal commits	Float64
Handler_delete_diff	Delta of row deletes	Float64
Handler_prepare_diff	Delta of prepared statements	Float64
Handler_read_first_diff	Delta of first index reads	Float64
Handler_read_key_diff	Delta of index key reads	Float64
Handler_read_next_diff	Delta of next row index reads	Float64
Handler_read_prev_diff	Delta of previous row index reads	Float64
Handler_read_rnd_diff	Delta of random row reads	Float64
Handler_read_rnd_next_diff	Delta of next row data file reads	Float64
Handler_rollback_diff	Delta of rollbacks	Float64
Handler_update_diff	Delta of row updates	Float64
Handler_write_diff	Delta of row inserts	Float64
SELECT_full_join_diff	Delta of full joins	Float64
SELECT_full_range_join_diff	Delta of full range joins	Float64
SELECT_range_diff	Delta of range joins	Float64
SELECT_range_check_diff	Delta of range check joins	Float64
SELECT_scan_diff	Delta of full scans	Float64
Sort_merge_passes_diff	Delta of sort merge passes	Float64
Sort_range_diff	Delta of range sorts	Float64
Sort_rows_diff	Delta of sorted rows	Float64
Sort_scan_diff	Delta of full table sorts	Float64
Table_locks_immediate_diff	Delta of immediate table locks	Float64
Table_open_cache_hits_diff	Delta of table open cache hits	Float64
Table_open_cache_misses_diff	Delta of table open cache misses	Float64
timestamp	Timestamp of the metric collection	DateTime64
tenant_id	Tenant or customer identifier	LowCardinality(String)
bu_id	Business unit identifier	LowCardinality(String)
host	Hostname of the MySQL server instance	LowCardinality(String)
type	Type of service or metric source	LowCardinality(String)
ClusterID	Unique identifier for the MySQL cluster	LowCardinality(String)
DBName	Name of the database instance	LowCardinality(String)
uniqueId	Unique identifier combining host, DB, etc. for deduplication	LowCardinality(String)
Query_cache_size	Total allocated size of the query cache in bytes	Float64
Qcache_hits	Number of times a query result was served from the cache	Float64
Qcache_inserts	Number of queries added to the cache	Float64
Qcache_lowmem_prunes	Number of queries removed due to low memory	Float64
Qcache_total_blocks	Total number of blocks in the query cache	Float64
Qcache_free_blocks	Number of free memory blocks in the query cache	Float64
Qcache_free_memory	Total free memory in the query cache (bytes)	Float64
Qcache_not_cached	Number of queries that were not cached due to various reasons	Float64
Qcache_queries_in_cache	Current number of queries stored in the query cache	Float64
Qcache_not_cached_diff	Delta of non-cached queries since the last check	Float64
Qcache_queries_in_cache_diff	Delta of queries currently in the cache since the last check	Float64
timestamp	Timestamp of the metric collection.	DateTime64
tenant_id	Identifier for the tenant.	LowCardinality(String)
bu_id	Identifier for the business unit.	LowCardinality(String)
host	Hostname of the database server.	LowCardinality(String)
type	Type of source (e.g., metric category).	LowCardinality(String)
ClusterID	Identifier for the database cluster.	LowCardinality(String)
DBName	Name of the database.	LowCardinality(String)
uniqueId	Unique identifier for the record.	LowCardinality(String)
Innodb_ibuf_merges_delete_mark	Merges in the insert buffer for delete mark operations.	Float64
Innodb_ibuf_merges_delete	Merges in the insert buffer for actual delete operations.	Float64
Innodb_ibuf_merges_insert	Merges in the insert buffer for insert operations.	Float64
Innodb_ibuf_merges	Total number of insert buffer merges.	Float64
Innodb_os_log_pending_fsyncs	Number of pending fsyncs on redo logs.	Float64
Innodb_os_log_pending_writes	Number of redo log writes pending.	Float64
Innodb_rwlock_s_spin_waits	Count of spin waits on shared read-write locks.	Float64
Innodb_rwlock_x_spin_waits	Count of spin waits on exclusive read-write locks.	Float64
Innodb_rwlock_s_spin_rounds	Rounds for spin waits on shared RW locks.	Float64
Innodb_rwlock_x_spin_rounds	Rounds for spin waits on exclusive RW locks.	Float64
Innodb_rwlock_s_os_waits	OS waits on shared RW locks.	Float64
Innodb_rwlock_x_os_waits	OS waits on exclusive RW locks.	Float64
Innodb_buffer_pool_pages_total	Total number of pages in the buffer pool.	Float64
Innodb_buffer_pool_pages_data	Number of pages containing data (not free or dirty).	Float64
Innodb_page_size	InnoDB page size in bytes.	Float64
Innodb_buffer_pool_pages_dirty	Number of modified (dirty) pages in buffer pool.	Float64
Innodb_row_lock_current_waits	Current number of row lock waits.	Float64
Innodb_data_writes	Number of data writes to disk.	Float64
Innodb_data_reads	Number of data reads from disk.	Float64
Innodb_os_log_fsyncs	Number of fsyncs on redo log files.	Float64
Innodb_row_lock_time	Total time spent waiting for row locks (ms).	Float64
Innodb_row_lock_waits	Total number of row lock wait occurrences.	Float64
Innodb_data_fsyncs	Number of fsync operations on data files.	Float64
Innodb_data_pending_fsyncs	Number of pending fsync operations.	Float64
Innodb_data_pending_reads	Number of pending read I/O operations.	Float64
Innodb_data_pending_writes	Number of pending write I/O operations.	Float64
Innodb_data_read	Total bytes read from data files.	Float64
Innodb_data_written	Total bytes written to data files.	Float64
Innodb_dblwr_pages_written	Pages written by the doublewrite buffer.	Float64
Innodb_dblwr_writes	Doublewrite operations executed.	Float64
Innodb_buffer_pool_pages_flushed	Pages flushed from the buffer pool.	Float64
Innodb_buffer_pool_pages_free	Number of free pages in the buffer pool.	Float64
Innodb_buffer_pool_read_ahead	Pages read in advance by read-ahead algorithm.	Float64
Innodb_buffer_pool_read_ahead_evicted	Pages evicted without being used after read-ahead.	Float64
Innodb_buffer_pool_read_ahead_rnd	Random read-ahead pages requested.	Float64
Innodb_buffer_pool_wait_free	Number of waits for buffer pool pages to be freed.	Float64
Innodb_buffer_pool_write_requests	Write requests to buffer pool pages.	Float64
Innodb_log_waits	Waits due to insufficient log buffer space.	Float64
Innodb_log_write_requests	Requests to write to the log buffer.	Float64
Innodb_log_writes	Physical writes to the log file.	Float64
Innodb_os_log_written	Bytes written to the redo log.	Float64
Innodb_pages_created	Pages created in the buffer pool.	Float64
Innodb_pages_read	Pages read from the disk.	Float64
Innodb_pages_written	Pages written to the disk.	Float64
Innodb_rows_deleted	Number of rows deleted.	Float64
Innodb_rows_inserted	Number of rows inserted.	Float64
Innodb_rows_read	Number of rows read.	Float64
Innodb_rows_updated	Number of rows updated.	Float64
Innodb_buffer_pool_reads	Number of reads from disk into the buffer pool.	Float64
Innodb_buffer_pool_read_requests	Number of logical read requests from the buffer pool (i.e., reads that didn't need a disk access).	Float64
Innodb_buffer_pool_bytes_data	Total bytes used for data pages in the buffer pool.	Float64
Innodb_buffer_pool_bytes_dirty	Total bytes of dirty (modified but not flushed) pages in the buffer pool.	Float64
Innodb_buffer_pool_size	Total size (in pages) of the InnoDB buffer pool.	Float64
Innodb_ibuf_merges_delete_mark_diff	Change in number of delete mark merges in insert buffer.	Float64
Innodb_ibuf_merges_delete_diff	Change in delete merges from insert buffer.	Float64
Innodb_ibuf_merges_insert_diff	Change in insert merges from insert buffer.	Float64
Innodb_ibuf_merges_diff	Total change in insert buffer merges.	Float64
Innodb_os_log_pending_fsyncs_diff	Delta in pending fsyncs on redo logs.	Float64
Innodb_os_log_pending_writes_diff	Delta in pending redo log writes.	Float64
Innodb_rwlock_s_spin_waits_diff	Delta in spin waits for shared RW locks.	Float64
Innodb_rwlock_x_spin_waits_diff	Delta in spin waits for exclusive RW locks.	Float64
Innodb_rwlock_s_spin_rounds_diff	Delta in spin rounds for shared RW locks.	Float64
Innodb_rwlock_x_spin_rounds_diff	Delta in spin rounds for exclusive RW locks.	Float64
Innodb_rwlock_s_os_waits_diff	Delta in OS waits for shared RW locks.	Float64
Innodb_rwlock_x_os_waits_diff	Delta in OS waits for exclusive RW locks.	Float64
Innodb_buffer_pool_read_requests_diff	Delta in logical read requests.	Float64
Innodb_buffer_pool_reads_diff	Delta in physical reads from disk into buffer pool.	Float64
Innodb_page_size_diff	Change in InnoDB page size (usually static, used for alerting on config drift).	Float64
Innodb_pages_created_diff	Delta in pages created in buffer pool.	Float64
Innodb_pages_read_diff	Delta in pages read from disk.	Float64
Innodb_pages_written_diff	Delta in pages written to disk.	Float64
Innodb_data_writes_diff	Delta in data writes to disk.	Float64
Innodb_data_reads_diff	Delta in data reads from disk.	Float64
Innodb_data_fsyncs_diff	Delta in fsyncs on data files.	Float64
Innodb_data_read_diff	Delta in bytes read.	Float64
Innodb_data_written_diff	Delta in bytes written.	Float64
Innodb_buffer_pool_pages_flushed_diff	Delta in buffer pool pages flushed.	Float64
Innodb_buffer_pool_wait_free_diff	Delta in waits for buffer pool free pages.	Float64
Innodb_buffer_pool_write_requests_diff	Delta in write requests to buffer pool.	Float64
Innodb_log_waits_diff	Delta in log waits (log buffer full).	Float64
Innodb_log_write_requests_diff	Delta in log write requests.	Float64
Innodb_log_writes_diff	Delta in physical log writes.	Float64
timestamp	Timestamp when the process data was collected.	DateTime64
tenant_id	Identifier for the tenant (multi-tenant context).	LowCardinality(String)
bu_id	Identifier for the business unit.	LowCardinality(String)
host	Hostname of the MySQL server instance.	LowCardinality(String)
type	Type of the source (e.g., metric, log, etc.).	LowCardinality(String)
ClusterID	Identifier for the cluster where the MySQL instance is running.	LowCardinality(String)
DBName	Name of the database from which the data is collected.	LowCardinality(String)
ID	Unique thread or connection ID assigned by MySQL.	Float64
USER	MySQL user associated with the connection.	String
HOST	Host and port from which the client is connected.	String
DB	Default database currently in use by the connection.	String
COMMAND	Type of command the connection is executing (e.g., Sleep, Query, Connect).	String
TIME	Time in seconds the thread has been in its current state.	Float64
STATE	Human-readable status of the thread (e.g., "Waiting for table metadata lock").	String
INFO	SQL query being executed, if applicable. May be NULL if not running a query.	String
EXECUTION_ENGINE	The execution engine used (e.g., "SQL", "SPJ", may be empty in standard MySQL).	String