创建mysql配置文件

  请尊重知识，请尊重原创 更多资料参考请见  http://www.cezuwang.com/listFilm?page=1&areaId=906&filmTypeId=1
(一) mysql配置文件
  mysql 的缔造者 们从来不会认为当前服务器上只有mysql这一种服务，所以mysql的默认配置总是以占用尽可能小的资源、
仅仅可以支持mysql程序启动并且能执行一些简单的查询为目的。所以一切以mysql默认配置为基准进行的性能测试都是耍流氓的行为。
  mysql的默认配置基本上可以满足大多数人的需求了。比如说采用InnoDB为存储引擎，指定用户，指定数据存放路径，日志文件以及临时表的
  大小等。
 
  但是若想获取更高性能的mysql配置，还必须对其中的部分选项做修改。
  1 配置大小合适的缓冲池(Buffer Pool) 和日志文件(log file) 是必须的。默认值都太小了。
    缓冲池的大小没有放之天下而皆准的配置，有一些需要遵循的原则。
1)从服务器的内存总量开始。
2)减掉操作系统的内存占用，如果mysql不是唯一运行在这个服务器上的程序，还要扣掉其他程序可能占用的内存。
3)减去一些mysql 自身需要的内存，例如为每个查询操作分配的缓冲。
4)减去足够让操作系统缓存InnoDB日志文件的内存，至少是足够缓存最近经常访问的部分。
5)减去其他配置的mysql缓冲和缓存需要的内存，例如查询缓存。
6)除以105%，这差不多接近InnoDB管理缓冲池增加的自身管理开销。
7)把结果四舍五入，向下取一个合理的数值。向下舍入不会有太大的影响，但是如果分配太多可能就会是件很糟糕的事情。
  分配的小只可能产生较小的性能影响，但是分配过大，则会出现较严重的问题:内存交换、磁盘抖动、内存耗尽、硬件死机。

  (二) 配置内存使用
    1 配置mysql正确的使用内存量对高性能是至关重要的。mysql的内存消耗分为两类：可以控制的内存和不可以控制的内存。无法控制
mysql服务器运行、解析查询以及其内部管理所消耗的内存，但是为特定目的而使用多少内存则可以有很多参数来控制。
按照下面的步骤来配置内存：
1)确定可以使用的内存上限。
2)确定每个连接mysql需要多少内存，例如排序缓冲和临时表。
3)确定操作系统需要多少内存才够用。包括同一台机器上其他程序占用的内存。
4)把剩下的内存全部交给mysql的缓存，例如InnoDB的缓冲池，这样做很有意义。
下面分门别类讲解每一条会占用多少内存。

    2 mysql可以使用多少内存
  在任何给定的操作系统上，mysql都有允许使用的内存上限。基本出发点是机器安装了多少内存。
  还需要考虑操作系统和架构的限制。例如32位操作系统对于一个给定的进程可以处理多少内存是有限制的。因为mysql是单进程
  多线程的运行模式，她整体可用的内存量也许会受操作系统严格的限制，例如32位linux内核通常限制任意进程可以使用的内存量在2.5GB
  到2.7GB范围内，运行时地址空间溢出是非常危险的。在其他操作系统中，特殊的参数和古怪的事情必须考虑到，例如对堆栈大小和其他配置的限制。
 
  在mysql5.1和更新的版本中，部分限制已经被取消了，在mysql的手册中记载了每个变量的最大值。
 
3 每个连接需要的内存
  mysql保持一个连接只需要少量的内存。但是我们需要知道在高峰时期mysql将消耗多少内存，这是非常重要的。
  当预测内存峰值消耗时，不需要假设最坏情况，更好地办法是观察服务器在真实的工作压力下使用了多少内存，可以在进程的虚拟内存大小那里看到。在
  许多类unix系统里，可以观察top命令中的virt列或者ps命令中的vsz列。
 
4 为操作系统保留内存
  操作系统也需要保留足够的内存给她工作。如果没有虚拟内存正在交换到磁盘(paging)，这就表明操作系统内存足够的最佳迹象。
  不需要为操作系统预留缓存，操作系统通常会利用剩下来的内存来做文件系统缓存。
 
