mysql性能怎么调整 mysql内存调优

mysql耗内存吗？应该怎么处理？

mysql耗内存吗?很多人都说MySQL占用了很大的虚拟内存，那么这个问题应该怎么解决呢?下面是我收集整理的一些方法，现在分享给大家!

创新互联公司：公司2013年成立为各行业开拓出企业自己的“网站建设”服务，为成百上千家公司企业提供了专业的成都网站设计、成都网站建设、外贸网站建设、网页设计和网站推广服务， 定制设计由设计师亲自精心设计，设计的效果完全按照客户的要求，并适当的提出合理的建议，拥有的视觉效果，策划师分析客户的同行竞争对手，根据客户的实际情况给出合理的网站构架，制作客户同行业具有领先地位的。

解决mysql耗内存的具体方法一：

在分析的过程中发现最耗内存的是MySQL，其中近1GB的内存被它吞了，而且不在任务管理器体现出来。这个数据库软件是EMS要用到了，所以必须要运行。这个软件在安装的时候会根据机器的实际内存自动进行配置，PC机物理内存越多，它默认占有的内存就越多，难怪3GB的内存被它给吞了近1GB。

优化方法：

1. 退出EMS clientserver

2. 在CMD里运行：net stop mysql

3. 找到MySQL\MySQL Server的安装目录，里面有个my.ini文件，参考附件的配置对参数query_cache_size tmp_table_size myisam_sort_buffer_size key_buffer_size innodb_buffer_pool_size进行修改，注意不要改动innodb_log_file_size，修改前备份my.ini

4. 在CMD里运行：net start mysql，如果提示成功，则说明修改的参数没有什么问题，如果失败，重新调整一下上面的参数

5. 找到EMS 安装目录runGUI.bat runServer.bat脚本，找到-Xmx700m，改为-Xmx256m，注意修改前备份这两个文件，感谢Liping Sun提供帮助

6. 重新运行EMS

前后对比，对于3GB的PC，发现可以节省近1GB的内存。对于2GB的PC，也可以节省600-800MB。优化后发现EMS启动稍微慢一些，但是其它的软件运行速度提高了很多，不在经常出现卡机现象了。如果在运行过程中发现EMS特别慢的话，自己也可以适当放大上面提到的一些参数。

my.ini

# MySQL Server Instance Configuration File

# ----------------------------------------------------------------------

# Generated by the MySQL Server Instance Configuration Wizard

#

#

# Installation Instructions

# ----------------------------------------------------------------------

#

# On Linux you can copy this file to /etc/my.cnf to set global options,

# mysql-data-dir/my.cnf to set server-specific options

# (@localstatedir@ for this installation) or to

# ~/.my.cnf to set user-specific options.

#

# On Windows you should keep this file in the installation directory

# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To

# make sure the server reads the config file use the startup option

# "--defaults-file".

#

# To run run the server from the command line, execute this in a

# command line shell, e.g.

# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"

#

# To install the server as a Windows service manually, execute this in a

# command line shell, e.g.

# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"

#

# And then execute this in a command line shell to start the server, e.g.

# net start MySQLXY

#

#

# Guildlines for editing this file

# ----------------------------------------------------------------------

#

# In this file, you can use all long options that the program supports.

# If you want to know the options a program supports, start the program

# with the "--help" option.

#

# More detailed information about the individual options can also be

# found in the manual.

#

#

# CLIENT SECTION

# ----------------------------------------------------------------------

#

# The following options will be read by MySQL client applications.

# Note that only client applications shipped by MySQL are guaranteed

# to read this section. If you want your own MySQL client program to

# honor these values, you need to specify it as an option during the

# MySQL client library initialization.

#

[client]

port=3306

[mysql]

default-character-set=utf8

# SERVER SECTION

# ----------------------------------------------------------------------

#

# The following options will be read by the MySQL Server. Make sure that

# you have installed the server correctly (see above) so it reads this

# file.

#

[mysqld]

# The TCP/IP Port the MySQL Server will listen on

port=3306

#Path to installation directory. All paths are usually resolved relative to this.

basedir="D:/Program Files/MySQL/MySQL Server 5.1/"

#Path to the database root

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"

# The default character set that will be used when a new schema or table is

# created and no character set is defined

character-set-server=utf8

# The default storage engine that will be used when create new tables when

