If you need to know how your MySQL database is performing, or maybe you want to move your MySQL on-premise database to MySQL Heatwave on Oracle Cloud Infrastructure (OCI), this post will explain how to manually collect metrics on connections, resource usage, query performance and database characteristics.  This post doesn’t contain every single possible command you can use, but it should give you a general overview of collecting statistics. 

If you want an easier and automated way to collect statistics, you can use OCI’s Database Management service to monitor your on-premise and/or cloud MySQL/HeatWave instances.  And, you could even use third-party database management tools as well.  But, if you want to manually see how your instance is performing, I will explain the commands you can use for each different set of metrics.  I have added links to the MySQL manual pages (where applicable) if you want to read more about each command. 

Categories of Statistics

Connection Metrics: Understand concurrent connections and thread activity.
Resource Usage: Monitor CPU, memory, and I/O indirectly through MySQL metrics.
Query Performance: Identify query load and bottlenecks.
Database and Table Statistics: Assess data size and structure.
InnoDB Engine Metrics: Gather buffer pool, transaction, and I/O statistics.
Performance Schema (Optional): Detailed performance metrics for advanced analysis.

Server Status versus Server System Variables

In MySQL, Server Status Variables and Server System Variables serve distinct purposes, providing different types of information about the MySQL server’s operation and configuration. Understanding their differences is crucial for monitoring, tuning, and sizing your MySQL instance, especially when planning a migration to  MySQL with HeatWave on OCI.

Server Status Variables are runtime metrics that reflect the current state and activity of the MySQL server since it started or since the last reset of certain counters. They provide a snapshot of performance, usage, and operational statistics.  Status variables are used for monitoring and diagnostics to understand how the server is performing, identify bottlenecks, and assess resource usage. The MySQL server maintains many status variables that provide information about its operation. You can view these variables and their values by using the SHOW [GLOBAL | SESSION] STATUS statement (see “SHOW STATUS Statement”). The optional GLOBAL keyword aggregates the values over all connections, and SESSION shows the values for the current connection.

Characteristics:

  • Dynamic and Read-Only: Status variables are updated by the server as it runs and cannot be modified by users.
  • Reset Behavior: Most status variables reset when the server restarts. Some can be reset using FLUSH STATUS;.
  • Scope: Available in global (SHOW GLOBAL STATUS;) and session (SHOW SESSION STATUS;) scopes. Global shows server-wide metrics; session shows metrics for the current connection.

Server System Variables are configuration settings that control the behavior and resource allocation of the MySQL server. They define how the server operates and how resources are managed. Their purpose is to configure and tune the server’s behavior, such as memory allocation, connection limits, or query handling.  The MySQL server maintains many system variables that affect its operation. Most system variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically at runtime using the SET statement, which enables you to modify operation of the server without having to stop and restart it. Some variables are read-only, and their values are determined by the system environment, by how MySQL is installed on the system, or possibly by the options used to compile MySQL. Most system variables have a default value, but there are exceptions, including read-only variables. You can also use system variable values in expressions.

Characteristics:

  • Configurable: Many system variables can be modified by users with appropriate privileges, either at startup (via my.cnf or command-line options) or dynamically (SET GLOBAL or SET SESSION).
  • Persistent or Dynamic: Some persist across restarts (set in config files), while others can be changed at runtime but reset on restart unless saved.
  • Scope: Available in global (SHOW GLOBAL VARIABLES;) and session (SHOW SESSION VARIABLES;) scopes. Global applies to the server; session applies to the current connection.

SQL Commands

Connection Metrics:  These commands help you understand the number of connections and thread activity, critical for sizing ECPUs and memory for peak load.  This is just a partial list, and as you gather your statsistics, you might find a need to add more for your particular use-case and environment.

— Current and peak connections

SHOW STATUS LIKE 'Aborted_clients';  -- Connections aborted due to errors or timeouts
    SHOW STATUS LIKE 'Aborted_connects';  -- Failed connection attempts
    SHOW STATUS LIKE 'Connections';  -- Total connection attempts
    SHOW STATUS LIKE 'Max_used_connections';  -- Peak connections since server start
    SHOW STATUS LIKE 'Threads_connected';  -- Current active connections
    SHOW STATUS LIKE 'Threads_running';  -- Number of threads actively processing queries
    
    SHOW VARIABLES LIKE 'max_connections';  -- Maximum allowed connections

