Notes on <High Performance MySQL> -- Ch13: MySQL Server Status

Chapter 13: MySQL Server Status

System Variables

SHOW VARIABLES

SHOW STATUS

“Status variable” :  a value from SHOW STATUS

“system variable”:  a server configuration variable

 

SHOW STATUS

SHOW GLOBAL STATUS

Information_schema.global_status

Information_schema.session_status

-          Thread and Connection Statistics

• Connections, Max_Used_Connections, Threads_Connected
• Aborted_clients, Aborted_connects
• Bytes_received, Bytes_sent
• Slow_launch_threads, Threads_cached, Threads_created, Threads_running

-          Binary Logging Status

• Binlog_cache_use
• Binlog_cache_disk_use

-          Command Counters

Com_*

-          Temporary Files and Tables

->SHOW GLOBAL STATUS LIKE ‘Created_tmp%’

-          Handler Operations

The handler API is the interface between MySQL and its storage engines. The Handler_* variables count handler operations, such as the number of times MySQL asks a storage engine to read the next row from an index.

->SHOW GLOBAL STATUS LIKE ‘Handler_%’

-          MyISAM Key Buffer

->SHOW GLOBAL STATUS LIKE ‘Key_%’

-          File Descriptors

->SHOW GLOBAL STATUS LIKE ‘Open_%’

-          Query Cache

->SHOW GLOBAL STATUS LIKE ‘Qcache_%’

-          SELECT Types

->SHOW GLOBAL STATUS LIKE ‘Select_%’

-          Sorts

->SHOW GLOBAL STATUS LIKE ‘Sort_%’

-          Table Locking

• Table_locks_immediate
• Table_locks_waited

-          Secure Sockets Layer(SSL)

->SHOW GLOBAL STATUS LIKE ‘Ssl_%’;

-          InnoDB-Specific

Innodb_*

-          Plug-in-Specific

-          Miscellaneous

-           

SHOW INNODB STATUS

SHOW (ENGINE) INNODB STATUS

-          Header

-          SEMAPHORES

Event counters, a list of current waits

-          Latest Foreign Key Error

-          Latest Detected Deadlock

There are actually two types of InnoDB deadlocks. The first, which is what most people are accustomed to, is a true cycle in the waits-for graph. The other type is a waits-for graph that is too expensive to check for cycles. If InnoDB has to check more than a million locks in the graph, or if it recurses through more than 200 transactions while checking, it gives up and says there is a deadlock. These numbers are hardcoded constants in the InnoDB source.

-          Transactions

-          File I/O

• Insert buffer thread:

Responsible for insert buffer merges.

• Log thread:

Responsible for asynchronous log flushes

• Read thread:

Performs read-ahead operations to try to prefetch data InnoDBB predicts it will need.

• Write thread:

Flushes dirty buffer

-          Insert Buffer and Adaptive Hash Index

-          Log

-          Buffer Pool and Memory

-          Row Operations

-           

SHOW PROCESSLIST

SHOW (FULL) PROCESSLIST

The process list is the list of connections, or threads, that are currently connected to MySQL. SHOW PROCESSLIST lists the threads, with information about each thread’s status.
 

SHOW MUTEX STATUS

SHOW MUTEX STATUS returns detailed InnoDB mutex information and is mostly useful for gaining insight into scalability and concurrency problems. Each mutex protects a critical section in the code.

 

Replication Status

SHOW MASTER STATUS

SHOW BINARY LOGS

SHOW SLAVE STATUS

 

INFORMATION_SCHEMA