【基于鲲鹏及openEuler20.03TLS下MySQL8.0.17性能调优】
【基于鲲鹏及openEuler20.03TLS下MySQL8.0.17性能调优】
- 一、环境说明
- 二、实验过程
- 三、实验小结
一、环境说明
- 华为云ECS 规格：8vCPU 32G arm架构
- 操作系统：openEuler 20.03.TLS
- MySQL版本：8.0.17
二、实验过程
-
创建用户及用户组：
groupadd mysql useradd -g mysql mysql passwd mysql创建数据目录：
mkdir -p /data/mysql cd /data/mysql mkdir data tmp run log relaylogchown -R mysql:mysql /data ll / -
安装依赖
修改yum源：sed -i "s/gpgcheck=1/gpgcheck=0/g" /etc/yum.repos.d/openEuler.repo yum -y install bison ncurses ncurses-devel libaio-devel openssl openssl-devel gmp gmp-devel mpfr mpfr-devel libmpc libmpc-devel wget tar gcc gcc-c++ git rpcgen cmake libtirpc-devel openldap-devel openldap net-tools java --skip-broken --allowerasing yum install java -y --nobest或者
yum -y install bison ncurses ncurses-devel libaio-devel openssl openssl-devel gmp gmp-devel mpfr mpfr-devel libmpc libmpc-devel wget tar gcc gcc-c++ git rpcgen cmake libtirpc libtirpc-devel openldap-devel openldap net-tools doxygen java --skip-broken --allowerasing --nobest以下是openEuler操作系统需要做的：
vim /etc/yum.repos.d/openEuler.repo,修改的地方如下所示：[update] name=update baseurl=https://repo.huaweicloud.com/openeuler/openEuler-22.03-LTS/update/$basearch/ enabled=1 gpgcheck=0 gpgkey=https://repo.huaweicloud.com/openeuler/openEuler-22.03-LTS/OS/$basearch/RPM-GPG-KEY-openEuleryum源生效：
yum clean all yum makecacheyum list libtirpc yum update -y libtirpc yum list libtirpc-devel yum install -y libtirpc-devel.aarch64 -
升级cmake
- 下载
cd /home wget https://cmake.org/files/v3.5/cmake-3.5.2.tar.gz --no-check-certificate - 解压
tar -zxvf cmake-3.5.2.tar.gz cd cmake-3.5.2 ./bootstrap - 查看cpu核心数
cat /proc/cpuinfo | grep processor | wc -l - 编译及安装：
-j后面的16为cpu核心数的整数倍，这里是2倍make -j 16 make install
3.5 确认CMake的版本是否为3.5.2。
hash -r /usr/local/bin/cmake --version - 下载
-
确认GCC的版本是否为7.3.0 默认是7.3.0 （
openEuler操作系统 默认就是7.3.0不需要升级）这里贴出的是centos7.6版本需要手动升级gcc，如下所示：cd /home wget https://mirrors.tuna.tsinghua.edu.cn/gnu/gcc/gcc-7.3.0/gcc-7.3.0.tar.gz --no-check-certificate tar -xvf gcc-7.3.0.tar.gz cd /home/gcc-7.3.0 ./configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-bootstrap make -j 16 make -j 16 install gcc -v -
安装编译MySQL
cd /home wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.0.17.tar.gz --no-check-certificate tar -zxvf mysql-boost-8.0.17.tar.gz cd /home/mysql-8.0.17 mkdir build cd build cmake .. -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql/data -DWITH_BOOST=/home/mysql-8.0.17/boost/boost_1_69_0cmake成功后，进行编译安装：
make -j 16 make -j 16 install之后，查看MySQL版本信息：
ls /usr/local/mysql/ /usr/local/mysql/bin/mysql --version -
运行MySQL
配置可参考：鲲鹏社区官网
https://www.hikunpeng.com/document/detail/zh/kunpengdbs/ecosystemEnable/MySQL/kunpengmysql8017_03_0013.html具体步骤如下：
-
编辑my.cnf文件
rm -f /etc/my.cnf echo -e "[mysqld_safe]\nlog-error=/data/mysql/log/mysql.log\npid-file=/data/mysql/run/mysqld.pid\n[mysqldump]\nquick\n[mysql]\nno-auto-rehash\n[client]\ndefault-character-set=utf8\n[mysqld]\nbasedir=/usr/local/mysql\nsocket=/data/mysql/run/mysql.sock\ntmpdir=/data/mysql/tmp\ndatadir=/data/mysql/data\ndefault_authentication_plugin=mysql_native_password\nport=3306\nuser=mysql\n" > /etc/my.cnf查看/etc/my.cnf，执行
cat /etc/my.cnf命令，内容如下：[mysqld_safe]
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/run/mysqld.pid
[mysqldump]
quick
[mysql]
no-auto-rehash
[client]
default-character-set=utf8
[mysqld]
basedir=/usr/local/mysql
socket=/data/mysql/run/mysql.sock
tmpdir=/data/mysql/tmp
datadir=/data/mysql/data
default_authentication_plugin=mysql_native_password
port=3306
user=mysql -
修改用户组和权限
chown mysql:mysql /etc/my.cnf ll /etc/my.cnf -
MySQL加入service服务:
chmod 777 /usr/local/mysql/support-files/mysql.server cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql chkconfig mysql on -
修改“/etc/init.d/mysql”的用户组和用户权限为mysql:mysql:
chown -R mysql:mysql /etc/init.d/mysql ll /etc/init.d/mysql. -
安装完成后，将MySQL二进制文件的路径添加到环境变量中:
echo export PATH=$PATH:/usr/local/mysql/bin >> /etc/profile -
使环境变量配置生效:
source /etc/profile -
查看环境变量：
env -
修改数据目前权限：
chmod 755 /data/mysql/data/ -
初始化数据库：
mysqld --defaults-file=/etc/my.cnf --initialize会看到如下信息，记住数据库初始密码，下面操作需要：
[mysql@ecs-sjm-mysql ~]$ mysqld --defaults-file=/etc/my.cnf --initialize
2023-08-19T06:02:19.505175Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) initializing of server in progress as process 39906
2023-08-19T06:02:21.093406Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost:Pf<I7a4SOjd<
2023-08-19T06:02:22.164717Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) initializing of server has completed -
初始化完成后，查看数据目录下数据文件“/data/mysql/data”的用户组和用户权限为mysql:mysql（因为前面/etc/my.cnf文件中配置的操作系统用户是user=mysql）：
ll /data/mysql/data -
启动数据库进程：
service mysql start或者：
mysqld --defaults-file=/etc/my.cnf &或者：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf & -
查看数据进程：
ps -ef | grep mysql -
查看数据库端口号：
netstat -anpt netstat -anpt | grep mysql netstat -anpt | grep 3306 -
登录数据库：
mysql -uroot -p -S /data/mysql/run/mysql.sock -
登录数据库以后，修改通过root用户登录数据库的密码：
alter user 'root'@'localhost' identified by "123456";创建全域root用户（允许root从其他服务器访问）：
create user 'root'@'%' identified by '123456';进行授权：
grant all privileges on *.* to 'root'@'%'; flush privileges;退出数据库：exit
用修改后的密码重新登录数据库：
mysql -uroot -p -S /data/mysql/run/mysql.sock
-
-
安装BenchMarkSQL
-
下载安装包
cd /home wget https://mirrors.huaweicloud.com/kunpeng/archive/kunpeng_solution/database/patch/benchmarksql5.0-for-mysql.zip -
修改权限
cd /home/benchmarksql5.0-for-mysql/run chmod +x *.sh -
新建：
vim props.mysql，添加如下内容：db=mysql driver=com.mysql.cj.jdbc.Driver conn=jdbc:mysql://127.0.0.1:3306/tpcc?useSSL=false user=root password=123456 warehouses=50 loadWorkers=10 terminals=50 runTxnsPerTerminal=0 runMins=10 limitTxnsPerMin=1000000000 terminalWarehouseFixed=true newOrderWeight=45 paymentWeight=43 orderStatusWeight=4 deliveryWeight=4 stockLevelWeight=4
-
-
TPC-C测试数据导入
- 执行导入命令：
./home/benchmarksql5.0-for-mysql/run/runDatabaseBuild.sh props.mysql，会看到如下信息打印出来：[root@ecs-sjm-mysql run]# ./runDatabaseBuild.sh props.mysql # ------------------------------------------------------------ # Loading SQL file ./sql.mysql/tableCreates.sql # ------------------------------------------------------------ create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); create table bmsql_warehouse ( w_id integer not null, w_ytd decimal(12,2), w_tax decimal(4,4), w_name varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(20), w_state char(2), w_zip char(9), constraint pk_warehouse primary key (w_id) ); create table bmsql_district ( d_w_id integer not null, d_id integer not null, d_ytd decimal(12,2), d_tax decimal(4,4), d_next_o_id integer, d_name varchar(10), d_street_1 varchar(20), d_street_2 varchar(20), d_city varchar(20), d_state char(2), d_zip char(9), constraint pk_district primary key (d_w_id, d_id) ); create table bmsql_customer ( c_w_id integer not null, c_d_id integer not null, c_id integer not null, c_discount decimal(4,4), c_credit char(2), c_last varchar(16), c_first varchar(16), c_credit_lim decimal(12,2), c_balance decimal(12,2), c_ytd_payment decimal(12,2), c_payment_cnt integer, c_delivery_cnt integer, c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_middle char(2), c_data varchar(500), constraint pk_customer primary key (c_w_id, c_d_id, c_id), key bmsql_customer_idx1 (c_w_id, c_d_id, c_last, c_first) ); -- create sequence bmsql_hist_id_seq; create table bmsql_history ( hist_id integer not null auto_increment primary key, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(6,2), h_data varchar(24) ); create table bmsql_new_order ( no_w_id integer not null, no_d_id integer not null, no_o_id integer not null, constraint pk_new_order primary key (no_w_id, no_d_id, no_o_id) ); create table bmsql_oorder ( o_w_id integer not null, o_d_id integer not null, o_id integer not null, o_c_id integer, o_carrier_id integer, o_ol_cnt integer, o_all_local integer, o_entry_d timestamp, constraint pk_oorder primary key (o_w_id, o_d_id, o_id), constraint bmsql_oorder_idx1 unique key (o_w_id, o_d_id, o_carrier_id, o_id) ); create table bmsql_order_line ( ol_w_id integer not null, ol_d_id integer not null, ol_o_id integer not null, ol_number integer not null, ol_i_id integer not null, ol_delivery_d timestamp, ol_amount decimal(6,2), ol_supply_w_id integer, ol_quantity integer, ol_dist_info char(24), constraint pk_order_line primary key (ol_w_id, ol_d_id, ol_o_id, ol_number) ); create table bmsql_item ( i_id integer not null, i_name varchar(24), i_price decimal(5,2), i_data varchar(50), i_im_id integer, constraint pk_item primary key (i_id) ); create table bmsql_stock ( s_w_id integer not null, s_i_id integer not null, s_quantity integer, s_ytd integer, s_order_cnt integer, s_remote_cnt integer, s_data varchar(50), s_dist_01 char(24), s_dist_02 char(24), s_dist_03 char(24), s_dist_04 char(24), s_dist_05 char(24), s_dist_06 char(24), s_dist_07 char(24), s_dist_08 char(24), s_dist_09 char(24), s_dist_10 char(24), constraint pk_stock primary key (s_w_id, s_i_id) ); Starting BenchmarkSQL LoadDatadriver=com.mysql.cj.jdbc.Driver conn=jdbc:mysql://127.0.0.1:3306/tpcc?useSSL=false user=root password=*********** warehouses=50 loadWorkers=10 fileLocation (not defined) csvNullValue (not defined - using default 'NULL') Worker 000: Loading ITEM Worker 001: Loading Warehouse 1 Worker 002: Loading Warehouse 2 Worker 003: Loading Warehouse 4 Worker 004: Loading Warehouse 3 Worker 005: Loading Warehouse 5 Worker 006: Loading Warehouse 6 Worker 007: Loading Warehouse 7 Worker 008: Loading Warehouse 8 Worker 009: Loading Warehouse 9 Worker 000: Loading ITEM done Worker 000: Loading Warehouse 10 Worker 001: Loading Warehouse 1 done Worker 001: Loading Warehouse 11 Worker 002: Loading Warehouse 2 done Worker 003: Loading Warehouse 4 done Worker 002: Loading Warehouse 12 Worker 003: Loading Warehouse 13 Worker 007: Loading Warehouse 7 done Worker 009: Loading Warehouse 9 done Worker 008: Loading Warehouse 8 done Worker 006: Loading Warehouse 6 done Worker 008: Loading Warehouse 16 Worker 006: Loading Warehouse 17 Worker 004: Loading Warehouse 3 done Worker 004: Loading Warehouse 18 Worker 009: Loading Warehouse 15 Worker 007: Loading Warehouse 14 Worker 005: Loading Warehouse 5 done # ------------------------------------------------------------ # Loading SQL file ./sql.mysql/foreignKeys.sql # ------------------------------------------------------------ alter table bmsql_district add constraint d_warehouse_fkey foreign key (d_w_id) references bmsql_warehouse (w_id); alter table bmsql_customer add constraint c_district_fkey foreign key (c_w_id, c_d_id) references bmsql_district (d_w_id, d_id); alter table bmsql_history add constraint h_customer_fkey foreign key (h_c_w_id, h_c_d_id, h_c_id) references bmsql_customer (c_w_id, c_d_id, c_id); alter table bmsql_history add constraint h_district_fkey foreign key (h_w_id, h_d_id) references bmsql_district (d_w_id, d_id); alter table bmsql_new_order add constraint no_order_fkey foreign key (no_w_id, no_d_id, no_o_id) references bmsql_oorder (o_w_id, o_d_id, o_id); alter table bmsql_oorder add constraint o_customer_fkey foreign key (o_w_id, o_d_id, o_c_id) references bmsql_customer (c_w_id, c_d_id, c_id); alter table bmsql_order_line add constraint ol_order_fkey foreign key (ol_w_id, ol_d_id, ol_o_id) references bmsql_oorder (o_w_id, o_d_id, o_id); alter table bmsql_order_line add constraint ol_stock_fkey foreign key (ol_supply_w_id, ol_i_id) references bmsql_stock (s_w_id, s_i_id); alter table bmsql_stock add constraint s_warehouse_fkey foreign key (s_w_id) references bmsql_warehouse (w_id); alter table bmsql_stock add constraint s_item_fkey foreign key (s_i_id) references bmsql_item (i_id); # ------------------------------------------------------------ # Loading SQL file ./sql.mysql/buildFinish.sql # ------------------------------------------------------------ -- Extra commands to run after the tables are created, loaded, -- indexes built and extra's created. analyze table bmsql_warehouse; analyze table bmsql_district; analyze table bmsql_customer; analyze table bmsql_history; analyze table bmsql_oorder; analyze table bmsql_new_order; analyze table bmsql_order_line; analyze table bmsql_stock; analyze table bmsql_item; analyze table bmsql_config; [root@ecs-sjm-mysql run]#
- 执行导入命令：
-
benchmark tpcc性能测试
-
调参前的性能测试，运行如下命令：
./home/benchmarksql5.0-for-mysql/run/runBenchmark.sh props.mysql运行大概5分钟左右，登录MySQL数据库查看innodb引擎状态，在MySQL的CLI界面下执行如下命令：
mysql> show engine innodb status\G运行6分钟后的结果如下：可以看到，当前buffer pool已经没有剩余空间
BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137363456 Dictionary memory allocated 466598 Buffer pool size 8192 Free buffers 0 Database pages 8042 Old database pages 2988 Modified db pages 4082 Pending reads 0 Pending writes: LRU 118, flush list 0, single page 29 Pages made young 69965, not young 10678113 0.00 youngs/s, 0.00 non-youngs/s Pages read 4338871, created 15661, written 1514041 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 894 / 1000, young-making rate 1 / 1000 not 265 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 8042, unzip_LRU len: 0 I/O sum[632113]:cur[1284], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 32 read views open inside InnoDB Process ID=25966, Main thread ID=281472649129984 , state=sleeping Number of rows inserted 615270, updated 1175202, deleted 42995, read 45467001 1368.32 inserts/s, 2560.72 updates/s, 92.95 deletes/s, 98681.16 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================1 row in set (0.00 sec)最终运行结束后的结果如下所示：
09:11:24,784 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
09:11:24,784 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
09:11:24,784 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
09:11:24,784 [main] INFO jTPCC : Term-00, © 2003, Raul Barbosa
09:11:24,784 [main] INFO jTPCC : Term-00, © 2004-2016, Denis Lussier
09:11:24,786 [main] INFO jTPCC : Term-00, © 2016, Jan Wieck
09:11:24,786 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
09:11:24,786 [main] INFO jTPCC : Term-00,
09:11:24,786 [main] INFO jTPCC : Term-00, db=mysql
09:11:24,786 [main] INFO jTPCC : Term-00, driver=com.mysql.cj.jdbc.Driver
09:11:24,787 [main] INFO jTPCC : Term-00, conn=jdbc:mysql://127.0.0.1:3306/tpcc?useSSL=false
09:11:24,787 [main] INFO jTPCC : Term-00, user=root
09:11:24,787 [main] INFO jTPCC : Term-00,
09:11:24,787 [main] INFO jTPCC : Term-00, warehouses=50
09:11:24,787 [main] INFO jTPCC : Term-00, terminals=50
09:11:24,788 [main] INFO jTPCC : Term-00, runMins=10
09:11:24,789 [main] INFO jTPCC : Term-00, limitTxnsPerMin=1000000000
09:11:24,789 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
09:11:24,789 [main] INFO jTPCC : Term-00,
09:11:24,789 [main] INFO jTPCC : Term-00, newOrderWeight=45
09:11:24,789 [main] INFO jTPCC : Term-00, paymentWeight=43
09:11:24,789 [main] INFO jTPCC : Term-00, orderStatusWeight=4
09:11:24,789 [main] INFO jTPCC : Term-00, deliveryWeight=4
09:11:24,789 [main] INFO jTPCC : Term-00, stockLevelWeight=4
09:11:24,789 [main] INFO jTPCC : Term-00,
09:11:24,789 [main] INFO jTPCC : Term-00, resultDirectory=null
09:11:24,790 [main] INFO jTPCC : Term-00, osCollectorScript=null
09:11:24,790 [main]Term-00, Running Average tpmTOTAL: 13912.07 Current tpmTOTAL: 918048 Memory Usage: 77MB / 383MB
09:21:25,478 [Thread-34] INFO jTPCC : Term-00,
09:21:25,478 [Thread-34] INFO jTPCC : Term-00,
09:21:25,478 [Thread-34] INFO jTPCC : Term-00, MeasuredtpmC (NewOrders)=6273.2
09:21:25,478 [Thread-34] INFO jTPCC : Term-00, MeasuredtpmTOTAL=13911.31
09:21:25,478 [Thread-34] INFO jTPCC : Term-00, Session Start = 2023-08-19 09:11:25
09:21:25,478 [Thread-34] INFO jTPCC : Term-00, Session End = 2023-08-19 09:21:25
09:21:25,478 [Thread-34] INFO jTPCC : Term-00, Transaction Count = 139172
-
-
查看调优前的参数默认值：
innodb_buffer_pool_size：默认值为128M（134217728字节）mysql> show variables like 'innodb_buffer_pool%'; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_chunk_size | 134217728 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_in_core_file | ON | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 134217728 | +-------------------------------------+----------------+ 11 rows in set (0.00 sec)mysql> show variables like '%innodb_io_capacity%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 200 | | innodb_io_capacity_max | 2000 | +------------------------+-------+ 2 rows in set (0.00 sec)说明：Innodb_buffer_pool_pages_data：InnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。 使用压缩表时，报告的Innodb_buffer_pool_pages_data值可能大于Innodb_buffer_pool_pages_total。
Innodb_buffer_pool_pages_dirty：显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页的数量（脏页刷新）。
Innodb_buffer_pool_pages_flushed：表示从InnoDB缓冲池中刷新脏页的请求数。
Innodb_buffer_pool_pages_free：显示InnoDB缓冲池中的空闲页面
Innodb_buffer_pool_pages_misc：InnoDB缓冲池中的页面数量很多，因为它们已被分配用于管理开销，例如行锁或自适应哈希索引。此值也可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。
Innodb_buffer_pool_pages_total：InnoDB缓冲池的总大小，以page为单位。
innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
innodb_buffer_pool_read_requests：它表示从内存中逻辑读取的请求数。
innodb_buffer_pool_wait_free：通常，对InnoDB缓冲池的写入发生在后台。 当InnoDB需要读取或创建页面并且没有可用的干净页面时， InnoDB首先刷新一些脏页并等待该操作完成。 此计数器计算这些等待的实例。 如果已正确设置innodb_buffer_pool_size，则此值应该很小。如果大于0，则表示InnoDb缓冲池太小。
innodb_buffer_pool_write_request：表示对缓冲池执行的写入次数。
参考资料：https://www.cnblogs.com/innocenter/p/14948857.html#MySQL InnoDB Buffer Pool 里包含什么？ -
调优后参数配置，加入如下参数到
/etc/my.cnf：echo innodb_buffer_pool_size=24G >> /etc/my.cnf echo innodb_io_capacity=3000 >> /etc/my.cnf echo innodb_io_capacity_max=6000 >> /etc/my.cnf参数说明：1.
innodb_buffer_pool_size：它的是一个内存区域，用来缓存InnoDB存储引擎的表中的数据和索引数据。以便提高对InnoDB存储引擎表中数据的查询访问速度。默认值为128M，此处调优为增大innodb_buffer_pool_size至24G，建议配置为服务器可用内存的70-80%。缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。如果将缓冲池大小更改为不等于或等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，则缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。2.
innodb_io_capacity：设置InnoDB后台任务每秒执行的I/O操作数的上限，影响刷新脏页和写入缓冲池的速率。默认值为200。
3.innodb_io_capacity_max：如果刷新操作过于落后，InnoDB可以超过innodb_io_capacity的限制进行刷新，但是不能超过本参数的值。默认值为2000。
说明：上面两项参数优化取决于数据盘的性能，在磁盘性能允许的情况下，适当增大配置值可以提高读写性能。
参考资料：https://blog.csdn.net/javaanddonet/article/details/109517031 -
重启MySQL数据库：
service mysql restart -
查看调优之后的参数值：
mysql> show variables like '%innodb_io_capacity%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 3000 | | innodb_io_capacity_max | 6000 | +------------------------+-------+ 2 rows in set (0.01 sec)mysql> show variables like '%innodb_buffer_pool%'; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_chunk_size | 134217728 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_in_core_file | ON | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 25769803776 | +-------------------------------------+----------------+ 11 rows in set (0.00 sec)mysql> show global status like '%innodb_buffer_pool%'; +---------------------------------------+--------------------------------------------------+ | Variable_name | Value | +---------------------------------------+--------------------------------------------------+ | Innodb_buffer_pool_dump_status | Dumping of buffer pool not started | | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 230819 10:04:14 | | Innodb_buffer_pool_resize_status | | | Innodb_buffer_pool_pages_data | 2922 | | Innodb_buffer_pool_bytes_data | 47874048 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 146 | | Innodb_buffer_pool_pages_free | 1569938 | | Innodb_buffer_pool_pages_misc | 4 | | Innodb_buffer_pool_pages_total | 1572864 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 16454 | | Innodb_buffer_pool_reads | 2780 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 1633 | +---------------------------------------+--------------------------------------------------+ 18 rows in set (0.00 sec) -
调参后的性能测试，运行结束并查看结果，如下：
08:52:53,413 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
08:52:53,413 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
08:52:53,413 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
08:52:53,413 [main] INFO jTPCC : Term-00, © 2003, Raul Barbosa
08:52:53,413 [main] INFO jTPCC : Term-00, © 2004-2016, Denis Lussier
08:52:53,415 [main] INFO jTPCC : Term-00, © 2016, Jan Wieck
08:52:53,416 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
08:52:53,416 [main] INFO jTPCC : Term-00,
08:52:53,416 [main] INFO jTPCC : Term-00, db=mysql
08:52:53,416 [main] INFO jTPCC : Term-00, driver=com.mysql.cj.jdbc.Driver
08:52:53,416 [main] INFO jTPCC : Term-00, conn=jdbc:mysql://127.0.0.1:3306/tpcc?useSSL=false
08:52:53,416 [main] INFO jTPCC : Term-00, user=root
08:52:53,416 [main] INFO jTPCC : Term-00,
08:52:53,416 [main] INFO jTPCC : Term-00, warehouses=50
08:52:53,416 [main] INFO jTPCC : Term-00, terminals=50
08:52:53,418 [main] INFO jTPCC : Term-00, runMins=10
08:52:53,418 [main] INFO jTPCC : Term-00, limitTxnsPerMin=1000000000
08:52:53,418 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
08:52:53,418 [main] INFO jTPCC : Term-00,
08:52:53,418 [main] INFO jTPCC : Term-00, newOrderWeight=45
08:52:53,418 [main] INFO jTPCC : Term-00, paymentWeight=43
08:52:53,418 [main] INFO jTPCC : Term-00, orderStatusWeight=4
08:52:53,419 [main] INFO jTPCC : Term-00, deliveryWeight=4
08:52:53,419 [main] INFO jTPCC : Term-00, stockLevelWeight=4
08:52:53,419 [main] INFO jTPCC : Term-00,
08:52:53,419 [main] INFO jTPCC : Term-00, resultDirectory=null
08:52:53,419 [main] INFO jTPCC : Term-00, osCollectorScript=null
08:52:53,419 [main]Term-00, Running Average tpmTOTAL: 42882.26 Current tpmTOTAL: 2834772 Memory Usage: 118MB / 414MB
09:02:54,134 [Thread-5] INFO jTPCC : Term-00,
09:02:54,134 [Thread-5] INFO jTPCC : Term-00,
09:02:54,135 [Thread-5] INFO jTPCC : Term-00, MeasuredtpmC (NewOrders)=19302.38
09:02:54,135 [Thread-5] INFO jTPCC : Term-00, MeasuredtpmTOTAL=42882.23
09:02:54,135 [Thread-5] INFO jTPCC : Term-00, Session Start = 2023-08-19 08:52:53
09:02:54,135 [Thread-5] INFO jTPCC : Term-00, Session End = 2023-08-19 09:02:54
09:02:54,135 [Thread-5] INFO jTPCC : Term-00, Transaction Count = 429015结论：经过与调优前的运行结果对比，可以看到tpmC值有了较大幅度增长，验证了MySQL的性能得到提升（67.5%）。至此，实验完成。
三、实验小结
本次实验是基于鲲鹏服务器对MySQL性能进行调优的实验，该实验的难点在于源码编译和安装MySQL，因一些依赖和环境问题，容易出现问题，故记录于此，以便后续可随时查阅。如本篇博文对您有所帮助，请动动您发财的手指给博主点赞，感谢您的阅读！！！！也欢迎您的建议和意见！！！