5 为缓存分配内存
      如果服务器只运行mysql，所有不需要为操作系统以及查询处理保留的内存都可以用作mysql缓存。相比其他mysql需要为缓存分配更多的内存以避免
      磁盘访问。
      下面是我们认为对大部分情况来说最重要的缓存:
   InnoDB缓冲池
   InnoDB日志文件 和 myisam数据的操作系统缓存
   myisam键缓存
   查询缓存
   无法手工配置的缓存例如二进制日志和表定义文件的操作系统缓存
  
   如果只使用单一存储引擎，配置服务器就简单很多了。如果只使用InnoDB引擎，就只需要分配最少的资源给myisam(mysql内部系统表采用myisam)
   如果是混合使用各种存储引擎，就没有那么简单找到恰当的平衡，最好的办法是先做一个有根据的猜测，然后在运行中观察服务器。
      请尊重知识，请尊重原创 更多资料参考请见  http://www.cezuwang.com/listFilm?page=1&areaId=906&filmTypeId=1
6 InnoDB 缓冲池(Buffer Pool)
  InnoDB 严重依赖缓冲池。必须确认为他分配了足够的内存。可以使用innotop这样的工具监控InnoDB缓冲池的内存利用情况。
  通常只需要为大数据量和快速增长的数据设置大内存作为缓冲池。很大的缓冲池也会带来一些挑战例如预热和关闭可能会花费很长的时间
  如果有很多脏页在缓冲池里,InnoDB 关闭时可能会花费比较长的时间，因为在关闭之前需要把脏页写会到数据文件。如果知道什么时候关闭InnoDB，可以在运行时修改
  innodb_max_dirty_pages_pct变量，将值改小，然后等待线程清理缓冲池，在脏页较少时关闭。可以使用命令 sql>show engine innodb status;来观察脏页的刷新量。
 
 
7 myisam键缓存(key_buffer_size)
  myisam 在这里的使用频率不高，其键缓存只做简单介绍。
  myisam的键缓存不像其他引擎，myisam键缓存只缓存索引不缓存数据，配置的关键是key_buffer_size ，配置之前了解myisam索引实际上占用多少磁盘空间是
  必须的。使用下面查询，找到表的index_length的字段，把他们的值相加就可以得到索引存储占用的空间。
  sql> select sum(INDEX_LENGTH) from information_schema.`TABLES`
  即使没有任何MyIsAM表，仍然需要将key_buffer_size设置为较小的值，例如32M，因为mysql服务器有时会在内部使用MyIsAM表，例如group by
  语句会使用MyISAM作为临时表。
 
8 mysql键缓存块大小(key block size) 
  块大小也是很重要的，尤其是写密集型负载，因为 她影响了MyISAM、操作系统 缓存以及文件系统之间的交互。如果缓存块太小了，可能会碰到写时读，
  就是操作系统在执行写操作之前必须先从磁盘上读取一些数据，这会影响性能。
  我们模拟一个案例，假设操作系统的页大小是4KB，索引块是1KB。
  1 MyISAM请求从磁盘上读取1KB的块。
  2 操作系统从磁盘上读取4KB的数据并缓存，把1KB的数据交给MyISAM.
  3 操作系统丢弃缓存数据以给其他数据腾出缓存空间。
  4 MyISAM修改1KB的索引块，然后请求操作系统把它写回磁盘。
  5 操作系统从磁盘读取同一个4KB的数据，写入操作系统缓存，修改MyISAM的这1KB数据，然后把整个4KB的块写回磁盘。
  在第五步就发生了读时写，如果MyISAM的块大小和操作系统的一样大，就可以避免这种情况。
  在mysql 5.1以及更新版本中，可以设置MyISAM的索引块大小跟操作系统一样，以避免读时写。

9 线程缓存
  mysql可以缓存连接线程，避免再次创建连接线程。配置文件 thread_cache_size变量指定了Mysql可以保持在缓存中的线程数。一般不需要配置这个值，
  除非服务器会有很多连接请求。要检查线程缓存是否足够大，可以查看Threads_created状态变量。
  sql> show global status like 'Thread%';
 
10 表缓存
  表缓存的设计是服务器和存储引擎之间分离不彻底的产物。表缓存的真正好处是，可以让服务器避免修改MyISAM文件头来标记表"正在使用中"。
  InnoDB 有自己的表缓存设计。
  在mysql5.1版本中，表缓存分离成两部分，一个是打开表的缓存，一个是表定义缓存。分别通过table_open_cache 和 table_definition_cache来配置。
  其结果是表定义(解析.frm文件的结果)从其他资源中分离出来了。打开的表依然是每个线程、每个表用的，但是表定义是全局的，可以被所有连接有效地共享。
  通常把table_definition_cache定义的足够高，以缓存所有的表定义。除非有上万张表，否则这可能是最简单的方法。
  如果opened_tables 这个状态量很大或者在增长，这表示表缓存不够大。需要设置table_open_cache的值。
 
  表缓存设置的太大也有缺点，当服务器有很多MyISAM表时，可能会导致关机时间较长，因为关机前索引块必须完成刷新,表都必须被标记为不再打开。
 
  如果遇到mysql无法打开更多的文件错误，这个错误可以使用perror工具来检查具体代号的含义，那么就需要增加mysql允许打开文件的数量。这可以通过
  在my.cnf文件中 设置open_file_limit服务器变量来实现。
 
  线程和表缓存实际上消耗的内存并不多，相反，他们却可以有效的节约资源。虽然创建一个线程和打开一个表的消耗相对于其他操作来说代价并不高，但是这些确实累加起来的
  ，在高并发和大访问量的环境下，这些消耗还是很可观的。所以线程和表的缓存有时可以提升效率。
 
