Mysql优化(一)---服务器参数优化

总结如下：

　　(1)、back_log=500

　　这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。

　　当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值了。

　　(2)、interactive_timeout=7200

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

　　(3)、key_buffer_size=402649088

　　·是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你 能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。

　　(4)、max_connections=1024

　　允许的同时客户的数量。Too many connections 错误。

　　(5)、record_buffer=16773120

　　每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。

　　(6)、sort_buffer=16777208

　　每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)

　　(7)、table_cache=512

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

　　(8)、thread_cache_size=80

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

　

　　根据主机的硬件情况(特别是内存大小)进一步修改。

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

MYSQL服务器关键参数优化

key_buffer_size

这个参数对MyISAM表来说非常重要。如果我们的系统只是使用MyISAM表，可以把它设置为操作系统物理内存的 30-40%。取决于索引大小、数据量以及负载，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer都被利用了 。如果*.MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的话就太浪费了。如果没有MyISAM表，那么也保留16-32MB 的 key_buffer_size 以供临时表索引使用，也就是不要禁止这个参数（设置为0）。

我们下边给出几个比较重要的MYSQL服务器参数，这里我们着重讲述InooDB引擎相关的参数。

innodb_buffer_pool_size

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

有些总结中说到，在 Linux x86 上不要把内存设置太高，内存使用下面的加起来不要超过2G，这个我们提醒一下。

innodb_buffer_pool_size+ key_buffer_size+
max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB < 2G

innodb_additional_mem_pool_size

InnoDB 用来存储数据字典(data dictionary)信息和其它内部数据结构(internal data structures)的存储器组合(memory pool)大小。理想的值为 2M，
如果有更多的表你就需要在这里重新分配。如果 InnoDB 用尽这个池中的所有内存，它将从操作系统中分配内存，并将错误信息写入 MySQL 的错误日志中。
在 my.ini 中以数字格式设置。

innodb_log_file_size

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

innodb_log_buffer_size

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

innodb_flush_log_at_trx_commit

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

innodb_log_files_in_group

日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用。在 my.ini 中以数字格式设置。

innodb_lock_wait_timeout

在回滚(rooled back)之前，InnoDB 事务将等待超时的时间(单位 秒)。InnoDB 会自动检查自身在锁定表与事务回滚时的事务死锁。如果使用 LOCK TABLES 命令，或在同一个事务中使用其它事务安全型表处理器(transaction safe table handlers than InnoDB)，那么可能会发生一个 InnoDB 无法注意到的死锁。在这种情况下超时将用来解决这个问题。这个参数的默认值为 50 秒。在 my.ini 中以数字格式设置。

table_cache

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

tmp_table_size

如果内存内的临时表超过该值，MySQL自动将它转换为硬盘上的MyISAM表,所以我们在前边讲述key_buffer_size的时候曾经说过即使我们的系统中没有MyISAM表也要保留key_buffer_size的值为16-32M。如果系统有很多GROUP BY查询并且有大量内存，则可以增加tmp_table_size的值。

thread_cache

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

query_cache

如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL变慢。通常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。

max_connections

允许的并行客户端连接数目。根据系统的连接数来决定该参数的大小。

sort_buffer

就像你看到的上面这些全局表量，它们都是依据硬件配置以及不同的存储引擎而不同，但是会话变量通常是根据不同的负载来设定的。如果你只有一些简单的查询，那么就无需增加 sort_buffer_size 的值了，尽管你有 64GB 的内存。搞不好也许会降低性能。我通常在分析系统负载后才来设置会话变量。

innodb_file_io_threads

InnoDB 中的文件 I/O 线程。 通常设置为 4，但是在 Windows 下可以设定一个更大的值以提高磁盘 I/O。在 my.ini 中以数字格式设置。

innodb_fast_shutdown

如果把这个参数设置为0，InnoDB在关闭之前做一个完全净化和一个插入缓冲合并。这些操作要花几分钟时间，设置在极端情况下要几个小时。如果你设置这个参数为1，InnoDB在关闭之时跳过这些操作。默认值为1。如果你设置这个值为2 (在Netware无此值)， InnoDB将刷新它的日志然后冷关机，仿佛MySQL崩溃一样。已提交的事务不会被丢失，但在下一次启动之时会做一个崩溃恢复。

innodb_max_dirty_pages_pct

这是一个范围从0到100的整数。默认是90。InnoDB中的主线程试着从缓冲池写页面，使得脏页（没有被写的页面）的百分比不超过这个值。如果你有SUPER权限，这个百分比可以在服务器运行时按下面来改变：
SET GLOBAL innodb_max_dirty_pages_pct = value;

innodb_thread_concurrency

InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制。如果有性能问题，并且SHOW INNODB STATUS显示许多线程在等待信号，可以让线程“thrashing” ，并且设置这个参数更小或更大。如果你的计算机有多个处理器和磁盘，你可以试着这个值更大以更好地利用计算机的资源。一个推荐的值是系统上处理器和磁盘的个数之和。值为500或比500大会禁止调用并发检查。默认值是20，并且如果设置大于或等于20，并发检查将被禁止。

THREAD_STAC

每个线程的堆栈大小。用crash-me测试检测出的许多限制取决于该值。 默认值足够大，可以满足普通操作。