default-storage-engine=INNODB

# Set the SQL mode to strict

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will

# allow. One of these connections will be reserved for a user with

# SUPER privileges to allow the administrator to login even if the

# connection limit has been reached.

max_connections=1510

# Query cache is used to cache SELECT results and later return them

# without actual executing the same query once again. Having the query

# cache enabled may result in significant speed improvements, if your

# have a lot of identical queries and rarely changing tables. See the

# "Qcache_lowmem_prunes" status variable to check if the current value

# is high enough for your load.

# Note: In case your tables change very often or if your queries are

# textually different every time, the query cache may result in a

# slowdown instead of a performance improvement.

query_cache_size=16M

# The number of open tables for all threads. Increasing this value

# increases the number of file descriptors that mysqld requires.

# Therefore you have to make sure to set the amount of open files

# allowed to at least 4096 in the variable "open-files-limit" in

# section [mysqld_safe]

table_cache=3020

# Maximum size for internal (in-memory) temporary tables. If a table

# grows larger than this value, it is automatically converted to disk

# based table This limitation is for a single table. There can be many

# of them.

tmp_table_size=4M

# How many threads we should keep in a cache for reuse. When a client

# disconnects, the client's threads are put in the cache if there aren't

# more than thread_cache_size threads from before. This greatly reduces

# the amount of thread creations needed if you have a lot of new

# connections. (Normally this doesn't give a notable performance

# improvement if you have a good thread implementation.)

thread_cache_size=64

#*** MyISAM Specific options

# The maximum size of the temporary file MySQL is allowed to use while

# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.

# If the file-size would be bigger than this, the index will be created

# through the key cache (which is slower).

myisam_max_sort_file_size=100G

# If the temporary file used for fast index creation would be bigger

# than using the key cache by the amount specified here, then prefer the

# key cache method. This is mainly used to force long character keys in

# large tables to use the slower key cache method to create the index.

myisam_sort_buffer_size=4M

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.

# Do not set it larger than 30% of your available memory, as some memory

# is also required by the OS to cache rows. Even if you're not using

# MyISAM tables, you should still set it to 8-64M as it will also be

# used for internal temporary disk tables.

key_buffer_size=16M

# Size of the buffer used for doing full table scans of MyISAM tables.

# Allocated per thread, if a full scan is needed.

read_buffer_size=64K

read_rnd_buffer_size=256K

# This buffer is allocated when MySQL needs to rebuild the index in

# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE

# into an empty table. It is allocated per thread so be careful with

# large settings.

sort_buffer_size=256K

#*** INNODB Specific options ***

# Use this option if you have a MySQL server with InnoDB support enabled

# but you do not plan to use it. This will save memory and disk space

# and speed up some things.

#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata

# information. If InnoDB requires more memory for this purpose it will

# start to allocate it from the OS. As this is fast enough on most

# recent operating systems, you normally do not need to change this

# value. SHOW INNODB STATUS will display the current amount used.

innodb_additional_mem_pool_size=9M

# If set to 1, InnoDB will flush (fsync) the transaction logs to the

# disk at each commit, which offers full ACID behavior. If you are

# willing to compromise this safety, and you are running small

# transactions, you may set this to 0 or 2 to reduce disk I/O to the

# logs. Value 0 means that the log is only written to the log file and

# the log file flushed to disk approximately once per second. Value 2

# means the log is written to the log file at each commit, but the log

# file is only flushed to disk approximately once per second.

innodb_flush_log_at_trx_commit=1

# The size of the buffer InnoDB uses for buffering log data. As soon as

# it is full, InnoDB will have to flush it to disk. As it is flushed

# once per second anyway, it does not make sense to have it very large

# (even with long transactions).

innodb_log_buffer_size=5M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

# row data. The bigger you set this the less disk I/O is needed to

# access data in tables. On a dedicated database server you may set this

# parameter up to 80% of the machine physical memory size. Do not set it

# too large, though, because competition of the physical memory may

# cause paging in the operating system. Note that on 32bit systems you

# might be limited to 2-3.5G of user level memory per process, so do not

# set it too high.

innodb_buffer_pool_size=32M

# Size of each log file in a log group. You should set the combined size

# of log files to about 25%-100% of your buffer pool size to avoid

# unneeded buffer pool flush activity on log file overwrite. However,

