Note the first wave of high-performance mysql

Note the first wave of high-performance mysql

A transaction is an atomic SQL query, or an independent unit of work.. If the database engine can successfully execute all the statements in the group, it will execute these statements. If any of the statements fails to be executed, all statements are not executed. That is to say, the statements in the transaction are either fully executed or not executed. Then there is the ACID feature of the transaction. Atomicity: A transaction must be regarded as an inseparable minimum unit.All operations in the entire transaction must be successfully executed. Otherwise, all rollback is performed. It is impossible to execute only part of the operations to ensure that all statements in the transaction are an atom.. Consistency: the database always changes from a consistent state to another consistent state.If a statement in a transaction fails to be executed, any statement that has been successfully executed will not be saved to the database.. Isolation: generally (this is due to different transaction isolation levels ),A transaction that is invisible to other transactions before the final commit, Note that it is common. Durability: Once a transaction is committed, its modifications are permanently saved to the database. I have not understood this concept very well. (for example, for innodb, The innodb_flush_log_at_trx_commit parameter differs greatly from the time point at which data is written to the disk after the transaction is committed .) A database that implements ACID, such as mysql databases, usually requires stronger CPU processing capabilities, more disk space, and larger memory size. However, I have not found a reasonable solution for what kind of configuration is required. What kind of configuration is more reasonable for our futures trading platform. Isolation level (each level specifies the modifications made in a transaction, which are visible within and between transactions, and which are invisible. Lower-level isolation can usually execute higher concurrency and lower system overhead): read uncommitted (uncommitted read): At this level, modifications in the transaction, even if not committed, it is also visible to other transactions. Transactions can read uncommitted data (known as dirty reads). This level has many problems, and the performance will not be better than other levels. It is not recommended. Read committed: when a transaction starts, only modifications made by the COMMITTED transaction can be seen. In other words, as long as the transaction is not COMMITTED, other transactions cannot see the modifications made by this transaction. Repeatable read (repeatable read ):This level ensures that the results of reading the same record for multiple times in the same transaction are consistent.(I will perform tests later, but I have doubts) for the innodb Engine of mysql,The engine uses the MVCC mode for control and solves phantom read problems.However, in theory, if other engines do not solve the phantom read problem, phantom rows will occur (for phantom read and phantom rows, study later ). The default isolation level of mysql is repeated read. SERIALIZABLE: it enforces the serial execution of transactions, avoiding phantom read problems. To put it simply, at this level, the transaction locks every row of data during reading, which may cause a large number of lock contention and timeout issues. In actual application, it is rarely used. AUTOCOMMIT: mysql uses the AUTOCOMMIT mode by default. That is to say,If a transaction is not started explicitly, each query is committed as a transaction.. In this case, if a transaction is displayed, that is, when start transaction is used to start the transaction, all queries in the transaction are no longer separated into small transactions for commit operations. Transaction Log: transaction logs can help improve the efficiency of transactions,When modifying table data, the storage engine only needs to modify your memory copy, and then record the modification behavior to the transaction logs lasting on the hard disk, instead of durating the modified data to the disk every time. Transaction logs use the append method. Therefore, when writing logs, the sequential IO in a small area on the disk is much faster. After the transaction log persists, the data modified in the memory can be slowly flushed back to the disk in the background, and the modification data needs to be written twice to the disk. If the modified data has been recorded in the transaction log and persisted, even if the data itself has not been written back to the disk, the operating system crashes and the storage engine can automatically reply to the data when it is restarted.

Practice Difference between committed read and Repeatable readAt the isolation level of committed read, we conduct the following experiment to verify that "any modifications made to a transaction are invisible to other transactions from the beginning before the transaction is committed ", test insert first