Resource Usage Metrics:   MySQL doesn’t directly expose CPU/memory usage (you’d need OS tools like top or htop for that), but these commands provide proxy metrics for resource consumption.

— Global status for resource-related metrics

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';  -- Buffer pool usage (memory for caching data/indexes)
    SHOW GLOBAL STATUS LIKE 'Innodb_os%';  -- I/O operations (read/write to disk)
    SHOW GLOBAL STATUS LIKE 'Innodb_row%';  -- Row operations (indicates query load)
    SHOW GLOBAL STATUS LIKE 'Queries';  -- Total queries executed
    SHOW GLOBAL STATUS LIKE 'Questions';  -- Queries from user clients (excludes internal)
    SHOW GLOBAL STATUS LIKE 'Slow_queries';  -- Queries exceeding long_query_time
    
    SHOW VARIABLES LIKE 'long_query_time';  -- Threshold for slow queries (seconds)

Query Performance Metrics:  These help identify query load and potential bottlenecks.

— Slow query log status (if enabled)

SHOW VARIABLES LIKE 'slow_query_log%';  -- Check if slow query logging is enabled
    SHOW VARIABLES LIKE 'log_output';  -- Where slow query logs are stored (file/table)

— Query cache (if enabled, only for MySQL 5.7 and earlier – this was deprecated in MySQL 8.0)

SHOW STATUS LIKE 'Qcache%';  -- Query cache hits, misses, and memory usage
    SHOW VARIABLES LIKE 'query_cache%';  -- Query cache configuration

— Temporary tables (indicates complex queries)

SHOW STATUS LIKE 'Created_tmp%';  -- Temporary tables created in memory or on disk

Database and Table Statistics:  These provide data size and structure details to size the HeatWave cluster and DB System memory.

— Total database size

SELECT 
        table_schema AS 'Database',
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size_MB'
    FROM information_schema.tables
    GROUP BY table_schema;

— Table-level size and row counts

SELECT 
        table_schema AS 'Database',
        table_name AS 'Table',
        ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size_MB',
        table_rows AS 'Row_Count'
    FROM information_schema.tables
    WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql');

— Table storage engine and index details

SELECT 
        table_schema AS 'Database',
        table_name AS 'Table',
        engine AS 'Engine',
        ROUND(data_length / 1024 / 1024, 2) AS 'Data_Size_MB',
        ROUND(index_length / 1024 / 1024, 2) AS 'Index_Size_MB'
    FROM information_schema.tables
    WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql');

InnoDB Engine Metrics:  InnoDB is the default storage engine for MySQL and the Oracle Cloud MySQL DB System, so these metrics are critical for resource sizing.

— InnoDB buffer pool usage

SHOW ENGINE INNODB STATUS\G  -- Detailed status (look for "Buffer pool" section)
    SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';  -- Pages used, free, total
    SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- Configured buffer pool size
    SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';  -- Number of buffer pool instances

— InnoDB I/O and transaction metrics

SHOW STATUS LIKE 'Innodb_data%';  -- Data reads/writes (I/O load)
    SHOW STATUS LIKE 'Innodb_log%';  -- Log file writes (redo log activity)
    SHOW STATUS LIKE 'Innodb_rows%';  -- Row operations (inserts, updates, deletes)
    SHOW STATUS LIKE 'Innodb_trx%';  -- Active transactions

— InnoDB lock and wait metrics

SHOW STATUS LIKE 'Innodb_row_lock%';  -- Row lock waits (contention)
    SHOW ENGINE INNODB STATUS\G  -- Check "TRANSACTIONS" section for deadlocks or lock waits

Performance Schema (Advanced): The Performance Schema variables provides detailed insights into query performance, wait events, and resource usage. Ensure it’s enabled (SHOW VARIABLES LIKE 'performance_schema';).

— Enable Performance Schema (if not already enabled, requires server restart)

SET GLOBAL performance_schema = ON;

— Top wait events (indicates bottlenecks – via the events_waits_summary_global_by_event_name table)

SELECT 
        event_name,
        count_star AS 'Count',
        sum_timer_wait / 1000000000 AS 'Total_Wait_ms'
    FROM performance_schema.events_waits_summary_global_by_event_name
    WHERE count_star > 0
    ORDER BY sum_timer_wait DESC
    LIMIT 10;

— Query performance (top queries by execution time via the events_statements_summary_by_digest table)

