Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

MySQL 8.0 Study 014 Tips

2023-03-03

MySQL 8.0 Study 014 Tips

学习系列

  • MySQL MySQL skip-grants-tables Tips
  • MySQL employees-schema 导入测试
  • MySQL ICP(Index Condition Pushdown) 测试
  • MySQL analysis_binlog 解析binary log测试

MySQL skip-grants-tables Tips

防火墙什么的均正常但是无法被远程访问到。简单的使用客户端登录报错。

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

查看教程/端口绑定/

[root@ol8mysql01 ~]# ps -ef | grep mysql
root        5415       1  0 Mar02 pts/0    00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/root/sandboxes/ee_8_0_32/my.sandbox.cnf
root        5778    5415  0 Mar02 pts/0    00:09:38 /opt/mysql/8.0.32/bin/mysqld --defaults-file=/root/sandboxes/ee_8_0_32/my.sandbox.cnf --basedir=/opt/mysql/8.0.32 --datadir=/root/sandboxes/ee_8_0_32/data --plugin-dir=/opt/mysql/8.0.32/lib/plugin --user=root --log-error=/root/sandboxes/ee_8_0_32/data/msandbox.err --pid-file=/root/sandboxes/ee_8_0_32/data/mysql_sandbox8032.pid --socket=/tmp/mysql_sandbox8032.sock --port=8032
root       27945    5297  0 16:04 pts/0    00:00:00 grep --color=auto mysql
[root@ol8mysql01 ~]# lsof -i:8032
COMMAND  PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  5778 root   22u  IPv4  32282      0t0  TCP *:pro-ed (LISTEN)
[root@ol8mysql01 ~]# ss -nltp | grep 8032
LISTEN 0      151          0.0.0.0:8032       0.0.0.0:*    users:(("mysqld",pid=5778,fd=22))
LISTEN 0      70                 *:18032            *:*    users:(("mysqld",pid=5778,fd=20))
[root@ol8mysql01 ~]# 

下面端口如果是0的话,而且配置了skip-grants-tables的话,可能报连接错误。

mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 8032  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'skip_net%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> 

参数情况:

[root@ol8mysql01 ee_8_0_32]# ps -ef | grep mysql
root        5415       1  0 Mar02 pts/0    00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/root/sandboxes/ee_8_0_32/my.sandbox.cnf
root        5778    5415  0 Mar02 pts/0    00:09:43 /opt/mysql/8.0.32/bin/mysqld --defaults-file=/root/sandboxes/ee_8_0_32/my.sandbox.cnf --basedir=/opt/mysql/8.0.32 --datadir=/root/sandboxes/ee_8_0_32/data --plugin-dir=/opt/mysql/8.0.32/lib/plugin --user=root --log-error=/root/sandboxes/ee_8_0_32/data/msandbox.err --pid-file=/root/sandboxes/ee_8_0_32/data/mysql_sandbox8032.pid --socket=/tmp/mysql_sandbox8032.sock --port=8032
root       27980    5297  0 16:14 pts/0    00:00:00 grep --color=auto mysql
[root@ol8mysql01 ee_8_0_32]# find /opt/mysql/8.0.32 -name mysqld
/opt/mysql/8.0.32/bin/mysqld
[root@ol8mysql01 ee_8_0_32]# /opt/mysql/8.0.32/bin/mysqld --verbose --help | grep skip-network
  --skip-networking   Don't allow connection with TCP/IP
skip-networking                                              FALSE
[root@ol8mysql01 ee_8_0_32]# /opt/mysql/8.0.32/bin/mysqld --verbose --help | grep skip-grant
  --skip-grant-tables Start without grant tables. This gives all users FULL
skip-grant-tables                                            FALSE
[root@ol8mysql01 ee_8_0_32]# 

–skip-grant-tables

Because starting the server with --skip-grant-tables disables authentication checks, the server also disables remote connections in that case by enabling skip_networking.

因为使用 skip-grants-tables 参数禁用了权限表,这种情况下 mysql 会默认开启 skip-networking 来禁用远程连接。目的是为了安全。

配置 skip-grants-tables 引起无法远程连接 mysql 服务端的故障,解决方法也是非常的简单注释重启。

MySQL employees-schema 导入测试

MySQL官方文档中关于此employees-schema数据库参考如下

employees-installation

github test_db

MySQL employees-schema 导入和确实

ER 结构图如下:

MySQL_MySQL_employees-schema

