Architecture Design: System Storage (5)--mysql Database performance optimization (1)

Next: Architecture Design: System Storage (4)-Block storage scheme (4)

1. mysql Overview

From the beginning of this paper, we will discuss the performance optimization scheme and cluster scheme of the relational database based on the block storage scheme. The selection of a relational database will target the most widely used MySQL data in startups and the Internet industry, but the MySQL installation process and basic usage knowledge are not within our scope of discussion. Following a few articles we first discuss the main factors that affect the performance of a single MySQL node, and then introduce the principles and technical solutions of MySQL read-write separation, data table longitudinal splitting.

MySQL database has been acquired by Oracle and developed in multiple versions. Currently the most widely used and free version of MySQL is MySQL Community (Community edition), plus three paid MySQL versions MySQL standard (MySQL standards), MySQL Enterprise (MySQL Business edition), MySQL Cluster (MySQL cluster edition), these three versions are calculated according to the CPU core, and the price is from low to high. Finally Oracle also offers two mini MySQL versions: MySQL classic, this version of MySQL only provides MyISAM storage engine but installs quickly and takes up less space; MySQL Embedded (embedded version), This version of the competition software is SQLite. Although the community version is free and the functionality provided by this release is not rich in Enterprise Edition, the same hardware conditions single node performance is not as good as the basic version of the enterprise. But we can build a relatively inexpensive and well-performing MySQL database cluster using the features provided by the Community version itself and some third-party software.

2. Selection of Database engine

One of the most important concepts in the MySQL database is the database engine, which differs greatly from the way the database engine works, resulting in a performance difference between MySQL database services. For example, if the database engine needs to support transactions, it must satisfy the basic nature of the Transaction--aicd attribute (AICD: atomicity, isolation, consistency, and permanence. is the basic knowledge so do not repeat here), then nature needs a certain processing mechanism to achieve these characteristics. The real effect of this is that the database engine that supports transactions consumes more time than the database engine that does not support transactions, in cases where the same amount of data is written. Here we first list the database engines (parts) supported in MySQL Database Community Edition:

• The Memory:memory storage engine stores the table's data completely in memory. In the historical version of the MySQL database and other engines similar to the database engine are the heap, which was the fastest-accessing database engine in the MySQL database. But because these two database engines work completely in memory, if MySQL or the server restarts, the data saved in the database engine will be lost.

• Blackhole: Chinese name "Black hole", data tables using the Blackhole database engine do not store any data, only log binary logs based on database operation procedures. Its primary role is as a repeater for MySQL master-slave replication, and it can add a business filtering mechanism on top of it.

• The Myisam:myisam database engine is the default database engine for MySQL databases. MyISAM uses a form-locking mechanism to optimize multiple concurrent read and write operations (in effect, a mechanism used to avoid data dirty reads). But this kind of mechanism has a certain waste to the use of storage space. MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space. The MySQL database-related technologies described in this article will not involve this database engine.

• The INNODB:INNODB database engine is the most widely used database engine in various versions of the MySQL database, and if not specifically described in the following article, the InnoDB database engine is said to be the default. The InnoDB database engine uses a logging mechanism to provide transactional support.

3. Basic I/O performance

To understand the performance issues in the MySQL database, let's start by figuring out what the latter actually did and how it was done when the client committed a transactional operation to the MySQL database. The working processes described in this section revolve around the InnoDB database engine:

The author has only drawn the InnoDB database engine in the process of insert/update a transaction involved in the important work area, innodb the actual work details than the steps shown are much more complex. As mentioned above, the InnoDB database engine is a database engine that supports transactions, so how to solve the problem of data consistency in the case of abnormal crashes is one of the most important tasks in its design. InnoDB Database engine uses logs to solve this problem, note that this is the InnoDB database engine log, not the MySQL database global binary log. InnoDB Database engine logs have another name: Redo logs (redo log), because this part of the log is primarily useful for recovering data from the InnoDB engine after the database crashes and restarts.

