InnoDB Background Threads and memory

InnoDB has multiple blocks of memory that you can think of as a large pool of memory that is responsible for the following tasks:

1. Maintains multiple internal data structures that all processes/threads need access to.
2. Cache the data on the disk for quick and easy reading, and cache it here before modifying the data on the disk file.
3. Redo log (redo log) buffer.
4. ..........

The primary role of a background thread is to refresh the data in the memory pool to ensure that the memory in the buffer pool caches the most recent data . In addition, the modified data file is flushed to the disk file , and InnoDB can be restored to normal operation in the event of an exception in the database.

Background thread

Because Oracle is a multi-process architecture (except under Windows), there are some simple commands to learn about the background processes that Oracle is currently running, such as the IPCS command . In general, Oracle's core background processes are ckpt, dbwn, LGWR, ARCn, Pmon, Smon, and so on.

InnoDB storage Engine is also such a schema, but is a multi-threaded version of the implementation, look at InnoDB source code, found that InnoDB is not the way to operate the database process. The InnoDB storage engine implements almost all of the functionality on a thread called the master thread .

By default, the background thread of the InnoDB storage engine has 4 classes-IO thread,1 masterthread,1 lock Monitoring thread , and1 Error monitoring thread .

As shown below:

Show Engine InnoDB Status\g

As you can see, the IO threads are insert buffer thread, log thread, read thread, write thread, respectively. The number of IO thread cannot be adjusted under the Linux platform, but the IO thread can be increased by the parameter innodb_file_io_threads under the Windows platform. The InnoDB plugin version began to increase the number of default IO thread, with the default read thread and write thread growing to 4, and no longer using the Innodb_file_io_threads parameter, but using a separate Innodb_read_io_threads and innodb_write_io_threads parameters.

Show variables like ' innodb_version ' \g

Show variables like ' innodb_%io_threads ' \g

Memory

The InnoDB storage engine memory consists of the buffer pool , the redo log buffer pool (redo log buffer), and The additional memory pool (additional Memory pool), which is determined by the size of the parameters innodb_buffer_pool_size and innodb_log_buffer_size in the configuration file, respectively.

As shown below: (shown in bytes, respectively).

Show variables like ' innodb_buffer_pool_size ' \g

Show variables like ' innodb_log_buffer_size ' \g

Show variables like ' innodb_additional_mem_pool_size ' \g

Buffer pool

A buffer pool is the portion of the largest block of memory used to hold a cache of various data. Because the InnoDB storage engine works, it always reads the database files by page (16K per page) to the buffer pool and then retains the cached data in the buffer pool by the least recently used (LRU) algorithm. if the database file needs to be modified, the pages in the cache pool are always first modified (the page is dirty after the modification), and then the dirty pages of the buffer pool are flushed (flush) to the file at a certain frequency. You can view the specific usage of Innodb_buffer_pool using the command show Engine InnoDB status\g, as shown below.

In buffer pool and memory, you can see the use of InnoDB storage engine buffers, and the buffersize indicates how many buffer frames (buffer frame), each buffer frame is 16K , so there is a buffer pool of 8192*16/1024g memory allocated here. The free buffers represents the currently idle buffer frame , andDatabase pages represents the buffer frame already in use , andModified db pages represents the number of dirty pages. in the current state, this database is less stressful because there are a large number of free pages in the buffer pool that are available for further use by the database.

Note : The show engine InnoDB status command shows not the current state, but the state of the InnoDB storage engine in the past time frame, as we can see from the above example, Per second averages Calculated the information represented by the last seconds is the database state in the past 24 seconds.

Specifically, the types of data pages cached in the buffer pool are: index pages , data pages ,undo pages , Insert buffers (insert buffer), adaptive Hash Indexes (Adaptive hash index),lock information stored by InnoDB (lock info), data dictionary Information (dictionary), and so on. It is not easy to assume that the buffer pool simply caches index pages and data pages, which are only a large part of the buffer pool.

A good indication of the structure of the memory in the InnoDB storage engine.

PS: Parameter innodb_buffer_pool_size Specifies the size of the buffer pool, under the 32-bit Windows system, the parameter INNODB_BUFFER_POOL_AWE_MEM_MB can also enable the Address window extension (AWE) feature, The limit for memory usage under 32-bit is exceeded. However, when using this parameter, it is important to note that once the AWE feature is enabled, the InnoDB storage engine will automatically disable the ability of the adaptive Hash Index (Adaptive hash indexes) .

Log buffering

Strictly speaking, it should be a redo (redo) log buffer. The redo log information is first placed in this buffer and then flushed to the Redo log file at a certain frequency. This value generally does not need to be set to large, because the redo log buffers are normally flushed to the log file every second, so we only need to ensure that the amount of transactions generated per second is within this buffer size.

Additional Pool of memory

The extra memory pool is usually ignored by the DBA and is not considered important, but the opposite is true. In the InnoDB storage engine, the management of memory is done in a way known as the Memory Heap (heap). when allocating memory to some data structures themselves , you need to request from an additional pool of memory, which is requested from the buffer pool when there is not enough memory in the zone . The InnoDB instance requests space in the buffer pool (innodb_buffer_pool), and the framebuffer (frame buffer) in each buffer pool also has a corresponding buffer control object that records such as LRU, lock, Wait for such information, and the memory of this object needs to be requested from the additional memory pool. Therefore, when you apply for a large InnoDB buffer pool, this value should also increase accordingly.

InnoDB Background Threads and memory