mysql内存优化总结

       服务器负载方面的优化（高性能mysql总结）

服务器负载方面优化包括内存，IO和CPU，属于操作系统和硬件方面。

服务器负载优化的目标是低延时（时间查询快），高吞吐（并发执行查询）。

CPU：目前mysql每个查询只能使用一个CPU，因此，并发查询时无法扩展cpu的数量。数据库并发的问题有两类：逻辑并发和内部并发。逻辑并发是指应用程序的资源争用，例如锁。内部并发是指 信号量，InnoDB缓冲池页面的资源争用等。

内存：关于内存，主要需要考虑以下几方面：内存的上下限，内存大小，每个连接mysql需要使用多少内存。绑定变量和InnoDB数据字典，使用大的临时表或者复杂存储过程的查询也可能消耗内存。可以通过查看虚拟内存Linux中top的VIRT和PS名利的VSZ列。Mysql比较重要的缓存如下：

 a.InnoDB缓冲池

b.InnoDB日志文件和MyISAM数据的操作系统缓存

c.MyISAM键缓存

d.查询缓存

e.无法手工配置的缓存，二进制日志和表定义文件的操作系统缓存。

a.InnoDB缓冲池：缓存索引，行的数据，锁，插入缓存和自适应哈希索引。关闭InnoDB，修改innodb_max_dirty_pages_pct变量小。监控Innodb：Innodb_buffer_pool_pages_dirty和show engine innodb status;来观察脏页的刷新量。

c.MyISAM键缓存

  MyISAM只缓存索引，不缓存数据。

配置参数：

1.       key_buffer_size 大小参照索引存储占用的空间：

select sum(index_length) from information_schema.tables where engine=’MYISAM’;

2.       设置多个键缓冲方法：

A．在配置文件中设置

key_buffer_1.key_buffer_size=*G

key_buffer_2.key_buffer_size=*G

               B.使用cache_index将表映射到缓冲区

                    Cache_index table1,table2 in key_buffer_1;

               C.把表的索引载入缓存中

                 Load index into cache table1,table2.

3.       监控键缓存的方法：

Show status /show variables

4.       计算缓冲区的使用率

100-((key_blocks_unused*key_cache_block_size)*100/key_buffer_size)

           5.键缓存块大小: key_block_size,通过create index语句的Key_block_size指定

d.查询缓存：mysql查询缓存保存查询返回的完整结果。缓存命中的问题，任何字符上的不一致都会导致缓存不命中，或者不确定的数据，例如now(),current_data()和current_user或者connection_ID.查询中包含任何自定义的函数，系统函数，临时表，系统变量，系统表都不会缓存。Mysql先申请query_cache_min_res_unit的空间，然后锁住空间块，找一个校的数据开存储结果。如果还有剩余，则释放后放入空闲内存部分。查询缓存的是否有效的指标命中率和是否提升性能。未命中缓存是指没有从查询缓存中返回数据，内存不足，缓存碎片和数据修改都是造成查询缓存的原因。

查询缓存只能在会话级别进行，在数据库或者表级别不要控制查询缓存。

解决方法：1.通过参数Com_*来查看数据修改情况。2.通过qcache_lowmem_prunes来查看多少次失效是内存不足导致的。

查询缓存参数：

1.       query_cache_type (OFF,ON,DEMAND)，是否打开。demand是指只有在查询语句中明确写明sql_cache的语句才放入查询缓存。

2.       query_cache_size查询缓存使用的总内存空间，字节（设置时1024整数倍）。值=0，代表关闭查询缓存。

3.       query_cache_min_res_unit 分配内存块时的最小单位。

4.       query_cache_limit 能够缓存的最大查询结果。如果结果超过此大小，那么不缓存，并返回Qcache_not_catched标志。处理方法，在查询中加入SQL_NO_CACHE

5.       query_cache_wlock_invalidate,如果锁住，是否仍然从缓存中返回结果。默认OFF

减少碎片的方法：

1.       选择合适的query_cache_min_res_unit

2.       设置单个查询最合适大小：（query_cache_size-Qcache_free_memory）除以Qcache_queries_in_cache.

3.       观察Qcache_free_blocks的空闲块多少。 Qcache_total_blocks与Qcache_free_block的比例不能超过50%

4.       Flush query cache完成碎片整理。使查询缓存重新排序

5.       Reset query cache 清空缓存。

查询缓存内存空间小，删除老的缓存结果。增加状态值Qcache_lowmem_prunes的值

e.线程缓存： thread_cache_size指定了可以缓存的线程数。

            Thread_connected

            Thread_created 观察线程缓存是否足够大

表缓存：存储的对象是表。Opened_tables很大，表缓存不够，增加table_cache系统变量。Open_files_limit允许打开文件数量。