[root@ol8mysql01 vagrant]# unzip test_db-master.zip 
Archive:  test_db-master.zip
d01cb62fcfa4671773f167480df174d8ce4316c1
   creating: test_db-master/
  inflating: test_db-master/Changelog  
  inflating: test_db-master/README.md  
  inflating: test_db-master/employees.sql  
  inflating: test_db-master/employees_partitioned.sql  
  inflating: test_db-master/employees_partitioned_5.1.sql  
   creating: test_db-master/images/
  inflating: test_db-master/images/employees.gif  
  inflating: test_db-master/images/employees.jpg  
  inflating: test_db-master/images/employees.png  
  inflating: test_db-master/load_departments.dump  
  inflating: test_db-master/load_dept_emp.dump  
  inflating: test_db-master/load_dept_manager.dump  
  inflating: test_db-master/load_employees.dump  
  inflating: test_db-master/load_salaries1.dump  
  inflating: test_db-master/load_salaries2.dump  
  inflating: test_db-master/load_salaries3.dump  
  inflating: test_db-master/load_titles.dump  
  inflating: test_db-master/objects.sql  
   creating: test_db-master/sakila/
  inflating: test_db-master/sakila/README.md  
  inflating: test_db-master/sakila/sakila-mv-data.sql  
  inflating: test_db-master/sakila/sakila-mv-schema.sql  
  inflating: test_db-master/show_elapsed.sql  
  inflating: test_db-master/sql_test.sh  
  inflating: test_db-master/test_employees_md5.sql  
  inflating: test_db-master/test_employees_sha.sql  
  inflating: test_db-master/test_versions.sh  
[root@ol8mysql01 vagrant]#cd test_db-master
[root@ol8mysql01 test_db-master]# pwd
/vagrant/test_db-master
[root@ol8mysql01 test_db-master]# # mysql -S /tmp/mysql_sandbox8033.sock < employees.sqlINFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:45
[root@ol8mysql01 test_db-master]# 
[root@ol8mysql01 test_db-master]# time mysql -S /tmp/mysql_sandbox8033.sock -t < test_employees_sha.sql
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name   | expected_records | expected_crc                             |
+--------------+------------------+------------------------------------------+
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
+--------------+------------------+------------------------------------------+

+--------------+------------------+------------------------------------------+
| table_name   | found_records    | found_crc                                |
+--------------+------------------+------------------------------------------+
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
+--------------+------------------+------------------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| departments  | OK            | ok        |
| dept_emp     | OK            | ok        |
| dept_manager | OK            | ok        |
| employees    | OK            | ok        |
| salaries     | OK            | ok        |
| titles       | OK            | ok        |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:00:47         |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC     | OK     |
| count   | OK     |
+---------+--------+

real	0m46.895s
user	0m0.017s
sys	0m0.000s
[root@ol8mysql01 test_db-master]# 

MySQL ICP(Index Condition Pushdown) 测试

Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。

  • 当关闭ICP时,index 仅仅是data access 的一种访问方式,存储引擎通过索引回表获取的数据会传递到MySQL Server 层进行where条件过滤。
  • 当打开ICP时,如果部分where条件能使用索引中的字段,MySQL Server 会把这部分下推到引擎层,可以利用index过滤的where条件在存储引擎层进行数据过滤,而非将所有通过index access的结果传递到MySQL server层进行where过滤.

优化效果:ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数,减少io次数,提高查询语句性能。

关闭索引下推可以使用如下命令

set optimizer_switch='index_condition_pushdown=off';

测试

创建employees first_name/last_name的组合索引

mysql [localhost:8032] {msandbox} ((none)) > use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql [localhost:8032] {msandbox} (employees) > show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | PRIMARY  |            1 | emp_no      | A         |      299246 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

mysql [localhost:8032] {msandbox} (employees) > desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > create index idx_emp_first_lastname on employees(first_name,last_name);
Query OK, 0 rows affected (2.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:8032] {msandbox} (employees) > show index from employees;
+-----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | PRIMARY                |            1 | emp_no      | A         |      299246 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employees |          1 | idx_emp_first_lastname |            1 | first_name  | A         |        1311 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employees |          1 | idx_emp_first_lastname |            2 | last_name   | A         |      276828 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

mysql [localhost:8032] {msandbox} (employees) > 

mysql [localhost:8032] {msandbox} (employees) > select * from employees limit 10000,2;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20001 | 1962-05-16 | Atreye     | Eppinger  | M      | 1990-04-18 |
|  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > 

