mariadb系统变量之（二）性能优化

1. 最大连接数配置：max_connections

• Description: The maximum number of simultaneous client connections. See also Handling Too Many Connections. Note that this value affects the number of file descriptors required on the operating system. Minimum was changed from 1 to 10 to avoid possible unexpected results for the user (MDEV-18252).
• Commandline: --max-connections=#
• Scope: Global
• Dynamic: Yes
• Data Type: numeric
• Default Value: 151
• Range:
  • 10 to 100000 (>= MariaDB 10.3.6, MariaDB 10.2.15, MariaDB 10.1.33)
  • 1 to 100000 (<= MariaDB 10.3.5, MariaDB 10.2.14, MariaDB 10.1.32)

2. 缓存相关的配置：

innodb_buffer_pool_size：缓存大小配置

• Description: InnoDB buffer pool size in bytes. The primary value to adjust on a database server with entirely/primarily XtraDB/InnoDB tables, can be set up to 80% of the total memory in these environments. If set to 2 GB or more, you will probably want to adjust innodb_buffer_pool_instances as well. See the XtraDB/InnoDB Buffer Pool for more on setting this variable, and also Setting Innodb Buffer Pool Size Dynamically if doing so dynamically.
• Commandline: --innodb-buffer-pool-size=#
• Scope: Global
• Dynamic: Yes (>= MariaDB 10.2.2), No (<= MariaDB 10.2.1)
• Data Type: numeric
• Default Value: 134217728 (128MB)
• Range: 5242880 (5MB) to 9223372036854775807 (8192PB)

innodb_buffer_pool_instances：缓存实例个数

• Description: If innodb_buffer_pool_size is set to more than 1GB, innodb_buffer_pool_instances divides the InnoDB buffer pool into this many instances. The default was 1 in MariaDB 5.5, but for large systems with buffer pools of many gigabytes, many instances can help reduce contention concurrency. The default is 8 in MariaDB 10 (except on Windows 32-bit, where it varies according to innodb_buffer_pool_size, or from MariaDB 10.2.2, where it is set to 1 if innodb_buffer_pool_size < 1GB). Each instance manages its own data structures and takes an equal portion of the total buffer pool size, so for example if innodb_buffer_pool_size is 4GB and innodb_buffer_pool_instances is set to 4, each instance will be 1GB. Each instance should ideally be at least 1GB in size.
• Commandline: --innodb-buffer-pool-instances=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: <= MariaDB 10.0.3: 1
• Default Value: >= MariaDB 10.0.4: 8, 1 (>= MariaDB 10.2.2 if innodb_buffer_pool_size < 1GB), or dependent on innodb_buffer_pool_size (Windows 32-bit)
• Introduced: MariaDB 5.5.20

3. IO相关的配置

innodb_io_capacity：用来当刷新脏数据时，控制MySQL每秒执行的写IO量。

• Description: Limit on I/O activity for XtraDB/InnoDB background tasks, including merging data from the insert buffer and flushing pages. Should be set to around the number of I/O operations per second that system can handle, based on the type of drive/s being used. You can also set it higher when the server starts to help with the extra workload at that time, and then reduce for normal use. Ideally, opt for a lower setting, as at higher value data is removed from the buffers too quickly, reducing the effectiveness of caching. See also innodb_flush_sync.
  • See InnoDB Page Flushing: Configuring the InnoDB I/O Capacity for more information.
• Commandline: --innodb-io-capacity=#
• Scope: Global
• Dynamic: Yes
• Data Type: numeric
• Default Value: 200
• Range: 100 to 18446744073709551615 (264-1)

Reference:

1. https://mariadb.com/kb/en/library/innodb-system-variables

2. https://mariadb.com/kb/en/library/server-system-variables/

3. MySQL性能调优 – 你必须了解的15个重要变量