Mysql参数优化

Innodb引擎下mysql自身配置优化

1 参数介绍

1 内存利用方面

1 innodb_buffer_pool_size

这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。
该参数分配内存的原则：
这个参数默认分配只有8M，可以说是非常小的一个值。
如果是一个专用DB服务器，那么他可以占到内存的70%-80%。
如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。
例如：数据大小为50M,那么给这个值分配innodb_buffer_pool_size＝64M

设置方法，在my.cnf文件里：
innodb_buffer_pool_size=4G

mysql> select @@innodb_buffer_pool_size;   # 查看MySQL缓存大小
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+
1 row in set (0.00 sec)

2 日志方面

2 innodb_log_file_size

说明：

• 小日志文件使写入速度更慢，崩溃恢复速度更快

• 大日志文件使写入更快，崩溃恢复速度更慢

• 由于事务日志相当于一个写缓冲，而小日志文件会很快的被写满，这时候就需要频繁地刷新到硬盘，速度就慢了。如果产生大量的写操作，MySQL可能就不能足够快地刷新数据，那么写性能将会降低。

• 大的日志文件，另一方面，在刷新操作发生之前给你足够的空间来使用。反过来允许InnoDB填充更多的页面。
  对于崩溃恢复 – 大的重做日志意味着在服务器启动前更多的数据需要读取，更多的更改需要重做，这就是为什么崩溃恢复慢了。

查看重做日志大小

mysql> show variables like '%innodb_log_file%';
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| innodb_log_file_size      | 268435456 |
| innodb_log_files_in_group | 2         |
+---------------------------+-----------+
2 rows in set (0.00 sec)

设置方法：

在my.cnf文件里： innodb_log_file_size = 256M

innodb_log_files_in_group （一般为2）

找出重做日志的正确大小:

mysql> pager grep seq
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 1777308180429
...
Log sequence number 1777354541591
 
mysql> nopager
mysql> select (1777354541591-1777308180429)*60/1024/1024;
+--------------------------------------------+
| (1777354541591-1777308180429)*60/1024/1024 |
+--------------------------------------------+
|                              2652.80696869 |
+--------------------------------------------+
1 row in set (0.00 sec)

在这个60s的采样情况下，InnoDB每小时写入2.6GB数据。所以如果innodb_log_files_in_group没有更改(默认是2，是InnoDB重复日志的最小数字)，然后设置innodb_log_file_size为2560M，那么你实际上两个日志文件加起来有5GB，够你写两小时数据了

3 innodb_log_buffer_size

作用：事务在内存中的缓冲，也就是日志缓冲区的大小， 默认设置即可，具有大量事务的可以考虑设置为16M。
如果这个值增长过快，可以适当的增加innodb_log_buffer_size
另外如果你需要处理大理的TEXT，或是BLOB字段，可以考虑增加这个参数的值。
设置方法：在my.cnf文件里：
innodb_log_buffer_size=3M

mysql> select @@innodb_log_buffer_size;
+--------------------------+
| @@innodb_log_buffer_size |
+--------------------------+
|                  8388608 |
+--------------------------+
1 row in set (0.00 sec)

3 文件IO分配，空间占用方面

4 innodb_file_per_table

作用：使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间。默认是关闭的，建议打开（innodb_file_per_table=1）
分配原则：只有使用不使用。但DB还需要有一个公共的表空间。
设置方法：在my.cnf文件里：
innodb_file_per_table=1

mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

5 innodb_file_io_threads

作用：文件读写IO数，这个参数只在Windows上起作用。在Linux上只会等于4，默认即可！
设置方法：在my.cnf文件里：
innodb_file_io_threads=4

6 innodb_open_files

作用：限制Innodb能打开的表的数据。
分配原则：这个值默认是300。如果库里的表特别多的情况，可以适当增大为1000。innodb_open_files的大小对InnoDB效率的影响比较小。但是在InnoDBcrash的情况下，innodb_open_files设置过小会影响recovery的效率。所以用InnoDB的时候还是把innodb_open_files放大一些比较合适。
设置方法：在my.cnf文件里：
innodb_open_files=800

mysql> select @@innodb_open_files;
+---------------------+
| @@innodb_open_files |
+---------------------+
|                2000 |
+---------------------+
1 row in set (0.00 sec)

