当innodb_dedicated_server=1时，mysql实例会认为所处服务器为mysql专有服务器，mysql实例会根据服务器可用系统资源自动调整innodb_buffer_pool_size、innodb_redo_log_capacity、innodb_flush_method参数配置。
前言
今天技术群里有人问：“是否有人在MySQL 8.0启用了innodb_dedicated_server参数？”。
先来说说innodb_dedicated_server参数是什么？是否配置mysql实例为当前服务器专有服务器。
配置当前服务器为mysql专用服务器，可配置参数：
- OFF：缺省配置
- ON：当服务器仅只有mysql服务时配置为ON，InnoDB会自动配置innodb_buffer_pool_size、innodb_redo_log_capacity、innodb_flush_method参数。
PS：只有当mysql实例可使用所在服务器所有可用资源池时参会推荐启用innodb_dedicated_server参数，如果mysql实例与其它服务共享服务器，则不建议启用innodb_dedicated_server参数。
测试
环境背景
- MySQL 8.0（我环境上只有mysql实例）
- MEM：2GB
- CPU：4
- SWAP：2GB
innodb_dedicated_server=0
查看参数配置
mysql> select @@global.innodb_buffer_pool_size;
+----------------------------------+
| @@global.innodb_buffer_pool_size |
+----------------------------------+
| 134217728 |
+----------------------------------+
mysql> select @@global.innodb_redo_log_capacity;
+-----------------------------------+
| @@global.innodb_redo_log_capacity |
+-----------------------------------+
| 104857600 |
+-----------------------------------+
mysql> select @@global.innodb_flush_method;
+------------------------------+
| @@global.innodb_flush_method |
+------------------------------+
| fsync |
+------------------------------+
prepare阶段：
[root]# sysbench --db-driver=mysql \
--mysql-host=192.168.0.81 \
--mysql-port=3306 \
--mysql-user=sysbench \
--mysql-password=Your_password \
--mysql-db=sysbench \
--table_size=100000 \
--tables=5 \
/data/sysbench/src/lua/oltp_read_write.lua prepare
run阶段：
[root]# sysbench --db-driver=mysql \
--mysql-host=192.168.0.81 \
--mysql-port=3306 \
--mysql-user=sysbench \
--mysql-password=Your_password \
--mysql-db=sysbench \
--tables=5 \
--time=600 \
--threads=8 \
--report-interval=5 \
/data/sysbench/src/lua/oltp_read_write.lua run
输出：
SQL statistics:
queries performed:
read: 3740422
write: 1068678
other: 534341
total: 5343441
transactions: 267168 (445.26 per sec.)
queries: 5343441 (8905.28 per sec.)
ignored errors: 5 (0.01 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0296s
total number of events: 267168
Latency (ms):
min: 5.43
avg: 17.96
max: 569.74
95th percentile: 33.72
sum: 4798662.26
Threads fairness:
events (avg/stddev): 33396.0000/144.59
execution time (avg/stddev): 599.8328/0.02
cleanup阶段：
[root]# sysbench --db-driver=mysql \
--mysql-host=192.168.0.81 \
--mysql-port=3306 \
--mysql-user=sysbench \
--mysql-password=Your_password \
--mysql-db=sysbench \
--tables=5 \
/data/sysbench/src/lua/oltp_read_write.lua cleanup
innodb_dedicated_server=1
启用innodb_dedicated_server参数
[root]# vi /etc/mysql/my.cnf
[mysqld]
innodb_dedicated_server=1
重启mysql服务
[root]# /etc/init.d/mysql.service restart
查看参数配置
mysql> select @@global.innodb_buffer_pool_size;
+----------------------------------+
| @@global.innodb_buffer_pool_size |
+----------------------------------+
| 1073741824 |
+----------------------------------+
mysql> select @@global.innodb_redo_log_capacity;
+-----------------------------------+
| @@global.innodb_redo_log_capacity |
+-----------------------------------+
| 536870912 |
+-----------------------------------+
mysql> select @@global.innodb_flush_method;
+------------------------------+
| @@global.innodb_flush_method |
+------------------------------+
| O_DIRECT_NO_FSYNC |
+------------------------------+
prepare阶段：
[root]# sysbench --db-driver=mysql \
--mysql-host=192.168.0.81 \
--mysql-port=3306 \
--mysql-user=sysbench \
--mysql-password=Your_password \
--mysql-db=sysbench \
--table_size=100000 \
--tables=5 \
/data/sysbench/src/lua/oltp_read_write.lua prepare
run阶段：
[root]# sysbench --db-driver=mysql \
--mysql-host=192.168.0.81 \
--mysql-port=3306 \
--mysql-user=sysbench \
--mysql-password=Your_password \
--mysql-db=sysbench \
--tables=5 \
--time=600 \
--threads=8 \
--report-interval=5 \
/data/sysbench/src/lua/oltp_read_write.lua run
输出：
SQL statistics:
queries performed:
read: 4107026
write: 1173434
other: 586717
total: 5867177
transactions: 293358 (488.88 per sec.)
queries: 5867177 (9777.60 per sec.)
ignored errors: 1 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0618s
total number of events: 293358
Latency (ms):
min: 5.42
avg: 16.36
max: 252.89
95th percentile: 29.19
sum: 4798519.29
Threads fairness:
events (avg/stddev): 36669.7500/139.43
execution time (avg/stddev): 599.8149/0.02
cleanup阶段：
[root]# sysbench --db-driver=mysql \
--mysql-host=192.168.0.81 \
--mysql-port=3306 \
--mysql-user=sysbench \
--mysql-password=Your_password \
--mysql-db=sysbench \
--tables=5 \
/data/sysbench/src/lua/oltp_read_write.lua cleanup
总结
配置innodb_dedicated_server前后受影响的参数变化
| VARIABLE_NAME | innodb_dedicated_server=0 | innodb_dedicated_server=1 |
|---|---|---|
| innodb_buffer_pool_size | 128MB | 1GB |
| innodb_redo_log_capacity | 100MB | 500MB |
| innodb_flush_method | fsync | O_DIRECT_NO_FSYNC |
配置innodb_dedicated_server前后压测结果对比
| TYPE | innodb_dedicated_server=0 | innodb_dedicated_server=1 | ↑ % |
|---|---|---|---|
| TPS | 445 | 488 | 9.7% |
| QPS | 8905 | 9777 | 9.8% |
仅当数据库服务器有且只有1个mysql实例时可以配置innodb_dedicated_server=1，mysql实例会根据服务器当前可用资源情况自动调节innodb_buffer_pool_size、innodb_redo_log_capacity、innodb_flush_method，一般而言会有相应的性能提升。而当mysql与其它服务共享一台服务器时则不建议开启此参数。
参考：