Transaction 1	Transaction 2
Mysql> show variables like 'tx _ isolation '; + --------------- + ---------------- + | Variable_name | Value | + --------------- + ---------------- + | Tx_isolation | READ-COMMITTED | + --------------- + ---------------- + 1 row in set Mysql> start transaction; Query OK, 0 rows affected Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | + ---- + ------- + 2 rows in set	1
2	Mysql> start transaction; Query OK, 0 rows affected Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | + ---- + ------- + 2 rows in set Mysql> insert into test values (3, 3 ); Query OK, 1 row affected Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set
Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | + ---- + ------- + 2 rows in set	3
4	Mysql> commit; Query OK, 0 rows affected
Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set Mysql> commit; Query OK, 0 rows affected	5

Through the above sequential execution, we can see that "at the beginning of a transaction, we can only see modifications made by committed transactions", and "A transaction starts until it is committed, any modifications made are invisible to other transactions. "Then," the same query may be executed twice in a transaction and different results may be obtained ".
Isolation level unchanged, test update

Transaction 1	Transaction 2
Mysql> show variables like 'tx _ isolation '; + --------------- + ---------------- + | Variable_name | Value | + --------------- + ---------------- + | Tx_isolation | READ-COMMITTED | + --------------- + ---------------- + 1 row in set Mysql> start transaction; Query OK, 0 rows affected Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set	1
2	Mysql> start transaction; Query OK, 0 rows affected Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set Mysql> update test set value = 11 where id = 1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 11 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set
Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set	3
4	Mysql> commit; Query OK, 0 rows affected
Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 11 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set Mysql> commit; Query OK, 0 rows affected	5
Mysql> show variables like 'tx _ isolation '; + --------------- + ---------------- + | Variable_name | Value | + --------------- + ---------------- + | Tx_isolation | READ-COMMITTED | + --------------- + ---------------- + 1 row in set Mysql> start transaction; Query OK, 0 rows affected Mysql> select value from test where id = 1; + ------- + | Value | + ------- + | 11 | + ------- + 1 row in set	6
7	Mysql> start transaction; Query OK, 0 rows affected Mysql> update test set value = 1 where id = 1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 Mysql> select value from test where id = 1; + ------- + | Value | + ------- + | 1 | + ------- + 1 row in set
Mysql> select value from test where id = 1; + ------- + | Value | + ------- + | 11 | + ------- + 1 row in set	8
9	Mysql> commit; Query OK, 0 rows affected

Mysql> select value from test where id = 1; + ------- + | Value | + ------- + | 1 | + ------- + 1 row in set Mysql> commit; Query OK, 0 rows affected

10

As shown in the preceding steps, When READ-COMMITTED is used, the query results of a transaction are affected by the update and insert operations of other transactions..

The isolation level can be read repeatedly. First, let's take a look at how Modify transaction isolation level(The following describes how to change the Repeatable read to the committed read. Note that if you follow the test order, modify it)

 

 

mysql> show variables like 'tx_isolation';+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ |+---------------+-----------------+1 row in setmysql> set global transaction isolation level read committed;Query OK, 0 rows affectedmysql> show variables like 'tx_isolation';+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ |+---------------+-----------------+1 row in setmysql> 

Does the isolation level change? Close the current command line and open a new command line.

mysql> show variables like 'tx_isolation';+---------------+----------------+| Variable_name | Value          |+---------------+----------------+| tx_isolation  | READ-COMMITTED |+---------------+----------------+1 row in setmysql> 

After the transaction isolation level is modified, we start testing.

Transaction 1	Transaction 2
Mysql> show variables like 'tx _ isolation '; + --------------- + ----------------- + | Variable_name | Value | + --------------- + ----------------- + | Tx_isolation | REPEATABLE-READ | + --------------- + ----------------- + 1 row in set Mysql> start transaction; Query OK, 0 rows affected Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set	1
2	Mysql> show variables like 'tx _ isolation '; + --------------- + ----------------- + | Variable_name | Value | + --------------- + ----------------- + | Tx_isolation | REPEATABLE-READ | + --------------- + ----------------- + 1 row in set Mysql> start transaction; Query OK, 0 rows affected Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set Mysql> insert into test values (4, 4 ); Query OK, 1 row affected Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | + ---- + ------- + 4 rows in set
Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set	3
4	Mysql> commit; Query OK, 0 rows affected
Mysql> select * from test; + ---- + ------- + | Id | value | + ---- + ------- + | 1 | 1 | | 2 | 2 | | 3 | 3 | + ---- + ------- + 3 rows in set Mysql> commit; Query OK, 0 rows affected	5

