生产环境更改表结构、SQL分析工具、查看连接信息、索引情况查询、IO相关、语句相关、 swap分区

本文介绍: 当调用We b 服务的HTTP请求数达到tomcat的最大线程数时，还有新的HTTP请求到来，这时tomcat会将该请求放在等待队列中，这个a cc eptCount就是指能够接受的最大等待数，默认100。：To m cat可创建的最大的线程数，每一个线程处理一个请求，超过这个请求数后，客户端请求只能排队，等有线程释放才能处理。（4核8g 内存，线程数800，一般是核数*200。线程数，tomcat 启动时的初始化的线程数，默认10。），队列也做缓冲池用，但也不能无限长，不但消耗内存，而且出队入队也消耗CPU。

在服务端中查看 连接 信息

查看 连接数

show global status like 'Thread%';

在这里插入图片描述
Thread_cached: 线程缓存中的线程数
Thread_connected: 当前打开的连接数
Thread_created:为处理连接而创建的线程数
Thread_running: 未休眠的线程数

优化Thre ad s_create d

Thread_created表示创建过的线程数、Thre a d_c re a t e d 过大—–&g t;说明 mysql 服务一直在创建线程，比较耗资源

查看thread_cache_size，默认为**-1**

show variables like 'thread_cache_size';

短连接：适当大一点
长连接：设置小一点，一般在50-100

在这里插入图片描述

# 修改thread_cache_size
set global thread_cache_size=64;

my.cnf–>永久生效

[mysqld]
thread_cache_size=64

SQL分析 工具

查看慢查询条数

show global status like '%Slow_queries%';

查看SQL执行成本：SHOW PROFILE

Show Profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，默认情况下处于关闭状态，并保存最近15次的运行结果。
我们可以在会话级别开启这个功能

set profiling = 'ON';
# 接着看下当前会话都有哪些 profiles，使用下面这条命令：
show profiles;

如果我们想要某一次查询的开销，可以使用：

# 查看最近一次查询的开销
show profile;
# 查询某个 Query_ID的查询开销
show profile for query N;
#不过SHOW PROFILE命令将被弃用,用 以下语句代替
select * from information_schema.profiling;

索引情况查询

查询冗余索引

select * from sys.schema_redundant_indexes;

查询未使用过的索引

select * from sys.schema_unused_indexes;

查询索引的使用情况

select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;

表相关

查询表的访问量

select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;

查询占用bufferpool较多的表

select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;

查看表的全表扫描情况

select * from sys.statements_with_full_table_scans where db='dbname';

语句 相关

监控SQL执行的频率

select db,exec_count,query from sys.statement_analysis
order by exec_count desc;

监控使用了排序的SQL

select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;

监控使用了临时表或者磁盘临时表的SQL

select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by

IO相关

查看消耗磁盘IO的文件

select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

InnoDB相关

行锁阻塞情况

select * from sys.innodb_lock_waits;

优化 超时 时间防止sleep

默认：8小时没请求就断开

可能导致过多sleep 进程的两个参数interactive_timeout、wait_timeout

交互式查看

show session variables like 'interactive_timeout';

mysql客户端
在这里插入图片描述
非交互式

show session variables like 'wait_timeout';

jdbc连接等
在这里插入图片描述

优化

set global wait_timeout=600;

在这里插入图片描述

[mysqld]
wait_timeout=600

Mysql 的too many connections解决办法

调大max_connections和减少超时时间 wait_timeout，防止一直 sleep不释放连接

 show variables like 'max_connections'; #默认151

考虑增加从服务器分散读压力
调大max_connections

show variables like 'max_connections';
show global status like 'Max_used_connections';

(Max_used_connections / max_connections) > 0.4

此时Max_used_connections =350 ，算出 max_co nnections=875

set global max_connections=875;
set session max_connections=875;

在这里插入图片描述

mysql的连接数保持时间太长

show global variables like 'wait_timeout';

在这里插入图片描述

set global wait_timeout=300;
set session wait_timeout=300;

在这里插入图片描述

[mysqld]
group_concat_max_len = 10240
# 最大睡眠时间
wait_timeout=300
# 超时时间设置
interactive_timeout = 500

重启 mysql 生效

sw ap 分区

cat /proc/sys/vm/swappiness

默认 60

vi /etc/sysctl.conf # 打开 系统配置文件
vm.swappiness=10
sysctl -p

vmstat 命令

报告关于内核线程、虚拟内存、磁盘、陷阱和 CPU 活动的统计信息

vmstat  2  #每隔2秒打印一次

swappiness参数值可设置范围在0到100之间。
此参数值越低，就会让Linux系统尽量少用swap分区，多用内存
参数值越高就是反过来，使内核更多的去使用swap空间
Ubuntu系统sw app iness默认值为60，表示的含义可以这样来理解，当剩余物理内存低于40%（40=100-60）时，开始使用swap分区
CentOS系统此参数的默认值是30