Open guanhui07 opened 5 years ago
show variables like 'innodb_file_per_table 停止MYsql 服务器，修改参数（my.cnf加入innodb_file_per_table），并删除Inoodb相关文件（可以重建Data目录）。
支持行级锁 InnoDB支持行级锁。
行级锁可以最大程度地支持并发。
行级锁是由存储引擎层实现的。
将table_name加表级锁命令：lock table table_name write; 写锁会阻塞其它用户对该表的‘读写’操作，直到写锁被释放：unlock tables； 锁的开销越大，粒度越小，并发度越高。 表级锁通常是在服务器层实现的。 行级锁是存储引擎层实现的。innodb的锁机制，服务器层是不知道的
5.4 阻塞和死锁 （1）阻塞是由于资源不足引起的排队等待现象。 （2）死锁是由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放。
参考条件： 事务 备份(Innobd免费在线备份) 崩溃恢复 存储引擎的特有特性 总结:Innodb大法好。
sort_buffer_size #定义了每个线程排序缓存区的大小，MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存（直接分配该参数的全部内存）； join_buffer_size #定义了每个线程所使用的连接缓冲区的大小，如果一个查询关联了多张表，MySQL会为每张表分配一个连接缓冲，导致一个查询产生了多个连接缓冲； read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小，MySQL有查询需要时会为其分配内存，其必须是4k的倍数； read_rnd_buffer_size #索引缓冲区大小，MySQL有查询需要时会为其分配内存，只会分配需要的大小。
如何为缓存池分配内存： Innodb_buffer_pool_size，定义了Innodb所使用缓存池的大小，对其性能十分重要，必须足够大，但是过大时，使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中；
存储、主机和操作系统方面：
主机架构稳定性；
I/O规划及配置；
Swap交换分区；
OS内核参数和网络问题。
应用程序方面：
应用程序稳定性；
SQL语句性能；
串行访问资源；
性能欠佳会话管理；
这个应用适不适合用MySQL。
数据库优化方面：
内存；
数据库结构（物理&逻辑）；
实例配置。
1）MySQL
2）msyqladmin：MySQL客户端，可进行管理操作
3）mysqlshow：功能强大的查看shell命令
4）show [SESSION | GLOBAL] variables：查看数据库参数信息
5）SHOW [SESSION | GLOBAL] STATUS：查看数据库的状态信息
6）information_schema：获取元数据的方法
7）SHOW ENGINE INNODB STATUS：Innodb引擎的所有状态
8）SHOW PROCESSLIST：查看当前所有连接session状态
9）explain：获取查询语句的执行计划
10）show index：查看表的索引信息
11）slow-log：记录慢查询语句
12）mysqldumpslow：分析slowlog文件的
1）Zabbix：监控主机、系统、数据库（部署zabbix监控平台）
2）pt-query-digest：分析慢日志
3）MySQL slap：分析慢日志
4）sysbench：压力测试工具
5）MySQL profiling：统计数据库整体状态工具
6）Performance Schema：MySQL性能状态统计的数据
7）workbench：管理、备份、监控、分析、优化工具（比较费资源）
1）show processlist；
2）explain select id ,name from stu where name='clsn'; # ALL id name age sex；
select id,name from stu where id=2-1 函数 结果集>30；show index from table；
3）通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题；
4）show status like '%lock%'; # 查询锁状态
kill SESSION_ID; # 杀掉有问题的session。
1）查看slowlog，分析slowlog，分析出查询慢的语句；
2）按照一定优先级，一个一个排查所有慢语句；
3）分析top SQL，进行explain调试，查看语句执行时间；
4）调整索引或语句本身。
cpu: vmstat、sar top、htop、nmon、mpstat；
mem: free、ps-aux；
IO设备（磁盘、网络）： iostat、ss、netstat、iptraf、iftop、lsof；
Cpu负载高，IO负载低：
1）内存不够；
2）磁盘性能差；
3）SQL问题--->去数据库层，进一步排查SQL 问题；
4）IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）；
5）tps过高：大量的小数据IO、大量的全表扫描。
IO负载高，Cpu负载低：
1）大量小的IO写操作：
autocommit，产生大量小IO；IO/PS，磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的IO次数。
2）大量大的IO 写操作：SQL问题的几率比较大
IO和cpu负载都很高：
硬件不够了或SQL存在问题。
定位问题点吮吸：硬件-->系统-->应用-->数据库-->架构（高可用、读写分离、分库分表）。
根据数据库类型，主机CPU选择、内存容量选择、磁盘选择：
1）平衡内存和磁盘资源；
2）随机的I/O和顺序的I/O；
3）主机 RAID卡的BBU（Battery Backup Unit）关闭。
CPU的两个关键因素：核数、主频
根据不同的业务类型进行选择：
1）CPU密集型：计算比较多，OLTP - 主频很高的cpu、核数还要多
2）IO密集型：查询比较，OLAP - 核数要多，主频不一定高的
1）根据存储数据种类的不同，选择不同的存储设备；
2）配置合理的RAID级别（raid5、raid10、热备盘）；
3）对与操作系统来讲，不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas、sata）。
4）raid卡：
主机raid卡选择： 实现操作系统磁盘的冗余（raid1）； 平衡内存和磁盘资源； 随机的I/O和顺序的I/O； 主机raid卡的BBU（Battery Backup Unit）要关闭。
使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）
MySQL尽量避免使用swap。
阿里云的服务器中默认swap为0。
IO: raid、no lvm、ext4或xfs、ssd、IO调度策略。
Swap调整(不使用swap分区) /proc/sys/vm/swappiness的内容改成0（临时），/etc/sysctl. conf上添加vm.swappiness=0（永久）
这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。
修改MySQL的配置参数innodbflush method，开启O_DIRECT模式：
这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。
值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多。
IO调度策略：
系统参数调优 vim/etc/sysctl.conf
net.ipv4.ip_local_port_range = 1024 65535：# 用户端口范围
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
fs.file-max=65535：# 系统最大文件句柄，控制的是能打开文件最大数量
SQL优化方向：执行计划、索引、SQL改写。
架构优化方向：高可用架构、高性能架构、分库分表。
https://cloud.tencent.com/developer/article/1004367
guanhui07
commented
5 years ago guanhui07
commented
5 years ago
show variables like 'innodb_file_per_table 停止MYsql 服务器，修改参数（my.cnf加入innodb_file_per_table），并删除Inoodb相关文件（可以重建Data目录）。
支持行级锁 InnoDB支持行级锁。
行级锁可以最大程度地支持并发。
行级锁是由存储引擎层实现的。
将table_name加表级锁命令：lock table table_name write; 写锁会阻塞其它用户对该表的‘读写’操作，直到写锁被释放：unlock tables； 锁的开销越大，粒度越小，并发度越高。 表级锁通常是在服务器层实现的。 行级锁是存储引擎层实现的。innodb的锁机制，服务器层是不知道的
5.4 阻塞和死锁 （1）阻塞是由于资源不足引起的排队等待现象。 （2）死锁是由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放。
参考条件： 事务 备份(Innobd免费在线备份) 崩溃恢复 存储引擎的特有特性 总结:Innodb大法好。
sort_buffer_size #定义了每个线程排序缓存区的大小，MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存（直接分配该参数的全部内存）； join_buffer_size #定义了每个线程所使用的连接缓冲区的大小，如果一个查询关联了多张表，MySQL会为每张表分配一个连接缓冲，导致一个查询产生了多个连接缓冲； read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小，MySQL有查询需要时会为其分配内存，其必须是4k的倍数； read_rnd_buffer_size #索引缓冲区大小，MySQL有查询需要时会为其分配内存，只会分配需要的大小。
如何为缓存池分配内存： Innodb_buffer_pool_size，定义了Innodb所使用缓存池的大小，对其性能十分重要，必须足够大，但是过大时，使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中；
存储、主机和操作系统方面：
主机架构稳定性；
I/O规划及配置；
Swap交换分区；
OS内核参数和网络问题。
应用程序方面：
应用程序稳定性；
SQL语句性能；
串行访问资源；
性能欠佳会话管理；
这个应用适不适合用MySQL。
数据库优化方面：
内存；
数据库结构（物理&逻辑）；
实例配置。
1）MySQL
2）msyqladmin：MySQL客户端，可进行管理操作
3）mysqlshow：功能强大的查看shell命令
4）show [SESSION | GLOBAL] variables：查看数据库参数信息
5）SHOW [SESSION | GLOBAL] STATUS：查看数据库的状态信息
6）information_schema：获取元数据的方法
7）SHOW ENGINE INNODB STATUS：Innodb引擎的所有状态
8）SHOW PROCESSLIST：查看当前所有连接session状态
9）explain：获取查询语句的执行计划
10）show index：查看表的索引信息
11）slow-log：记录慢查询语句
12）mysqldumpslow：分析slowlog文件的
1）Zabbix：监控主机、系统、数据库（部署zabbix监控平台）
2）pt-query-digest：分析慢日志
3）MySQL slap：分析慢日志
4）sysbench：压力测试工具
5）MySQL profiling：统计数据库整体状态工具
6）Performance Schema：MySQL性能状态统计的数据
7）workbench：管理、备份、监控、分析、优化工具（比较费资源）
1）show processlist；
2）explain select id ,name from stu where name='clsn'; # ALL id name age sex；
select id,name from stu where id=2-1 函数 结果集>30；show index from table；
3）通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题；
4）show status like '%lock%'; # 查询锁状态
kill SESSION_ID; # 杀掉有问题的session。
1）查看slowlog，分析slowlog，分析出查询慢的语句；
2）按照一定优先级，一个一个排查所有慢语句；
3）分析top SQL，进行explain调试，查看语句执行时间；
4）调整索引或语句本身。
cpu: vmstat、sar top、htop、nmon、mpstat；
mem: free、ps-aux；
IO设备（磁盘、网络）： iostat、ss、netstat、iptraf、iftop、lsof；
Cpu负载高，IO负载低：
1）内存不够；
2）磁盘性能差；
3）SQL问题--->去数据库层，进一步排查SQL 问题；
4）IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）；
5）tps过高：大量的小数据IO、大量的全表扫描。
IO负载高，Cpu负载低：
1）大量小的IO写操作：
autocommit，产生大量小IO；IO/PS，磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的IO次数。
2）大量大的IO 写操作：SQL问题的几率比较大
IO和cpu负载都很高：
硬件不够了或SQL存在问题。
定位问题点吮吸：硬件-->系统-->应用-->数据库-->架构（高可用、读写分离、分库分表）。
根据数据库类型，主机CPU选择、内存容量选择、磁盘选择：
1）平衡内存和磁盘资源；
2）随机的I/O和顺序的I/O；
3）主机 RAID卡的BBU（Battery Backup Unit）关闭。
CPU的两个关键因素：核数、主频
根据不同的业务类型进行选择：
1）CPU密集型：计算比较多，OLTP - 主频很高的cpu、核数还要多
2）IO密集型：查询比较，OLAP - 核数要多，主频不一定高的
1）根据存储数据种类的不同，选择不同的存储设备；
2）配置合理的RAID级别（raid5、raid10、热备盘）；
3）对与操作系统来讲，不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas、sata）。
4）raid卡：
使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）
MySQL尽量避免使用swap。
阿里云的服务器中默认swap为0。
IO: raid、no lvm、ext4或xfs、ssd、IO调度策略。
Swap调整(不使用swap分区) /proc/sys/vm/swappiness的内容改成0（临时），/etc/sysctl. conf上添加vm.swappiness=0（永久）
这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。
修改MySQL的配置参数innodbflush method，开启O_DIRECT模式：
这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。
值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多。
IO调度策略：
系统参数调优 vim/etc/sysctl.conf
net.ipv4.ip_local_port_range = 1024 65535：# 用户端口范围
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
fs.file-max=65535：# 系统最大文件句柄，控制的是能打开文件最大数量
SQL优化方向：执行计划、索引、SQL改写。
架构优化方向：高可用架构、高性能架构、分库分表。