From the above results, we can see that "this level ensures that the same transaction reads the same results multiple times ". Here there is no magic line, because my database engine is innodb, and the book says this engine can solve the phantom read problem.

 

 

The isolation level can be read repeatedly, and the update test is performed.

Transaction 1	Transaction 2
Mysql> start transaction; Query OK, 0 rows affected Mysql> select value from test where id = 1; + ------- + | Value | + ------- + | 1 | + ------- + 1 row in set
	Mysql> start transaction; Query OK, 0 rows affected Mysql> select value from test where id = 1; + ------- + | Value | + ------- + | 1 | + ------- + 1 row in set Mysql> update test set value = 11 where id = 1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 Mysql> select value from test where id = 1; + ------- + | Value | + ------- + | 11 | + ------- + 1 row in set
Mysql> select value from test where id = 1; + ------- + | Value | + ------- + | 1 | + ------- + 1 row in set
	Mysql> commit; Query OK, 0 rows affected
Mysql> select value from test where id = 1; + ------- + | Value | + ------- + | 1 | + ------- + 1 row in set Mysql> commit; Query OK, 0 rows affected

The update operation also meets the expected results.

 

 

In the description of transaction logs, we can see that as long as the modified data has been written to the log and persisted, even if the data is not written to the disk, the system will still recover data when it is restarted. Let's take a look at the introduction of innodb_flush_log_at_trx_commit parameter provided on the official website.

Controls the balance between strict ACID compliance for commit operations, and higher performancethat is possible when commit-related I/O operations are rearranged and done in batches. You canachieve better performance by changing the default value, but then you can lose up to a second oftransactions in a crash.? The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDBlog buffer are written out to the log file at each transaction commit and the log file is flushed to disk.? With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately onceper second and the log file is flushed to disk. No writes from the log buffer to the log file are performedat transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second,due to process scheduling issues. Because the flush to disk operation only occurs approximately onceper second, you can lose up to a second of transactions with any mysqld process crash.? With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transactioncommit and the log file is flushed to disk approximately once per second. Once-per-second flushingis not 100% guaranteed to happen every second, due to process scheduling issues. Because theflush to disk operation only occurs approximately once per second,

Generally, this attribute serves the ACID principle of the database, and the default value is 1, but in actual conditions (our project combines spring and mybatis, this may be due to some improper settings). Setting it to 2 will improve the transaction performance. As you can see from the document," 1, the innodb cache will refresh the disk when the transaction is committed or every second. 2, the innodb cache writes transaction logs but does not refresh the disk during transaction commit, and then refresh the disk every second.", 2 is much better than 1, but I don't have a good understanding of the potential risks. According to the results shown in the document," when the operating system crashes, 2, it will lose 1 second of Data ", but think about the time node, 1. when the transaction does not have a commit, the power is down. At this time, the data is certainly not successfully updated, because the transaction log has not been written yet. 2. after a transaction is committed, a power failure occurs when the transaction log is written. In this case, no data can be recovered whether the parameter value is 1 or 2. when a disk is refreshed every second, a power failure occurs. According to the literal meaning of "high-performance mysql", since the transaction log is persistent, the data will be automatically restored after the restart. The question is, under what circumstances will the risks of 2 and 1 occur. With regard to autocommit mysql, this parameter is enabled by default. For projects that have applied spring and mybatis, whether to disable or enable this parameter is enabled, I have not figured out the performance impact. If you are enthusiastic, you can give a reply. In addition, we can use the following command to set this parameter. Of course, 1 is ON and 0 is OFF.

mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | ON    |+---------------+-------+1 row in setmysql> set autocommit = 0;Query OK, 0 rows affectedmysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | OFF   |+---------------+-------+1 row in set

Summary: The overall research has been a lot of time, but the impact of this parameter on performance has not been fully explored, but the above knowledge points are also very important. I would like to ask people with experience in this area to help, thank you very much.