In order to improve the performance of MySQL database, the INNODB database engine data operation process is basically completed in memory, and then through a certain policy (described in detail) to synchronize the log data in InnoDB log buffer memory area to disk InnoDB File log Group area. The InnoDB file Log group region is primarily used to store log files for the InnoDB database engine, which is composed of multiple log files of the same size and are read-write in sequence. The Innodb_log_file_size parameter determines the size of each file, and the Innodb_log_files_in_group parameter determines how many log files are in the entire log group.

When the MySQL database completes the initialization process, these log files will pre-occupy a contiguous disk space on the disk as set by the parameter. The phenomenon is that although there is no data in the database, the total size of the log file is already the value of Innodb_log_file_size * Innodb_log_files_in_group:

# InnoDB数据库引擎 日志文件示例....total 1.0G-rw-rw---- 1 mysql mysql 500M May  4 06:09 ib_logfile0-rw-rw---- 1 mysql mysql 500M May  4 06:09 ib_logfile1....

The purpose of this is to ensure that subsequent synchronous log data operations are sequential, not random . When the log data is written to the end of the last file, the next logging data is then re-written from the beginning of the first log file.

3-1. The production of I/O performance problems

The four identity pointers in the InnoDB log buffer memory space are the most important elements of the InnoDB Database engine log Processing section: Log sequence, log flushed, Pages flushed, and last checkpoint, respectively. these four identities relate to the different data recovery strategies of the InnoDB during a crash restart and the key principles in I/O performance optimizations . These four identities are actually four numeric values they share a pool of values (called LSN, log sequence number, whose total length is a 64-bit unsigned integer), representing the current InnoDB processing state for the transaction operation. And their values have the following characteristics:

Log sequence >= log flushed >= Pages flushed >= last checkpoint

2. Whenever InnoDB receives a full database Insert/update request transaction, a new LSN is created. New LSN = old LSN + log size for this write. This latest log will be tagged with log sequence, and if multiple transaction requests are received, InnoDB will also sort the logs in a given order, and then generate a new LSN in turn. This step is done entirely in memory, so there is no I/O performance issue .

3. MySQL will then begin to perform various details in this transaction. The INNODB database engine specifically has a InnoDB Buffer pool memory space for data changes or data additions. the size is controlled by the innodb_buffer_pool_size parameter , which is derived from the InnoDB data file and is present in the InnoDB buffer pool as a page. A new page is generated when there is an insert operation in the log, and when there is an update operation in the journal, InnoDB checks to see if the data already exists in the page cache and updates the contents of the page cache directly if there is a (hit). If there is no (missing), the original data is read from the InnoDB data file to InnoDB Buffer pool and then updated. Here are a few questions to note:

   Remember the "read-ahead" Technology we talked about when we talked about disk devices? The idea of this technique is that if the data in an area is read and used then the area adjacent to it will be read and used in the near future. So to improve read efficiency, the disk control chip reads the target block on the disk along with several blocks adjacent to it. The InnoDB database engine also used the idea that reading a page would read the adjacent page at the same time, but whether it would be the purpose of mentioning I/O performance or a different operating environment (explained later).

   When InnoDB completes the data operation in the InnoDB Buffer pool, the page referred to by the changed data will be different from the data stored on disk at this time, such that the page is called Dirty . How to control dirty pages is the key to maintaining data consistency, and the InnoDB database engine is to first write the log information for the transaction to the InnoDB file log group log files. There are three write strategies here, which can be controlled by the Innodb_flush_log_at_trx_commit parameter :

   • Innodb_flush_log_at_trx_commit = 0 o'clock, InnoDB writes all the completed transaction log information for this phase to disk in 1 seconds. The write success here does not mean that the write to the Linux operating system in the page cache succeeds, but rather waits for the operating system to actually write to the notification on the physical disk (see the previous article on the file system for details). This means that even if the data operation in the InnoDB Buffer pool succeeds, but once the database system crashes, the business system loses the data written in the first 1 seconds: because there is no log on the disk media, the data information cannot be recovered after an abnormal restart.

   • Innodb_flush_log_at_trx_commit = 1 o'clock, InnoDB works by writing transaction log information to disk when a log operation is completed (executing a transaction writes a transaction log). Similarly, the write success here is also to wait for the operating system to return a notification that the physical disk was actually written.

   • Innodb_flush_log_at_trx_commit = 2 o'clock, InnoDB works by writing log information to the disk as it completes a log operation. However, this mode of operation InnoDB does not wait for the operating system to return a successful write notification on the physical disk and will continue to work. In fact, at this time, the data generally still exists in the Linux operating system cache memory block, so this mode is best to use the file system with the log function, and confirm that the file system logging enabled.

   The last action of the

   InnoDB database engine In this step is to change the transaction log LSN value of the log flushed identity pointer value to the current last completed refresh action. The actually performs this step, and a transacted operation is really successful .

