A tentative study of MySQL default engine InnoDB (iii)

Written in front

This blog to take on a MySQL default engine InnoDB primary discussion (ii) continue to explore the MySQL database InnoDB storage engine

InnoDB file

MySQL database and InnoDB storage engine tables for various types of files:

• Parameter file
• Log file (error log file, binary log file, slow query log file, query log file)
• Socket file (Unix socket connection, avoid TCP protocol, Web server and MySQL server on the same machine can be used to improve communication efficiency)
• PID file (save MySQL instance process id)
• MySQL table structure file
• InnoDB Storage Engine Files

Parameter file

View MySQL configuration file

grepmy.cnf

Check the current MySQL instance configuration item

show variables like "innodb%"\G

Configuration parameters:

• Dynamic Parameters "MySQL instance can be changed in run"
• Static parameter (static) "cannot be changed during the life cycle of the instance Eg:datadir"

Dynamic formatting of parameters

set [@@global. | @@session.]system_var_name = expreg :   set read_buffer_size = 1024000;   set @@session.read_buffer_size = 2048000;   set @@global.read_buffer_size  = 4096000;

Static parameters can not be modified dynamically, the forced modification will be error;
Some dynamic parameters can only be modified in the session, Eg:autocommit;
The whole instance will take effect when some dynamic parameters are modified, eg:binlog_cache_size;
Some dynamic parameters can be used in the session to modify, but also throughout the life of the modification, eg:read_buffer_size;

Log file

Log files record the various activities of the MySQL database and can be used to quickly and accurately find problems and optimize them through the analysis log;

• Errors log (error logs)
• Binary log (Binlog)
• Slow query logs (slow query log)
• Query logs (log)

Error log

The error log records the start, run, and close process of MySQL, which can quickly locate the problem;

View MySQL Instance error file path

show variables like "log_error"\G

Slow query log

The slow query log allows you to set a threshold for all SQL statements that run longer than that threshold to be recorded in a slow query log file;
Can be very good to help optimize the database;

 Show variables  like "Slow_query_log"\g # to see if the slow query log set slow_query_log is turned on on | OFF;# on | Turn slow query Log off Show variables  like "Log_output"\g # View slow query logging to a file or table set log_output=  TABLE| FILE;# Set the slow query log output to table or files Show variables  like "Slow_query_log_file"\g # View slow query log file path show variables  like "Long_query_time"\g # View slow query threshold set long_query_time=;# Set the slow query threshold to 10s Show variables  like "Log_queries_not_using_indexes"\g # See if it's turned on, Do not use indexes also logged in the slow query log set log_queries_not_using_indexes= on | OFF;# Turn on or off Show variables  like "Log_throttle_queries_not_using_indexes"\g # per minute allow "because no index is used" The number of SQL statements recorded in the slow query log # log_throttle_queries_not_using_indexes = 0;Indicates unlimited quantity, may be logged frequently, be careful

• Parsing a slow query log file using the Mysqldumpslow tool (when setting up Log_output=file)

• View Slow query log table (when setting log_output=table)

Slow_log table default is the CSV storage engine, the query efficiency is not very high, can be set to Mysiam, but the personal proposal is set to archive storage engine;

Query log

The query log records all MySQL requests (insert,update,delete,select), whether or not they are correct;
The default record to the file, open log_output=table after logging to the Mysql.general_log table;

like"general_log"\G   # 查看是否开启查询日志set @@globalON|OFF;   # 开启or关闭查询日志like"general_log_file"\G  # 查看查询日志文件路径like"log_output"\G  # 查看查询日志输出到文件还是表中

General recommendations to close, the default is to close the query log;

Binary log

Binary logging all operations that make changes to the MySQL database (excluding query operations such as Select,show)

• Recovery (recovery) eg: point-in-time recovery
• Replication (replication) Eg:master-slave replication
• Audit (audit) eg: Analyze binary log files to see if there is an injection attack, etc.

configuration file Settings Log-bin [=binlog_file_name] Open binary log;
If you do not specify Binlog_file_name, the default is the host name;
Binary log files are placed in the DataDir data directory

show variables like "datadir"\G 

The Mysql-bin.index file is a binary index file, storing the binary log sequence number

Binary log-related configuration parameters:

