Mysql调优：

原创

a591244761 2013-07-31 08:48:33 博主文章分类：mysql双机热备和调优 ©著作权

©著作权归作者所有：来自51CTO博客作者a591244761的原创作品，请联系作者获取转载授权，否则将追究法律责任

Mysql调优：

IO密集型，最需要内存来减少IO。

使用缓存，已经使用了就适当增加，文件限制、连接限制、队列限制都打开。
索引调优。

一、日志文件

日志分类：
  查询日志强烈建议关闭。任何的查询SQL操作都会被记录，非常浪费宝贵的IO资源
  二进制日志重要的场合都是打开。它是数据备份、增量备份、数据恢复和MySQL复制基本条件。
  错误日志仅仅是发生错误的时候才会使用一点IO，当发生错误的时候，俄可以用于排错，所以非常必要打开。
  慢查询日志当某个SQL语句超过指定的时间，都会被记录到该日志中，主要是用于性能调优，发现性能瓶颈的地方。

查看服务端支持的参数和默认参数值
# /usr/local/mysql/libexec/mysqld --verbose --help

mysql> show variables;

二进制日志

mysql> show variables like '%binlog%';
+-----------------------------------------+------------+
| Variable_name                           | Value      |
+-----------------------------------------+------------+
| binlog_cache_size                       | 32768      |
| binlog_direct_non_transactional_updates | OFF        |
| binlog_format                           | STATEMENT  |
| innodb_locks_unsafe_for_binlog          | OFF        |
| max_binlog_cache_size                   | 4294963200 |
| max_binlog_size                         | 1073741824 |
| sync_binlog                             | 0          |
+-----------------------------------------+------------+

binlog_cache_size 当数据库支持事务存储引擎的时候，就会用它临时缓存事务所产生的二进制记录。每个连接独享一个。如果经常出现多语句的事务操作，可以适当调整大一点。一般使出较多的数据库，建议设置为1-2M。不能太大，因为如果出现故障（mysql或者系统本身），cache中的日志没来得及写到日志文件中就会有可能数据丢失。

max_binlog_cache_size 所有连接的binlog_cache_size总和不能超过该值。

max_binlog_size 单个二进制日志文件的最大尺寸。默认值1G，最大值也是1G。

sync_binlog 默认是0，可以指定为任意整数N
0，代表每一秒中就binlog_cache中的日志记录保存到日志文件中，但并不进行刷盘操作（sync），也就说记录有可能还在文件系统缓存中。如果是mysqld进程crash不会导致记录丢失，但是系统crash就会导致一秒的事务丢失。这是性能最好的设定

1，代表每当完成了1个事务就会马上把该事务对应的二进制记录写到日志文件中，并且进行刷盘操作，保证记录记录到磁盘，而不在文件系统缓存中。对IO资源使用率非常高，也是最影响性能，但保证了数据安全。

N，代表每当完成了N个事务就会....(同1)

建议：master或者是只有一台服务器的时候，强烈建议设定为1，其他不是很重要的环境，例如slave服务器，就可以设定为0

查看当前会话中已经使用了多少binlog_cache
mysql> show status like 'binlog%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 0     |
+-----------------------+-------+

打开二进制日志文件功能

log-bin=/data/mysqld-bin <---强烈建议具体指定文件的名字和路径
log-bin-index=/data/mysqld-bin-index

静态参数，修改之后必须重启mysqld

慢查询日志

打开该功能：
slow-query-log
slow_query_log_file=/data/mysqld-slow.log
log-slow-admin-statements
log-queries-not-using-indexes
long_query_time=2 默认10秒

该版本支持动态修改：
mysql> set global log_slow_queries=1;

# mysqldumpslow --help

# mysqldumpslow -s t -r -t 10 /data/mysqld-slow.log

Reading mysql slow query log from /data/mysqld-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  alter table squid add index(url(N))

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  create index ipidx on squid(ip)

连接线程池缓存：

thread_cache_size 可以缓存的连接数。0关闭，可以设定任意正整数的数字。
  一般建议长连接（持久连接）32以下，一般是数据仓库环境，并发量一般很低
   短连接 32－64左右，一般在线事务处理（web应用），并发量一般很高
  最终设定值还是要参考状态数据。

thread_stack 默认192K，每个线程连接上来，马上分配192K内存作为该线程使用。一般不需要对该值修改。

临时打开：
mysql> set global thread_cache_size=8;

[mysqld]
thread_cache_size=8

短连接，最大一般设定为500~800的并发连接。

连接线程命中率：
( Connections - Threads_created ) / Connections * 100 %

建议命中率应该达到95%以上

=======================================================================

