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

生产环境 更改表结构

在服务端中查看连接信息

查看连接数

show global status like 'Thread%';

Thread_cached: 线程 缓存中的线程数
Thread_connected: 当前打开的连接数
Thread_created:为 处理连接而创建的线程数
Thread_running: 未休眠的线程数

优化Threads_created

Thread_created表示 创建过的 线程数、Thread_created 过大----->说明 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;

索引情况查询

1. 查询冗余索引

select * from sys.schema_redundant_indexes;

2. 查询未使用过的索引

select * from sys.schema_unused_indexes;

3. 查询索引的使用情况

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

表相关

1. 查询表的访问量

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;

2. 查询占用bufferpool较多的表

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

3. 查看表的全表扫描情况

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

语句相关

1. 监控SQL执行的频率

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

2. 监控使用了排序的SQL

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

3. 监控使用了临时表或者磁盘临时表的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

1. 考虑增加从服务器分散读压力

2. 调大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_connections=875

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

3. 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生效

swap分区

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系统swappiness默认值为60，表示的含义可以这样来理解，当剩余物理内存低于40%（40=100-60）时，开始使用swap分区
CentOS系统此参数的默认值是30

建议

服务器的Linux系统的swappiness参数设置为10。设置为100可能会影响整体性能
如果内存充足，就可以将这个值设置很低，甚至为0，以避免系统进行swap而影响性能