This picture is the memory structure diagram officially provided by MySQL version 5.7

Memory Structure

Buffer Pool

• Innodb_buffer_pool_size setting size in production:
  • If MySQL is deployed on an independent server, it is recommended to set it to 80%.Otherwise, set it to 80%~90% of the remaining memory
  • View the command show variables like '%innodb_buffer%';

Change Buffer Write Buffer

• By default, it occupies 25% of the space of Buffer_pool, and supports a maximum of 50%. It can be adjusted according to the business. If there is more writing and less reading, it can be appropriately increased.-> innodb_change_buffer_max_size
• View command: show variables like '%innodb_change_buffer_max_size%';
• Set command: set global innodb_change_buffer_max_size = 30;
• When updating a piece of data, the data exists in the Buffer Pool and can be modified directly in the Buffer Pool, one memory operation.If the data does not exist in the Buffer Pool (misses), a memory operation will be performed directly in the Change Buffer. When the next query is made, the data will be read from the disk first, and then the data will be read from the Change Buffer for merging, and finallyLoad into the Buffer Pool.
• Change Buffer, only for non-unique index pages.
  • Because if the index is set to be unique, the InnoDB mechanism must perform a uniqueness check when modifying, that is to say, the disk must be queried and an IO operation must be performed.At this time, the data will be directly queried into the Buffer Pool, and then modified in the buffer pool, and the Change Buffer operation will not be performed.

Log Buffer

• Query command:
• show variables like '%innodb_log%';
  • View Log Buffer size and disk log file size and number
    • The default Log Buffer size is 16MB
    • The default disk single log file size is 48MB, and the number is 2
• The command to query the behavior of writing files and flashing disks:
• show variables like '%innodb_flush_log%';
  • Write file refers to the process of writing Log Buffer to Operating System Cache (OS Cache)
  • Brushing refers to the process of writing OS Cache to disk
• innodb_flush_log_at_trx_commit
  • 0: Perform log file writing and disk flushing operations every 1 second, and data will be lost for up to 1 second
  • 1: The transaction is committed, the log is written and the disk is flushed immediately, the data is not lost, but the IO operation is performed frequently (default is 1)
  • 2: The transaction is committed, the log operation is written immediately, and the disk flush operation is performed every 1 second

disk structure

System Tablespace

• View command: show variables like '%innodb_data_file_path%';
  • ibdata1:12M:autoextend refers to the file name: ibdata1, size 12M, autoextend
  • InnoDB Data Dictionary data dictionary
    • Consists of internal system tables, used to store metadata
  • Doublewrite Buffer Double write buffer
    • Before the Buffer Pool actually writes to other tables, it first writes the dirty pages in the page page to the double-write buffer.There are two functions, one is to improve the writing speed, and the other is to retrieve lost data from the double write buffer in the event of an accident, so as to achieve double insurance.Double write buffer on by default
    • Related commands:
      • Check if it is enabled: show variables like '%innodb_doublewrite%';
      • View the flushing strategy: show variables like '%innodb_flush_method%';
        • The default value is NULL
        • It is recommended to set O_DIRECT under Unix system to improve the efficiency of flashing
  • Undo Logs
    • There are multiple undo_0x files in Undo Tablespaces. Before the transaction is started, the original data that has not been modified is backed up to roll back the transaction in unexpected situations.
  • Redo Log redo log
    • A disk-based data structure for incomplete transaction write data during crash recovery. MySQL writes to redo log files in a round-robin fashion.Redo log files
      The default is two files, a single 48M.

Other chapters ->Jump

end...