11 InnoDB数据字典
   InnoDB 有自己的表缓存策略，可以称之为表定义缓存或者数据字典。当InnoDB打开一张表的时候，就会在数据字典中新增一个对应的对象。
  由于没有对应的缓存过期策略，服务器可能会出现内存泄露，这种问题通常只会出现在数千张大表时。如果这个问题有影响，可以通过Percona Server的一个
  选项来控制数据字典的大小，他会从数据字典中移除没有使用的表。
 
  另外一个性能问题是第一次打开表时会计算统计信息，这需要很多IO操作，所以代价很高。相比MyISAM，InnoDB没有将统计信息持久化，
  而是在每次打开表时重新计算，在打开之后，每隔一段时间或者遇到触发事件(改变表的内容或者查询infomation_schema表),也会重新计算统计信息。如果有很多表
  服务器会花费数个小时来启动并完成预热，在这个时候，服务器会花费很多事件在等待IO而不是做其他事情。
  为了解决这个问题，可以在percona server 中打开innodb_use_sys_status_table选项来持久化存储统计信息到磁盘,在mysql 5.6中，这个选项叫做innodb_analyze_is_persistent
  .
 
  在正常启动之后，InnoDB统计操作仍然可能对服务器和一些特定的查询产生冲击。可以关闭innodb_stats_on_metadata选项来避免】
  耗时的表统计信息刷新。关闭之后效果是很不一样的。
     请尊重知识，请尊重原创 更多资料参考请见  http://www.cezuwang.com/listFilm?page=1&areaId=906&filmTypeId=1
  如果设置了InnoDB的innodb_file_per_table选项,InnoDB任意时刻可以保持打开.ibd文件的数量是有限的。InnoDB打开文件和MyISAM的方式不一样，MyISAM用表缓存来持有打开表的文件描述符，
  而InnoDB在打开表和打开文件之间没有直接的关系。InnoDB为每个文件使用单个的全局的文件描述符。如果可以，最好把innodb_open_files的值设置的足够大以使服务器可以保持所有的.ibd文件同时打开。
 
 
12 配置mysql的IO行为
   mysql的IO操作时很昂贵的。必须对IO操作做出最合理的配置。
   1)InnoDB IO配置
     InnoDB有一系列复杂的缓存和文件设计可以提升性能。对于常见的应用，最重要的一部分内容是InnoDB日志文件大小、InnoDB怎样刷新他的日志缓冲以及InnoDB怎样执行IO。
InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无需在每个事务提交时把缓冲块的赃块刷新到磁盘。
     InnoDB用日志把随机IO变成顺序IO，一旦日志安全写到磁盘，事务就持久化了，即使数据变更还没写到数据文件，InnoDB可以根据日志恢复已经提交的事务。
最终InnoDB还是必须把变更写到数据文件，因为日志有固定的大小。InnoDB使用环形方式写日志，到达日志的尾部会重新跳转到开头继续写但是不会覆盖还没有持久化到数据文件的日志记录。

整体的日志文件大小受控于innodb_log_file_size 和 innodb_log_files_in_group两个参数，这对写性能非常重要。默认情况下，只有两个5M的文件，这对高性能工作来说太小了。至少需要
几百M或者几个GB的日志文件。InnoDB使用多个文件作为一组循环日志，不需要修改默认的日志文件数量，修改每个文件的大小即可。修改文件大小，必须完全关闭mysql，将旧的
日志文件保存，重新配置参数并重启。

mysql 日志缓冲区默认是1M，这是非常小的，会造成大量的IO，可以通过设置innodb_log_buffer_size来控制日志缓冲区的大小。
通常不需要把日志缓冲区设置的非常大，推荐的范围是1-8M即可，除非要写很多相当大的blob记录。

可以通过 mysql>show engine InnoDB status;的输出中log部分来监控InnoDB日志和日志缓冲区的IO性能，通过mysql>show global status like 'Innodb_os_log_written';
对日志文件写出了多少数据。一个好的经验是 观察10-100秒间隔的数字，然后记录峰值，以此判断设置是否合理。例如峰值是每秒写入100K的数据，那么1M的日志缓冲区就够了。
日志文件的全部大小，应该能足够容纳服务器一个小时的活动内容。

