巩义网站建设方案表,全面的vi设计公司,企业管理咨询服务合同模板,个人简历ppt模板免费下载【基于鲲鹏及openEuler20.03TLS下MySQL8.0.17性能调优】 一、环境说明二、实验过程三、实验小结 一、环境说明
华为云ECS 规格#xff1a;8vCPU 32G arm架构操作系统#xff1a;openEuler 20.03.TLSMySQL版本#xff1a;8.0.17
二、实验过程 创建用户及用户组#xff1a;… 【基于鲲鹏及openEuler20.03TLS下MySQL8.0.17性能调优】 一、环境说明二、实验过程三、实验小结 一、环境说明
华为云ECS 规格8vCPU 32G arm架构操作系统openEuler 20.03.TLSMySQL版本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/gpgcheck1/gpgcheck0/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]
nameupdate
baseurlhttps://repo.huaweicloud.com/openeuler/openEuler-22.03-LTS/update/$basearch/
enabled1
gpgcheck0
gpgkeyhttps://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 install3.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_CONFIGmysql_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-setutf8\n[mysqld]\nbasedir/usr/local/mysql\nsocket/data/mysql/run/mysql.sock\ntmpdir/data/mysql/tmp\ndatadir/data/mysql/data\ndefault_authentication_pluginmysql_native_password\nport3306\nusermysql\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-setutf8 [mysqld] basedir/usr/local/mysql socket/data/mysql/run/mysql.sock tmpdir/data/mysql/tmp datadir/data/mysql/data default_authentication_pluginmysql_native_password port3306 usermysql 修改用户组和权限 chown mysql:mysql /etc/my.cnf
ll /etc/my.cnfMySQL加入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会看到如下信息记住数据库初始密码下面操作需要 [mysqlecs-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 rootlocalhost: PfI7a4SOjd 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文件中配置的操作系统用户是usermysql 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 rootlocalhost 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添加如下内容 dbmysql
drivercom.mysql.cj.jdbc.Driver
connjdbc:mysql://127.0.0.1:3306/tpcc?useSSLfalse
userroot
password123456
warehouses50
loadWorkers10
terminals50
runTxnsPerTerminal0
runMins10
limitTxnsPerMin1000000000
terminalWarehouseFixedtrue
newOrderWeight45
paymentWeight43
orderStatusWeight4
deliveryWeight4
stockLevelWeight4TPC-C测试数据导入 执行导入命令 ./home/benchmarksql5.0-for-mysql/run/runDatabaseBuild.sh props.mysql会看到如下信息打印出来[rootecs-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 LoadDatadrivercom.mysql.cj.jdbc.Driver
connjdbc:mysql://127.0.0.1:3306/tpcc?useSSLfalse
userroot
password***********
warehouses50
loadWorkers10
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 extras 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;
[rootecs-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 ID25966, Main thread ID281472649129984 , statesleeping
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, dbmysql 09:11:24,786 [main] INFO jTPCC : Term-00, drivercom.mysql.cj.jdbc.Driver 09:11:24,787 [main] INFO jTPCC : Term-00, connjdbc:mysql://127.0.0.1:3306/tpcc?useSSLfalse 09:11:24,787 [main] INFO jTPCC : Term-00, userroot 09:11:24,787 [main] INFO jTPCC : Term-00, 09:11:24,787 [main] INFO jTPCC : Term-00, warehouses50 09:11:24,787 [main] INFO jTPCC : Term-00, terminals50 09:11:24,788 [main] INFO jTPCC : Term-00, runMins10 09:11:24,789 [main] INFO jTPCC : Term-00, limitTxnsPerMin1000000000 09:11:24,789 [main] INFO jTPCC : Term-00, terminalWarehouseFixedtrue 09:11:24,789 [main] INFO jTPCC : Term-00, 09:11:24,789 [main] INFO jTPCC : Term-00, newOrderWeight45 09:11:24,789 [main] INFO jTPCC : Term-00, paymentWeight43 09:11:24,789 [main] INFO jTPCC : Term-00, orderStatusWeight4 09:11:24,789 [main] INFO jTPCC : Term-00, deliveryWeight4 09:11:24,789 [main] INFO jTPCC : Term-00, stockLevelWeight4 09:11:24,789 [main] INFO jTPCC : Term-00, 09:11:24,789 [main] INFO jTPCC : Term-00, resultDirectorynull 09:11:24,790 [main] INFO jTPCC : Term-00, osCollectorScriptnull 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, Measured tpmC (NewOrders) 6273.2 09:21:25,478 [Thread-34] INFO jTPCC : Term-00, Measured tpmTOTAL 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默认值为128M134217728字节 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_dataInnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。 使用压缩表时报告的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_miscInnoDB缓冲池中的页面数量很多因为它们已被分配用于管理开销例如行锁或自适应哈希索引。此值也可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。 Innodb_buffer_pool_pages_totalInnoDB缓冲池的总大小以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_size24G /etc/my.cnf
echo innodb_io_capacity3000 /etc/my.cnf
echo innodb_io_capacity_max6000 /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, dbmysql 08:52:53,416 [main] INFO jTPCC : Term-00, drivercom.mysql.cj.jdbc.Driver 08:52:53,416 [main] INFO jTPCC : Term-00, connjdbc:mysql://127.0.0.1:3306/tpcc?useSSLfalse 08:52:53,416 [main] INFO jTPCC : Term-00, userroot 08:52:53,416 [main] INFO jTPCC : Term-00, 08:52:53,416 [main] INFO jTPCC : Term-00, warehouses50 08:52:53,416 [main] INFO jTPCC : Term-00, terminals50 08:52:53,418 [main] INFO jTPCC : Term-00, runMins10 08:52:53,418 [main] INFO jTPCC : Term-00, limitTxnsPerMin1000000000 08:52:53,418 [main] INFO jTPCC : Term-00, terminalWarehouseFixedtrue 08:52:53,418 [main] INFO jTPCC : Term-00, 08:52:53,418 [main] INFO jTPCC : Term-00, newOrderWeight45 08:52:53,418 [main] INFO jTPCC : Term-00, paymentWeight43 08:52:53,418 [main] INFO jTPCC : Term-00, orderStatusWeight4 08:52:53,419 [main] INFO jTPCC : Term-00, deliveryWeight4 08:52:53,419 [main] INFO jTPCC : Term-00, stockLevelWeight4 08:52:53,419 [main] INFO jTPCC : Term-00, 08:52:53,419 [main] INFO jTPCC : Term-00, resultDirectorynull 08:52:53,419 [main] INFO jTPCC : Term-00, osCollectorScriptnull 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, Measured tpmC (NewOrders) 19302.38 09:02:54,135 [Thread-5] INFO jTPCC : Term-00, Measured tpmTOTAL 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因一些依赖和环境问题容易出现问题故记录于此以便后续可随时查阅。如本篇博文对您有所帮助请动动您发财的手指给博主点赞感谢您的阅读也欢迎您的建议和意见
