mysql的参数调优

 

1 最大连接数设置  max_connections

当mysql允许的最大连接数设置不合理时，会出现” MySQL: ERROR 1040: Too manyconnections”的错误

mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ mysql> show global status like 'Max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 112 |

对于门户网站等高并发情况，需要使用缓存+分布式数据库等，此处只针对一般应用系统，

比较理想的设置是：Max_used_connections / max_connections   * 100% ≈ 85%

注：MySQL服务器允许的最大连接数16384；

设置方式：在my.cnf配置文件的[mysqld]新增以下参数

max_connections = 500

2 连接数缓存设置  back_log

 back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。

如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，对于Linux系统推荐设置为小于512的整数。

注：查看当前系统的TCP/IP连接的侦听队列的大小：cat /proc/sys/net/ipv4/tcp_max_syn_backlog。

mysql>  show variables like 'back_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | back_log      | 80    | +---------------+-------+ 1 row in set (0.00 sec)

设置方式：在my.cnf配置文件的[mysqld]下新增以下参数

back_log=256

3 等待超时设置 wait-timeout  

MySQL默认的wait-timeout  值为8个小时，可以通过命令show variables like 'wait_timeout'查看结果值;

而对生产环境而言，wait-timeout设置为8小时后将会有大量的空闲连接白白占用内存，甚至导致后来的新请求报“Too many connections”的错误。

可用 show processlist 命令查看当前mysql的状态，如果发现MYSQL中有大量的Sleep进程，则需要 修改wait-timeout值了。

注：

wait_timeout:服务器关闭非交互连接之前等待活动的秒数。

interactive_timeout：服务器关闭交互式连接前等待活动的秒数。

在线程启动时，根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义).

这两个参数必须配合使用。否则单独设置wait_timeout无效

 

对于应用程序而言，修改wait_timeout以避免连接超时不是一个好的选择，而应该设置连接池内连接的生存周期，使之小于上一项中所设置的wait_timeout 的值。 

比如修改 c3p0 数据源的配置文件：

<bean id="dataSource"  class="com.mchange.v2.c3p0.ComboPooledDataSource">            <!--设置连接池内连接的生存周期，超过这个时间自动断开-->          <property name="maxIdleTime"value="1800"/>         <!-定期使用连接池内的连接->        <property name="idleConnectionTestPeriod" value="18000"/>      <property name="testConnectionOnCheckout" value="true"/>      <!--other properties -->     </bean>

4 开启慢查询监控

mysql> show variables like '%slow_query%'; +---------------------+-------------------------------------------+ | Variable_name       | Value                                     | +---------------------+-------------------------------------------+ | slow_query_log      | OFF                                       | | slow_query_log_file | /var/lib/mysql/bjlg-40p117-hw-08-slow.log | +---------------------+-------------------------------------------+ $ mysqldumpslow -s c -t 20  [slow_log_filename];  #从慢查询日志中列出访问次数最多的20个sql语句

修改方式：在my.cnf文件的[mysqld]里面加上以下内容

long_query_time=2 slow_query_log=on  slow_query_log_file=/var/lib/mysql/slow-query.log

 

 

5 连接超时设置 connect_timeout

即connect_timeout参数，mysql客户端在尝试与mysql服务器建立连接时，mysql服务器返回错误握手协议前等待客户端数据包的最大时限。默认10秒。

修改方式：在mysql配置文件的【mysqld】设置以下参数

connect_timeout=10

6 MySAM引擎索引块大小的设置  key_buffer_size

key_buffer_size是对MyISAM表性能影响最大的一个参数， 这个参数是用来设置索引块（index blocks）缓存的大小，它被所有线程共享，严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。

 

mysql> show variables like 'key_buffer_size'; +-----------------+------------+ | Variable_name   | Value      | +-----------------+------------+ | key_buffer_size | 536870912  | +-----------------+------------+ mysql> show global status like 'key_read%'; +------------------------+-------------+ | Variable_name          | Value       | +------------------------+-------------+ | Key_read_requests      | 27813678764 | | Key_reads          | 6798830     | +------------------------+-------------+

 

Key_reads 代表命中磁盘的请求个数， Key_read_requests 是总数

计算索引未命中缓存的概率：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%

这个值越小越好，但也不能为了过分追求较低的索引未命中率而牺牲较大的内存；

对于1GB内存的机器，该值可以在8~64之间合理设置以达到期望的key_cache_miss_rate

注：默认key_buffer_size为8M

 

