配置内存篇

可以认为MySQL的内存消耗分为两类：可以控制的内存和不可以控制的内存。

按照下面的步骤来配置内存：

（1）确定可以使用的内存上限；

（2）确定每个连接MySQL需要使用多少内存，例如排序缓冲和临时表。

（3）确定操作系统需要多少内存才够用。包含同一台机器上其他程序使用过的内存，如定时任务。

（4）把剩下的内存全部给MySQL的缓存，例如InnoDB的缓冲池，这样做也很有意义。

1 .MySQL 可以使用多少内存。在任何给定的操作系统上，MySQL都有允许使用的内存上限。


2 .每个链接需要的内存。MySQL保持一个连接（线程）只需要少量的内存。它还要求一个基本量的内存来执行任何给定查询。你需要为高峰时期执行的大量查询预留好足够的内存。否则，查询执行可能缺乏内存而导致执行效率不佳或者执行失败。


3 .为操作系统保留内存。跟查询一样，操作系统也需要保留足够的内存给它工作。如果没有虚拟内存正在交换到到磁盘，就是表明操作系统内存足够的最佳迹象。


4 .为缓存分配内存。相比其他，MySQL需要为缓存分配更多的内存。它使用缓存来避免磁盘访问，磁盘访问比内存访问数要慢的多。操作系统可能会缓存一些数据，这对MySQL有些好处（尤其是M有ISAM），但是MySQL自身也需要大量的内存。

下面是我们都认为大部分情况来说最重要的缓存：

（1）InnoDB缓冲池

（2）InnoDB日志文件和MyISAM数据的操作系统缓存

（3）MyISAM键缓存

（4）查询缓存

（5）无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存。


5 .InnoDB缓冲池。

如果大部分都是InnoDB表，InnoDB缓冲池或许比其他任何东西更需要内存。InnoDB缓冲池并不仅仅缓存索引；它还会缓存行的数据，自适应哈希索引，插入缓存（Insert Buffer）、锁，以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序写回。总之，InnoDB严重依赖缓冲池，你必须确认为它分配了足够的内存。通过可以通过使用show命令得的变量或者例如innotop这样的工具监控InnoDB缓冲池的内存利用情况。


6 . MyISAM键缓存(Key Caches)

MyISAM的键缓存也被称为键缓冲，默认只有一个键缓存，但也可以创建多个。不像InnoDB和其他一些存储引擎，M有ISAM自身只缓存索引，不缓存数据（依赖操作系统缓存数据）。如果大部分是MyISAM表，就应该为键缓存分配比较多的内存。

查询information_schema表的index_length字段，把他们的值相加，就可以得到索引存储占用的空间。     

mysql> select sum(index_length) from information_schema.tables where engine='MYISAM';
+-------------------+
| sum(index_length) |
+-------------------+
|            166912 |
+-------------------+
1 row in set (0.40 sec)

   如果是类unix系统，也可以使用下面的命令：   

$du -sch `find /path/to/mysql/data/directory/  -name "*.MYI"`

   键缓存设置不要超过索引的总大小，或者不超过为操作系统个缓存暴露总内存的25%~50%,以更小为准。

   每10秒钟获得一次状态值的变化量：   

$mysqladmin -uroot -p extended-status -r -i 10 |grep key_reads


7 . MySQL 键缓缓存块的大小(Key Block Size)

   块大小也是很重要的（尤其是密集型负载），它影响了MyISAM、操作系统缓存，以及文件系统之间的交互。如果缓存块太小了，可能会碰到写时读取（read-around write），操作系统在执行写操作之前必须先从磁盘上读取一些数据。假设操作系统的页大小是4KB（在x86架构上通常都是这样），并且索引块大小时1KB:

    (1) MyISAM请求从磁盘上读取1KB的块。

   （2）操作系统从磁盘上读取4KB的数据并缓存，然后发送需要的1KB数据给MyISAM。

   （3）操作系统丢弃缓存数据以给其他数据腾出缓存。

   （4）MyISAM修改1KB的索引块，然后请求操作系统把它写回磁盘。

   （5）操作系统从磁盘读取同一个4KB的数据，写入操作系统缓存，修改MyISAM改动这1KB的数据，然后把这个4KB的块写回磁盘。