查询缓存query cache

对每个查询语句进行hash运算(对大小写敏感)并且作为key，把对应的查询结果放到query_cache，但下次重复执行相同的查询（hash运算结果一样），那么直接从query_cache取出结果，省去后面的所有操作，最重要的是不需要涉及磁盘IO。如果qeury_cache中的查询结果涉及到的表发生更改，这些更改包括：增加、修改、删除记录，表结果变化都会导致query_cache中的结果失效，就会cache缓存记录删除。

马上生效：
mysql> set global query_cache_size=16*1024*1024;

query_cache_size=16M

mysql> select num from qq.user where password='123';
Empty set (2 min 9.88 sec)

mysql> select num from qq.user where password='123';
Empty set (0.00 sec)

什么场合适合使用query_cache
thread <---保存用户发表的帖子，不适合
user用户表  ，不适合，只要有用户修改密码，修改昵称，注册新用户都会导致query_cache失效
user_type保存用户级别定义表 <---记录相对稳定，但是查询非常频繁，适合使用
   将军 10000
   副将 8000
   ....

可以通过两个提示，决定是否是否使用query_cache
SQL_CACHE, SQL_NO_CACHE

不会去保存或者查看query_cache
mysql> select SQL_NO_CACHE num from qq.user where password='123';

Qcache_lowmem_prunes 由于内存空间低导致需要删除一部分旧的缓存数据才能缓存该次查询的结果

计算命中率： 90%以上

Qcache_hits/ ( Qcache_inserts + Qcache_hits ) * 100 %

表缓存：

以上设定还首先于
mysql> show variables like 'open_file%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| open_files_limit | 204800 |
+------------------+--------+

根据数据库的表的规模来设定

mysql> show status like 'open_table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Open_table_definitions | 16    |
| Open_tables            | 11    |
+------------------------+-------+

=========================================================================

1297557818.709 1830 192.168.1.3 TCP_MISS/200 324 GET http://www.hxuming.com/ip.php - DIRECT/205.209.136.218 text/html

time sip status target
timestamp char(17) smallint varchar(256)

# time awk '{gsub(/.*\//,"",$4);print strftime("%Y-%m-%d %H:%M:%S",$1)"\t"$3"\t"$4"\t"$7}' access.log.1 > done.log

mysql> create table log (
     id int unsigned not null primary key auto_increment,
    time timestamp,
    sip char(17),
    status smallint,
    target varchar(256) );

mysql> load data local infile '/share/weeken/mysql_tune/done.log'
into table test.log fields terminated by '\t'
(time,sip,status,target);

mysql> explain select * from log where sip='192.168.1.237' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1573125
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from log where id=11001\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:

索引调优：

特别针对MyISAM存储引擎。

mysql> create index idx_sip on test.log(sip);

mysql> explain select * from log where sip='192.168.1.237' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log
         type: ref
possible_keys: idx_sip
          key: idx_sip
      key_len: 52
          ref: const
         rows: 61252
        Extra: Using where
1 row in set (0.00 sec)

添加索引：
mysql> alter table test.log add index(target(128));

有些查询是不支持索引的：
前缀模糊匹配不支持索引
mysql> select count(*) from test.log where target like '%http://hi.baidu';

后缀模糊匹配，支持索引：
mysql> select count(*) from test.log where target like 'http://hi.baidu%';

索引缓存的调优key_buffer调优：

可以考虑调整key_cache_division_limit,把一部分的key_buffer_size作为hot_area,保留经常被查询的索引数据，避免由于空间不足导致把命中率非常高的数据清除掉。

key_buffer使用百分比：
Key_blocks_used / (Key_blocks_used + Key_blocks_unused ) * 100%
要求：99%以上才算合理分配

读命中率：
Key_read_requests / (Key_read_requests + Key_reads ) * 100%
要求95%以上才算合理

自定key_buffer的区域使用：

mysql> set global key_buffer_size=16*1024*1024;

自定义：一个hot_key区域，专门用于保存指定的某个表的索引

1、设定该区域的大小和名字
mysql> set global hot_key.key_buffer_size=4*1024*1024;

mysql> select @@hot_key.key_buffer_size;
+---------------------------+
| @@hot_key.key_buffer_size |
+---------------------------+
| 4194304 |
+---------------------------+

2、定义该区域可以用于保存哪个表的索引：

3、加载相应的索引数据到指定key中：

树根－－枝干－－－叶子（最后能够定位到需要查找记录所在数据文件具体位置）

常见的一些性能测试工具：
super-smack <---比较好
mysqlslap

mysqlard <---

phpmyadmin <---不错的

Innodb
MyISAM