测试

  • 查询语句: MySQL的最左前缀原则, first_name 可以使用索引,last_name采用了like 模糊查询,不能使用索引。
[root@ol8mysql01 msb_8_0_32]# ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost:8032] {msandbox} ((none)) > use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql [localhost:8032] {msandbox} (employees) > set profiling=1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |
|  17961 | 1963-07-09 | Jaber      | Schieder  | M      | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |
|  17961 | 1963-07-09 | Jaber      | Schieder  | M      | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |
|  17961 | 1963-07-09 | Jaber      | Schieder  | M      | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > show profiles;
+----------+------------+----------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                      |
+----------+------------+----------------------------------------------------------------------------+
|        1 | 0.00084375 | select * from employees where first_name='Jaber' and last_name like '%der' |
|        2 | 0.00070300 | select * from employees where first_name='Jaber' and last_name like '%der' |
|        3 | 0.00097475 | select * from employees where first_name='Jaber' and last_name like '%der' |
+----------+------------+----------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > explain select * from employees where first_name='Jaber' and last_name like '%der';
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_emp_first_lastname | idx_emp_first_lastname | 58      | const |  264 |    11.11 | Using index condition |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |
|  17961 | 1963-07-09 | Jaber      | Schieder  | M      | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |
|  17961 | 1963-07-09 | Jaber      | Schieder  | M      | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > select * from employees where first_name='Jaber' and last_name like '%der';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |
|  17961 | 1963-07-09 | Jaber      | Schieder  | M      | 1985-12-18 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > show profiles;
+----------+------------+------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                              |
+----------+------------+------------------------------------------------------------------------------------+
|        1 | 0.00084375 | select * from employees where first_name='Jaber' and last_name like '%der'         |
|        2 | 0.00070300 | select * from employees where first_name='Jaber' and last_name like '%der'         |
|        3 | 0.00097475 | select * from employees where first_name='Jaber' and last_name like '%der'         |
|        4 | 0.00111750 | explain select * from employees where first_name='Jaber' and last_name like '%der' |
|        5 | 0.00021700 | set optimizer_switch='index_condition_pushdown=off'                                |
|        6 | 0.00297800 | select * from employees where first_name='Jaber' and last_name like '%der'         |
|        7 | 0.00226100 | select * from employees where first_name='Jaber' and last_name like '%der'         |
|        8 | 0.00251200 | select * from employees where first_name='Jaber' and last_name like '%der'         |
+----------+------------+------------------------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > explain select * from employees where first_name='Jaber' and last_name like '%der';
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_emp_first_lastname | idx_emp_first_lastname | 58      | const |  264 |    11.11 | Using where |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > set profiling=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (employees) > 
  • ICP 开启时的执行计划 含有 Using index condition 标示 ,表示优化器使用了ICP对数据访问进行优化。
    • last_name 的like ‘%der’条件可以通过索引字段last_name 进行过滤,在存储引擎内部通过与where条件的对比,直接过滤掉不符合条件的数据。
    • 该过程不回表,只访问符合条件的1条记录并返回给MySQL Server ,有效的减少了io访问和各层之间的交互。
  • ICP 关闭时的执行计划显示use where.

ICP的使用限制

  1. 当sql需要全表访问时,ICP的优化策略可用于range, ref, eq_ref, ref_or_null 类型的访问数据方法 。
  2. 支持InnoDB和MyISAM表。
  3. ICP只能用于二级索引,不能用于主索引。
  4. 并非全部where条件都可以用ICP筛选。如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。
  5. ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
  6. 5.6 版本的不支持分表的ICP 功能,5.7 版本的开始支持。
  7. 当sql 使用覆盖索引时,不支持ICP 优化方法。

MySQL analysis_binlog 解析binary log测试

analysis_binlog 工具利用内存换取时间,加上可以并行分析多个文件,所以能做到高效解析 binlog,但是这可能也是缺点。 不建议在生产环境服务器使用,否则容易将内存打满。

dbdeployer 安装最新MySQL 8.0.32

# wget https://github.com/datacharmer/dbdeployer/releases/download/v1.71.0/dbdeployer-1.71.0.linux.tar.gz
# tar xzvf dbdeployer-1.71.0.linux.tar.gz 
# chmod +x dbdeployer-1.71.0.linux
# mv dbdeployer-1.71.0.linux /usr/local/bin/dbdeployer
# mkdir -p ~/sandboxes/{mysql_package,mysql_base}
# dbdeployer defaults update sandbox-binary $HOME/sandboxes/mysql_base
# dbdeployer defaults show

