MySQL - SHOW ENGINE Statement

MySQL SHOW ENGINE Statement

The MySQL SHOW ENGINE statement displays the operation information about the specified engine.

Syntax

Following is the syntax of the MySQL SHOW ENGINE Statement −

SHOW ENGINE engine_name {STATUS | MUTEX}

Example

Following query displays the status of the engine INNODB.

SHOW ENGINE INNODB STATUS\G;

Output

The above mysql query produces the following output −

*************** 1. row ***************
  Type: InnoDB
  Name:
Status:
=====================================
2021-05-15 12:06:23 0x2dd8 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 8 srv_active, 0 srv_shutdown, 3265 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 42
OS WAIT ARRAY INFO: signal count 42
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . .
------------
TRANSACTIONS
------------
Trx id counter 69645
Purge done for trx's n:o < 69640 undo n:o < 0 state: running but idle
History list length 0
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . .
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . .
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . .
---
LOG
---
Log sequence number        62802072
Log buffer assigned up to  62802072
Log buffer completed up to 62802072
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . .
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8585216
Dictionary memory allocated 391033
Buffer pool size   512
Free buffers       255
Database pages     256
Old database pages 0
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . .
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=13048, Main thread ID=0000000000001D90 , state=sleeping
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . .
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Example

Following query displays the status of the engine PERFORMANCE_SCHEMA −

SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G;

Output

After executing the above query, it will produce the following output −

*************** 1. row ***************
  Type: performance_schema
  Name: events_waits_current.size
Status: 168
*************** 2. row ***************
  Type: performance_schema
  Name: events_waits_current.count
Status: 1536
*************** 3. row ***************
  Type: performance_schema
  Name: events_waits_history.size
Status: 168
*************** 4. row ***************
  Type: performance_schema
  Name: events_waits_history.count
Status: 2560
*************** 5. row ***************
  Type: performance_schema
  Name: events_waits_history.memory
Status: 430080
*************** 6. row ***************
  Type: performance_schema
  Name: events_waits_history_long.size
Status: 168
*************** 7. row ***************
  Type: performance_schema
  Name: events_waits_history_long.count
Status: 10000
*************** 8. row ***************
  Type: performance_schema
  Name: events_waits_history_long.memory
Status: 1680000
*************** 9. row ***************
  Type: performance_schema
  Name: (pfs_mutex_class).size
Status: 256
*************** 10. row ***************
  Type: performance_schema
  Name: (pfs_mutex_class).count
Status: 350
*************** 11. row ***************
  Type: performance_schema
  Name: (pfs_mutex_class).memory
Status: 89600
*************** 12. row ***************
  Type: performance_schema
  Name: (pfs_rwlock_class).size
Status: 256
*************** 13. row ***************
  Type: performance_schema
  Name: (pfs_rwlock_class).count
Status: 60
*************** 14. row ***************
  Type: performance_schema
  Name: (pfs_rwlock_class).memory
Status: 15360
*************** 15. row ***************
  Type: performance_schema
  Name: (pfs_cond_class).size
Status: 256
*************** 16. row ***************
  Type: performance_schema
  Name: (pfs_cond_class).count
Status: 150
*************** 17. row ***************
  Type: performance_schema
  Name: (pfs_cond_class).memory
Status: 38400
*************** 18. row ***************
  Type: performance_schema
  Name: (pfs_thread_class).size
Status: 256
*************** 19. row ***************
  Type: performance_schema
  Name: (pfs_thread_class).count
Status: 100
************** 20. row ***************
  Type: performance_schema
  Name: (pfs_thread_class).memory
Status: 25600
..........................
..........................
..........................