7 innodb_data_file_path

指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。
例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend 两个数据文件放在不同的磁盘上。数据首先放在ibdata1 中，当达到900M以后，数据就放在ibdata2中。
设置方法，在my.cnf文件里：
innodb_data_file_path =ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:1G:autoextend

mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

8 innodb_data_home_dir

放置表空间数据的目录，默认在mysql的数据目录，设置到和MySQL安装文件不同的分区可以提高性能。
设置方法，在my.cnf文件里：（比如mysql的数据目录是/data/mysql/data，这里可以设置到不通的分区/home/mysql下）
innodb_data_home_dir = /home/mysql

4 公共参数

9 innodb_thread_concurrency

线程并发数，建议设置为CPU内核数*2
设置方法，在my.cnf文件里：
innodb_thread_concurrency = 16

10 max_connections

设置最大连接（用户）数，每个连接MySQL的用户均算作一个连接，max_connections的默认值为100。此值需要根据具体的连接数峰值设定。
设置方法，在my.cnf文件里：
max_connections = 3000

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
1 row in set (0.00 sec)

11 query_cache_size

查询缓存大小，如果表的改动非常频繁，或者每次查询都不同，查询缓存的结果会减慢系统性能。可以设置为0。
设置方法，在my.cnf文件里：
query_cache_size = 512M

mysql> select @@query_cache_size;
+--------------------+
| @@query_cache_size |
+--------------------+
|            1048576 |
+--------------------+
1 row in set (0.00 sec)

12 sort_buffer_size

connection级的参数，排序缓存大小。一般设置为2-4MB即可。
设置方法，在my.cnf文件里：
sort_buffer_size = 1024M

mysql> select @@sort_buffer_size;
+--------------------+
| @@sort_buffer_size |
+--------------------+
|             262144 |
+--------------------+
1 row in set (0.00 sec)

13 max_allowed_packet

网络包的大小，为避免出现较大的网络包错误，建议设置为16M
设置方法，在my.cnf文件里：
max_allowed_packet = 16M

mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|              4194304 |
+----------------------+
1 row in set (0.00 sec)

14 table_open_cache

当某一连接访问一个表时，MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开，则会直接访问缓存中的表，以加快查询速度；如果该表未被缓存，则会将当前的表添加进缓存并进行查询。
通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。
如果发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么就需要增加table_open_cache的值;设置为512即可满足需求。
设置方法，在my.cnf文件里：
table_open_cache = 512

mysql> select @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
|               2000 |
+--------------------+
1 row in set (0.00 sec)

2 配置文件参数解释

参数解释：

# Binary log/replication(这里主要是复制功能，也就是主从,提前配置好，后面讲主从配置)

#二进制日志
log-bin
#为了在最大程序上保证复制的InnoDB事务持久性和一致性
sync_binlog=1
sync_relay_log=1
#启用此两项,可用于实现在崩溃时保证二进制及从服务器安全的功能
relay-log-info-repository=TABLE
master-info-repository=TABLE
#设置清除日志时间
expire_logs_days=7
#行复制
binlog_format=ROW
#mysql数据库事务隔离级别有四种(READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE)
transaction-isolation=READ-COMMITTED

#cache
#内部内存临时表的最大值
tmp_table_size=512M
character-set-server=utf8
collation-server=utf8_general_ci
#即跳过外部锁定
skip-external-locking
#MySQL能暂存的连接数量(根据实际设置)
back_log=1024
#指定索引缓冲区的大小,只对MyISAM表起作用,这里写上也没有关系
key_buffer_size=1024M
#这条指令限定用于每个数据库线程的栈大小
thread_stack=256k
#当一个查询不断地扫描某一个表,MySQL会为它分配一段内存缓冲区
read_buffer_size=8M
#线程缓存
thread_cache_size=64
#查询缓存大小
query_cache_size=128M
#内部内存临时表的最大值,每个线程都要分配
max_heap_table_size=256M
#将查询结果放入查询缓存中
query_cache_type=1
#代表在事务过程中容纳二进制日志SQL语句的缓存大小
binlog_cache_size = 2M
#同样是缓存表大小
table_open_cache=128
#缓存线程
thread_cache=1024
#推荐设置为服务器 CPU核数的2倍
thread_concurrency=8
wait_timeout=30
#表和表联接的缓冲区的大小
join_buffer_size = 1024M
#是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存
sort_buffer_size=8M
#随机读取数据缓冲区使用内存
read_rnd_buffer_size = 8M

