MySQL architecture and History a. Concurrency control

1. A shared lock (read lock) is shared and does not block each other

A write lock blocks all other write and read locks

B. the transaction

1. Transaction ACID

  • A transaction must be treated as an indivisible, minimal unit of work in which all operations are either committed successfully or rolled back on failure. It is not possible for a transaction to perform only some of the operations

  • Consistency A database always transitions from one consistent state to another consistent state

  • Isolation Changes made by one transaction are not visible to other transactions until they are finally committed

  • Durability Once a transaction is committed, their modifications are permanently saved in the database

2. Four isolation levels

  • READ UNCOMMITTED, changes in a transaction, even when they are not committed, are visible to other transactions. Transactions can READ UNCOMMITTED data, also known as Dirty reads, and this level can cause problems

  • READ COMMITTED, the default isolation level in most database systems where, at the start of a transaction, changes made by COMMITTED transactions can only be “seen”, and any changes made by a transaction from the start until the COMMITTED transaction are invisible to other transactions. Also called nonrepeatable read (Phantom read is possible). This is when a transaction reads a range of records and another transaction inserts a new record in that range. When the previous transaction reads the range of records again, Phantom Row will be generated

  • REPEATABLE READ, through InnoDB and XtraDB storage engines, is MySQL’s default transaction isolation level

  • SERIALIZABLE The highest level of SERIALIZABLE, which avoids the magic read problem by forcing transactions to be executed sequentially. Locks are placed on every row read, which can lead to a large number of timeouts and lock contention

3. Deadlock: Two or more transactions occupy the same resource and request to lock the resource occupied by the other, resulting in a vicious cycle

4. Transaction logging: When the storage engine modifies the data of a table, it only needs to modify its memory copy and record the modification in the transaction log that persists on disk, instead of persisting the modified data to disk each time. After the transaction log is persisted, the modified data in memory can be slowly flushed back to disk in the background, which is called write-ahead Logging.

C. Multi-version concurrency control

1. Multi-version Concurrency Control (MVCC) is a variant of row-level locking, but it avoids locking in many cases and is therefore cheaper. Implementation mechanisms vary, but most implement non-blocking reads, and writes lock only the necessary rows

2.MVCC is implemented by saving snapshots of data at a certain point in time. There are optimistic and pessimistic types, and only work at REPEATABLE READ and READ COMMITTED isolation levels

D. mysQL Storage engine

1. The. FRM file of MySQL saves the TABLE definition. SHOW TABLE STATUS displays the TABLE information

2. Unless there is a very specific reason to use another storage engine, the InnoDB engine should be preferred

3. Don’t believe the rule of thumb that MyISAM is faster than InnoDB. It’s not absolute

MySQL benchmark testing

A. Why do we need benchmarking

1. Benchmarking can observe how the system behaves under different pressures, assess the capacity of the system, grasp what are the important changes, or observe how the system handles different data

B. Benchmarking strategy

1. Two main strategies:

  • Overall testing for the entire system (integrated full-stack)

  • Test MySQL individually (single-component)

2. What indicators to test:

  • Throughput, refers to the number of transactions per unit of time, commonly measured in units of transactions per second (TPS), or transactions per minute (TPM)

  • Response time or delay, the total time required for a test task, which may be measured in microseconds, milliseconds, seconds, or minutes, depending on the application. Percentile response time is usually used instead of the maximum response time

  • Note the Threads_running status of the MySQL database during the test

  • Scalability, doubling the work of the system and, ideally, doubling the performance (i.e., doubling throughput), is very useful for capacity specifications, providing information that other tests cannot provide to help uncover application bottlenecks

Due to the limited space, I put more information in WORD document, if there is a need to forward + attention, PRIVATE message I get