# note that a larger logfile size will increase the time needed for the

# recovery process.

innodb_log_file_size=88M

# Number of threads allowed inside the InnoDB kernel. The optimal value

# depends highly on the application, hardware as well as the OS

# scheduler properties. A too high value may lead to thread thrashing.

innodb_thread_concurrency=8

　 　解决mysql耗内存的具体方法二：

更改后如下：

innodb_buffer_pool_size=576M -256M InnoDB引擎缓冲区占了大头，首要就是拿它开刀

query_cache_size=100M -16M 查询缓存

tmp_table_size=102M -64M 临时表大小

key_buffer_size=256m -32M

重启mysql服务后，虚拟内存降到200以下.

另外mysql安装目录下有几个文件：my-huge.ini 、my-large.ini、my-medium.ini...这几个是根据内存大小作的建议配置，新手在设置的时候也可以参考一下。

2G内存的MYSQL数据库服务器 my.ini优化 (my.ini)

2G内存,针对站少,优质型的设置,试验特：

table_cache=1024 物理内存越大,设置就越大.默认为2402,调到512-1024最佳

innodb_additional_mem_pool_size=8M 默认为2M

innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列队满后再统一储存,默认为1

innodb_log_buffer_size=4M 默认为1M

innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,默认为8

key_buffer_size=256M 默认为218 调到128最佳

tmp_table_size=64M 默认为16M 调到64-256最挂

read_buffer_size=4M 默认为64K

read_rnd_buffer_size=16M 默认为256K

sort_buffer_size=32M 默认为256K

max_connections=1024 默认为1210

试验一：

table_cache=512或1024

innodb_additional_mem_pool_size=2M

innodb_flush_log_at_trx_commit=0

innodb_log_buffer_size=1M

innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,默认为8

key_buffer_size=128M

tmp_table_size=128M

read_buffer_size=64K或128K

read_rnd_buffer_size=256K

sort_buffer_size=512K

max_connections=1024

试验二：

table_cache=512或1024

innodb_additional_mem_pool_size=8M

innodb_flush_log_at_trx_commit=0

innodb_log_buffer_size=4M

innodb_thread_concurrency=8

key_buffer_size=128M

tmp_table_size=128M

read_buffer_size=4M

read_rnd_buffer_size=16M

sort_buffer_size=32M

max_connections=1024

一般：

table_cache=512

innodb_additional_mem_pool_size=8M

innodb_flush_log_at_trx_commit=0

innodb_log_buffer_size=4M

innodb_thread_concurrency=8

key_buffer_size=128M

tmp_table_size=128M

read_buffer_size=4M

read_rnd_buffer_size=16M

sort_buffer_size=32M

max_connections=1024

经过测试.没有特殊情况,最好还是用默认的.

2G内存,针对站多,抗压型的设置,最佳：

table_cache=1024 物理内存越大,设置就越大.默认为2402,调到512-1024最佳

innodb_additional_mem_pool_size=4M 默认为2M

innodb_flush_log_at_trx_commit=1

(设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1)

innodb_log_buffer_size=2M 默认为1M

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

key_buffer_size=256M 默认为218 调到128最佳

tmp_table_size=64M 默认为16M 调到64-256最挂

read_buffer_size=4M 默认为64K

read_rnd_buffer_size=16M 默认为256K

sort_buffer_size=32M 默认为256K

max_connections=1024 默认为1210

thread_cache_size=120 默认为60

query_cache_size=64M

优化mysql数据库性能的十个参数

(1)、max_connections：

允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 too many connections 错误。 默认数值是100，我把它改为1024 。

(2)、record_buffer：

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128k)，我把它改为16773120 (16m)

(3)、key_buffer_size：

索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8m)，我的mysql主机有2gb内存，所以我把它改为 402649088(400mb)。

4)、back_log：

要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。

back_log 值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。

当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。

(5)、interactive_timeout：

服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 client_interactive 选项的客户。 默认数值是28800，我把它改为7200。

(6)、sort_buffer：

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是2097144(2m)，我把它改为 16777208 (16m)。

(7)、table_cache：

为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。

(8)、thread_cache_size：

可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 connections 和 threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。

(9)mysql的搜索功能

用mysql进行搜索，目的是能不分大小写，又能用中文进行搜索

只需起动mysqld时指定 --default-character-set=gb2312

(10)、wait_timeout：

服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。

注：参数的调整可以通过修改 /etc/my.cnf 文件并重启 mysql 实现。这是一个比较谨慎的工作，上面的结果也仅仅是我的一些看法，你可以根据你自己主机的硬件情况(特别是内存大小)进一步修改。

MySQL性能调优 – 你必须了解的15个重要变量

前言:

MYSQL 应该是最流行了 WEB 后端数据库。虽然 NOSQL 最近越来越多的被提到，但是相信大部分架构师还是会选择 MYSQL 来做数据存储。本文作者总结梳理MySQL性能调优的15个重要变量，又不足需要补充的还望大佬指出。

1.DEFAULT_STORAGE_ENGINE

如果你已经在用MySQL 5.6或者5.7，并且你的数据表都是InnoDB，那么表示你已经设置好了。如果没有，确保把你的表转换为InnoDB并且设置default_storage_engine为InnoDB。

为什么？简而言之，因为InnoDB是MySQL(包括Percona Server和MariaDB)最好的存储引擎 – 它支持事务，高并发，有着非常好的性能表现(当配置正确时)。这里有详细的版本介绍为什么

2.INNODB_BUFFER_POOL_SIZE

这个是InnoDB最重要变量。实际上，如果你的主要存储引擎是InnoDB，那么对于你，这个变量对于MySQL是最重要的。

基本上，innodb_buffer_pool_size指定了MySQL应该分配给InnoDB缓冲池多少内存，InnoDB缓冲池用来存储缓存的数据，二级索引，脏数据(已经被更改但没有刷新到硬盘的数据)以及各种内部结构如自适应哈希索引。

根据经验，在一个独立的MySQL服务器应该分配给MySQL整个机器总内存的80%。如果你的MySQL运行在一个共享服务器，或者你想知道InnoDB缓冲池大小是否正确设置，详细请看这里。

3.INNODB_LOG_FILE_SIZE

InnoDB重做日志文件的设置在MySQL社区也叫做事务日志。直到MySQL 5.6.8事务日志默认值innodb_log_file_size=5M是唯一最大的InnoDB性能杀手。从MySQL 5.6.8开始，默认值提升到48M,但对于许多稍繁忙的系统，还远远要低。

根据经验，你应该设置的日志大小能在你服务器繁忙时能存储1-2小时的写入量。如果不想这么麻烦，那么设置1-2G的大小会让你的性能有一个不错的表现。这个变量也相当重要，更详细的介绍请看这里。

当然，如果你有大量的大事务更改，那么，更改比默认innodb日志缓冲大小更大的值会对你的性能有一定的提高，但是你使用的是autocommit，或者你的事务更改小于几k，那还是保持默认的值吧。

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

默认下，innodb_flush_log_at_trx_commit设置为1表示InnoDB在每次事务提交后立即刷新同步数据到硬盘。如果你使用autocommit，那么你的每一个INSERT, UPDATE或DELETE语句都是一个事务提交。

同步是一个昂贵的操作(特别是当你没有写回缓存时)，因为它涉及对硬盘的实际同步物理写入。所以如果可能，并不建议使用默认值。

两个可选的值是0和2:

* 0表示刷新到硬盘，但不同步(提交事务时没有实际的IO操作)

* 2表示不刷新和不同步(也没有实际的IO操作)

所以你如果设置它为0或2，则同步操作每秒执行一次。所以明显的缺点是你可能会丢失上一秒的提交数据。具体来说，你的事务已经提交了，但服务器马上断电了，那么你的提交相当于没有发生过。

显示的，对于金融机构，如银行，这是无法忍受的。不过对于大多数网站，可以设置为innodb_flush_log_at_trx_commit=0|2，即使服务器最终崩溃也没有什么大问题。毕竟，仅仅在几年前有许多网站还是用MyISAM，当崩溃时会丢失30s的数据(更不要提那令人抓狂的慢修复进程)。

那么，0和2之间的实际区别是什么？性能明显的差异是可以忽略不计，因为刷新到操作系统缓存的操作是非常快的。所以很明显应该设置为0，万一MySQL崩溃(不是整个机器)，你不会丢失任何数据，因为数据已经在OS缓存，最终还是会同步到硬盘的。

5.SYNC_BINLOG

