mysql分布式思维（八）- my优化

一、上一节未完内容:
   1.MyISAM 表锁机制优化建议
      1.1 缩短锁定时间
            a)尽量减少大的复杂的query，将复杂的拆分成多个简单的
     b)尽可能高效的索引
     c)myisam存储引擎表只存放必要的信息，控制字段类型
      1.2分离并行操作
           有参数concurrent_in,1,2三个值
      1.3 读写优先级
           有参数low_priority_updates=1 mysql尽量先处理读请求
    如果写要求高不用设置该参数

   2.Innodb 行锁优化建议
      a)尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
      b)合理设计索引，尽量缩小锁的范围
      c)尽可能较少检索条件，避免间隙锁
      d)尽量控制事务大小，减少锁定资源量和时间长度。
      e)尽可能低级别事务隔离

    3.系统锁定争用情况的查看
      表级锁查看
      show status like 'table%';
        Table_locks_immediate---->产生表级锁定的次数
 Table_locks_waited  ---->表级锁定争用而发生等待的次数

     行级锁查看
         show status like 'innodb_row_lock%';
    5个参数状态分析
     | Innodb_row_lock_current_waits | 0
            | Innodb_row_lock_time          | 50896
            | Innodb_row_lock_time_avg      | 10179
            | Innodb_row_lock_time_max      | 16198
            | Innodb_row_lock_waits         | 5

   create table innodb_monitor(a int)engine=innodb;show innodb status;

二、mysql server 安装优化
    1.选择合适的二进制发行版
        除了官方提供的MySql AB 之外有很多第三方的也很不错。
 第三方比较出名的percona,特别是针对innodb存储引擎做了很多的优化并提供了很多的性能诊断工具

     2.可以通过源码安装
         自己去编译
        ----->进入源码目录执行 ./configure -help ---->可以查看参数可以自己的需要制定参数设置去编译源代码
               ----->icc(interl c compile)编译器  --->多算法和很多方面做了更多的优化

三、mysql 日志设置优化
       1.核心是binlog
            --->相关参数　　show variables like '%binlog%';
                       binlog_cache_size(容纳二进制日志sql语句缓存的大小)
                          ---->binlog_cache_use/binlog_cache_disk_use
                       binlog_direct_non_transactional_updates
                       binlog_format
                       binlog_stmt_cache_size
                       innodb_locks_unsafe_for_binlog
                       max_binlog_cache_size
                          ---->能使用的最大的Cache大小
     ---->Multi-statement transaction required more than max_binlog_cache_ssize byte of storage的错误

                       max_binlog_size
                  ----->日志的最大值 一般512M or 1G  大小并非严格
                       max_binlog_stmt_cache_size
                       sync_binlog(把binlog-cache中的数据同步到磁盘的策略)  在master/slave的时候很重要
                  ----->对系统来说至关重要
    ---->0,n

      ---->在复制环境中有8个参数可以配置
             Binlog_Do_DB  设置哪些数据库的Schema 要记录到binlog
      Binlog_Ignore_DB  不要记录到binlog
      Replicate_Do_DB
      Replicate_Ignore_DB
      Replicate_Do_Table
      Replicate_Ignore_Table
      Replicate_Wild_Do_Table支持通配符
      Replicate_Wild_Ignore_Table支持通配符

 2.Slow Query log
      ---->show variables like 'log_slow%';
               show variables like 'long_query%';

四、Query Cache
       1.了解query Cache 的利弊
             ---->hash运算    当有上万的查询来时，cpu损耗不能忽略不计
      ---->Cache失效问题  --->数据修改频繁
      ----->缓存的是ResultSet (一级缓存而不是二级缓存，可能重复缓存数据)
      ----->show variables like '%query_cache%';
           have_query_cache
                  query_cache_limit 该参数要重视
                  query_cache_min_res_unit  该参数要重视
                   query_cache_size
                   query_cache_type
                    query_cache_wlock_invalidate
  ---->show status like 'Qcache%';
      Qcache_free_blocks
                   Qcache_free_memory
 Qcache_hits
 Qcache_inserts
 Qcache_lowmem_prunes
 Qcache_not_cached
 Qcache_queries_in_cache
 Qcache_total_blocks
     通过以上参数可以了解Cache的使用情况

     2.第三方的插件  flushCache 可以把mysql的一级缓存改造成二级缓存

 

 

   五、其它关于server的优化
       1.max_connections;   500到800  只要mysql主机性能允许可以更大
       2.max_user_connections;
       3.net_buffer_length
       4.thread_cache_size  50到100之间
       5.thread_stack
          show variables like 'thread%';
   show status like 'connections';
           show status like '%thread%';
     Thread_Cache_hit = (connections-Thread_created)/connections *100%;
      6.join_buffer_size ,sort_buffer_size 要注意