MySQL Tutorials - Herong's Tutorial Examples

∟Storage Engines in MySQL Server

∟MySQL Status Variables for InnoDB Engine

This section describes some important MySQL status variables that represent statistic counts related the InnoDB storage engine.

As of MySQL 8.0, there are 61 server status variables that provides statistical counts related to the InnoDB storage engine. Here are some important ones you should pay attention:

• Innodb_buffer_pool_bytes_data - The total number of bytes in the InnoDB buffer pool containing data.
• Innodb_buffer_pool_pages_data - The number of pages in the InnoDB buffer pool containing data.
• Innodb_buffer_pool_pages_dirty - The current number of dirty pages in the InnoDB buffer pool.
• Innodb_buffer_pool_pages_flushed - The number of requests to flush pages from the InnoDB buffer pool.
• Innodb_buffer_pool_pages_free - The number of free pages in the InnoDB buffer pool.
• Innodb_buffer_pool_pages_total - The total size of the InnoDB buffer pool, in pages.
• Innodb_buffer_pool_read_requests - The number of logical read requests.
• Innodb_buffer_pool_reads - The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.
• Innodb_buffer_pool_write_requests - The number of writes done to the InnoDB buffer pool.
• Innodb_data_pending_reads - The current number of pending reads.
• Innodb_data_pending_writes - The current number of pending writes.
• Innodb_data_read - The amount of data read since the server was started (in bytes).
• Innodb_data_reads - The total number of data reads (OS file reads).
• Innodb_data_writes - The total number of data writes.
• Innodb_data_written - The amount of data written so far, in bytes.
• Innodb_num_open_files - The number of files InnoDB currently holds open.
• Innodb_os_log_written - The number of bytes written to the InnoDB redo log files.
• Innodb_page_size - InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be easily converted to bytes.
• Innodb_pages_created - The number of pages created by operations on InnoDB tables.
• Innodb_pages_read - The number of pages read from the InnoDB buffer pool by operations on InnoDB tables.
• Innodb_pages_written - The number of pages written by operations on InnoDB tables.
• Innodb_row_lock_current_waits - The number of row locks currently being waited for by operations on InnoDB tables.
• Innodb_row_lock_time - The total time spent in acquiring row locks for InnoDB tables, in milliseconds.
• Innodb_row_lock_waits - The number of times operations on InnoDB tables had to wait for a row lock.
• Innodb_rows_deleted - The number of rows deleted from InnoDB tables.
• Innodb_rows_inserted - The number of rows inserted into InnoDB tables.
• Innodb_rows_read - The number of rows read from InnoDB tables.
• Innodb_rows_updated - The number of rows updated in InnoDB tables.

You can see the all InnoDB related system variables and their current values using the "SHOW VARIABLES ..." statement:

mysql> show status like '%innodb%';
...

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

 Perl Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Window Functions for Statistical Analysis

 Use Index for Better Performance

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

►Storage Engines in MySQL Server

 What Are Storage Engines

 What Is InnoDB Storage Engine

 Convert Table to InnoDB Storage Engine

 Clustered Index Used by InnoDB Engine

 Statistic Information on InnoDB Tables

►MySQL Status Variables for InnoDB Engine

 MySQL System Variables for InnoDB Engine

 InnoDB Storage Engine - Primary and Secondary Indexes

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB

MySQL Status Variables for InnoDB Engine - Updated in 2023, by Herong Yang