5. But dirty pages involving data changes have not yet been updated to disk, why is the processing of things counted as successful? This is because even if the database crashes at this time, we can redo the data by the full log stored on the disk. Well, it's best to synchronize dirty pages, right? In the third step InnoDB the database engine will update the dirty pages (the oldest dirty pages) involved in the most recent log flush to disk. When the dirty page is synchronized to disk, the INNODB database engine will update the LSN value of the pages flushed identity point, indicating that the transaction (and the previous transaction) that this LSN value represents has completed the data synchronization action on both the memory and the disk. When the InnoDB database engine makes dirty page updates, the dirty pages are submitted in bulk to the cache memory block of the Linux operating system in accordance with a certain period of time policy. the number of dirty pages per batch submission is determined by the innodb_io_capacity parameter .

   The pages flush policy supported by the different versions of the InnoDB database engine is not the same, but the most basic rules do not change, that is, periodic refreshes. Starting with MySQL version 5.6, the INNODB database engine provides a innodb_adaptive_flushing parameter to the manager, and when this parameter is set to "no" InnoDB the database engine detects the dirty page in InnoDB Buffer The ratio in the pool, as well as the instant I/O status, determines the period of the pages flush. if the scale of the dirty page in InnoDB Buffer pool reaches the percentage threshold set by the innodb_max_dirty_pages_pct (default 75) parameter, the INNODB database engine will follow the Innodb_io_ Capacity_max (default 2000) the number of parameter settings synchronizes this dirty page to disk .

   When disk I/O performance is insufficient and the innodb_io_capacity setting is too large, it can cause I/O requests to be blocked due to a longer I/O queue, resulting in a longer i/if the innodb_max_dirty_pages_pct threshold is accumulated o blocks the queue, which in turn causes the I/O performance of the physical server not to be fully used. So innodb_io_capacity settings are important, especially when the reader is using SSD and high-speed disk arrays at the hardware level.

7. Checkpoint is the last identity point in the InnoDB database engine. This identity point indicates that all log information, data information, which is less than or equal to the LSN value of this identity point, does not need to be re-checked after a database exception has been restarted. All transactions that have an LSN value greater than checkpoint need to be redo , but the redo policy will vary depending on the identity area where the LSN value is located:

   • When the value of the LSN representing the transaction is within the range of log Sequence--log flushed (not including log flushed), the transaction in memory is not processed when the database crashes, and this part of the transaction operation will be discarded upon recovery.

   • When the value of the LSN representing the transaction is within the range of log flushed--pages flushed (excluding Pages flushed), the database crashes when the disk already has full log records of those transactions. The INNODB database engine reads the log data and continues execution until the LSN value representing these transactions is marked as checkpoint (or less than the LSN value of the checkpoint identity). It is important to note that some transactions that are in this range during a database crash may have completed part of the data synchronization action, but must be incomplete. So even if the transaction is to re-disk synchronization, to ensure data consistency.

   • In fact, in earlier versions of MySQL version 5.5, there were only three identities in the InnoDB Database engine: Log sequence, log flushed, and checkpoint. This means that when a dirty page is successfully synchronized to disk, the LSN value of the checkpoint identity is updated directly. The subsequent version of the MySQL database adds the pages flushed identity point, which is designed to ensure that updates to checkpoint and pages flush can have separate cycles, reducing the performance cost.