为检测该参数设置的是否合理，可以用key_blocks_*参数进行验证：

mysql> show global status like 'key_blocks_u%'; +------------------------+-------------+ | Variable_name          | Value       | +------------------------+-------------+ | Key_blocks_unused      | 0           | | Key_blocks_used        | 413543      | +------------------------+-------------+

 

Key_blocks_unused 表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数，如果这台服务器所有的缓存都用到了，就要增加key_buffer_size

7 InnoDB引擎索引快大小的设置 innodb_buffer_pool_size

innodb_buffer_pool_size:主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。

mysql> show variables like 'Innodb_buffer_pool%';       +-------------------------------------+----------------+ | Variable_name                       | Value          | +-------------------------------------+----------------+ | innodb_buffer_pool_dump_at_shutdown | OFF            | | innodb_buffer_pool_dump_now         | OFF            | | innodb_buffer_pool_filename         | ib_buffer_pool | | innodb_buffer_pool_instances        | 8              | | innodb_buffer_pool_load_abort       | OFF            | | innodb_buffer_pool_load_at_startup  | OFF            | | innodb_buffer_pool_load_now         | OFF            | | innodb_buffer_pool_size             | 134217728      | +-------------------------------------+----------------+

另外InnoDB和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。

 提示： 缓存命中率=(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%，可以据此调整 innodb_buffer_pool_size 参数大小。

mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+------------+ | Variable_name                         | Value      | +---------------------------------------+------------+ | Innodb_buffer_pool_read_ahead_rnd     | 0          | | Innodb_buffer_pool_read_ahead         | 1848002    | | Innodb_buffer_pool_read_ahead_evicted | 2993       | | Innodb_buffer_pool_read_requests      | 2495956708 | | Innodb_buffer_pool_reads              | 2226108    | +---------------------------------------+------------+

 

8 临时表存储空间的设置 tmp_table_size

临时表可以在更高级的查询中使用（例如 GROUP BY 字句），其中数据在进一步进行处理之前，都必须先保存到临时表中。

理想情况下，在内存中创建临时表；但是如果临时表变得太大，就需要写入磁盘中。合理的设置临时表，使其尽可能的落在内存中，将加快查询速度。

mysql> show global status like 'created_tmp%'; +-------------------------+---------+ | Variable_name           | Value   | +-------------------------+---------+ | Created_tmp_disk_tables | 21197   | | Created_tmp_files       | 58      | | Created_tmp_tables      | 1771587 | +-------------------------+---------+

Created_tmp_tables ：临时表占用的内存空间

Created_tmp_disk_tables ： 临时表占用的磁盘空间

Created_tmp_files表示MySQL ：服务创建的临时文件文件数

比较理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

查看MySQL服务器对临时表的配置

mysql> show variables where Variable_name in  >('tmp_table_size', 'max_heap_table_size'); +---------------------+-----------+ | Variable_name       | Value     | +---------------------+-----------+ | max_heap_table_size      | 268435456 | | tmp_table_size      | 536870912 | +---------------------+-----------+

max_heap_table_size=268435456也即256M,只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表，tmp_table_size =536870912也即临时表总大小是512M

注：mysql5.6对max_heap_table_size  和tmp_table_size 的默认设置都是16M，如果内存允许，可以适当调大。

9 线程缓存设置  thread_cache_size

与表的缓存类似，对于线程来说也有一个缓存。 mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接

mysql> show global status like 'Thread%'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | Threads_cached    | 46    | | Threads_connected | 2     | | Threads_created   | 570   | | Threads_running   | 1     | +-------------------+-------+

Threads_created表示创建过的线程数，如果Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置

mysql> show variables like 'thread_cache_size'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | thread_cache_size | 64    | +-------------------+-------+

10 默认编码设置

utf8mb4 编码是 utf8 编码的超集且兼容 utf8，能存储 4 个字节的 unicode 编码。注意不需要在 [client] 、[mysql] 下额外设置 default-character-set 或 character-set-server。

MySQL 5.7.x 上最佳实践如下：

[mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci character-set-client-handshake=FALSE

 

11 常规化参数配置，根据自己的实际服务器的性能进行设置参数

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin
binlog_format=mixed
  
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
  
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
 
# character set
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character-set-client-handshake=FALSE
  
# Default user
user=mysql
  
# slow query
slow_query_log=on
slow_query_log_file=/var/lib/mysql/slow-query.log
 
  
# Adjust as your needed
max_connections=512
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=64M
  
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid