mysql 数据库活跃线程数持续升高 造成数据库比较慢 mysql 线程数与cpu的关系

影响性能的几个方面：

1. 服务器硬件
2. 服务器系统
3. 数据库存储引擎的选择
4. 数据库参数配置
5. 数据库结构设计和SQL语句

一、服务器硬件

1、CPU的选择

我们的应用是否是CPU密集型的

对于cpu密集型的应用，我们需要加快sql语句的处理速度。由于mysql的sql语句处理是单线程的，因此我们需要更好的cpu，而不是更多的cpu。

我们系统的并发量如何

一个cpu同时只能处理一条sql语句。所以，高并发量的情况下，就需要更多的cpu而不是更快的cpu。

选择32位还是64位的cpu

64位已经是默认配置了。

2、内存的选择

内存的io要远高于磁盘，即便是SSD或者Fusion_IO。所以把数据缓存到内存中读取，可以大大提高性能。
常用的mysql引擎中，MyISAM把索引缓存到内存，数据不缓存。而InnoDB同时缓存数据和索引。

缓存不仅对读取有益，对写入也是可以优化的，我们可以通过缓存，将多次写入合并成一次写入操作。

怎么选择内存：主频尽量大，型号尽量相同，单条内存容量尽量打。

3、磁盘的选择

• 传统机械磁盘
• RAID增强传统机械磁盘
• 固态存储SSD和PCIe卡
• 网络存储NAS和SAN

如何选择传统机械硬盘

• 存储容量
• 传输速度
• 访问时间
• 主轴转速
• 物理尺寸：越小性能越高，存储空间越小

RAID增强

什么是RAID：把多个容量小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。

RAID 0，多磁盘串联。成本最低，数据容易丢失
RAID 1，镜像。磁盘利用率降低一半。
RAID5，分布式奇偶校验磁盘阵列
RAID10，分片镜像

等级	特点	是否冗余	盘数	读	写
RAID0	便宜，快速，危险	否	N	快	快
RAID1	高速读，简单，安全	有	2	快	慢
RAID5	安全，成本这种	有	N+1	快	取决于最慢的盘
RAID10	贵，高速，安全	有	2N	快	快

推荐RAID10

固态存储

相比机械磁盘，固态磁盘有更好的随机读写性能。
相比机械磁盘，固态磁盘能更好的支持并发。
相比机械磁盘，固态磁盘 更容易损坏

• SSD：
  1.使用SATA接口。可以替换传统磁盘而不需任何改变
  2.SATA接口的SSD同样支持RAID技术。注意SSD的RAID控制器与传统不同。
• PCI-E
  1.无法使用SATA接口，需要独特的驱动和配置
  2.价格相对于SSD要贵，但是性能比SSD更好

固态存储的使用场景：
1.适用于存在大量的随机I/O的场景。
2.适用于解决单线程负载的I/O瓶颈。

网络存储

SAN：通过光钎链接服务器，服务器可以将其当做硬盘使用。适合大量顺序读写
NAS：使用网络连接，通过基于文件的协议如NFS或SMB来访问。

网络存储适合的场景：

• 数据库备份

4、网络性能的影响

延迟、带宽（吞吐）

网络带宽的影响，不必多说。可能很多人认为数据库服务器与Web服务器的通信是在内网下的，带宽影响不大。其实在大促的情况下，我们有50台服务器，同时向数据库请求2M的数据，那么就需要100M的带宽了。
建议：

• 采用高性能和高带宽的网络接口设备和交换机。
• 对多个网卡进行绑定，增强可用性和带宽。
• 尽可能的进行网络隔离。

5、服务器硬件对性能的影响，总结

• cpu
  1、64位的cpu一定要工作在64位的系统下。
  2、对于并发比较高的场景，cpu的数量比频率重要
  3、对于cpu密集型场景和复杂SQL则频率越高越好。
• 内存
  1、选择主板所能使用的最高频率的内存
  2、内存的大小对性能很重要，所以尽可能的大
• I/O子系统
  PCIe–>SSD–>Raid10–>磁盘–>SAN

二、服务器系统

MySql适合的操作系统

• Windows
• FreeBSD
• Solaris
• Linux

下面的内容以CentOs系统为例

系统参数优化

内核相关参数（/etc/sysctl.conf）

网络相关
- net.core.somaxconn=65535

对于一个TCP连接来说，服务器端和客户端需要进行三次握手来建立网络的连接。当三次握手成功之后，我们可以用netstat命令查看端口的状态由监听转变成了连接，接着该连接就可以传输数据了。对于一个监听状态的端口，都会有自己的监听队列，而该参数就决定了监听队列的最大长度。