innodb刷新日志缓冲的方式是，当innodb把日志缓冲刷新到磁盘日志文件时，会先用一个mutex锁住缓冲区，刷新到所需要的位置，然后移动剩下的条目到
缓冲区的前面，当mutex释放时，可能有多个事务已经准备好刷新其日志记录，innodb有一个group commit功能，可以在一个IO操作内提交多个事务。
日志缓冲必须被刷新到持久化存储，以确保提交的事务都被持久化了。和持久化相比，如果更看重性能，可以通过设置innodb_flush_log_at_trx_commit
变量来控制日志缓冲区刷新的频繁程度。
innodb_flush_log_at_trx_commit可能的值是 0 1 2 ，0 表示每秒钟刷新一次，1 表示每次提交事务都刷新到持久存储。 2 表示 每次提交时把日志缓冲写道日志文件 但是并不刷新，这是0与2最大的不同，也是为什么2是更合适的设置。
在大部分的操作系统中，把缓冲写到日志，只是简单的把数据从InnoDB的缓冲转移到了操作系统的缓存，还是在内存中，并没有真正的把数据写到持久化存储。
因此如果MySQL崩溃了或者是断电了，0与2的设置最多会丢失1秒钟的事务，因为数据只存在于操作系统的缓存。InnoDB会每秒尝试刷新日志文件到磁盘。

高性能事务处理需要的最佳配置时把innodb_flush_log_at_trx_commit 设置为1 并且把日志文件放到一个有电池保护的写缓存的RAID卷中。事实上，任何希望能扛过高负荷工作
负载的产品数据库服务器都需要这种类型的硬件。

innodb刷新日志缓冲的方式是，当innodb把日志缓冲刷新到磁盘日志文件时，会先用一个mutex锁住缓冲区，刷新到所需要的位置，然后移动剩下的条目到
缓冲区的前面，当mutex释放时，可能有多个事务已经准备好刷新其日志记录，innodb有一个group commit功能，可以在一个IO操作内提交多个事务。
日志缓冲必须被刷新到持久化存储，以确保提交的事务都被持久化了。和持久化相比，如果更看重性能，可以通过设置innodb_flush_log_at_trx_commit
变量来控制日志缓冲区刷新的频繁程度。
innodb_flush_log_at_trx_commit可能的值是 0 1 2 ，0 表示每秒钟刷新一次，1 表示每次提交事务都刷新到持久存储。 2 表示 每次提交时把日志缓冲写道日志文件

13  InnoDB怎样打开和刷新日志以及数据文件
    使用innodb_flush_method选项可以配置innodb如何跟文件系统相互作用。这个选项影响了innodb如何读写数据。在windows默认值是unbuffered其他操作
系统默认是fdatasync.可以通过show global varibales 观察这个值。如果使用类unix操作系统并且RAAID控制器带有电池保护的写缓存，我们建议使用
0_DIRECT,如果不是这样，使用默认值或者0_DIRECT都可能是最好的选择。

    14 InnoDB表空间
        InnoDB把数据保存在表空间，本质上市一个由一个或多个磁盘文件组成的虚拟文件系统。InnoDB用表空间实现很多功能，并不是只存储表和索引。她还保存了回滚日志、插入缓冲、回滚
缓冲以及其他内部数据结构。
        配置表空间,通过innodb_data_file_path配置项可以定制表空间配置文件。这些文件都放在innodb_data_home_dir指定的目录下。
innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G 这里配置了3G的表空间，使用了多个文件分散了驱动器的负载。
这种方式被innodb首位连接起来，逐个使用，通常这种方式并不能获得太多的负载收益，使用RAID控制器是分布负载更聪明的方式。  
 
这里建议使用innodb_file_per_table 并且给共享表空间设置大小范围，这样就不需要处理空间回收，如果遇到麻烦，可以使用percona server
设置innodb_lazy_drop_table 允许服务器慢慢清理掉属于被删除表的页面。
 
    建议使用，但是我们必须知道innodb_file_per_table的其他方面。
这个选项允许innodb为每张表使用一个文件，这使得删除一张表变得简单，并且可以很简单的把数据分配到不同的磁盘上。但是缺点是浪费空间，因为把单个innodb表空间的
内部碎片浪费分布到了多个.ibd文件，因为innodb的页大小是16KB，即使只有1KB的数据也需要占用16KB的空间。
这个选项还会使drop table 变得更差，不过可以通过设置innodb_lazy_drop_table来减轻。