Home > Article > System Tutorial > MySQL optimization parameters reference!
MySQL optimization parameters reference!
- PHPzOriginal
- 2024-07-01 07:51:19971browse
When it comes to tuning in daily MySQL operation and maintenance, the MySQL configuration file my.cnf cannot be ignored. The default parameters of MySQL cannot meet the needs of our daily online business, so optimizing parameters is also an indispensable link. I don’t want to list how many items there are in the my.cnf configuration and the meaning of each item. These can be found in the official documentation. The following only describes some parameters that should be paid attention to in daily work.
Some parameters are explained below. There are of course other settings that may come into play, depending on your load or hardware: you will need special tuning in situations with slow memory and fast disk, high concurrency and write-intensive workloads. However, the goal here is so that you can quickly get a robust MySQL configuration without spending too much time adjusting some insignificant MySQL settings or reading the documentation to find out which settings are important to you.
Starting from MySQL version 5.5, InnoDB is the default storage engine and it is used much more than any other storage engine. That's why it needs to be configured carefully.
Table data and indexes are stored in shared table spaces or separate table spaces. Our work scenario installation sets innodb_file_per_table = ON by default, which also facilitates the migration of separate table spaces during work. In MySQL 5.6, the default value of this property is ON.
The default value is 1, which means InnoDB fully supports ACID features. This value is most appropriate when your main concern is data security, such as on a master node. But for systems with slow disk (read and write) speeds, it will bring a huge overhead, because each time flushing changes to the redo log requires additional fsyncs.
Setting its value to 2 will result in unreliable behavior. Because committed transactions are only flushed to the redo log once per second, it is acceptable for some scenarios, such as the backup node of the primary node. This value is acceptable. A value of 0 is faster, but may result in some data loss in the event of a system crash: only applies to backup nodes. When talking about this parameter, another sync_binlog will definitely come to mind.
This configuration determines how data and logs are written to the hard disk. There are three methods in total. We use O_DIRECT by default. O_DIRECT mode: The writing operation of the data file is directly from the mysql innodb buffer to the disk, without going through the operating system buffer. The real completion is in the flush step, and the log still has to go through the OS buffer.
This configuration determines the cache allocated for transactions that have not yet been executed. The default value (1MB) is generally sufficient, but if your transaction contains large binary objects or large text fields, this cache will quickly fill up and trigger additional I/O operations. Look at the Innodb_log_waits status variable, if it is not 0, increase innodb_log_buffer_size.
This parameter should be something that must be paid attention to during operation and maintenance. The buffer pool is where data and indexes are cached. It is a core parameter of MySQL. The default is 128MB. Under normal circumstances, this parameter is set to 60%~70% of the physical memory. (However, our instances are basically mixed deployments of multiple instances, so this value needs to be analyzed based on the business scale.)
This is the size of the redo log. Redo logging is used to ensure that write operations are fast and reliable and can be recovered from crashes. If you know that your application needs to write data frequently and you are using MySQL 5.6, then you can set it to 4G from the beginning. (The specific size needs to be adjusted appropriately according to your own business)
innodb_support_xa can switch InnoDB's XA two-stage transaction submission. By default, innodb_support_xa=true supports XA two-stage transaction submission. Since XA two-stage transaction submission results in redundant flush and other operations, the performance impact will reach 10%. Therefore, in order to improve performance, some DBAs will set innodb_support_xa=false. In this case, redolog and binlog will not be synchronized, and there may be situations where transactions are submitted in the main database but are not recorded in the binlog. This may also cause the loss of transaction data.
This parameter is used to store data field information and other internal data structures. The more tables there are, the more memory needs to be allocated here. If InnoDB runs out of memory in this pool, InnoDB starts to allocate memory from the operating system and writes warning messages to the MySQL error log. The default is 8MB. The general setting is 16MB.
The default number of connections in the MySQL server is relatively small, usually around 100. It is best to set the maximum value larger. Generally, if you set it to 500~1000, each link will occupy a certain amount of memory, so the larger the parameter, the better. Some people will increase the size of this parameter when encountering too many connections. However, in fact, if there is a problem with the business volume or program logic or the SQL is not written well, even increasing this parameter will not help. It is only a matter of time before an error is reported again. Using connection pooling in your application or process pooling in MySQL can help solve this problem.
- Seesion-level memory allocation
max_threads(当前活跃连接数)* ( read_buffer_size– 顺序读缓冲，提高顺序读效率 +read_rnd_buffer_size– 随机读缓冲，提高随机读效率 +sort_buffer_size– 排序缓冲，提高排序效率 +join_buffer_size– 表连接缓冲，提高表连接效率 +binlog_cache_size– 二进制日志缓冲，提高二进制日志写入效率ß +tmp_table_size– 内存临时表，提高临时表存储效率 +thread_stack– 线程堆栈，暂时寄存SQL语句/存储过程 +thread_cache_size– 线程缓存，降低多次反复打开线程开销 +net_buffer_length– 线程持连接缓冲以及读取结果缓冲 +bulk_insert_buffer_size– MyISAM表批量写入数据缓冲 )
- Global level memory allocation
global buffer(全局内存分配总和) = innodb_buffer_pool_size — InnoDB高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等 + innodb_additional_mem_pool_size — InnoDB数据字典额外内存，缓存所有表数据字典 +innodb_log_buffer_size — InnoDB REDO日志缓冲，提高REDO日志写入效率 +key_buffer_size — MyISAM表索引高速缓冲，提高MyISAM表索引读写效率 +query_cache_size –查询高速缓存，缓存查询结果，提高反复查询返回效率+table_cahce — 表空间文件描述符缓存，提高数据表打开效率 +table_definition_cache –表定义文件描述符缓存，提高数据表打开效率
The ultimate goal of parameter optimization is to allow MySQL to better utilize resources by reasonably controlling memory allocation. Reasonable CPU usage is recommended to reduce Session memory allocation.
Make sure the server-id is different when copying the schema, usually the master ID is smaller than the slave ID.
If you want the database server to act as a backup node for the master node, then turning on the binary log is a must. If you do this, don't forget to set server_id to a unique value. Even with just one server, this (turning on binary logging) is useful if you want to do point-in-time data recovery: restore from your most recent backup (full backup) and apply the changes in the binary log (incremental backup ).
Binary logs will be saved permanently once created. So if you don't want to run out of disk space, you can use PURGE BINARY LOGS to purge old files, or set expire_logs_days to specify how many days after which the logs will be automatically purged. Binary logging is not without overhead, so it is recommended to turn this option off if you do not need it on a replica node that is not the primary node.
When the client connects to the database server, the server performs host name resolution, and when DNS is slow, establishing a connection will also be slow. It is therefore recommended to turn off the skip_name_resolve option when starting the server without doing a DNS lookup. The only limitation is that only IP addresses can be used in GRANT statements later, so care must be taken when adding this setting to an existing system.
The default value of sync_binlog is 0. Like the operating system's mechanism for refreshing other files, MySQL will not synchronize to the disk but relies on the operating system to refresh the binary log.
When sync_binlog =N (N>0), MySQL will use the fdatasync() function to synchronize its written binary log to the disk every time it writes the binary log N times. It is safest when both innodb_flush_log_at_trx_commit and sync_binlog are 1. In the case of mysqld service crash or server host crash, binary log may lose at most one statement or transaction. However, you cannot have your cake and eat it too. Double 1 will lead to frequent IO operations, so this mode is also the slowest way. For our business considerations and when business pressure allows, the default is double 1 configuration.
When the cascade architecture needs to be used in the business, the parameter log_slave_update = 1 must be turned on, otherwise the third level may not be able to receive the binlog generated by the first level, and thus data synchronization cannot be performed.
If the memory temporary table exceeds the limit, MySQL will automatically convert it into a disk-based MyISAM table and store it in the specified tmpdir directory. Therefore, configure tmpdir on a storage device with good performance and speed as much as possible.
slow_query_log = 1 #Open slow log
slow_query_log_file = /mysql/log/mysql.slow
long_query_time = 0.5 #Set how many seconds a query will be entered into the slow log
With the development of science and technology, more and more storage devices are beginning to shift from traditional mechanical components to permanent storage composed of electronic components, and the prices are becoming more and more acceptable to enterprises. After the speed of storage components is improved, it seems wasteful to use traditional mechanical component DB configuration. Therefore, MySQL configuration needs to be adjusted according to different storage technologies. For example, innodb_io_capacity needs to be increased, log files and redo should be placed on the mechanical hard disk, and undo should be placed on the mechanical hard disk. To SSD, atomic write does not require Double Write Buffer, InnoDB compression, single machine multiple instances + cgroup, etc. Analyze the I/O situation and dynamically adjust innodb_io_capacity and innodb_max_dirty_pages_pct; try to adjust innodb_adaptive_flushing to see the effect.
We have not yet done any tuning for innodb_write_io_threads and innodb_read_io_threads, but I believe that by adjusting to 8 or 16, the system I/O performance will be better. Also, you need to pay attention to the following points: any adjustment must be based on data support and rigorous analysis, otherwise it will be empty talk; this type of tuning is very meaningful and can really bring value. So we need to work harder and understand as much as possible why we need to make such adjustments.
- Innodb_thread_concurrency=0
- Innodb_sync_spin_loops=288
- table_definition_cache=2000
- Innodb_flush_method It is recommended to use O_DIRECT
- Innodb_io_capacity is set to the maximum IOPS supported by the disk
- Innodb_wirte_io_threads=8
- Innodb_read_io_threads=8
- Innodb_purge_threads=1
- In terms of Innodb’s pre-reading, if it is based on a main or unique index system, it is recommended to disable pre-reading
- Innodb_random_read_ahead = off
The above is the detailed content of MySQL optimization parameters reference!. For more information, please follow other related articles on the PHP Chinese website!