Tables
Viewing InnoDB Table Properties
To view the properties of an InnoDB table, issue a SHOW TABLE STATUS statement:
mysql> SHOW TABLE STATUS FROM test LIKE 't%' \G;
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-02-18 12:18:28
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
AUTO_INCREMENT Counter
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
InnoDB Disk I/O
InnoDB In-Memory Structures
- Buffer Pool
- Change Buffer
- Log Buffer
Buffer Pool
The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.
For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache using a variation of the least recently used (LRU) algorithm.
Refer to https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html
Monitoring the Buffer Pool
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size 131072
Free buffers 124908
Database pages 5720
Old database pages 2071
Modified db pages 910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
Refer to https://dev.mysql.com/doc/refman/8.0/en/innodb-standard-monitor.html
Buffer Pool Configuration
You can configure the various aspects of the buffer pool to improve performance.
- Ideally, you set the size of the buffer pool to as large a value as practical, leaving enough memory for other processes on the server to run without excessive paging. The larger the buffer pool, the more
InnoDBacts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. See Section 15.8.3.1, “Configuring InnoDB Buffer Pool Size”. - On 64-bit systems with sufficient memory, you can split the buffer pool into multiple parts to minimize contention for memory structures among concurrent operations. For details, see Section 15.8.3.2, “Configuring Multiple Buffer Pool Instances”.
- You can control how and when to perform read-ahead requests to prefetch pages into the buffer pool asynchronously in anticipation of impending need for them. For details, see Section 15.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.
- You can control when background flushing occurs and whether or not the rate of flushing is dynamically adjusted based on workload. For details, see Section 15.8.3.5, “Configuring Buffer Pool Flushing”.
InnoDB Buffer Pool Size
You can configure InnoDB buffer pool size offline or while the server is running. Behavior described in this section applies to both methods.
When increasing or decreasing innodb_buffer_pool_size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128M. For more information, see Configuring InnoDB Buffer Pool Chunk Size.
Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you configure innodb_buffer_pool_size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
Refer to https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html
InnoDB Buffer Pool Prefetching (Read-Ahead)
A read-ahead request is an I/O request to prefetch multiple pages in the buffer pool asynchronously, in anticipation of impending need for these pages. The requests bring in all the pages in one extent. InnoDB uses two read-ahead algorithms to improve I/O performance:
- Linear read-ahead is a technique that predicts what pages might be needed soon based on pages in the buffer pool being accessed sequentially. You control when
InnoDBperforms a read-ahead operation by adjusting the number of sequential page accesses required to trigger an asynchronous read request, using the configuration parameterinnodb_read_ahead_threshold. - Random read-ahead is a technique that predicts when pages might be needed soon based on pages already in the buffer pool, regardless of the order in which those pages were read. I
Buffer Pool Flushing
InnoDB performs certain tasks in the background, including flushing of dirty pages from the buffer pool. Dirty pages are those that have been modified but are not yet written to the data files on disk.
Buffer pool flushing is initiated when the percentage of dirty pages reaches the low water mark value defined by the innodb_max_dirty_pages_pct_lwm variable. The default low water mark is 10% of buffer pool pages. A innodb_max_dirty_pages_pct_lwm value of 0 disables this early flushing behaviour.
The purpose of the innodb_max_dirty_pages_pct_lwm threshold is to control the percentage dirty pages in the buffer pool and to prevent the amount of dirty pages from reaching the threshold defined by the innodb_max_dirty_pages_pct variable, which has a default value of 90. InnoDB aggressively flushes buffer pool pages if the percentage of dirty pages in the buffer pool reaches the innodb_max_dirty_pages_pct threshold.
Refer to https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-flushing.html
Log Buffer
The log buffer is the memory area that holds data to be written to the log files on disk. Log buffer size is defined by the innodb_log_buffer_size variable. The default size is 16MB. The contents of the log buffer are periodically flushed to disk. A large log buffer enables large transactions to run without the need to write redo log data to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, increasing the size of the log buffer saves disk I/O.
- Redo Log Buffer
Performance
Consider increasing the size of the log buffer. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. Log buffer size is configured using the innodb_log_buffer_size configuration option, which can be configured dynamically in MySQL 8.0.
InnoDB On-Disk Structures
Redo Logs
Refer to https://swsmile.info/post/mysql-redo-logs/
Optimizing InnoDB Disk I/O
Refer to
- https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-diskio.html
- https://dev.mysql.com/doc/refman/8.0/en/disk-issues.html
Reference
- https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html
- https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html