SELECT 
        digest_text AS 'Query',
        count_star AS 'Executions',
        avg_timer_wait / 1000000000 AS 'Avg_Execution_ms',
        sum_rows_examined AS 'Rows_Examined'
    FROM performance_schema.events_statements_summary_by_digest
    WHERE digest_text IS NOT NULL
    ORDER BY sum_timer_wait DESC
    LIMIT 10;

— Connection and thread activity (via the accounts table)

SELECT 
        user,
        host,
        COUNT(*) AS 'Connections'
    FROM performance_schema.accounts
    WHERE user IS NOT NULL
    GROUP BY user, host
    ORDER BY Connections DESC;

— Table I/O statistics (via the table_io_waits_summary_by_table)

SELECT 
        object_schema AS 'Database',
        object_name AS 'Table',
        count_read AS 'Reads',
        count_write AS 'Writes'
    FROM performance_schema.table_io_waits_summary_by_table
    WHERE count_read > 0 OR count_write > 0
    ORDER BY (count_read + count_write) DESC
    LIMIT 10;

Global Configuration:  These commands show server settings that impact resource usage and performance.

— Key memory and thread settings

SHOW VARIABLES LIKE 'join_buffer_size';  -- Buffer for joins
    SHOW VARIABLES LIKE 'max_heap_table_size';  -- Max size of MEMORY engine tables
    SHOW VARIABLES LIKE 'read_buffer_size';  -- Buffer for reads
    SHOW VARIABLES LIKE 'sort_buffer_size';  -- Buffer for sorts
    SHOW VARIABLES LIKE 'thread_stack';  -- Memory per connection thread
    SHOW VARIABLES LIKE 'tmp_table_size';  -- Max size of in-memory temp tables

— InnoDB-specific settings

SHOW VARIABLES LIKE 'innodb%';  -- All InnoDB configurations
    SHOW VARIABLES LIKE 'innodb_buffer_pool%';  -- Buffer pool settings
    SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';  -- Durability setting
    SHOW VARIABLES LIKE 'innodb_log_file_size';  -- Redo log size

How to Use These Commands

Run Periodically: Execute these commands during peak and normal workloads to capture runtime behavior. Use a tool like the MySQL Command-Line Client or MySQL Shell.
Automate Collection: Script these commands (e.g., in a Bash or Python script) to log output over time, and save the results in a text file.

Analyze Output:

Map to Oracle Cloud:

  • ECPUs: Estimate based on Threads_running and query load (e.g., 0.02 ECPUs per active connection).
  • Memory: Sum innodb_buffer_pool_size, connection overhead (e.g., 1 MB per connection), and OS needs.
  • HeatWave: Use table sizes to size the cluster (e.g., 1000 GB analytics data needs 1-2 HeatWave.512GB nodes, depending upon compression).

Notes:

  • Performance Schema: Enabled by default in MySQL 8.0+ but consumes memory. If disabled, enable it temporarily (SET GLOBAL performance_schema = ON;) for detailed stats.
  • Slow Query Log: If not enabled, turn it on (SET GLOBAL slow_query_log = ON;) and set long_query_time to a low value (e.g., 1 second) to capture slow queries.  But be careful – enabling the slow query log could consume a lot of disk space.
  • Frequency: Run these during peak load (e.g., business hours) and off-peak to get a full picture.  
  • Oracle Cloud Compatibility: These metrics apply to MySQL 8.0+, which is standard in the Oracle Cloud MySQL DB System. Ensure your on-premise version is compatible (upgrade if needed).  If you need to updgrade from MySQL 5.7, use the MySQL Shell Upgrade Checker Utility to see what changes need to be made in order to be compatible with MySQL 8.0+.

Example Output Interpretation

  • Threads_connected: 400, Max_used_connections: 500 — Plan for 500+ connections (max_connections=600).
  • Innodb_buffer_pool_pages_data: 1,000,000 pages (at 16 KB/page = 16 GB) — Set innodb_buffer_pool_size to 20-24 GB.
  • Slow_queries: 100 — Investigate queries for HeatWave offloading.
  • Table size: 1000 GB analytics data — Use HeatWave.512GB with 1-2 nodes, depending upon compression.  You may also use HeatWave Autopilot to estimate the optimal HeatWave cluster size for your analytics data.  

In summary, simply run these commands, collect the output, and the information will help you understand how your database is performing.  You may have to refer to the manual pages for details about the output values. Also, this information will assist you in deciding which MySQL DB System (OLTP) and MySQL HeatWave (OLAP) shape you need when you make the move to Oracle Cloud Infrastructure.