已经有大量的文档写到sync_binlog，以及它和innodb_flush_log_at_trx_commit的关系，下面我们来简单的介绍下：

a) 如果你的服务器没有设置从服务器，而且你不做备份，那么设置sync_binlog=0将对性能有好处。

b) 如果你有从服务器并且做备份，但你不介意当主服务器崩溃时在二进制日志丢失一些事件，那么为了更好的性能还是设置为sync_binlog=0.

c) 如果你有从服务器并且备份，你非常在意从服务器的一致性，以及能及时恢复到一个时间点(通过使用最新的一致性备份和二进制日志将数据库恢复到特定时间点的能力)，那么你应该设置innodb_flush_log_at_trx_commit=1，并且需要认真考虑使用sync_binlog=1。

问题是sync_binlog=1代价比较高 – 现在每个事务也要同步一次到硬盘。你可能会想为什么不把两次同步合并成一次，想法正确 – 新版本的MySQL(5.6和5.7，MariaDB和Percona Server)已经能合并提交，那么在这种情况下sync_binlog=1的操作也不是这么昂贵了，但在旧的mysql版本中仍然会对性能有很大影响。

6.INNODB_FLUSH_METHOD

将innodb_flush_method设置为O_DIRECT以避免双重缓冲.唯一一种情况你不应该使用O_DIRECT是当你操作系统不支持时。但如果你运行的是Linux，使用O_DIRECT来激活直接IO。

不用直接IO，双重缓冲将会发生，因为所有的数据库更改首先会写入到OS缓存然后才同步到硬盘 – 所以InnoDB缓冲池和OS缓存会同时持有一份相同的数据。特别是如果你的缓冲池限制为总内存的50%，那意味着在写密集的环境中你可能会浪费高达50%的内存。如果没有限制为50%，服务器可能由于OS缓存的高压力会使用到swap。

简单地说，设置为innodb_flush_method=O_DIRECT。

7.INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5引入了缓冲实例作为减小内部锁争用来提高MySQL吞吐量的手段。

在5.5版本这个对提升吞吐量帮助很小，然后在MySQL 5.6版本这个提升就非常大了，所以在MySQL5.5中你可能会保守地设置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以设置为8-16个缓冲池实例。

你设置后观察会觉得性能提高不大，但在大多数高负载情况下，它应该会有不错的表现。

对了，不要指望这个设置能减少你单个查询的响应时间。这个是在高并发负载的服务器上才看得出区别。比如多个线程同时做许多事情。

8.INNODB_THREAD_CONCURRENCY

InnoDB有一种方法来控制并行执行的线程数 – 我们称为并发控制机制。大部分是由innodb_thread_concurrency值来控制的。如果设置为0，并发控制就关闭了，因此InnoDB会立即处理所有进来的请求(尽可能多的)。

在你有32CPU核心且只有4个请求时会没什么问题。不过想像下你只有4CPU核心和32个请求时 – 如果你让32个请求同时处理，你这个自找麻烦。因为这些32个请求只有4 CPU核心，显然地会比平常慢至少8倍(实际上是大于8倍)，而然这些请求每个都有自己的外部和内部锁，这有很大可能堆积请求。

下面介绍如何更改这个变量，在mysql命令行提示符执行：

对于大多数工作负载和服务器，设置为8是一个好开端，然后你可以根据服务器达到了这个限制而资源使用率利用不足时逐渐增加。可以通过show engine innodb status\G来查看目前查询处理情况，查找类似如下行：

9.SKIP_NAME_RESOLVE

这一项不得不提及，因为仍然有很多人没有添加这一项。你应该添加skip_name_resolve来避免连接时DNS解析。

大多数情况下你更改这个会没有什么感觉，因为大多数情况下DNS服务器解析会非常快。不过当DNS服务器失败时，它会出现在你服务器上出现“unauthenticated connections” ，而就是为什么所有的请求都突然开始慢下来了。

所以不要等到这种事情发生才更改。现在添加这个变量并且避免基于主机名的授权。

10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

* innodb_io_capacity：用来当刷新脏数据时，控制MySQL每秒执行的写IO量。

* innodb_io_capacity_max: 在压力下，控制当刷新脏数据时MySQL每秒执行的写IO量

首先，这与读取无关 – SELECT查询执行的操作。对于读操作，MySQL会尽最大可能处理并返回结果。至于写操作，MySQL在后台会循环刷新，在每一个循环会检查有多少数据需要刷新，并且不会用超过innodb_io_capacity指定的数来做刷新操作。这也包括更改缓冲区合并（在它们刷新到磁盘之前，更改缓冲区是辅助脏页存储的关键）。

第二，我需要解释一下什么叫“在压力下”，MySQL中称为”紧急情况”，是当MySQL在后台刷新时，它需要刷新一些数据为了让新的写操作进来。然后，MySQL会用到innodb_io_capacity_max。

那么，应该设置innodb_io_capacity和innodb_io_capacity_max为什么呢？

最好的方法是测量你的存储设置的随机写吞吐量，然后给innodb_io_capacity_max设置为你的设备能达到的最大IOPS。innodb_io_capacity就设置为它的50-75%，特别是你的系统主要是写操作时。

通常你可以预测你的系统的IOPS是多少。例如由8 15k硬盘组成的RAID10能做大约每秒1000随机写操作，所以你可以设置innodb_io_capacity=600和innodb_io_capacity_max=1000。许多廉价企业SSD可以做4,000-10,000 IOPS等。

这个值设置得不完美问题不大。但是，要注意默认的200和400会限制你的写吞吐量，因此你可能偶尔会捕捉到刷新进程。如果出现这种情况，可能是已经达到你硬盘的写IO吞吐量，或者这个值设置得太小限制了吞吐量。

11.INNODB_STATS_ON_METADATA

如果你跑的是MySQL 5.6或5.7，你不需要更改innodb_stats_on_metadata的默认值，因为它已经设置正确了。

不过在MySQL 5.5或5.1，强烈建议关闭这个变量 – 如果是开启，像命令show table status会立即查询INFORMATION_SCHEMA而不是等几秒再执行，这会使用到额外的IO操作。

从5.1.32版本开始，这个是动态变量，意味着你不需要重启MySQL服务器来关闭它。

12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN INNODB_BUFFER_POOL_LOAD_AT_STARTUP

innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个变量与性能无关，不过如果你偶尔重启mysql服务器(如生效配置)，那么就有关。当两个都激活时，MySQL缓冲池的内容(更具体地说，是缓存页)在停止MySQL时存储到一个文件。当你下次启动MySQL时，它会在后台启动一个线程来加载缓冲池的内容以提高预热速度到3-5倍。

两件事：

第一，它实际上没有在关闭时复制缓冲池内容到文件，仅仅是复制表空间ID和页面ID – 足够的信息来定位硬盘上的页面了。然后它就能以大量的顺序读非常快速的加载那些页面，而不是需要成千上万的小随机读。

第二，启动时是在后台加载内容，因为MySQL不需要等到缓冲池内容加载完成再开始接受请求(所以看起来不会有什么影响)。

从MySQL 5.7.7开始，默认只有25%的缓冲池页面在mysql关闭时存储到文件，但是你可以控制这个值 – 使用innodb_buffer_pool_dump_pct，建议75-100。

这个特性从MySQL 5.6才开始支持。

13.INNODB_ADAPTIVE_HASH_INDEX_PARTS

如果你运行着一个大量SELECT查询的MySQL服务器(并且已经尽可能优化)，那么自适应哈希索引将下你的下一个瓶颈。自适应哈希索引是InnoDB内部维护的动态索引，可以提高最常用的查询模式的性能。这个特性可以重启服务器关闭，不过默认下在mysql的所有版本开启。

这个技术非常复杂，在大多数情况下它会对大多数类型的查询直到加速的作用。不过，当你有太多的查询往数据库，在某一个点上它会花过多的时间等待AHI锁和闩锁。

如果你的是MySQL 5.7，没有这个问题 – innodb_adaptive_hash_index_parts默认设置为8，所以自适应哈希索引被切割为8个分区，因为不存在全局互斥。

不过在mysql 5.7前的版本，没有AHI分区数量的控制。换句话说，有一个全局互斥锁来保护AHI，可能导致你的select查询经常撞墙。

所以如果你运行的是5.1或5.6，并且有大量的select查询，最简单的方案就是切换成同一版本的Percona Server来激活AHI分区。

14.QUERY_CACHE_TYPE