- net.core.netdev_max_backlog=65535
- net.ipv4.tcp_max_syn_backlog=65535

加快tcp连接的回收

• net.ipv4.tcp_fin_timeout=10
• net.ipv4.tcp_tw_reuse=1
• net.ipv4.tcp_tw_recycle=1

tcp连接接收和发送缓冲区大小的默认值和最大值

• net.core.wmem_default = 87380
• net.core.wmem_=16777216
• net.core.rmem_default=87380
• net.core.rmem_max=16777216

检测占用的tcp连接是否已经失效

• net.ipv4.tcp_keepalive_time=120 发送时间
• net.ipv4.tcp_keepalive_intvl=30 没有返回再次发送的间隔
• net.ipv4.tcp_keepalive_probes=3 发送几次

内存相关参数

• kernel.shmmax = 4294967295

注意：
1、这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个的Innodb缓冲池大小
2、这个值的大小对于64位linux系统，可取的最大值为物理内存值-1byte，建议值为大于物理内存的一半，一般取值大于Innodb缓冲池大小即可。

• vm.swappiness=0
  这个参数当内存不足时会对性能产生比较明显的影响。

拓展：
Linux系统内存交换区：在linux系统安装时都会有一个特殊的磁盘分区，称之为系统交换分区。如果我们使用free-m在系统中查看可以看到类似下面内容，其中swap就是交换分区。

当操作系统因为没有足够的内存时就会将一些虚拟内存写到磁盘的交换区中，这样就会发生内存交换

在MySQL服务器上是否要使用交换分区有一些争议：

-主张 完全禁用 的风险：
1、降低操作系统的性能
2、容易造成内存溢出，崩溃，或都被操作系统kill掉

结论：在MySQL服务器上保留交换分区，但是要控制何时使用。vm.swappiness=0就是告诉Linux内核除非虚拟内存完全满了，否则不要使用交换区。

增加资源限制（/etc/security/limit.conf）
这个文件实际上是linux PAM也就是插入式认证模块的配置文件。
其中重要的配置是：打开文件数的限制。

• * soft nofile 65535
• * hard nofile 65535

其中：*表示对所有用户有效，soft指当前系统生效的设置，hard表明系统中所能设定的最大值，nofile表示所限制的资源是打开文件的最大数目，65535就是限制的数量。

结论：把可打开的文件数量增加到65535，以保证可以打开足够多的文件句柄。
注意：这个文件的修改需要重启系统才可以生效。

磁盘调度策略（/sys/block/devname/queue/scheduler）

• noop anticipatory deadline [cfq]
  查看：cat /sys/block/sda/queue/scheduler

noop（电梯式调度策略）

noop实现了一个FIFO队列，它像电梯的工作方法一样对I/O请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一介质。

deadline（截止时间调度策略）
对数据库最好。

修改磁盘调度策略

echo deadline > /sys/block/sda/queue/scheduler

文件系统的选择

windows：
- FAT
- NTFS

linux：

• EXT3
• EXT4
• XFS（性能更高）

三、存储引擎

MySQL体系结构

注意：存储引擎是针对于表的而不是针对于库的（一个库中的不同表可以使用不同的存储引擎）

MyISAM

MySql5.5之前版本默认存储引擎。
MyISAM存储引擎表有MYD和MYI组成。

特性：

• 并发性与锁级别：对读写混合的并发性并不会太好
• 表损坏修复
• 支持的索引类型：全文索引
• 支持压缩：压缩表只支持读操作，不支持写操作

适用场景：

• 非事务型应用
• 只读类应用
• 空间类应用

★Innodb

系统表空间 和 独立表空间的选择

比较：

• 系统表空间无法简单的收缩文件大小
• 独立表空间可以通过optimize table命令收缩系统文件
• 系统表空间会产生IO瓶颈
• 独立表空间可以同时向多个文件刷新数据

建议：对Innodb使用独立表空间

如何把原来存在于系统表空间中的表转移到独立表空间中？

• 1、使用mysqldump导出所有数据库表数据
• 2、停止MySql服务，修改参数，并删除Innodb相关文件
• 3、重启MySql服务，重建Innodb系统表空间
• 4、重新导入数据

Innodb存储引擎的特性

• 事务性存储引擎
• 完全支持事务的ACID特性
• Redo Log 和 Undo Log
• Innodb支持行级锁（MyISAM支持表级锁），行级锁可以最大程度的支持并发，行级锁是由存储引擎层实现的

Innodb适用场景
基本上只要不使用到Innodb不包含的功能，基本上都使用Innodb作为默认存储引擎（例如MyISAM的空间应用，全文索引。但实际上MySql5.7之后Innodb已经支持了前面两点）。