MySQL server安装前后都需要做哪些优化

很多同学咨询我安装MySQL的时候都需要做哪些基础优化呢，本文简单总结下从os、MySQL server 一些常规优化

1、硬件部分

   1、建议单独跑MySQL server的服务器 使用ssd磁盘，提高磁盘的io

   2、建议选择使用RAID 10 而非RAID 5    RAID 10 虽然浪费了一部分磁盘空间，但是比RAID5 的IO能力提高不少

2、OS部分

   1、建议使用deadline/noop这两种I/O调度器，系统默认的为cfq

    2、建议使用xfs文件系统、高并发情况下凸显出其性能优势

3、系统内核优化

    1、将net.ipv4.tcp_tw_recycle设置为1

    2、将net.ipv4.tcp_tw_reuse 设置为1
          将以上两个参数开启，可快速减少在TIME-WAIT状态TCP连接数，提高TCP效率

    3、将vm.dirty_background_ratio设置为5
    4、将vm.dirty_ratio设置为10     

       vm.dirty_background_ratio:这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时（如5%）就会触发pdflush/flush/kdmflush等后台回写进程运行，将一定缓存的脏页异步地刷入外存
        vm.dirty_ratio而这个参数则指定了当文件系统缓存脏页数量达到系统内存百分之多少时（如10%），系统不得不开始处理缓存脏页（因为此时脏页数量已经比较多，为了避免数据丢失需要将一定脏页刷入外存）；在此过程中很多应用进程可能会因为系统转而处理文件IO而阻塞。

    5、将vm.swappiness设置为0  可以降低swapping的延迟(前提是确保机器不会出现内存不足的情况，若出现可能会被oom掉)

4、MySQL server参数优化

    1、建议使用innodb存储引擎、MySQL5.5之后的默认存储引擎是innodb引擎，可满足95%以上用户需求

    2、建议调整innodb_buffer_pool_size的大小，主要作用是缓存innodb表的索引，数据，插入数据时的缓冲默认为128M，如果是单独MySQL服务器，建议设置为物理内存的70%--80%左右。
        并不是设置的越大越好。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。 
        如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。例如：数据大小为50M,那么给这个值分配             innodb_buffer_pool_size＝64M就够了

    3、建议调整innodb_flush_log_at_trx_commit的值为2   默认为1
            0: Write the log buffer to the log file and flush the log file every second,but do nothing at transaction commit.
            1: the log buffer is writen out to the log file at each transaction commit and the flush to disk operation is performed on the log file
            2: the log buffer is writen out to the file at each commit,but the flush to disk operation is not performed on it.

    4、建议调整sync_binlog为10左右  默认值是0，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

    当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去

    5、 建议设置 innodb_data_file_path = ibdata1:1G:autoextend  用来指定innodb tablespace文件

    6、建议设置inodb_log_file_size = 256M
        mysql 5.5以前innodb的logfile最大设置为4GB,在5.6以后的版本中logfile最大的可以设为512GB.
innodb的logfile就是事务日志，用来在 mysqlcrash后的恢复.所以设置合理的大小对于mysql的性能非常重要

    7、建议设置innodb_log_files_in_group = 2

    8、建议设置innodb_file_per_table = 1 使用单独表空间易维护
            1. 如果使用软链接将大表分配到不同的分区上，易于管理数据文件
            2. 易于监控解决IO资源使用的问题
            3. 易于修复和恢复损坏的数据
            3.1 相互独立的，不会影响其他innodb表
            3.2 导出导入只针对单个表，而不是整个共享表空间
            4. 解决单个文件大小的限制
            5. 对于大量的delete操作，更易于回收磁盘空间
            6. 碎片较少，易于整理optimize table
            7. 易于安全审计
            8. 易于备份

    9、建议设置long_query_time =1 用于查询分析慢日志

    10、建议设置query_cache_type=0，建议关闭缓存

    11、建议设置innodb_flush_method = O_DIRECT
            这个参数控制着innodb数据文件及redo log的打开、刷写模式。 
            有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT 
            默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer 
            为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件 
            为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log。 
            在类unix操作系统中，文件的打开方式为O_DIRECT会最小化缓冲对io的影响，该文件的io是直接在用户空间的buffer上操作的，
             并且io操作是同步的，因此不管是read()系统调用还是write()系统调用，数据都保证是从磁盘上读取的

   12、建议设置binlog_format=row   默认为statement，强烈建议设置为row模式 记录每一条记录的修改，有利于主从同步


如若写的不对的地方，欢迎拍砖！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987453/viewspace-2149392/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29987453/viewspace-2149392/