MySQL操作系统和硬件优化

常见瓶颈

          1.CPU饱和（发生在MySQL使用数据被装入内存，或者从磁盘上读取数据时）
          2.I/O饱和（发生在需要数据比内存多得多的时候）

内存

         拥有大量内存最终目的避免磁盘I/O
         随机I/O和顺序I/O
             随机I/O从缓存中获益最多
             顺序I/O比随机I/O快（需要做索引操作）
         缓存好处：多次写入，一次刷写  可以进行I/O合并
         预写日志（事务系统）：发生在内存中，将随机I/O转化为顺序I/O。
         写入从缓存中获益，把随机I/O转化为顺序I/O，由操作系统进行批次处理，刷写到磁盘上。
         工作集包含数据和索引，使用缓存单元统计。innodb从磁盘读取数据时，取出整页（16KB）数据，取出索引时，也会取出整个索引页面（16KB），放入缓冲池中

硬盘

         普通硬盘读取数据：1.将磁头移到磁盘表面正确的位置
                                             2.等待磁盘旋转，需要的数据移到磁头下面，直到所有需要的数据都经过磁头
         选取磁盘可以考虑的因素：存储容量   传输速度    访问时间（决定随机查找速度）    转速（决定随机查找的速度和顺序读取的速度）    物理尺寸

RAID

         RAID0：便宜，效率高，无冗余，需要磁盘数N
         RAID1：读取比RAID0块，复制了数据，有冗余性，写入较慢，需要磁盘数2
         RAID5：在安全，速度和开销之间进行了折中，需要磁盘数N+1
         RAID10：贵，快速，安全，需要磁盘数2N

         RAID50:适应于极多数据，需要磁盘数2(N+1)

         （还需要了解）

网络配置

         打开skip_name_resolve，不会进行任何DNS查找。失效或者缓慢的域名解析对MySQL是个问题。
         调整端口范围
         增加队列允许的连接数

文件系统

         ext3选项
         data=writeback 只有元数据写入会被记录。对innodb时安全的
         data=ordered   只记录元数据，但在写入元数据之前写入数据。比writeback慢，崩溃时安全
         data=journal   提供原子性日志行为，在数据写入之前被写入日志。通常不使用，开销比较高
         关闭记录访问时间(noatime)，会提高性能

交换

         当物理内存数量不能容纳数据时候，操作系统会把虚拟内存中数据写入磁盘上。只有操作系统知道特定虚拟内存地址是在物理内存还是磁盘上。
         操作系统对虚拟内存和I/O进行某些控制。可以把/proc/sys/vm/swappiness设置为0或者1.告诉内核只有在极端需要虚拟内存时才进行交换
         使用innodb_flush_method=0_DIRECT减轻I/O压力。直接I/O不会被缓存。操作系统不会把它看成增加文件缓存的原因。只对InnoDb有效。

操作系统状态

         1）使用vmstat

              

               Procs：r显示有多少进程正在等待CPU时间。b显示处于不可中断的休眠的进程数量。在等待I/O
               Memory：swpd显示被交换到磁盘的数据块的数量。未被使用的数据块，用户缓冲数据块，用于操作系统的数据块的数量
               Swap：操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1和s0最好是0
               Io：每秒从设备中读入b1的写入到设备b0的数据块的数量。反映了磁盘I/O           
               System：显示了每秒发生中断的数量(in)和上下文交换(cs)的数量
               Cpu：显示用于运行用户代码，系统代码，空闲，等待I/O的CPU时间

         2）使用iostat 

                iostat -dk 5 （查看tps和吞吐量）

               

                tps：该设备每秒的传输次数。“一次传输”意思是“一次I/O请求”。多个逻辑请求可能会被合并为“一次I/O请求”。“一次传输”请求的大小是未知的。

                KB_wrtn/s：每秒从设备（drive expressed）读取的数据量；

                KB_wrtn/s：每秒向设备（drive expressed）写入的数据量；

                kB_read：读取的总数据量；

                kB_wrtn：写入的总数量数据量；这些单位都为Kilobytes。

                iostat -d -k -x 5 （查看设备使用率（%util）和响应时间（await））

               

                rrqm/s,wrqm/s:每秒合并的写入和读取请求的数量。合并指操作系统接受了多个逻辑请求，并且合并为单个对实际设备的请求
                r/s,w/s:每秒发送给设备的读写请求的数量
                rsec/s,wsec/s:每秒发生的扇区读写的数量
                avgrq-sz：扇区中请求大小
                avgqu-sz：在设备队列中等待请求数量
                await：响应请求需要的毫秒数，包括队列时间和服务时间。一般地系统IO响应时间应该低于5ms
                svctm：从头到尾服务请求的时间，包括队列时间和设备实际用来满足请求的时间
                %util：请求发起期间，cpu使用百分比
                设备能服务的并发请求数：(r/s+w/s)*(svctm/1000)

CPU密集型

         vmstat的us列值会比较高，还有观察iostat的%util的磁盘利用率

I/O密集型

         vmstat中大量进城处于不可中断的休眠中(b)，wa列值较高,iostat中磁盘利用率饱和，磁盘不能满足读取请求（不一定，还需要具体分析）
         写入请求总能被缓冲或同步
         读取请求本质上是同步的。无法缓冲，读取会造成大部分I/O等待