|
|
1.配置文件化
有些InnoDB的配置，是在实例初始化时，就决定了，所以，建议写在configuration file 文件中。
Because MySQL uses data file, log file, and page size settings to initialize InnoDB, it is recommended that you define these settings in an option file that MySQL reads at startup, prior to initializing InnoDB. Normally, InnoDB is initialized when the MySQL server is started for the first time.
一般都放在[mysqld]属性组里面
You can place InnoDB options in the [mysqld] group of any option file that your server reads when it starts.
2.一些关于【存储】相关的重要参数
Review the following storage-related considerations before proceeding with your startup configuration.
• In some cases, you can improve database performance by placing data and log files on separate physical disks. You can also use raw disk partitions (raw devices) for InnoDB data files, which may speed up I/O.
• InnoDB is a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash-recovery capabilities to protect user data. However, it cannot do so if the underlying【下层的，作为基础的】 operating system or hardware does not work as advertised【广告上的；宣传的】. Many operating systems or disk subsystems may delay or reorder【重新排列；重新布置】 write operations to improve performance. On some operating systems, the very fsync() system call that should wait until all unwritten data for a file has been flushed might actually return before the data has been flushed to stable storage. Because of this, an operating system crash or a power outage may destroy recently committed data, or in the worst case, even corrupt the database because write operations have been reordered. If data integrity is important to you, perform “pull-the-plug” tests before using anything in production. On macOS, InnoDB uses a special fcntl() file flush method. Under Linux, it is advisable to disable the write-back cache.
On ATA/SATA disk drives, a command such hdparm -W0 /dev/hda may work to disable the writeback cache. Beware that some drives or disk controllers may be unable to disable the write-back cache.
• With regard to InnoDB recovery capabilities that protect user data, InnoDB uses a file flush technique involving a structure called the doublewrite buffer, which is enabled by default (innodb_doublewrite=ON). The doublewrite buffer adds safety to recovery following an unexpected exit or power outage, and improves performance on most varieties of Unix by reducing the need for fsync() operations. It is recommended that the innodb_doublewrite option remains enabled if you are concerned with data integrity or possible failures.--- 强调innodb_doublewrite重要性
3.系统表空间配置
The innodb_data_file_path option defines the name, size, and attributes of InnoDB system tablespace data files. If you do not configure this option prior to initializing the MySQL server, the default behavior is to create a single auto-extending data file, slightly larger than 12MB, named ibdata1:ibdata1:12M:autoextend。
You can specify more than one data file using a semicolon-separated【用分号隔开】 list.例如：innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
The autoextend and max attributes can be used only for the data file that is specified last.--可以设置最大值。To specify a maximum size for an auto-extending data file, use the max attribute following the autoextend attribute. Use the max attribute only in cases where constraining disk usage is of critical importance. The following configuration permits ibdata1 to grow to a limit of 500MB:innodb_data_file_path=ibdata1:12M:autoextend:max:500M
When the autoextend attribute is specified, the data file automatically increases in size by 64MB increments as space is required. The innodb_autoextend_increment variable controls the increment size。--可以设置增长步伐值。
4.Doublewrite Buffer 相关配置
As of MySQL 8.0.20, the doublewrite buffer storage area resides in doublewrite files, which provides flexibility with respect to the storage location of doublewrite pages. In previous releases, the doublewrite buffer storage area resided in the system tablespace. The innodb_doublewrite_dir variable defines the directory where InnoDB creates doublewrite files at startup. If no directory is specified, doublewrite files are created in the innodb_data_home_dir directory, which defaults to the data directory if unspecified.
在8.0.20，doublewrite buffer没有独立的文件，是放在了系统表中间文件中了；8.0.20之后，就独立出来了。
怎么识别出文件呢？看就需要知道它的命名规则了
Doublewrite file names have the following format: #ib_page_size_file_number.dblwr (or .bdblwr with the DETECT_ONLY setting). For example, the following doublewrite files are created for a MySQL instance with an InnoDB pages size of 16KB and a single buffer pool:- #ib_16384_0.dblwr
- #ib_16384_1.dblwr
From MySQL 8.0.30, the amount of disk space occupied by redo log files is controlled by the innodb_redo_log_capacity variable, which can be set at startup or runtime; for example, to set the variable to 8GB in an option file, add the following entry:- [mysqld]
- innodb_redo_log_capacity = 8589934592
From MySQL 8.0.30, InnoDB attempts to maintain 32 redo log files, with each file equal to 1/32 * innodb_redo_log_capacity.
Before MySQL 8.0.30, InnoDB creates two 5MB redo log files named ib_logfile0 and ib_logfile1 in the data directory by default. You can define a different number of redo log files and different redo log file size when initializing the MySQL Server instance by configuring the innodb_log_files_in_group and innodb_log_file_size variables.--老版本relog文件的个数和大小设置
• innodb_log_files_in_group defines the number of log files in the log group. The default and recommended value is 2.
• innodb_log_file_size defines the size in bytes of each log file in the log group. The combined log file size (innodb_log_file_size * innodb_log_files_in_group) cannot exceed the maximum value, which is slightly less than 512GB. A pair of 255 GB log files, for example, approaches the limit but does not exceed it. The default log file size is 48MB. Generally, the combined size of the log files should be large enough that the server can smooth out peaks and troughs in workload activity, which often means that there is enough redo log space to handle more than an hour of write activity. A larger log file size means less checkpoint flush activity in the buffer pool, which reduces disk I/O.
6.Undo表空间的相关设置
Undo logs, by default, reside in two undo tablespaces created when the MySQL instance is initialized.【默认2个】
The innodb_undo_directory variable defines the path where InnoDB creates default undo tablespaces. If that variable is undefined, default undo tablespaces are created in the data directory. The innodb_undo_directory variable is not dynamic. Configuring it requires restarting the server.【所在位置】
The I/O patterns for undo logs make undo tablespaces good candidates for SSD storage.【建议放置在SSD中】
7.Global Temporary Tablespace 设置
The global temporary tablespace stores rollback segments for changes made to user-created temporary tables.--用途
A single auto-extending global temporary tablespace data file named ibtmp1 in the innodb_data_home_dir directory by default. The initial file size is slightly larger than 12MB.--命名和默认大小
8.Session Temporary Tablespace设置
In MySQL 8.0.15 and earlier, session temporary tablespaces store user-created temporary tables and internal temporary tables created by the optimizer when InnoDB is configured as the on-disk storage engine for internal temporary tables (internal_tmp_disk_storage_engine=InnoDB). From MySQL 8.0.16, InnoDB is always used as the on-disk storage engine for internal temporary tables.--随着版本的变化，也独立了出来
9.Page Size 设置
The innodb_page_size option specifies the page size for all InnoDB tablespaces in a MySQL instance. This value is set when the instance is created and remains constant afterward. Valid values are 64KB, 32KB, 16KB (the default), 8KB, and 4KB. Alternatively, you can specify page size in bytes (65536, 32768, 16384, 8192, 4096).---关键变量参数 和 可设置的大小
The default 16KB page size is appropriate for a wide range of workloads, particularly for queries involving table scans and DML operations involving bulk updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when a single page contains many rows. Smaller pages can also be more efficient for SSD storage devices, which typically use small block sizes. Keeping the InnoDB page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.---默认为16KB
10.Memory 设置
MySQL allocates memory to various caches and buffers to improve performance of database operations. When allocating memory for InnoDB, always consider memory required by the operating system, memory allocated to other applications, and memory allocated for other MySQL buffers and caches. For example, if you use MyISAM tables, consider the amount of memory allocated for the key buffer (key_buffer_size).
Buffers specific to InnoDB are configured using the following parameters:---主要设置参数
• innodb_buffer_pool_size defines size of the buffer pool, which is the memory area that holds cached data for InnoDB tables, indexes, and other auxiliary buffers. The size of the buffer pool is important for system performance, and it is typically recommended that innodb_buffer_pool_size is configured to 50 to 75 percent of system memory. The default buffer pool size is 128MB.----一定要修改，不能使用默认值。
Buffer pool size can be configured at startup or dynamically.
• On systems with a large amount of memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances. The number of buffer pool instances is controlled by the by innodb_buffer_pool_instances option. By default, InnoDB creates one buffer pool instance.
The number of buffer pool instances can be configured at startup.
• innodb_log_buffer_size defines the size of the buffer that InnoDB uses to write to the log files on disk. The default size is 16MB. A large log buffer enables large transactions to run without writing the log to disk before the transactions commit. If you have transactions that update, insert, or delete many rows, you might consider increasing the size of the log buffer to save disk I/O.
innodb_log_buffer_size can be configured at startup.
11 内存使用（分配）估算的公式
A formula similar to the following that calculates global and per-thread memory allocation for MySQL can be used to estimate MySQL memory usage. You may need to modify the formula to account for buffers and caches in your MySQL version and configuration.- innodb_buffer_pool_size
- + key_buffer_size
- + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
- + max_connections*2MB
---------《15.8.1 InnoDB Startup Configuration》
https://dev.mysql.com/doc/refman/8.0/en/innodb-init-startup-configuration.html
来源:https://www.cnblogs.com/xuliuzai/p/18091743
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！ |
|
