可以直接使用该配置参数www.9159.com:,介于MySQ

作者: www.9159.com  发布:2019-10-10

一.环境说明:

一、全局配置

(1)max_connections
最大连接数。默认值是151,最多2000。如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量。但是如果连接数越多,介于MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值。
查看最大连接数

mysql> SHOW VARIABLES LIKE 'max_connections';

查看响应的连接数

mysql> SHOW STATUS LIKE 'max%connections';

max_used_connections / max_connections * 100% (理想值≈85%) 
如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了,低于10%则设置过大。
(2)back_log
MySQL能暂存的连接数量,默认值是80,最多512,可设置为128。如果MySQL的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log。如果等待连接的数量超过back_log,将不被授予连接资源。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用。
(3)key_buffer_size
索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。
通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。

mysql> SHOW STATUS LIKE 'key_read%';

+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Key_read_requests | 90585564 |
| Key_reads         | 97031    |
+-------------------+----------+

计算索引未命中缓存的概率:
key_cache_miss_rate = Key_reads / Key_read_requests * 100%,设置在1/1000左右较好
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。
默认配置数值是8388608(8M),主机有4GB内存,可改为268435456(256M)
(4)query_cache_size
使用查询缓存(query cache),MySQL将查询结果存放在缓冲区中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。
最佳选项是将其从一开始就停用,设为0(现在MySQL 5.6的默认值)并利用其他方法加速查询:优化索引、增加拷贝分散负载或者启用额外的缓存(比如Redis或Memcached)。
通过检查状态值qcache_*,可以知道query_cache_size设置是否合理

mysql> SHOW STATUS LIKE 'qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 1031360  |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 10302865 |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小,要不就是碎片太多。
查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,如果Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;如果Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。
query_cache_type指定是否使用查询缓冲,可以设置为0、1、2,该变量是SESSION级的变量。
query_cache_limit指定单个查询能够使用的缓冲区大小,缺省为1M。
query_cache_min_res_unit指定分配缓冲区空间的最小单位,缺省为4K。检查状态值Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多,这就表明查询结果都比较小,此时需要减小query_cache_min_res_unit。
(5)read_buffer_size
是MySQL读入缓冲区的大小,将对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区,read_buffer_size变量控制这一缓冲区的大小,如果对表的顺序扫描非常频繁,并你认为频繁扫描进行的太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。
默认数值是131072(128K),可改为16773120(16M)
(6)read_rnd_buffer_size
随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。
默认数值是262144(256K),可改为16777208(16M)
(7)sort_buffer_size
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。
默认数值是10485760(1M),可改为16777208(16M)
(8)join_buffer_size
联合查询操作所能使用的缓冲区大小
read_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size为每个线程独占,也就是说,如果有100个线程连接,则占用为16M*100
(9)table_open_cache
表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。
通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。

mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 2000  |
| Opened_tables | 0     |
+---------------+-------+

如果open_tables等于table_cache,并且opened_tables在不断增长,那么就需要增加table_cache的值了。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。
1G内存机器,推荐值是128-256。内存在4GB左右的服务器该参数可设置为256M或384M。
(10)max_heap_table_size
用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。
这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。
(11)tmp_table_size
临时表的大小,例如做高级GROUP BY操作生成的临时表。如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于硬盘的MyISAM表。

mysql> SHOW GLOBAL STATUS LIKE 'created_tmp%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 2884297  |
| Created_tmp_files       | 870      |
| Created_tmp_tables      | 15899696 |
+-------------------------+----------+

每次创建临时表,Created_tmp_tables增加,如果临时表大小超过tmp_table_size,则是在磁盘上创建临时表,Created_tmp_disk_tables也增加。
Created_tmp_files表示MySQL服务创建的临时文件文件数,比较理想的配置是:
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
(12)thread_cache_size
线程缓存。当客户端断开之后,服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。

mysql> SHOW STATUS LIKE 'threads%';

+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Threads_cached    | 5       |
| Threads_connected | 13      |
| Threads_created   | 1095313 |
| Threads_running   | 1       |
+-------------------+---------+

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。如果过大,表明MySQL服务器一直在创建线程,这也是比较耗资源,可以适当增加thread_cache_size
Threads_connected :代表当前已建立连接的数量,因为一个连接就需要一个线程,所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动,已创建线程的数量。
Threads_running :代表当前激活的(非睡眠状态)线程数。并不是代表正在使用的线程数,有时候连接已建立,但是连接处于sleep状态,这里相对应的线程也是sleep状态。
建议设置接近Threads_connected值,再结合物理内存:1G-8;2G-16;3G-32 综合考虑一下值。
(13)interactive_timeout
一个交互连接在被服务器在关闭前等待行动的秒数。默认值是28800(8小时),可设置为7200。
(14)wait_timeout
一个非交互连接在被服务器在关闭前等待行动的秒数。要同时设置interactive_timeout和wait_timeout才会生效。

mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存),my.cnf_my.ini16g

此配置是老男孩生产线上使用的配置,在培训的时候,他给的,我在这里,对各参数添加了中文说明
这配置已经优化的不错了,如果你的mysql没有什么特殊情况的话,可以直接使用该配置参数

MYSQL服务器my.cnf配置文档详解
硬件:内存16G

[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit = 10240

back_log = 600
#在MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。

max_connections = 3000
#MySQL允许最大的进程连接数,如果经常出现Too Many Connections的错误提示,则需要增大此值。

max_connect_errors = 6000
#设置每个主机的连接请求异常中断的最大次数,当超过该次数,MYSQL服务器将禁止host的连接请求,直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。

table_cache = 614
#指示表调整缓冲区大小。# table_cache 参数设置表高速缓存的数目。每个连接进来,都会至少打开一个表缓存。#因此, table_cache 的大小应与 max_connections 的设置有关。例如,对于 200 个#并行运行的连接,应该让表的缓存至少有 200 × N ,这里 N 是应用可以执行的查询#的一个联接中表的最大数量。此外,还需要为临时表和文件保留一些额外的文件描述符。
# 当 Mysql 访问一个表时,如果该表在缓存中已经被打开,则可以直接访问缓存;如果#还没有被缓存,但是在 Mysql 表缓冲区中还有空间,那么这个表就被打开并放入表缓#冲区;如果表缓存满了,则会按照一定的规则将当前未用的表释放,或者临时扩大表缓存来存放,使用表缓存的好处是可以更快速地访问表中的内容。执行 flush tables 会#清空缓存的内容。一般来说,可以通过查看数据库运行峰值时间的状态值 Open_tables #和 Opened_tables ,判断是否需要增加 table_cache 的值(其中 open_tables 是当#前打开的表的数量, Opened_tables 则是已经打开的表的数量)。即如果open_tables接近table_cache的时候,并且Opened_tables这个值在逐步增加,那就要考虑增加这个#值的大小了。还有就是Table_locks_waited比较高的时候,也需要增加table_cache。

external-locking = FALSE
#使用–skip-external-locking MySQL选项以避免外部锁定。该选项默认开启

max_allowed_packet = 32M
#设置在网络传输中一次消息传输量的最大值。系统默认值 为1MB,最大值是1GB,必须设置1024的倍数。

sort_buffer_size = 2M
# Sort_Buffer_Size 是一个connection级参数,在每个connection(session)第一次需要使用这个buffer的时候,一次性分配设置的内存。
#Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。例如:500个连接将会消耗 500*sort_buffer_size(8M)=4G内存
#Sort_Buffer_Size 超过2KB的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。
#技术导读
#dev-doc:
#explain select*from table where order limit;出现filesort
#属重点优化参数

join_buffer_size = 2M
#用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

thread_cache_size = 300
# 服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。设置规则如下:1GB 内存配置为8,2GB配置为16,3GB配置为32,4GB或更高内存,可配置更大。

thread_concurrency = 8
# 设置thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那么thread_concurrency的应该为4; 2个双核的cpu, thread_concurrency的值应为8
#属重点优化参数

query_cache_size = 64M
## 对于使用MySQL的用户,对于这个变量大家一定不会陌生。前几年的MyISAM引擎优化中,这个参数也是一个重要的优化参数。但随着发展,这个参数也爆露出来一些问题。机器的内存越来越大,人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一下 query_cache_size的工作原理:一个SELECT查询在DB中工作后,DB会把该语句缓存下来,当同样的一个SQL再次来到DB里调用时,DB在该表没发生变化的情况下把结果从缓存中返回给Client。这里有一个关建点,就是DB在利用Query_cache工作时,要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时,Query_cache里的数据又怎么处理呢?首先要把Query_cache和该表相关的语句全部置为失效,然后在写入更新。那么如果Query_cache非常大,该表的查询结构又比较多,查询语句失效也慢,一个更新或是Insert就会很慢,这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统,该参数不适合分配过大。而且在高并发,写入量大的系统,建议把该功能禁掉。
#重点优化参数(主库 增删改-MyISAM)

query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小,缺省为1M

query_cache_min_res_unit = 2k
#默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费
#查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
#如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。
#查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小,要不就是碎片太多。
#查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

default-storage-engine = MyISAM
#default_table_type = InnoDB

thread_stack = 192K
#设置MYSQL每个线程的堆栈大小,默认值足够大,可满足普通操作。可设置范围为128K至4GB,默认为192KB。

transaction_isolation = READ-COMMITTED
# 设定默认的事务隔离级别.可用的级别如下:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED-读未提交2.READ COMMITTE-读已提交3.REPEATABLE READ -可重复读4.SERIALIZABLE -串行

tmp_table_size = 256M
# tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增加 tmp_table_size 值。如果超过该值,则会将临时表写入磁盘。
max_heap_table_size = 256M
long_query_time = 2
log_long_format
log-slow-queries=/data/3306/slow-log.log
#log-bin = /data/3306/mysql-bin
log-bin
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M

expire_logs_days = 7
key_buffer_size = 2048M
#批定用于索引的缓冲区大小,增加它可以得到更好的索引处理性能,对于内存在4GB左右的服务器来说,该参数可设置为256MB或384MB。

read_buffer_size = 1M
# MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

read_rnd_buffer_size = 16M
# MySql的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。

bulk_insert_buffer_size = 64M
#批量插入数据缓存大小,可以有效提高插入效率,默认为8M

myisam_sort_buffer_size = 128M
# MyISAM表发生变化时重新排序所需的缓冲

myisam_max_sort_file_size = 10G
# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).
# 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)

myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
# 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.
# 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.

myisam_recover
#自动检查和修复没有适当关闭的 MyISAM 表
skip-name-resolve
lower_case_table_names = 1

server-id = 1

innodb_additional_mem_pool_size = 16M
#这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小,类似于Oracle的library cache。这不是一个强制参数,可以被突破。

innodb_buffer_pool_size = 2048M
# 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大,并且不会暴增,那么无需把 innodb_buffer_pool_size 设置的太大了

innodb_data_file_path = ibdata1:1024M:autoextend
#表空间文件 重要数据

innodb_file_io_threads = 4
#文件IO的线程数,一般为 4,但是在 Windows 下,可以设置得较大。

innodb_thread_concurrency = 8
#服务器有几个CPU就设置为几,建议用默认设置,一般为8.

innodb_flush_log_at_trx_commit = 2
# 如果将此参数设置为1,将在每次提交事务后将日志写入磁盘。为提供性能,可以设置为0或2,但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写入日志文件,而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志,但日志文件每次刷新到磁盘一次。

innodb_log_buffer_size = 16M
#此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据.MySQL开发人员建议设置为1-8M之间

innodb_log_file_size = 128M
#此参数确定数据日志文件的大小,以M为单位,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间

innodb_log_files_in_group = 3
#为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3M

innodb_max_dirty_pages_pct = 90
#推荐阅读
# Buffer_Pool中Dirty_Page所占的数量,直接影响InnoDB的关闭时间。参数innodb_max_dirty_pages_pct 可以直接控制了Dirty_Page在Buffer_Pool中所占的比率,而且幸运的是innodb_max_dirty_pages_pct是可以动态改变的。所以,在关闭InnoDB之前先将innodb_max_dirty_pages_pct调小,强制数据块Flush一段时间,则能够大大缩短 MySQL关闭的时间。

innodb_lock_wait_timeout = 120
# InnoDB 有其内置的死锁检测机制,能导致未完成的事务回滚。但是,如果结合InnoDB使用MyISAM的lock tables 语句或第三方事务引擎,则InnoDB无法识别死锁。为消除这种可能性,可以将innodb_lock_wait_timeout设置为一个整数值,指示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)

innodb_file_per_table = 0
#独享表空间(关闭)

[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
log-error=/data/3306/mysql_oldboy.err
pid-file=/data/3306/mysqld.pid

#补充
#wait_timeout = 10
#指定一个请求的最大连接时间,对于4GB左右的内存服务器来说,可以将其设置为5-10。
#skip_networking
#开启该选可以彻底关闭MYSQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MYSQL数据库服务器的,则不要开启该选项,否则将无法正常连接。

#log-queries-not-using-indexes
将没有使用索引的查询也记录下来

此配置是老男孩生产线上使用的配置,在培训的时候,他给的,我在这里...

(一)

操作系统:CentOS 6.5 x86_64

二、InnoDB配置

(1)innodb_buffer_pool_size
缓冲池的大小,缓存数据和索引,对InnoDB整体性能影响较大,相当于MyISAM的key_buffer_size。如果只用Innodb,可以把这个值设为内存的70%-80%。越大越好,这能保证你在大多数的读取操作时使用的是内存而不是硬盘。
(2)innodb_log_buffer_size  
尚未执行的事务的缓存大小,默认值为8M,一般8M-16M。如果你有很多事务的更新,插入或删除操作,通过这个参数会大量的节省了磁盘I/O。但是如果你的事务中包含有二进制大对象或者大文本字段的话,这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量,如果它不是0,应该增大这个值。但太大了也是浪费内存,因为1秒钟总会flush一次,所以不需要设到超过1秒的需求。
(3)innodb_flush_log_at_trx_commit
把log buffer的数据写入日志文件并flush磁盘的策略,该值对插入数据的速度影响非常大。取值分别为0、1(默认值)、2(推荐值)
0:事务提交时,不写入磁盘,而是每秒把log buffer的数据写入日志文件,并且flush(刷到磁盘)。速度最快,但不安全。mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
1:每次事务提交时把log buffer的数据写入日志文件,并且flush(刷到磁盘)。最安全,但也最慢。确保了事务的ACID。
2:每次事务提交时把log buffer的数据写入日志文件,每秒flush(刷到磁盘)。速度较快,比0安全。操作系统崩溃或者系统断电会导致上一秒钟所有事务数据的丢失。
(4)innodb_log_file_size
在一个日志组每个日志文件的大小,用于确保写操作快速而可靠并且在崩溃时恢复。一般用64M-512M,具体取决于服务器的空间。大的文件提供更高的性能,但数据库恢复时会用更多的时间。
(5)innodb_additional_mem_pool_size
存储数据字典和其他内部数据结构的内存池大小。默认为1M,对于2G内存的机器,推荐值是20M,通常不用太大,应该与表结构的复杂度有关系。如果不够用,MySQL会在错误日志中写入一条警告信息。
(6)innodb_buffer_pool_instances
可以开启多个内存缓冲池,这样可以并行的内存读写。默认为8,一般为1-8。最常1s就会刷新一次,故不用太大。对于较大的事务,可以增大缓存大小。如果InnoDB缓存池被划分成多个区域,建议每个区域不小于1GB的空间。

 查看更多:
MySQL优化
MySQL各存储引擎
MySQL锁详解
MySQL事务
MySQL索引类型

[client]
port = 3306
socket = /tmp/mysql.sock

数据库:Mysql 5.6.22

[mysqld]
port = 3306
socket = /tmp/mysql.sock

服务器:阿里云VPS,32G Mem,0 swap

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1 #表示是本机的序号为1,一般来讲就是master的意思

 

skip-name-resolve
# 禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,
# 则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求

二.问题情况:

#skip-networking

1.某日发现公司线上系统的Mysql某个实例的从库长时间内存占用达到60%如下图

back_log = 600
# MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,
# 然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
# 如果期望在一个短时间内有很多连接,你需要增加它。也就是说,如果MySQL的连接数据达到max_connections时,新来的请求将会被存在堆栈中,
# 以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。
# 另外,这值(back_log)限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。
# 你的操作系统在这个队列大小上有它自己的限制(可以检查你的OS文档找出这个变量的最大值),试图设定back_log高于你的操作系统的限制将是无效的。

www.9159.com 1

max_connections = 1000
# MySQL的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量,以定夺该值的大小。

 

max_connect_errors = 6000
# 对于同一主机,如果有超出该参数值个数的中断错误连接,则该主机将被禁止连接。如需对该主机进行解禁,执行:FLUSH HOST。

2.于是开始按照以下步骤排查:

open_files_limit = 65535
# MySQL打开的文件描述符限制,默认最小1024;当open_files_limit没有被配置的时候,比较max_connections*5和ulimit -n的值,哪个大用哪个,
# 当open_file_limit被配置的时候,比较open_files_limit和max_connections*5的值,哪个大用哪个。

(1).查看mysql里的线程,观察是否有长期运行或阻塞的sql:

table_open_cache = 128
# MySQL每打开一个表,都会读入一些数据到table_open_cache缓存中,当MySQL在这个缓存中找不到相应信息时,才会去磁盘上读取。默认值64
# 假定系统有200个并发连接,则需将此参数设置为200*N(N为每个连接所需的文件描述符数目);
# 当把table_open_cache设置为很大时,如果系统处理不了那么多文件描述符,那么就会出现客户端失效,连接不上

show full processlist

max_allowed_packet = 4M
# 接受的数据包大小;增加该变量的值十分安全,这是因为仅当需要时才会分配额外内存。例如,仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。
# 该变量之所以取较小默认值是一种预防措施,以捕获客户端和服务器之间的错误信息包,并确保不会因偶然使用大的信息包而导致内存溢出。

经查看,没有发现相关线程,可排除该原因

binlog_cache_size = 1M
# 一个事务,在没有提交的时候,产生的日志,记录到Cache中;等到事务提交需要提交的时候,则把日志持久化到磁盘。默认binlog_cache_size大小32K

 

max_heap_table_size = 8M
# 定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变

(2).疑似mysql连接使用完成后没有真正释放内存,查看mysql内存,缓存的相关配置,使用如

tmp_table_size = 16M
# MySQL的heap(堆积)表缓冲大小。所有联合在一个DML指令内完成,并且大多数联合甚至可以不用临时表即可以完成。
# 大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。
# 如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说,如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果

show global variables like '%sort_buffer_size%';

read_buffer_size = 2M
# MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。
# 如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能

查看相关的配置项,结果列表汇总如下

read_rnd_buffer_size = 8M
# MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,
# MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大

www.9159.com 2

sort_buffer_size = 8M
# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。
# 如果不能,可以尝试增加sort_buffer_size变量的大小

( 注:上图为mysql使用内存计算器,具体地址为 )

join_buffer_size = 8M
# 联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每连接独享

 

thread_cache_size = 8
# 这个值(默认8)表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,
# 如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,
# 增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量,可以看到这个变量的作用。(–>表示要调整的值)
# 根据物理内存设置规则如下:
# 1G  —> 8
# 2G  —> 16
# 3G  —> 32
# 大于3G  —> 64

其中左列为mysql默认配置,右列为当前数据库的配置,可见预期内存使用最大值足足达到了1T,不符合当前系统负载量,说明当前配置不合理,需要进行调整

query_cache_size = 8M
#MySQL的查询缓冲大小(从4.0.1开始,MySQL提供了查询缓冲机制)使用查询缓冲,MySQL将SELECT语句和查询结果存放在缓冲区中,
# 今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。
# 通过检查状态值'Qcache_%',可以知道query_cache_size设置是否合理:如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,
# 如果Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;如果Qcache_hits的值不大,则表明你的查询重复率很低,
# 这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲

 

query_cache_limit = 2M
#指定单个查询能够使用的缓冲区大小,默认1M

(3).其中

key_buffer_size = 4M
#指定用于索引的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多。如果你使它太大,
# 系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads,
# 可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低,
# 至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得)。注意:该参数值设置的过大反而会是服务器整体效率降低

key_buffer_size = 32M         //key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。由于我的数据库引擎为innodb,大部分表均为innodb,此处取默认值一半32M。

ft_min_word_len = 4
# 分词词汇最小长度,默认4

 

transaction_isolation = REPEATABLE-READ
# MySQL支持4种事务隔离级别,他们分别是:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# 如没有指定,MySQL默认采用的是REPEATABLE-READ,ORACLE默认的是READ-COMMITTED

query_cache_size = 64M    //查询缓存大小,当打开时候,执行查询语句会进行缓存,读写都会带来额外的内存消耗,下次再次查询若命中该缓存会立刻返回结果。默认改选项为关闭,打开则需要调整参数项query_cache_type=ON。此处采用默认值64M。

log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30 #超过30天的binlog删除

 

log_error = /data/mysql/mysql-error.log #错误日志路径
slow_query_log = 1
long_query_time = 1 #慢查询时间 超过1秒则为慢查询
slow_query_log_file = /data/mysql/mysql-slow.log

tmp_table_size = 64M    //范围设置为64-256M最佳,当需要做类似group by操作生成的临时表大小,提高联接查询速度的效果,调整该值直到created_tmp_disk_tables / created_tmp_tables * 100% <= 25%,处于这样一个状态之下,效果较好,如果网站大部分为静态内容,可设置为64M,如果为动态页面,则设置为100M以上,不宜过大,导致内存不足I/O堵塞。此处我们设置为64M。

performance_schema = 0
explicit_defaults_for_timestamp

innodb_buffer_pool_size = 8196M   //这个参数主要作用是缓存innodb表的索引,数据,插入数据时的缓冲。专用mysql服务器设置的大小: 操作系统内存的70%-80%最佳。由于我们的服务器还部署有其他应用,估此处设置为8G。此外,这个参数是非动态的,要修改这个值,需要重启mysqld服务。设置的过大,会导致system的swap空间被占用,导致操作系统变慢,从而减低sql查询的效率。

#lower_case_table_names = 1 #不区分大小写

innodb_additional_mem_pool_size = 16M    //用来存放Innodb的内部目录,这个值不用分配太大,系统可以自动调。不用设置太高。通常比较大数据设置16M够用了,如果表比较多,可以适当的增大。如果这个值自动增加,会在error log有中显示的。此处我们设置为16M。

skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启

innodb_log_buffer_size = 8M  //InnoDB的写操作,将数据写入到内存中的日志缓存中,由于InnoDB在事务提交前,并不将改变的日志写入到磁盘中,因此在大事务中,可以减轻磁盘I/O的压力。通常情况下,如果不是写入大量的超大二进制数据(a lot of huge blobs),4MB-8MB已经足够了。此处我们设置为8M。

default-storage-engine = InnoDB #默认存储引擎

max_connections = 800    //最大连接数,根据同时在线人数设置一个比较综合的数字,最大不超过16384。此处我们根据系统使用量综合评估,设置为800。

innodb_file_per_table = 1
# InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间
# 独立表空间优点:
# 1.每个表都有自已独立的表空间。
# 2.每个表的数据和索引都会存在自已的表空间中。
# 3.可以实现单表在不同的数据库中移动。
# 4.空间可以回收(除drop table操作处,表空不能自已回收)
# 缺点:
# 单表增加过大,如超过100G
# 结论:
# 共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时,请合理调整:innodb_open_files

sort_buffer_size = 2M    //是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB,这里我们设置为2M。

innodb_open_files = 500
# 限制Innodb能打开的表的数据,如果库里的表特别多的情况,请增加这个。这个值默认是300

read_buffer_size = 2M   //(数据文件存储顺序)是MySQL读入缓冲区的大小,将对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区,read_buffer_size变量控制这一缓冲区的大小,如果对表的顺序扫描非常频繁,并你认为频繁扫描进行的太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能,read_buffer_size变量控制这一提高表的顺序扫描的效率 数据文件顺序。此处我们设置得比默认值大一点,为2M。

innodb_buffer_pool_size = 64M
# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.
# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,
# 所以不要设置的太高.

read_rnd_buffer_size = 250K  //是MySQL的随机读缓冲区大小,当按任意顺序读取行时(列如按照排序顺序)将分配一个随机读取缓冲区,进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要大量数据可适当的调整该值,但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值,以免内存开销过大。表的随机的顺序缓冲 提高读取的效率。此处设置为跟默认值相似,250KB。

innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
# 注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里,修改完后重启MySQL服务,允许值的范围从 1-64

join_buffer_size = 250K   //多表参与join操作时的分配缓存,适当分配,降低内存消耗,此处我们设置为250KB。

innodb_thread_concurrency = 0
# 默认设置为 0,表示不限制并发数,这里推荐设置为0,更好去发挥CPU多核处理能力,提高并发量

thread_stack = 256K   //每个连接线程被创建时,MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时,需要给它分配一定大小的内存堆栈空间,以便存放客户端的请求的Query及自身的各种状态和处理信息。Thread Cache 命中率:Thread_Cache_Hit = (Connections - Threads_created) / Connections * 100%;命中率处于90%才算正常配置,当出现“mysql-debug: Thread stack overrun”的错误提示的时候需要增加该值。此处我们配置为256K。

innodb_purge_threads = 1
# InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中,清除操作是主线程的一部分,这意味着运行时它可能会堵塞其它的数据库操作。
# 从MySQL5.5.X版本开始,该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单
# 独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1

binlog_cache_size = 250K  // 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存。作用是提高记录bin-log的效率。没有什么大事务,dml也不是很频繁的情况下可以设置小一点,如果事务大而且多,dml操作也频繁,则可以适当的调大一点。前者建议是1048576  --1M;后者建议是: 2097152 -- 4194304  即 2--4M。此处我们根据系统实际,配置为250KB。

innodb_flush_log_at_trx_commit = 2
# 0:如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘,提交事务的时候不做任何操作(执行是由mysql的master thread线程来执行的。
# 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交)默认的日志文件是ib_可以直接使用该配置参数www.9159.com:,介于MySQL会为每个连接提供连接缓冲区。logfile0,ib_logfile1
# 1:当设为默认值1的时候,每次提交事务的时候,都会将log buffer刷写到日志。
# 2:如果设为2,每次提交事务都会写日志,但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是,并不能保证100%每秒一定都会刷到磁盘,这要取决于进程的调度。
# 每次事务提交的时候将数据写入事务日志,而这里的写入仅是调用了文件系统的写入操作,而文件系统是有 缓存的,所以这个写入并不能保证数据已经写入到物理磁盘
# 默认值1是为了保证完整的ACID。当然,你可以将这个配置项设为1以外的值来换取更高的性能,但是在系统崩溃的时候,你将会丢失1秒的数据。
# 设为0的话,mysqld进程崩溃的时候,就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。
# 总结
# 设为1当然是最安全的,但性能页是最差的(相对其他两个参数而言,但不是不能接受)。如果对数据一致性和完整性要求不高,完全可以设为2,如果只最求性能,例如高并发写的日志服务器,设为0来获得更高性能

 

innodb_log_buffer_size = 2M
# 此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据。MySQL开发人员建议设置为1-8M之间

 

innodb_log_file_size = 32M
# 此参数确定数据日志文件的大小,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间

调整后各项性能参数如下图,且经过图表计算,实例使用的内存将稳定在12G左右,符合当前系统负载情况

innodb_log_files_in_group = 3
# 为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

www.9159.com 3

innodb_max_dirty_pages_pct = 90
# innodb主线程刷新缓存池中的数据,使脏数据比例小于90%

之后重启Mysql实例,发现内存占用量回落,并且长时间内没有再次发生占用过高情况,优化成功。

innodb_lock_wait_timeout = 120 
# InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒

 

bulk_insert_buffer_size = 8M
# 批量插入缓存大小, 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100-1000+条记录时, 提高效率。默认值是8M。可以针对数据量的大小,翻倍增加。

三.总结:

myisam_sort_buffer_size = 8M
# MyISAM设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区

具体涉及到内存分配,缓存的参数及其具体作用在此不一一赘述,后续可自行查阅相关资料,只有多次根据实际观测结果调优,才能得到符合当前业务系统运行的最佳配置。

myisam_max_sort_file_size = 10G
# 如果临时文件会变得超过索引,不要使用快速排序索引方法来创建一个索引。注释:这个参数以字节的形式给出

 

myisam_repair_threads = 1
# 如果该值大于1,在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内) 

参考文章:

interactive_timeout = 28800
# 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值:28800秒(8小时)

wait_timeout = 28800
# 服务器关闭非交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值,
# 取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。参数默认值:28800秒(8小时)
# MySQL服务器所支持的最大连接数是有上限的,因为每个连接的建立都会消耗内存,因此我们希望客户端在连接到MySQL Server处理完相应的操作后,
# 应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接,他们不仅会白白消耗内存,而且如果连接一直在累加而不断开,
# 最终肯定会达到MySQL Server的连接上限数,这会报'too many connections'的错误。对于wait_timeout的值设定,应该根据系统的运行情况来判断。
# 在系统运行一段时间后,可以通过show processlist命令查看当前系统的连接状态,如果发现有大量的sleep状态的连接进程,则说明该参数设置的过大,
# 可以进行适当的调整小些。要同时设置interactive_timeout和wait_timeout才会生效。

[mysqldump]
quick
max_allowed_packet = 16M #服务器发送和接受的最大包长度

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

 

(二)

[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-name-resolve
#禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!注:如果用winform连接mysql,加入此句速度会有很大的提升

skip-locking
# 避免MySQL的外部锁定,减少出错几率增强稳定性。

back_log = 384
指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求,该参数生效,主线程花费很短的时间检查连接并且启动一个新线程。 back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。

key_buffer_size = 32M
# key_buffer_size这对MyISAM表来说非常重要。如果只是使用MyISAM表,可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 — 记住,MyISAM表会使用操作系统的缓存来缓存数据,因此需要留出部分内存给它们,很多情况下数据比索引大多了。尽管如此,需要总是检查是否所有的 key_buffer 都被利用了 — .MYI 文件只有 1GB,而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表,那么也保留低于 16-32MB 的key_buffer_size 以适应给予磁盘的临时表索引所需。

innodb_buffer_pool_size = 2.4G
#这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,然而Innodb在默认的innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。– 如果你的数据量不大,并且不会暴增,那么无需把innodb_buffer_pool_size 设置的太大了。

innodb_additional_pool_size = 20M
#这个选项对性能影响并不太多,至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大),因此就需要看一下Innodb其他需要分配的内存有多少。

innodb_log_file_size = 512M
#在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高,但是要注意到可能会增加恢复时间。我经常设置为64-512MB,根据服务器大小而异。

innodb_log_buffer_size =16M
#默认的设置在中等强度写入负载以及较短事务的情况下,服务器性能还可以。如果存在更新操作峰值或者负载较大,就应该考虑加大它的值了。如果它的值设置太高了,可能会浪费内存 — 它每秒都会刷新一次,因此无需设置超过1秒所需的内存空间。通常8-16MB就足够了。越小的系统它的值越小。

innodb_flush_logs_at_trx_commit = 2
#是否为Innodb比MyISAM慢1000倍而头大?看来也许你忘了修改这个参数了。默认值是 1,这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中,而这相当耗费资源,尤其是没有电池备用缓存时。很多应用程序,尤其是从 MyISAM转变过来的那些,把它的值设置为 2 就可以了,也就是不把日志刷新到磁盘上,而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去,因此通常不会丢失每秒1-2次更新的消耗。如果设置为0就快很多了,不过也相对不安全了 — MySQL服务器崩溃时就会丢失一些事务。设置为2指挥丢失刷新到操作系统缓存的那部分事务。

max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小。注意:该参数对应的分配内存是每连接独占!如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。所以,对于内存在4GB左右的服务器推荐设置为6-8M。

read_buffer_size = 4M
#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样,该参数对应的分配内存也是每连接独享!

join_buffer_size = 8M
#联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每连接独享!

myisam_sort_buffer_size = 64M
table_cache = 512
#打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁,所以通常要加大缓存数量,使得足以最大限度地缓存打开的表。它需要用到操作系统的资源以及内存,对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话,那么设置为 1024 也许比较合适(每个线程都需要打开表),如果连接数比较大那么就加大它的值。我曾经见过设置为100,000的情况。

thread_cache_size = 64
#线程的创建和销毁的开销可能很大,因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大,那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。

query_cache_size = 64M
#指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察:

# > SHOW VARIABLES LIKE ‘%query_cache%’;
# > SHOW STATUS LIKE ‘Qcache%’;
# 如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲;Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。

tmp_table_size = 256M
max_connections = 768
#指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示,则需要增大该参数值。

max_connect_errors = 10000000
wait_timeout = 10
#指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。

thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量×2,在本例中,服务器有2颗物理CPU,而每颗物理CPU又支持H.T超线程,所以实际取值为4 × 2 = 8

skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接!

show status 命令
含义如下:
aborted_clients 客户端非法中断连接次数
aborted_connects 连接mysql失败次数
com_xxx xxx命令执行次数,有很多条
connections 连接mysql的数量
Created_tmp_disk_tables 在磁盘上创建的临时表
Created_tmp_tables 在内存里创建的临时表
Created_tmp_files 临时文件数
Key_read_requests The number of requests to read a key block from the cache
Key_reads The number of physical reads of a key block from disk
Max_used_connections 同时使用的连接数
Open_tables 开放的表
Open_files 开放的文件
Opened_tables 打开的表
Questions 提交到server的查询数
Sort_merge_passes 如果这个值很大,应该增加my.cnf中的sort_buffer值
Uptime 服务器已经工作的秒数

提升性能的建议:
1.如果opened_tables太大,应该把my.cnf中的table_cache变大
2.如果Key_reads太大,则应该把my.cnf中key_buffer_size变大.可以用Key_reads/Key_read_requests计算出cache失败率
3.如果Handler_read_rnd太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥索引的键的作用
4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率
5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基于内存的临时表代替基于磁盘的

本文由9159.com发布于www.9159.com,转载请注明出处:可以直接使用该配置参数www.9159.com:,介于MySQ

关键词: