Mysql调优：-白红宇

Mysql调优：

阅读量：6767 次

发布时间：2019-06-26

本文共 11272 字，大约阅读时间需要 37 分钟。

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> show variables like '%slow%';

+---------------------+-----------------------+

| Variable_name | Value |

+---------------------+-----------------------+

| log_slow_queries | ON |

| slow_launch_time | 2 |

| slow_query_log | ON |

| slow_query_log_file | /data/mysqld-slow.log |

+---------------------+-----------------------+

该版本支持动态修改：

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),

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),

Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N

SET timestamp=N;

create index ipidx on squid(ip)

连接线程池缓存：

mysql> show variables like 'thread%';

+-------------------+---------------------------+

| Variable_name | Value |

+-------------------+---------------------------+

| thread_cache_size | 0 |

| thread_handling | one-thread-per-connection |

| thread_stack | 196608 |

+-------------------+---------------------------+

thread_cache_size 可以缓存的连接数。0关闭，可以设定任意正整数的数字。

一般建议长连接（持久连接）32以下，一般是数据仓库环境，并发量一般很低

短连接 32－64左右，一般在线事务处理（web应用），并发量一般很高

最终设定值还是要参考状态数据。

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

临时打开：

mysql> set global thread_cache_size=8;

[mysqld]

thread_cache_size=8

mysql> show status like 'thread%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Threads_cached | 0 | 缓存池有多少个线程被缓存起来

| Threads_connected | 1 | 当前有多个连接线程正在使用

| Threads_created | 2 | 服务启动以来，一共创建了多少个连接线程

| Threads_running | 1 | 正在执行指令的线程有多少个

+-------------------+-------+

mysql> show variables like '%connections%';

+----------------------+-------+

| Variable_name | Value |

+----------------------+-------+

| max_connections | 151 | 最大并发连接数

| max_user_connections | 0 | 单个帐号最多并发连接数

+----------------------+-------+

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

mysql> show status like 'Connect%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Connections | 8 | 服务启动以来，一共接受过多少个连接。

+---------------+-------+

连接线程命中率：

( Connections - Threads_created ) / Connections * 100 %

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

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

查询缓存query cache

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

mysql> show variables like '%query_cache%';

+------------------------------+---------+

| Variable_name | Value |

+------------------------------+---------+

| have_query_cache | YES | 是否具有查询缓存功能

| query_cache_limit | 1048576 | 单个结果集最大使用空间

| query_cache_min_res_unit | 4096 | 单个结果集最少分配空间

| query_cache_size | 0 | 整个查询缓存的大小

| query_cache_type | ON | 是否启动查询缓存

| query_cache_wlock_invalidate | OFF |

+------------------------------+---------+

马上生效：

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';

如何判断是否得是否恰当？

mysql> show status like 'Qcache%';

+-------------------------+----------+

| Variable_name | Value |

+-------------------------+----------+

| Qcache_free_blocks | 1 | 空闲的块，如果数字越大，碎片越多

| Qcache_free_memory | 16766848 | 空闲的query_cache的空间

| Qcache_hits | 6 | 命中的次数

| Qcache_inserts | 2 | 没命中，需要插入query_cache中的次数

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 6 | 由各种原因导致无法缓存的查询的次数

| Qcache_queries_in_cache | 1 | 缓存了多少个查询语句的结果

| Qcache_total_blocks | 4 | 使用了多少个block

+-------------------------+----------+

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

计算命中率： 90%以上

Qcache_hits/ ( Qcache_inserts + Qcache_hits ) * 100 %

表缓存：

mysql> show variables like 'table%cache';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| table_definition_cache | 256 | 表定义缓存

| table_open_cache | 64 | 表数据文件缓存：数据文件，索引文件

+------------------------+-------+

以上设定还首先于

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 |

+------------------------+-------+

其他buffer:

mysql> show variables like 'join_buffer_size';

+------------------+--------+

| Variable_name | Value |

+------------------+--------+

| join_buffer_size | 131072 | 128K，用于多表联合查询的缓存，1－2M

+------------------+--------+

1 row in set (0.00 sec)

mysql> show variables like 'sort_buffer_size';

+------------------+---------+

| Variable_name | Value |

+------------------+---------+

| sort_buffer_size | 2097144 | 排序用的缓存，一般2~4M. 用于group by ,order by.

+------------------+---------+

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

1297557818.709 1830 192.168.1.3 TCP_MISS/200 324 GET - 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调优：

mysql> show variables like 'key%';

+--------------------------+---------+

| Variable_name | Value |

+--------------------------+---------+

| key_buffer_size | 8384512 | 专门用户保存索引数据的内存空间（MyISAM）

| key_cache_age_threshold | 300 | 数字越小，hot_area缓存越容易降级为warm

| key_cache_block_size | 1024 |

| key_cache_division_limit | 100 | 把100%内存空间作为warm_area的空间

+--------------------------+---------+

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

mysql> show status like 'key%';

+------------------------+----------+

| Variable_name | Value |

+------------------------+----------+

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 7229 | 未使用的块

| Key_blocks_used | 7245 | 使用了的块

| Key_read_requests | 17978120 | 读命中的次数

| Key_reads | 473 | 读丢失的次数

| Key_write_requests | 4825733 | 写命中的次数

| Key_writes | 47346 | 写丢失的次数

+------------------------+----------+

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、定义该区域可以用于保存哪个表的索引：

mysql> cache index test.log in hot_key;

+----------+--------------------+----------+----------+

+----------+--------------------+----------+----------+

+----------+--------------------+----------+----------+

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

mysql> load index into cache test.log ;

+----------+--------------+----------+----------+

+----------+--------------+----------+----------+

+----------+--------------+----------+----------+

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

常见的一些性能测试工具：

super-smack <---比较好

mysqlslap

mysqlard <---

phpmyadmin <---不错的

Innodb

MyISAM

转载于:https://blog.51cto.com/2364821/1260995

你可能感兴趣的文章