# scp mysql-commercial-8.0.32-linux-glibc2.12-x86_64.tar.xz ol8mysql:/root

# mkdir /opt/mysql/mysql_ee -p
# dbdeployer --sandbox-binary=/opt/mysql/mysql_ee unpack mysql-commercial-8.0.32-linux-glibc2.12-x86_64.tar.xz
# dbdeployer deploy single 8.0.32 --sandbox-binary=/opt/mysql/mysql_ee  --sandbox-directory=ee_8_0_32 --bind-address=0.0.0.0 --port=8032 --remote-access='%' --native-auth-plugin --gtid --my-cnf-options="skip_name_resolve" --pre-grants-sql="create user lin@'%' identified with mysql_native_password by 'mysql';grant all on *.* to lin@'%' with grant option;flush privileges;"

# mysql -ulin -P8032 -pmysql

Client connect:

# mysql -ulin -pmysql -hol8mysql01 -P8032 -e "select version()";
/opt/mysql/mysql_ee/8.0.32/bin/mysql -ulin -P8032 -p

[root@ol8mysql ~]# hostname
ol8mysql
[root@ol8mysql ~]# cat /proc/cpuinfo | grep -i process
processor	: 0
processor	: 1
[root@ol8mysql ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:           1972         663         521          16         787        1145
Swap:          4095           0        4095
[root@ol8mysql ~]# mysql -ulin -hol8mysql -P8032 -pmysql -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| version()         |
+-------------------+
| 8.0.32-commercial |
+-------------------+
[root@ol8mysql ~]# 

sysbench 工具安装

Create test database:

mysql -ulin -pmysql -hol8mysql -P8032 -e "create database if not exists mstest";

script:

#!/bin/bash
# Ref:https://gitee.com/mo-shan/script/blob/master/sysbench.sh
mysql_conne_route_info=(lin mysql ol8mysql 8032)
threads_num=4
db_name="mstest"
table_num=10
table_size=100000
export PATH="/root/sysbench-master/src:${PATH}"
case ${1} in 
    "--prepare" | "-p")
        sysbench /root/sysbench-master/src/lua/oltp_read_only.lua --mysql-host=${mysql_conne_route_info[2]} \
            --mysql-port=${mysql_conne_route_info[3]} --mysql-db=${db_name} --mysql-user=${mysql_conne_route_info[0]} \
            --mysql-password=${mysql_conne_route_info[1]} --table_size=${table_size} --tables=${table_num} \
            --threads=${threads_num} --events=500000 --report-interval=10 --time=60 prepare
        ;;
    "--cleanup" | "-c")
        sysbench /root/sysbench-master/src/lua/oltp_read_only.lua --mysql-host=${mysql_conne_route_info[2]} \
            --mysql-port=${mysql_conne_route_info[3]} --mysql-db=${db_name} --mysql-user=${mysql_conne_route_info[0]} \
            --mysql-password=${mysql_conne_route_info[1]} --table_size=${table_size} --tables=${table_num} \
            --threads=${threads_num} --events=500000 --report-interval=10 --time=60 cleanup
        ;;
    "--run" | "-r")
        sysbench /root/sysbench-master/src/lua/oltp_update_non_index.lua --mysql-host=${mysql_conne_route_info[2]} \
            --mysql-port=${mysql_conne_route_info[3]} --mysql-db=${db_name} --mysql-user=${mysql_conne_route_info[0]} \
            --mysql-password=${mysql_conne_route_info[1]} --table_size=${table_size} --tables=${table_num} \
            --threads=${threads_num} --events=500000 --report-interval=10 --time=60 run
        ;;
    "--help" | "-h" | *)
        echo -e "\n\t\033[33mUsage: bash ${0} [--prepare|-p|--run|-r|--cleanup|-c|--help|-h]\033[0m\n"
        echo -e "\t\t\033[34m[--prepare|-p] \t\tPrepare test data to MySQL.\033[0m"
        echo -e "\t\t\033[34m[--run|-r]     \t\tStart stress test to MySQL.\033[0m"
        echo -e "\t\t\033[34m[--cleanup|-c] \t\tCleaning up test data from MySQL.\033[0m\n"
        exit 1
        ;;
esac

Test 测试