如果人认为查询缓存效果很好，肯定应该使用它。好吧，有时候是有用的。不过这个只在你在低负载时有用，特别是在低负载下大多数是读取，小量写或者没有。

如果是那样的情况，设置query_cache_type=ON和query_cache_size=256M就好了。不过记住不能把256M设置更高的值了，否则会由于查询缓存失效时，导致引起严重的服务器停顿。

如果你的MySQL服务器高负载动作，建议设置query_cache_size=0和query_cache_type=OFF，并重启服务器生效。那样Mysql就会停止在所有的查询使用查询缓存互斥锁。

15.TABLE_OPEN_CACHE_INSTANCES

从MySQL 5.6.6开始，表缓存能分割到多个分区。

表缓存用来存放目前已打开表的列表，当每一个表打开或关闭互斥体就被锁定 – 即使这是一个隐式临时表。使用多个分区绝对减少了潜在的争用。

从MySQL 5.7.8开始，table_open_cache_instances=16是默认的配置。

欢迎做Java的工程师朋友们私信我资料免费获取免费的Java架构学习资料（里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料）

其中覆盖了互联网的方方面面，期间碰到各种产品各种场景下的各种问题，很值得大家借鉴和学习，扩展自己的技术广度和知识面。

MySQL数据库性能优化有哪些技巧？

1.存储引擎的选择如果数据表需要事务处理，应该考虑使用InnoDB，因为它完全符合ACID特性。如果不需要事务处理，使用默认存储引擎MyISAM是比较明智的。并且不要尝试同时使用这两个存储引擎。思考一下：在一个事务处理中，一些数据表使用InnoDB，而其余的使用MyISAM.结果呢？整个subject将被取消，只有那些在事务处理中的被带回到原始状态，其余的被提交的数据转存，这将导致整个数据库的冲突。然而存在一个简单的方法可以同时利用两个存储引擎的优势。目前大多数MySQL套件中包括InnoDB、编译器和链表，但如果你选择MyISAM，你仍然可以单独下载InnoDB，并把它作为一个插件。很简单的方法，不是吗？

2.计数问题如果数据表采用的存储引擎支持事务处理（如InnoDB），你就不应使用COUNT（*）计算数据表中的行数。这是因为在产品类数据库使用COUNT（*），最多返回一个近似值，因为在某个特定时间，总有一些事务处理正在运行。如果使用COUNT（*）显然会产生bug，出现这种错误结果。

3.反复测试查询查询最棘手的问题并不是无论怎样小心总会出现错误，并导致bug出现。恰恰相反，问题是在大多数情况下bug出现时，应用程序或数据库已经上线。的确不存在针对该问题切实可行的解决方法，除非将测试样本在应用程序或数据库上运行。任何数据库查询只有经过上千个记录的大量样本测试，才能被认可。

4.避免全表扫描通常情况下，如果MySQL（或者其他关系数据库模型）需要在数据表中搜索或扫描任意特定记录时，就会用到全表扫描。此外，通常最简单的方法是使用索引表，以解决全表扫描引起的低效能问题。然而，正如我们在随后的问题中看到的，这存在错误部分。

5.使用“EXPLAIN”进行查询当需要调试时，EXPLAIN是一个很好的命令，下面将对EXPLAIN进行深入探讨。

mysql 性能优化参数配置

mysql -u root -p 回车输入密码进入mysql

show processlist;

查看连接数，可以发现有很多连接处于sleep状态，这些其实是暂时没有用的，所以可以kill掉

show variables like "max_connections";

查看最大连接数，应该是与上面查询到的连接数相同，才会出现too many connections的情况

set GLOBAL max_connections=1000;

修改最大连接数，但是这不是一劳永逸的方法，应该要让它自动杀死那些sleep的进程。

show global variables like 'wait_timeout';

这个数值指的是mysql在关闭一个非交互的连接之前要等待的秒数，默认是28800s

set global wait_timeout=300;

修改这个数值，这里可以随意，最好控制在几分钟内

set global interactive_timeout=500;

修改这个数值，表示mysql在关闭一个连接之前要等待的秒数，至此可以让mysql自动关闭那些没用的连接，但要注意的是，正在使用的连接到了时间也会被关闭，因此这个时间值要合适

SHOW VARIABLES LIKE '%table_open_cache%';

查看

show global status like 'Open%tables';