MySQL 8.0 Study 009 Tips

mysql [localhost:8032] {msandbox} (test) > create table t001(id int, col1 int);
Query OK, 0 rows affected (0.03 sec)

mysql [localhost:8032] {msandbox} (test) > show create table t001\G
*************************** 1. row ***************************
       Table: t001
Create Table: CREATE TABLE `t001` (
  `id` int DEFAULT NULL,
  `col1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > set session sql_generate_invisible_primary_key=on;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > show create table t001\G
*************************** 1. row ***************************
       Table: t001
Create Table: CREATE TABLE `t001` (
  `id` int DEFAULT NULL,
  `col1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > create table t002(id int,col1 int);
Query OK, 0 rows affected (0.03 sec)

mysql [localhost:8032] {msandbox} (test) > show create table t002\G
*************************** 1. row ***************************
       Table: t002
Create Table: CREATE TABLE `t002` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  `col1` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

mysql [localhost:8032] {msandbox} (test) > show global status like 'innodb_redo%';
+-------------------------------------+------------+
| Variable_name                       | Value      |
+-------------------------------------+------------+
| Innodb_redo_log_read_only           | OFF        |
| Innodb_redo_log_uuid                | 1075899837 |
| Innodb_redo_log_checkpoint_lsn      | 5307998064 |
| Innodb_redo_log_current_lsn         | 5307998064 |
| Innodb_redo_log_flushed_to_disk_lsn | 5307998064 |
| Innodb_redo_log_logical_size        | 512        |
| Innodb_redo_log_physical_size       | 3276800    |
| Innodb_redo_log_capacity_resized    | 104857600  |
| Innodb_redo_log_resize_status       | OK         |
| Innodb_redo_log_enabled             | ON         |
+-------------------------------------+------------+
10 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > alter instance disable innodb redo_log;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8032] {msandbox} (test) > show global status like 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > alter instance enable innodb redo_log;
Query OK, 0 rows affected (0.03 sec)

mysql [localhost:8032] {msandbox} (test) > show global status like 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

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

在导入数据的场景下，正确的操作步骤是：
1、关闭innodb 的redo log
2、检查redo log状态，确认已经关闭
3、导数据
4、开启innodb 的redo log
5、检查redo log状态，确认已经关闭

使用该特性，有以下几点注意事项：
该特性仅用于新实例导入数据场景，尽量避免用于线上的生产环境；
Redo logging关闭状态下，支持正常流程的关闭和重启实例；但在异常宕机情况下，可能会导致丢数据和页面损坏；Redo logging关闭后异常宕机的实例需要废弃重建，直接重启会有如下报错：[ERROR] [MY-013578] [InnoDB] Server was killed when Innodb Redo logging was disabled. Data files could be corrupt. You can try to restart the database with innodb_force_recovery=6.
Redo logging关闭状态下，不支持cloning operations和redo log archiving这两个功能；
执行过程中不支持其他并发的ALTER INSTANCE操作；

从实际测试情况来看，禁用与启用 redo log 有 10%~30% 的执行时间差异。

禁用 redo log 不影响 binlog 功能，可以正常同步。
禁用 redo log 是实例级，不支持表级。
禁用 redo log若发生 crash 是无法 recovery 的，OLTP 系统谨慎使用。
适用于大量数据导入场景。

mysql [localhost:8032] {msandbox} (test) > show variables like 'innodb_ddl_threads';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_ddl_threads | 4     |
+--------------------+-------+
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > show variables like 'innodb_ddl_buffer_size';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| innodb_ddl_buffer_size | 1048576 |
+------------------------+---------+
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > show variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_parallel_read_threads | 4     |
+------------------------------+-------+
1 row in set (0.00 sec)

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

The following limitations apply:

Parallel threads are not supported for building indexes that include virtual columns.

Parallel threads are not supported for full-text index creation.

Parallel threads are not supported for spatial index creation.

Parallel scan is not supported on tables defined with virtual columns.

Parallel scan is not supported on tables defined with a full-text index.

Parallel scan is not supported on tables defined with a spatial index.

# dnf install tcpdump -y

tcpdump -s 0 -l -w - dst 192.168.56.130 and port 8032 -i en1 |strings 
其中-i指定监听的网络接口

-i   :指定监听的网络接口
any  :这个是设置筛选的网络端口，此处举例是筛选的所有端口，如果知道详细的端口，可以替换这个内容
-A   :以ASCII格式打印出所有分组
-s 0 :取消抓包的长度限制，不加这个参数的话抓到数据的长度会被限制显示不出来
port :指定数据库端口，不知道可以不指定
-w - |strings 把包的数据，用字符展示出来，单独使用会报错，没加这个数据会显示省略号…
grep 'mh_core_data' C10  : 根据关键字显示前后10 行的数据，关键字的选择很重要，如果知道表名就根据表名筛选，这样能避免筛到很多无用的日志，也可以用select、update、delete、where、count等其他关键字，具体的关键字选择还要根据实际需求确定
抓到的数据有一些汉字显示成了…解决办法就是使用-w - |strings

tcpdump -s 65535 -x -q -tttt -i any -c 100000 port 8032 -i

tcpdump -i eth1 -s 0 -l -w - dst port 8032 | strings

-s 65535 或者 -s 0都表示抓取一个完整的数据包，在IP数据包中==总长度=首部+数据部分==，因此如下图所示，总长度共16~31,16bit，2^16-1=65535，即抓取整个数据包。
-x 打印每个数据包包头跟数据包内容，要想分析数据，这个参数是必须的；
-nn 不解析ip到主机名、端口号到服务名，而是直接以 ip、port的形式显示
-q 安静输出，很少打印有关协议的信息
-tttt 在每行打印前打印日期，有必要，作为时间统计
-i any 抓取所有网口的包（不包括lo），其实这里抓取 eth0就可以了
c 100000 抓取10w个数据包
port 8032 端口号

[root@ol8mysql01 ~]# tcpdump -s 65535 -x -nn -tttt -i any -c 100000 port 8032
dropped privs to tcpdump
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on any, link-type LINUX_SLL (Linux cooked v1), capture size 65535 bytes

[root@ol8mysql ~]# mysql -ulin -h192.168.56.130 -P8032 -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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> select * from test.emp limit 10;
+----+-------+--------+---------+-----+------------+---------+--------+--------+
| id | empno | ename  | job     | mgr | hiredate   | sal     | comn   | deptno |
+----+-------+--------+---------+-----+------------+---------+--------+--------+
|  1 |    11 | xFzEoQ | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    107 |
|  2 |    12 | OaiMPH | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    106 |
|  3 |    13 | OsFR6N | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    103 |
|  4 |    14 | YdXZWA | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    107 |
|  5 |    15 | GviO2y | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    103 |
|  6 |    16 | DwBecb | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    109 |
|  7 |    17 | V7DEdX | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    107 |
|  8 |    18 | LMsNrE | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    106 |
|  9 |    19 | Lj8zjI | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    102 |
| 10 |    20 | SUJNEG | SALEMAN |   1 | 2023-01-29 | 2000.00 | 400.00 |    101 |
+----+-------+--------+---------+-----+------------+---------+--------+--------+
10 rows in set (0.00 sec)

mysql> 

MySQL 8.0 的加密插件在使用中兼顾了安全与性能，建议使用默认开启，在 MySQL 8.0.16 后支持 TLSv1.3 协议。

目前网络上尝试过对 TLS 解密的手法，经笔者测试均不能在 wireshark 上解密 MySQL 8.0 的 TLS 加密消息。如果有新手法，可留言交流。

如果要进行一些 wireshark 对 MySQL 抓包的测试，需要看到 SQL query，目前建议采用 MySQL 5.7 版本或在 MySQL 8.0 上 skip-ssl 关闭加密通信。

top - 18:19:39 up  2:09,  1 user,  load average: 0.00, 0.01, 0.00
Tasks: 105 total,   1 running, 104 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.0 sy,  0.0 ni, 97.6 id,  0.0 wa,  2.4 hi,  0.0 si,  0.0 st
MiB Mem :   1972.8 total,    424.3 free,    606.3 used,    942.1 buff/cache
MiB Swap:   4096.0 total,   4096.0 free,      0.0 used.   1199.5 avail Mem 

正在进行大量tps
函数/排序/类型转化/逻辑IO访问...

pstack
获取堆栈信息
问题线程的定位
负载较低
mysql_pid=4522
pstack $mysql_pid>pstack.info

pt-pmp对堆栈信息排序
pt-pmp pstack.info | less


gdb
堆栈跟踪
等待分析
侵入性大

mysqld_pid 4522
gdb -p $mysqld_pid
(gdb) info thread 显示运行的所有线程
(gdb) thread 4 切换到某个线程
bt 显示调用栈

pstack 和gdb都可以获取堆栈信息，区别是gdb是交互式的（需要手动退出），pstack是快照形式（执行3s自动退出）
对mysqld使用gdb后，已经建立的连接无法再执行语句，新连接无法建立

strace
查看进程的系统调用信息
负载较高

查看系统调用
strace -cp $mysqld_pid

查看执行语句
mysqld_pid=4522
strace -f -F -ff -o mysqld-strace -s 1024 -p $mysqld_pid
find ./ -name "mysqld-strace" -type f -print |xargs grep -n "SELECT.FROM"

查看读写的文件
strace -o /tmp/strace_output.txt -T -tt -f -e trace=read,open -p “mysqld_pid”

perf
进程内部函数调用情况
负载较低

mysqld内部函数整体消耗情况
perf top -p 4522

记录所有内部函数调用
该命令会输出记录到perf.data中
perf record -p 4522

解析并查看perf.data内容
perf script -i perf.data>perf.log && less perf.log

吞吐量下降（tps）
查询响应时间增加
慢查询数增加
对mysql的并发陡增，也会产生影响

How to detect MySQL high CPU usage
Very often people link high server load to high CPU usage. That is not always true. The server load can go high due to bottlenecks in any resource. This can include memory, disk I/O, network or CPU.

The top reason for MySQL induced server load is due to memory or I/O exhaustion.
If it is I/O induced bottleneck, the %wa (called wait average) will have the highest CPU%. In contrast, if it is a memory induced load, the “free” memory limits to just a few MBs.

Fixing MySQL high CPU
If the server load is indeed related to high CPU usage, we’ve found the following fixes to be useful:

Enable InnoDB to handle a high number of concurrent connections – Check MySQL “PROCESSLIST”, and if you see a lot of queries in “LOCK” status, it means a lot of queries are put on hold because MyISAM tables are handling other transactions. To fix this convert those tables into the InnoDB engine which supports row-level locking.
Enable persistent connections – If you have only a single application that receives thousands of connections per hour, enabling persistent MySQL connections can improve performance. If the server has multiple applications (like a shared web hosting server) this may not work.
Block abusive processes – When a website is under attack (like DoS, comment spamming, etc.), it ends up in an abnormally high number of established connections in a short time. Use the “PROCESSLIST” in MySQL to identify the top users, and block access to the abusive connections.
Optimize database queries – Some web applications use complex queries to display site information. These queries can take a long time to execute, and cause CPU load. Get the list of such queries from the “slow query log” and reduce the number of joins and other table manipulations under a single query.
Check for “leap second bug” – On July 1st, 2015, there was an addition of a leap second to standard UTC time. In servers running old Linux kernel versions, and which uses time servers, this is seen to cause MySQL high load. If you have an old Linux kernel, try resetting the time using the command date -s “$(date)”.

How to prevent MySQL high CPU usage
Server traffic changes and databases grow over time. MySQL high CPU issues can be prevented to a large extent if the database server is audited and tuned for performance.

MySQL performance tuning – MySQL uses various buffers and cache systems to execute queries. As the volume and complexity of database queries change, the server settings need to be adjusted for optimum performance. There are various tools such as mysqltuner to identify any settings that need adjustment.
Security audit and hardening – Spamming and DoS attacks can easily overwhelm a database server. Implement web application firewalls such as ModSecurity, and DoS firewalls such as ModEvasive to prevent attackers from affecting server uptime.
Implementing load balancing – As the server traffic grows it might be required to split the load into multiple servers. MySQL can be configured for master-master and master-slave replication which allows queries to be served from any server in a cluster.
Optimizing database queries – If web applications are poorly coded, no amount of database optimization will fix the server load. Monitor MySQL’s “slow query log” and reduce the number of JOINs to make the database faster.
Using high-performance alternatives such as Percona – MySQL has many memory/CPU bottlenecks due to the way it processes queries. MySQL spin-offs such as MariaDB and Percona resolves these issues and helps achieve better stability.

会话恢复

只要Screen本身没有终止，在其内部运行的会话都可以恢复。这一点对于远程登录的用户特别有用——即使网络连接中断，用户也不会失去对已经打开的命令行会话的控制。只要再次登录到主机上执行screen -r就可以恢复会话的运行。同样在暂时离开的时候，也可以执行分离命令detach，在保证里面的程序正常运行的情况下让Screen挂起（切换到后台）。这一点和图形界面下的VNC很相似。

多窗口

在Screen环境下，所有的会话都独立的运行，并拥有各自的编号、输入、输出和窗口缓存。用户可以通过快捷键在不同的窗口下切换，并可以自由的重定向各个窗口的输入和输出。Screen实现了基本的文本操作，如复制粘贴等；还提供了类似滚动条的功能，可以查看窗口状况的历史记录。窗口还可以被分区和命名，还可以监视后台窗口的活动。

会话共享

Screen可以让一个或多个用户从不同终端多次登录一个会话，并共享会话的所有特性（比如可以看到完全相同的输出）。它同时提供了窗口访问权限的机制，可以对窗口进行密码保护。

# yum -y install epel-release
# yum -y install screen

http://ftp.gnu.org/gnu/ncurses/
http://ftp.gnu.org/gnu/screen/

### 查看有哪些screen可用
screen -ls
screen -list
### 创建一个screen
screen -S <session name>
### 进入现有screen
screen -r <screeen name>/<screen id>
### 加入screen会话
screen -x <screeen name>/<screen id>
### 退出
ctrl + a + d
screen -d
screen -d <screen name>
### kill screen
kill -i <screen id>
### 清理所有挂掉的screen
screen -wipe

[root@ol8mysql01 ~]# screen -S session01
[remote detached from 24119.session01]
[root@ol8mysql01 ~]# 
[root@ol8mysql01 ~]# screen -ls
There is a screen on:
	24119.session01	(Detached)
1 Socket in /run/screen/S-root.
[root@ol8mysql01 ~]# ps -ef | grep -i screen
root       24119       1  0 22:40 ?        00:00:00 SCREEN -S session01
root       24150    5306  0 22:41 pts/0    00:00:00 grep --color=auto -i screen
[root@ol8mysql01 ~]# ps -ef | grep 24119
root       24119       1  0 22:40 ?        00:00:00 SCREEN -S session01
root       24120   24119  0 22:40 pts/1    00:00:00 /bin/bash
root       24152    5306  0 22:41 pts/0    00:00:00 grep --color=auto 24119
[root@ol8mysql01 ~]#