Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand and master mysql parameter files and types". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand and master mysql parameter files and types".

1. Chapter 4: database files

Database level files: parameter file my.cnf, error log error log, slow query log slow log, full log general log, binary log binlog, audit log audit log, relay log relay log, socket file socket, process file pid, table structure file

Storage engine level: redo log,undo log log files.

1.1. Parameter file

Parameter file priority: / etc/my.cnf > / etc/mysql/my.cnf > / usr/local/mysql/my.cnf > / .my.cnf

To set the default parameter file, you need to set-- defaults-file parameter

In the my.cnf file, it is divided into client section and server section.

Client section is used to configure mysql client parameters.

For more information on server parameters, please see: show variables like'% Parameter%'.

Innodb_buffer_pool: database available memory, physical memory 50% Mur80% position 5.7 can be modified online

Innodb_buffer_pool_instance: multiple InnoDB buffers can be adjusted after 5.6.6 by default, which means that the buffer can be divided into multiple regions to improve concurrency. Each buffer is self-managed, and you can see how each instance uses memory through show engine innodb status. Multiple instances take effect only if the innodb_buffer_pool is greater than 1G.

Quickly load metadata information in memory

When the instance goes down, the data in memory disappears. Re-reading will increase the pressure on IO. The following parameters can be solved: innodb_buffer_pool_load_at_staratup and innodb_buffer_pool_dump_at_shutdown.

These two parameters are set to enabled (5.7 default enabled). When the instance is closed, the metadata information of the hot data can be dump out and saved to the ib_buffer_pool file. View the ib_buffer_pool file name: show variables like'% innodb_buffer_poll_file%'.

When the instance is started, the metadata is quickly loaded into memory. In fact, the metadata is the list information of space number and page number. The query statement is as follows:

Switch database: use infomation_schema

Select space,page_number from INNODB_BUFFER_PAGE limit3

Innodb_data_file_path: system tablespace file path and ibdata1 file size can be specified. Default is 10m. 1G is recommended.

Innodb_flash_log_at_trx_commit, sync_binlog, innodb_max_dirty_pages_pct: these three parameters affect the refresh parameters of redo log, binlog, and dirty pages.

Interactive_timeout: the amount of time that the server waits for activity before closing the cross connection. Default is 8 hours (28800s)

Wait_timeout: the time the server waits for activity before shutting down non-crossover connections. Default is 8 hours (28800s)

These two parameters should be set to the same value together. To avoid excessive connection time, it is recommended to adjust it to 300-600 seconds; 5.7 default is 300s.

The maximum number of concurrent threads in the innodb_thread_concurrency:innodb kernel. Default is 0, which means there is no limit.

Innodb_flash_method: affects the innodb data file and redo log file opening and brushing mode. It is recommended to select the default value of 0_DIRECT. Data files are written directly to disk from mysql innodb buffer without going through os buffer.

Innodb_old_blocks_time: determines how long the data page in young pages list will be transferred to old pages list.

Innodb_old_blocks_pct: determines the percentage of old pages list in the entire list. Case: if you use mysqldump operation in large table scanning, it is possible to kick out the hot data and bring IO pressure to the database, which can appropriately reduce the value allocation of innodb_old_blocks_pct to ensure that more thermal data will not be washed away.

Transaction_isolation: isolation level of the transaction, default REPEATABLE-READ, and other READ-UNCOMMITTED, READ-COMMITTED,SERIALIZABLE

Innodb_open_files: you can open .ibd at the same time to ask the number of your home. The minimum number is 10. The default is 300. 65535 is recommended.

Innodb_log_buffer_size: log buffer size, data changes are recorded into the buffer, such as innodb_log_waits (the number of times to wait for the log buffer to be flushed out) is greater than 0, and continues to grow, it is necessary to increase the log buffer, range 16M-64M.

Innodb_log_file_size: size of the redo log log.

The amount of data of log files in the innodb_log_files_in_group:redo log filegroup, at least 2 by default

Max_connections: maximum number of database connections. Default is 151.The number of internal concurrency of active tone can be adjusted.

The number of concurrency within innodb_thread_concurrency:innodb. By default, 0 is not limited. According to the system pressure, it can be changed to twice the CPU. Reuse connections by setting thread_pool.

There is monitoring to read the following table of infomation_schma, and you can close innodb_stats_on_metadata. Method: set global innodb_stats_on_metadata=0

Expire_logs_days: represents the expiration time of the binlog, in days.

Slow_query_log: slow log switch. 1 means to enable, and the production environment should be enabled.

Long_query_time: logs that exceed the time limit are recorded.

Log_queries_not_using_indexes: if sql does not have an index, it will be recorded in the slow query log, and the production environment will be enabled. Set global log_queries_not_using_indexes = on

Server-id: a unique identifier that represents the same set of master-slave structures.

Binlog_format: binary log format. Production environment is secure with row. There is no data loss caused by cross-database replication.

Lower_case_table_names: 0 is case-sensitive by default, 1 is not case-sensitive, and is stored in lowercase.

Innodb_fast_shutdown: affects the behavior when innodb is turned off. 0 is the default and the safest and slowest, and 3 is the fastest.

When 0:InnoDB is closed, you need to execute purge all,merge change buffer,flush dirty pages

Do not execute purge all,merge insert buffer or flush dirty page when 1:InnoDB is closed

When 2:InnoDB is closed: do not execute full purge,merge insert buffer, refresh dirty pages to disk, and only write redobuffer to the log.

Innodb_force_reecovery: affects the recovery behavior of the InnoDB storage engine. Value range: 0-6

0: perform all restore operations when a restore is needed. Default is 0

1: ignore checked corrupt pages

2: prevent the main thread from running. If the main thread wants to execute full purge, it will cause crash.

3: no transaction rollback operation is performed

4: merge operation of insert buffer is not performed

5: if you do not view the revocation log, the uncommitted transactions met by InnoDB will be regarded as committed.

6: no roll forward operation

Innodb_status_output,innodb_status_output_locks: it is recommended to disable it, that is, set it to 0, otherwise all the information monitored by the database will be recorded in error.log, and the log will grow too fast.

Innodb_io_capcity: affects the number of dirty pages refreshed and the number of insert buffers.

Auot_increment_increment: the value of each increment of the self-increasing field. Default is 1.

Auto_increment_offset: the value begins with the increment field.

1.2. Parameter type

Parameters are divided into dynamic parameters and static parameters.

Dynamic parameters:

Set global: represents the global parameter. The modification and exit session is still valid, but it is invalid after restart.

Set session: valid only for the current session and expires as soon as you exit.

Static parameters: cannot be modified online, the modification will report a read only variable error. You can only modify the configuration file and restart the database to take effect.

Thank you for your reading, the above is the content of "how to understand and master mysql parameter files and types". After the study of this article, I believe you have a deeper understanding of how to understand and master mysql parameter files and types. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.