Summary of the MySQL inndodb storage engine (composition, lock, transaction, backup, optimization)

The figure above summarizes the logical and physical architecture components, locks and transactions, backup policies, and tuning advantages of innodb mysql. The following describes each point.

In terms of thread processing, Mysql is a multi-threaded architecture consisting of a master thread, a lock monitoring thread, an error monitoring thread, and multiple I/O threads. In addition, a thread is enabled for a connection for service.

Io threads are also divided into insert buffer that saves random IO, which is similar to oracle redo logs for transaction control, and IO threads for multiple writes, multiple read hard disks, and memory swap.

In terms of memory allocation, including innodb buffer pool and log buffer. The innodb buffer pool includes insert buffer, datapage, index page, data dictionary, and adaptive hash.

In terms of Logical Data Structure, innodb includes tablespaces, segments, partitions, pages/blocks, and rows. The index structure is a B-tree structure, including the secondary index and primary key index. The leaf node of the secondary index is the primary key PK, and the leaf node of the primary key index points to the stored data block.

In terms of physical storage files, including the frm table definition file, each table has an ibd file (which needs to be set), ibdata file (system tablespace MySQL database file, store InnoDB system information and user database table data and indexes, data dictionary, undo, all tables are shared ),

Ib_logfile: the binary log file of binlog.

 

To ensure consistency, the classification of locks is generally divided into shared locks and exclusive locks. mysql mainly has these locks, row locks, page locks, table locks, and innodb Storage engine is Row-level locks.

Shared locks (S -- allow only the current or other threads to read, do not allow any threads to modify this record) and exclusive locks (X --- only allow the current thread to update or delete a row of records, other thread operations are not allowed (including querying this record)

When performing a SELECT query, a shared lock is generated. Of course, an exclusive lock is generated for select for update.

When a lock is used, Major deadlocks are generated. Generally, resources are locked in parallel. Common scenarios are,

A thread updates the index value based on the primary key (the X lock is applied to the data row first, and the X lock is applied to the records in the index block ), the other is to update the relevant fields based on the index (first add the X lock to the records in the index block, and then add the X lock to the data row ).

 

To ensure data integrity, MySQL InnoDB supports transaction operations,

Taking into account the different requirements of consistency and concurrency, the isolation level between transactions is

Read uncommited (dirty read, non-repeated read, phantom read (lowest isolation level, high concurrency performance )),

Read commited (unrepeatable read, phantom read problems (locking the row being read )),

Repeated read (MySQL Default) (phantom read (Lock all rows read )),

Serialize read (verify that all cases will not occur (Lock table )).

In terms of backup, it can be divided into cold backup (physical file copy, data file, undo, insert buffer, etc. ------- stop), warm backup (dump, import ---- lock required), and hot backup (xtrabackup ).

You can perform full backup, Incremental Backup (via BINLOG or lsn (xtrabackup), or real-time backup (replication)

In terms of performance tuning, you can consider the following aspects,

A. Operating System Level, kernel and socket optimization, and network optimization bond

B. server level (connection management, network management, table management, logs)

C. Application level (for example, considering the index, schema optimization should be appropriately redundant; optimizing the CPU and memory problems caused by SQL queries)

D. IO Storage Level,

InnoDB is mainly used in OLTP applications, generally io-intensive applications. It makes full use of the cache mechanism on the basis of improving Io capability. You need to consider the following,

1. Expand the InnoDB buffer pool as much as possible on the basis of ensuring the system's available memory. It is generally set to 3/4 of the physical memory.

2. Logs and data storage need to be separated. logs are written in sequence. Raid 1 + 0 is required and buffer-Io is used. Data is read and written in Discrete mode and direct Io is used,

Avoid overhead caused by file system cache.

Storage capacity, SAS disk raid operations (RAID card cache, read cache disabled, disk cache disabled, pre-read disabled, only writeback buffer is used, but the charge and discharge issues need to be considered ),

Of course, if the data size is small, data storage can use high-speed devices, such as fusion Io and SSD.

For data writing, control the dirty page refresh frequency, and control the cache hit rate for Data Reading. Therefore, estimate the iops required by the system and evaluate the number of hard disks required.

(The iops of fusion Io is above 10000, and the average hard disk is 150)

4. Use the file system cache only when recording transaction logs; Avoid MySQL using swap whenever possible

(VM. swappiness = 0 can be released when the memory is insufficient)

5. Io Scheduling Optimization to reduce head movement

See the following parameters:

Assume MySQL (physical memory 32 GB, CPU physical 40 core)

Table space needs to be pre-allocated due to the Jitter Caused by extended table space.

Innodb_file_per_table (true) --- scatter Io

Innodb_flush_log_at_trx_commit (set to 1, <2 <0) 2 is the file system, 1 is flush directly to the storage

Sync_binlog (set to 1 <0) 0 is the file system

Innodb_flush_method (the default value is fdatasync, o_dsync, and o_direct. Here it is set to o_direct)

Binlog_cache_size (32 K by default)

Innodb_buffer_pool_size (set to 24 GB)

Innodb_max_dirty_pages_pct (default: 90% ---> 75%)

Innodb_read_io_threads/innodb_write_io_threads (four by default. Can high-speed storage devices be larger)

Innodb_adaptive_flushing (on, determine the most appropriate number of dirty pages to be refreshed Based on the generation speed of the redo log)

Innodb_thread_concurrency (twice the CPU core, 80)

Innodb_io_capacity (default value: 200 --- refresh dirty pages and insert buffer)

Innodb_doublewrite

Innodb_purge_threads

Max_connections (100 by default)

Max_user_connections (30 by default, changed to 100)

Innodb_page_size (8 K. If the full table scan is 16 K, if the SSD, 4 K random performance is good)