8. 线程缓存

   线程缓存保存那些当前没有与连接关联但是准备为后面新的连接服务的线程。当一个新的连接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分赔给这个新的连接。当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存。如果没有空间的话，MySQL会销毁这个线程。只要MySQL在缓存里还有空闲的话线程，他就可以迅速地响应连接请求，因为这样就不用为每个链接创建新的线程。

   thread_cache_size变量指定MySQL可以保持在缓存中的线程数。一般不需要配置这个值，除非服务器会有很多连接请求。要检查线程缓存是否足够大，可以查看Thread_created状态变量。如果观察到很少有每秒创建的新线程数少于10个的时候，通常，应该尝试保持线缓存足够大，但是实际上经常也可能看到每秒少于1个新线程的情况。

   一个好的办法就是观察Threads_connected变量并且尝试设置thread_cache_size足够大以便能处理业务压力正常的波动。例如，若Thread_cache_size通常保持在100~120,则可以设置缓存大小为20.如果他保持在500~700，200的线程缓存应该足够大了。可以这样认为：在700个连接的时候，可能没有线程在缓存中；在500个连接的时候，有200个缓存的线程准备为负载再次增加到700个链接时使用。

    把线程缓存设置的非常大，在大部分时间是没有必要的，但是设置的非常小也不能节省太多的内存，所以也没有什么好处。每个在线程缓存中的线程或者休眠状态的线程，通常使用256KB左右的内存。相对于正在处理查询线程来说，这个内存不算很大。通常应该保证线程缓存足够大，以避免Thread_created频繁增长。如果这个数字很大（例如，几千个线程），可能需要Thread_cache_size设置的稍微小一些，因为一些操作系统不能很好的处理庞大的线程数，即使其中大部分是休眠。


9.表缓存（Table cache）

   表缓存的概念和线程缓存的概念是相似的，但存储的对象代表的是表。每个在缓存中的对象包含相关表.frm文件的解析结果，加上一些其他数据。准确的说，在对象里的其他数据的内容以来于表的存储引擎。例如，对MyISAM ，是表的数据和索引的文件描述符。对于Merge表则可能使多个文件描述符，因为Merge表可以有很多底层表。

   在MySQL5.1的版本中表缓存分离成两个部分：一个是打开表的缓存，一个是定义缓存（通过table_open_cache和table_definition_cache变量来设置）。其结果是，表定义（解析.frm文件的结果）从其他的资源中分离出来了。例如表描述符。打开表依然是每个线程、每个表用的，但是表定义的是全局的，可以被所有连接有效地共享。同城可以把table_definition_cache设置的足够高，以缓存所有的表定义。除非有上万张表，否则，这可能是最简单的方法。

   如果Opened_tables状态变量很大或者在增长，可能是因为表缓存不够大，那么可以认为增加table_cache系统变量（或者是MySQL5.1中的table_open_cache）。然而，当创建和删除临时表时，要注意这个计数器的增长，如果经常需要创建和删除临时表，那么该计数器就会不停地增长。

   把表缓存设置的非常大的缺点是，当服务器有很多MyISAM表时，可能会导致关机时间较长，因为关机前索引块必须完成刷新，表都必须标记为不在打开。同样的原因，也可能使Flush tables with read lock操作花费很长一段时间。更为重要的是，检查表缓存算法不是很有效，稍后会更详细地说明。

   如果遇到MySQL无法打开更多文件的错误（可以使用perror工具来检查错误号代表的含义），那么可能需要增加MySQL允许打开的文件数量。这可以通过my.cnf文件中那个设置open_files_limit服务器变量来实现。

   线程和表缓存实际上用的内存并不多，相反却可以有效节约资源。虽然创建一个新线程或者打开一个新的表，相对于其他MySQL操作来书代价并不算高，但他们的开ixaoshi会累加的。所以缓存线程和表有时可以提升效率。


10. InnoDB数据字典(Data Dictionary)

    InnoDB有自己的表缓存，可以称为表定义缓存或者数据字典，在目前的MySQL版本中还不能对它进行配置。当InnoDB打开一张表，就增加了一个对应的对象到数据字典。每张表可能占用4KB或者更多的内存（尽管在MySQL 5.1中对空间的需求小了很多）。当表关闭的时候也不会从数据字典中移除它们。

    如果设置了InnoDB的innoDB_file_per_table选项（后面会描述），InnoDB任意时刻可以保持打开.ibd文件的数量也是有限制的。这由InnoDB存储引擎负责，而不是MySQL服务器管理，并且有innodb_openfiles来控制。innoDB打开文件和MyISAM的方式不一样,MyISAM用表缓存来持有打开表的文件描述符，而InnoDB在打开表和打开文件之间没有直接的关系。InnoDB为每个.idb文件使用单个，全局的文件描述符。如果可以，最好把innodb_open_files的值设置的足够大以使服务器可以保持所有的.ibd文件同时打开。


使用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的值,用基于内存的临时表代替基于磁盘的