mysql -ulin -pmysql -hol8mysql -P8032 
flush binary logs;
show binary logs;

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      8748 | No        |
| mysql-bin.000002 |       220 | No        |
| mysql-bin.000003 | 190916435 | No        |
| mysql-bin.000004 |       197 | No        |
+------------------+-----------+-----------+
4 rows in set (0.01 sec)

mysql>

压力测试:

[root@ol8mysql01 ~]# bash sysbench.sh -r
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 4 tps: 458.71 qps: 458.71 (r/w/o: 0.00/458.71/0.00) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 422.69 qps: 422.69 (r/w/o: 0.00/422.69/0.00) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 402.63 qps: 402.63 (r/w/o: 0.00/402.63/0.00) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 4 tps: 416.88 qps: 416.88 (r/w/o: 0.00/416.88/0.00) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 4 tps: 397.71 qps: 397.71 (r/w/o: 0.00/397.71/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 4 tps: 423.54 qps: 423.54 (r/w/o: 0.00/423.54/0.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           25227
        other:                           0
        total:                           25227
    transactions:                        25227  (420.37 per sec.)
    queries:                             25227  (420.37 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      420.3673
    time elapsed:                        60.0118s
    total number of events:              25227

Latency (ms):
         min:                                    3.05
         avg:                                    9.51
         max:                                   32.61
         95th percentile:                       16.12
         sum:                               239892.24

Threads fairness:
    events (avg/stddev):           6306.7500/20.63
    execution time (avg/stddev):   59.9731/0.00

[root@ol8mysql01 ~]# 

analysis_binlog

参考: analysis_binlog

git clone https://gitee.com/mo-shan/analysis_binlog.git

bin/analysis_binlog

#将这里的mysqlbinlog_path改成mysqlbinlog工具的绝对路径,否则可能会因版本太低导致错误
(1)更改mysqlbinlog路径
(2)更改analysis_binlog家目录路径

chmod +x bin/analysis_binlog 
echo "export PATH=$(pwd)/bin:${PATH}" >> ${HOME}/.bashrc

示例:

-bfile: 指定binlog文件, 支持多个文件并行分析, 多个文件用逗号相隔, 需要并行分析时请结合-w参数使用
-w : 指定并行数, 当需要分析多个binlog文件时该参数有效, 默认是1
-t : 指定显示结果的格式/内容, 供选选项有"detail|simple". 当指定detail的时候结果较为详细, 会打印详细的分析过程, 消耗时间也不直观, simple只做了统计工作
-s : 指定排序规则, 供选选项有"insert|update|delete". 默认会把统计结果做一个排序, 按照表的维度统计出insert update delete的次数, 并按照次数大小排序(默认insert)

Logs:

[root@ol8mysql01 ~]# mysql -ulin -pmysql -hol8mysql -P8032 -e "show binary logs"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      8748 | No        |
| mysql-bin.000002 |       220 | No        |
| mysql-bin.000003 |         0 | No        |
| mysql-bin.000004 | 190916622 | No        |
| mysql-bin.000004 | 190916622 | No        |
| mysql-bin.000005 |  17275206 | No        |
+------------------+-----------+-----------+

[root@ol8mysql01 ~]# scp ol8mysql:/root/sandboxes/ee_8_0_32/data/mysql-bin.000005 /tmp
root@ol8mysql's password: 
mysql-bin.000005                                                                                                                                                           100%   16MB  64.5MB/s   00:00  
[root@ol8mysql01 ~]# 

analysis_binlog -bfile=/tmp/mysql-bin.000005 -w=2 

# analysis_binlog -bfile=/tmp/mysql-bin.000005 -w=2 

[2023-03-02 14:20:34] [WARN] [192.168.3.110] Version : v_1.3
[2023-03-02 14:20:34] [INFO] [192.168.3.110] THREAD_1:Analysing --> /tmp/mysql-bin.000005
[2023-03-02 14:20:37] [INFO] [192.168.3.110] THREAD_1:Analysis completed --> /tmp/mysql-bin.000005. Analyze the results in this file '/root/analysis_binlog/res/mysql-bin.000005.res'

# # cat /root/analysis_binlog/res/mysql-bin.000005.res 
Table                                             First Time          Last Time           Type      affe(row) Ins(s)    Upd(s)    Del(s)    Total(s)  
mstest.sbtest10                                   230302 13:28:09     230302 13:29:09     DML       0         0         2607      0         2607      
mstest.sbtest4                                    230302 13:28:09     230302 13:29:09     DML       0         0         2589      0         2589      
mstest.sbtest5                                    230302 13:28:09     230302 13:29:09     DML       0         0         2584      0         2584      
mstest.sbtest8                                    230302 13:28:09     230302 13:29:09     DML       0         0         2563      0         2563      
mstest.sbtest1                                    230302 13:28:09     230302 13:29:09     DML       0         0         2539      0         2539      
mstest.sbtest6                                    230302 13:28:09     230302 13:29:09     DML       0         0         2531      0         2531      
mstest.sbtest7                                    230302 13:28:09     230302 13:29:09     DML       0         0         2522      0         2522      
mstest.sbtest2                                    230302 13:28:09     230302 13:29:09     DML       0         0         2512      0         2512      
mstest.sbtest3                                    230302 13:28:09     230302 13:29:09     DML       0         0         2499      0         2499      
mstest.sbtest9                                    230302 13:28:09     230302 13:29:09     DML       0         0         2492      0         2492      

Table                                             First Time          Last Time           Type      affe(row) Ins(s)    Upd(s)    Del(s)    Trans(total)
The total                                         230302 13:28:09     230302 13:29:09     DML       0         0         25438               25438     

Table                                             First Time          Last Time           Type      affe(row) Ins(T)    Upd(T)    Del(T)    Trans(total)
Transaction                                       230302 13:28:09     230302 13:29:09     DML       0         0         25438     0         25438     

# 

#拷贝一份继续解析成sql文件,并行的话 加上-w=2

[root@ol8mysql ~]# analysis_binlog -mpath=/opt/mysql/mysql_ee/8.0.32/bin/mysqlbinlog -bfile=/tmp/mysql-bin.000005 --binlog2sql --save-way=table

[2023-03-02 14:23:53] [WARN] [192.168.3.110] Version : v_1.3
[2023-03-02 14:23:53] [INFO] [192.168.3.110] THREAD_1:Analysing --> /tmp/mysql-bin.000005
[2023-03-02 14:24:01] [INFO] [192.168.3.110] THREAD_1:Analysis completed --> /tmp/mysql-bin.000005

[root@ol8mysql ~]# 
[root@ol8mysql table]# pwd
/root/analysis_binlog/res/table
[root@ol8mysql table]# cd ..
[root@ol8mysql res]# pwd
/root/analysis_binlog/res
[root@ol8mysql res]# ls -tlr
total 16
-rw-r--r-- 1 root root 2408 Mar  2 13:05 mysql-bin.000004.res
-rw-r--r-- 1 root root 2408 Mar  2 13:05 mysql-bin.000003.res
-rw-r--r-- 1 root root 2408 Mar  2 14:20 mysql-bin.000005.res
drwxr-xr-x 3 root root 4096 Mar  2 14:23 table
[root@ol8mysql res]# cd table
[root@ol8mysql table]# ls -tlr
total 13848
drwxr-xr-x 2 root root       6 Mar  2 13:10 big
-rw-r--r-- 1 root root 1386910 Mar  2 14:24 mysql-bin.000005_mstest.sbtest9.log
-rw-r--r-- 1 root root 1426557 Mar  2 14:24 mysql-bin.000005_mstest.sbtest8.log
-rw-r--r-- 1 root root 1403572 Mar  2 14:24 mysql-bin.000005_mstest.sbtest7.log
-rw-r--r-- 1 root root 1408695 Mar  2 14:24 mysql-bin.000005_mstest.sbtest6.log
-rw-r--r-- 1 root root 1438066 Mar  2 14:24 mysql-bin.000005_mstest.sbtest5.log
-rw-r--r-- 1 root root 1440863 Mar  2 14:24 mysql-bin.000005_mstest.sbtest4.log
-rw-r--r-- 1 root root 1390803 Mar  2 14:24 mysql-bin.000005_mstest.sbtest3.log
-rw-r--r-- 1 root root 1397978 Mar  2 14:24 mysql-bin.000005_mstest.sbtest2.log
-rw-r--r-- 1 root root 1413137 Mar  2 14:24 mysql-bin.000005_mstest.sbtest1.log
-rw-r--r-- 1 root root 1453572 Mar  2 14:24 mysql-bin.000005_mstest.sbtest10.log
[root@ol8mysql table]# 

Referece

参考:

employees-installation

github test_db

索引性能优化之ICP、MRR、BKA理论加实践

Have a good work&life! 2023/02 via LinHong


Similar Posts

Comments