3-2. Key points of I/O performance

1. Log Flush and Pages flush

   From the description in the previous section, we generally know that there are two I/O operations in the processing of a transaction in the InnoDB database engine: Log flush and Pages flush.

   The process of log flush is to write the completed transaction log to the log file, and because of the way the log files are organized in the INNODB database engine, the operations on the disks in log flush are sequential writes. And the technical team can also use the Innodb_flush_log_at_trx_commit parameter to adjust the synchronization policy of InnoDB log buffer to InnoDB File log group, which helps to further improve log flush performance. This is why the MySQL database (which most of the relational databases apply) is built directly on the block storage scenario, not on the file storage scheme or on the object storage scenario.

   The process of the Pages flush is less fortunate, and the InnoDB database engine cannot know in advance what data the database will hold or what area the target data for the next update operation and select operation is stored in. So the InnoDB database engine reads and updates for page only based on random read-write. Then the pages flush process requires more solutions to the problem of how to maintain I/O performance.

   • For example, when reading a page, the "read-ahead" approach reads the page near the target page, and writes the page adjacent to the target page ("write near") when the page is written. The pre-read policy can be set through the Innodb_read_ahead_threshold parameter and completed by the read thread, and the Innodb_flush_neighors parameter can control whether the "near write" policy is turned on. In general, "pre-read"/"near write" is turned on by default, but the "pre-read"/"near write" mentality itself requires some accuracy, and low-hit "read-ahead" reduces INNODB I/O performance. There is also a "random pre-read", which is turned off by default in MySQL version 5.6, and will be abolished in subsequent releases, so this is no longer covered.

   • For example, the action to submit a page to a disk is designed to be periodic and batch, and always keep the InnoDB Buffer pool memory Area dirty pages (Dirty page) at a certain scale, these policies are primarily innodb_io_ Capacity, innodb_max_dirty_pages_pct, Innodb_io_capacity_max and other parameters control.

   • For example, by adjusting the innodb_buffer_pool_size parameter to obtain a larger InnoDB Buffer Pool memory area, store More page. In fact, the InnoDB Buffer pool area includes not only the page cache data section we've introduced, but also other data chunks. For example, to quickly locate the hash index structure for B + tree indexes. Adjusting the Innodb_buffer_pool_size parameter will allow these data regions to enjoy the benefits of memory capacity-at least not frequently, forced cleanup of content space.

3. Basic hardware conditions

   In accordance with the block storage scheme described earlier in this topic ("Architecture Design: System Storage (1)-Block storage scheme (1)", "Architecture Design: System Storage (2)-Block storage scheme (2)"), if the underlying hardware media storing MySQL data is just a mechanical disk, So no matter how to optimize the other parameters of MySQL, MySQL actually on the disk sequential I/O speed is theoretically only about 100mb/s. This still does not calculate the hardware layer check, not calculate the different file system processing time and so on, so the actual I/O speed will only be slower. In addition, if you use a single piece of mechanical disk storage MySQL data, then disk space expansion is also a problem. The largest single-block mechanical disk available on the market now has only 10TB of storage space. When this part of the capacity is used to expand it is basically an impossible task to complete. Finally this storage method also has the security question, the single piece mechanical disk in the continuous high I/O environment is very easy to damage, as long as is a certain capital support company, the mechanical disk itself is regarded as the consumable.

   So even in a start-up company's online production environment, it is not recommended to use a single mechanical disk to store any business data that needs to be persisted . If this is due to funding issues, it is recommended to use the out-of-the-box PAAs environment provided by some cloud service providers because these PAAs
   The environment itself supports data recovery, and the I/O performance and computing performance of the MySQL database is not a bottleneck for the business system for the time being, using the expensive hardware/software environment that the cloud service provider has built.

3-3. Breakthrough I/O performance

=================================================
(next)

Architecture Design: System Storage (5)--mysql Database performance optimization (1)