#connect
#是一个MySQL中与安全有关的计数器值,它负责阻止过多尝试失败的客户端以防止暴力破解密码
max-connect-errors=100000
#连接数
max-connections=1000
#开启查询缓存
explicit_defaults_for_timestamp=true
#mysql服务器能够工作在不同的模式下,并能针对不同的客户端以不同的方式应用这些模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

3 对性能优化影响较大的变量

下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量。

1 连接请求的变量：

1 max_connections

MySQL的最大连接数，增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多， 介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
数值过小会经常出现ERROR 1040: Too many connections错误，可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。

show variables like ‘max_connections’ 最大连接数
show status like ‘max_used_connections’响应的连接数

如下：

mysql> show variables like ‘max_connections‘;
+———————–+——-+
| Variable_name　| Value |
+———————–+——-+
| max_connections | 256　　|
+———————–+——-+
mysql> show status like ‘max%connections‘;
+———————–+——-+
| Variable_name　      | Value |
+—————————-+——-+
| max_used_connections | 256|
+—————————-+——-+

max_used_connections / max_connections * 100% （理想值≈ 85%）
如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

2 back_log

MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到 max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。
当观察你主机进程列表（mysql> show full processlist），发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log 的值了。
默认数值是50，可调优为128，对系统设置范围为小于512的整数。

2 缓冲区变量

1 table_cache

表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如 果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了 （上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能 不稳定或者连接失败。
1G内存机器，推荐值是128－256。内存在4GB左右的服务器该参数可设置为256M或384M。

4 慢查询配置与分析

1 简介

慢查询日志官方给出的理解太费劲了，本博主，按照日常思维进行一下讲解吧，慢查询日志顾名思义，就是查询慢的日志记录啊，我们在数据库中的增删改查等操作，如果执行时间超过了数据库中慢查询设置的默认查询时间之后，就会把这些执行较慢的sql记录到日志中，像这样的日志叫做慢查询日志。这么一说，大家应该比较好理解了吧，哈哈。

2 参数配置

1. slow_query_log`

   是否开启慢查询日志，1表示开启，0表示关闭。

2. log-slow-queries

   旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

3. slow-query-log-file
   新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

4. long_query_time
   慢查询阈值，当查询时间多于设定的阈值时，记录日志。一般设置成1s或者2s

5. log_queries_not_using_indexes
   未使用索引的查询也被记录到慢查询日志中（可选项）。

6. log_output：
   日志存储方式。

   1. log_output='FILE'表示将日志存入文件，默认值是'FILE'
   2. log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。
   3. MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。
   4. 日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

3 查看和配置

查看慢查询是否开启：

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启，如下所示：

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_query_log      | OFF                                      |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
  
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
  
mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_query_log      | ON                                       |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
#使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此），下面的操作我们在my.cnf中添加

开启慢查询并设置存储方式：

　　slow_query_log =1

　　log_output = file　　　　

自定义设置慢查询存储的路径

　　slow_query_log_file=/usr/local/mysql/data/mysql-slow.log

自定义设置慢查询请求时间的配置为1s

　　long_query_time = 1

重启mysql服务器之后查看参数是否设置成功

　　/etc/init.d/mysqld restart

4 mysqldumpslow分析日志

选项：

-s, 是表示按照何种方式排序

c: 访问计数

l: 锁定时间

r: 返回记录

t: 查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

日志返回参数说明：

Reading mysql slow query log from /usr/local/mysql/data/mysql-slow.log

Count: 1  Time=177.51s (177s)  Lock=0.00s (0s)  Rows=597096.0 (597096), [root]@[112.10.84.231]

  select * from s1 where email like 'S'
  
-- count 执行次数
-- time 执行时间
-- lock 锁定时间
-- rows 返回条数
-- root[root]@localhost  通过那个用户执行的

示例：

得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /usr/local/mysql/data/mysql-slow.log

得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /usr/local/mysql/data/mysql-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /usr/local/mysql/data/mysql-slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more