• Max_binlog_size

  Specifies the maximum value of a single binary file, exceeding that size, which will result in a new binary, suffix +1, and recorded in the. index file

• Binlog_cache_size

  When a transaction is executed, all uncommitted binary logs are logged to a cache.
  When a transaction commits, the binary log in the cache is written directly to the binary log file,
  Binlog_cache_size Set cache size, default is 32k;

  Binlog_cache_size is session-based,
  When a thread starts a transaction, MySQL automatically assigns a cache of size binlog_cache_size.
  So binlog_cache_size can't be too big "it's like Nginx's client_header_buffer_size."

• Sync_binlog

  The default binary log is not synchronized to the disk each time, when the database is down, there may be some data do not brush disk;
  Sync_binlog set not write buffer how many times you sync to disk, default sync_binlog=0

  For maximum high availability when replicating using the InnoDB storage engine, it is recommended to turn on

• Binlog-do-db

• Binlog-ignore-db

• Log-slave-update

  binlog-do-db | Binlog-ignore-db specify those libraries or ignore those libraries write binary logs

  LOG-SLAVE-UPDATE Specifies which master-slave synchronization is to be performed

• Binlog_format

  • STATEMENT binary Logging Logical SQL statements,
  • Row record table rows change, will take up more storage space, master-slave replication will increase the network overhead, but there is better reliability
  • The MIXED is statement by default, with a special row

  Binary files can be parsed using the Mysqlbinlog tool

Socket file

UNIX can connect to MySQL locally using sockets
You can refer to the way NGIGX calls php-cgi

PID File

Save MySQL Process ID

MySQL table structure definition file

Regardless of the storage engine used, MySQL will create a file with a suffix of frm for each table, and the table structure is defined in the file.

InnoDB Storage Engine Files

The files described above are the MySQL database itself files, and the storage engine is irrelevant;
The InnoDB storage engine has its own files:

• Table Space Files
• Redo log Files

Table Space Files

show variables like "innodb_file_per_table"\Gset innodb_file_per_table = ON|OFF;  # 开启or关闭独立表空间show variables like "innodb_data_file_path"\G   # 查看共享表空间文件

When innodb_file_per_table stand-alone table space is opened, the independent table space only stores the data, index, buffer bitmap and other information of the table, and other information is still stored in the shared table space, such as inserting buffer data, etc.

Redo log Files

The data directory for the INOODB storage engine has two files named Ib_logfile0 and Ib_logfile1;

At least 1 Redo log groups (group) per storage engine
Each group has at least 2 redo log files, the default is Ib_logfile0, Ib_logfile1, two file size consistent, in the way of cyclic writing;

Redo log file related configuration parameters:

• Innodb_log_file_size specifying the log file size
• Innodb_log_files_in_group Specify the number of redo log files under each filegroup
• INNODB_MIRRORED_LOG_GROUPS Specifies the number of log mirror filegroups, default is 1, no mirror
• Innodb_log_group_home_dir the path of the log file group

Tips: Redo log files are too large to recover data may take a long time, too small, will frequently switch redo logs, resulting in frequent async checkpoint;

Binary log files vs. redo log files:

• Binary log files record MySQL database related logs, including all storage engines; redo log file records InnoDB Storage engine transaction log
• Binary log files are more Binlog_format different, record logical SQL statements (STATEMENT), specific updates (ROW), mixed use according to scene (MIXED), redo log files record the physical changes of each page;
• The binary log is continuously written to the binary log buffer, and the transaction is submitted with a brush disk at a time, and the redo log is continuously written to the redo log file during the transaction.

Checkpoint technology a trigger condition is a transaction commit, and using the Innodb_flush_log_at_trx_commit parameter, you can control when a transaction commits a redo log write disk;

Innodb_flush_log_at_trx_commit:

• 0: The redo log is not forced to be written to the redo log file when the transaction commits
• 1: The redo log buffers are synchronized to disk when the transaction commits, and Fsync (synchronizing the file system cache)
• 2: The redo log is asynchronously brushed back to disk (written to the file system cache) when the transaction commits

Postscript

Follow-up will introduce

• Table
• Index and Algorithm B +
• Lock
• Transaction

A tentative study of MySQL default engine InnoDB (iii)