Shulou(Shulou.com)05/31 Report--

How to understand the memory structure of MySQL database Innodb and how to use memory? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Overview

Many friends may have a lot of questions about how Innodb uses memory. I'll give a brief introduction to the innodb memory structure here, and then explain how innodb is allocated when it starts.

1. INNODB memory structure

(1) clustered index and nonclustered index:

Clustered index: primary key, ordered, stored in the same order as memory

Nonclustered indexes: non-primary key, disordered

The clustered index stores data in the table at the leaf node.

Nonclustered indexes store primary keys and index columns on leaf nodes

When you use a nonclustered index to query the data, get the primary key on the leaf and find the data you want to find. (the process of getting the primary key and looking for it is called returning to the table.)

(2) buffer pool:

A buffer pool is used to hold a cache of various data. Innodb always reads the data on disk (database files) into the buffer pool by page (16K), and then saves the data in the buffer pool according to the least recently used algorithm (LRU).

If the database file needs to be modified, always modify the page in the cache pool first (after modification, the page is dirty), and then refresh to disk at a certain frequency.

(3) insert buffer (insert buffer):

Conditions of use: 1. The index is a secondary index; 2. The index is not unique.

That is, the primary key index does not use insert buffering. The primary key index is a clustered index, the insertion is sequential, and the execution efficiency is relatively high, so it does not need buffering.

However, when there is a secondary index (nonclustered index, non-primary key) in the table, it is not necessarily sequential, discrete access is required, and insert performance is degraded.

Therefore, the insert and update operations of nonclustered indexes are not inserted directly into the index page, but into the buffer, and then perform the merge operation of the insert buffer and nonclustered index leaf nodes at a certain frequency.

Note: because the non-primary key index leaf node stores the primary key and current column values, when using a nonclustered index query, first check the tree of the secondary index to find the corresponding primary key, and then check the tree of the primary key index, it will be checked twice, which is not efficient.

(4) redo log (redo log):

When a transaction commits, Innodb first reads the data from disk to memory for modification, then writes the transaction log to the log buffer (log buffer), then flushes it to the redo log file (redo log file) for persistence, and then periodically flushes it to disk.

Used to continue transactions that have been commit but the data has not been fully written back to disk when the instance fails to recover.

(5) double write (write twice):

To prevent redo log from being damaged during writing, we need to keep a backup. If there is a failure, restore the redo log from the backup first, and then restore the data.

(6) undo log:

Record the mirror before the data is modified, which is used to roll back the uncommitted transaction to the state before the transaction starts.

Undo operation: when the Innodb storage engine rolls back, it actually does the previous work. For insert operations, Innodb completes a delete, and for update, it executes a reverse update, putting back the rows before modification.

(7) Adaptive hash indexing:

Innodb monitors the frequency of index lookups on the table, and automatically creates a hash index if it is found that the establishment of a hash index will increase speed. Instead of indexing the entire table, some pages are built based on the frequency of access.

(8) transaction submission:

During the transaction, each time the sql statement is executed, the undo log and redo log are recorded, and the data is updated to form a dirty page. Then redo log will set down the disk according to time or space, and undo log and dirty pages will be set down according to checkpoint. After falling, the corresponding redo log can be deleted.

At this point, the transaction is not commit yet. If a crash occurs, first check the checkpoint record, use the corresponding redo log for data and undo log recovery, then check the status of the undo log to find that the transaction has not been committed, and then use undo log to roll back. When the transaction performs the commit operation, all the redo log related to the transaction will be removed from the disk, and only if all the redo log are successfully removed will the commit be considered successful.

Then the dirty pages of data in memory continue to be dropped according to checkpoint. If a crash occurs at this time, only redo log is used to recover the data.

two。 Some important concepts:

NBLOCKS=Innodb_buffer_pool has multiple pages (block) = innodb_buffer_pool_size/16384 (16k)

OS_THREADS= if (innodb_buffer_pool_size > = 1000Mb) = 50000

Else if (innodb_buffer_pool_size > = 8Mb) = 10000

Else = 1000 (this value is only used on * nixes systems, there is a slight difference for Windows to calculate OS_THREADS)

3. The memory used by Innodb includes:

Innodb_buffer_pool_size

Innodb_additional_mem_pool_size

Innodb_log_buffer_size

Adaptive index hash, size (innodb buffer Index Management area) = innodb_buffer_pool_size/64

System dictionary hash,size (innodb internal dictionary area) = 6 * innodb_buffer_pool_size/512

Memory for sync_array,size (cost for Innodb internal syncronzation) = OS_THREAD * 512

Memory for os_event,size (cost of syncronzation for innodb memory) = OS_THREAD * 216,

Memory for locking system (lock management system for memory), size = 5 * 4 * NBBLOCKS

4. The formula for calculating innodb memory usage is:

Innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812Unip 16384 * innodb_buffer_pool_size + OS_THREADS * 368

For 812x16384 * Innodb_buffer_pool_size, it can be simply calculated with innodb_buffer_pool_size / 20, for OS_THREADS * 368.

OS_THREADS * 368 = 17.5MB if innodb_buffer_pool_size > 1000MB OS_THREADS * 3368 = 3.5MB if innodb_buffer_pool_size > 8MB

To give an example:

If you have a 1500MB innodb_buffer_pool_size that contains innocence, additionalmememetry, poolcake size = 20 MB,innodb_log_buffer_size = 8m

Innodb will apply to the system for memory of = 1500m + 20m + 8m + 1500max 20m + 17.5m = 1620.5m

According to the above conditions, you can figure out how much memory Innodb needs most, so that you can have a plan for the memory usage of the server.

This is the answer to the question about how to understand the memory structure of MySQL database Innodb and how to use memory. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.