漫漫长路，其修远兮！
分类： Mysql/postgreSQL
2013-03-07 15:01:32
1.几个动态修改的变量
key_buffer_size:键缓冲区大小，只在实际使用的时候才分配空间，主要是mysisam引擎使用
table_cache_size/table_open_cache：设置不会马上生效，要等到下一个线程打开表时才会生效，表缓存的数量
可以配合status中的open_tables和opend_talbes,如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了
在 MySQL5.1.3之前的版本通过 table_cache 参数设置，但从MySQL5.1.3开始改为 table_open_cache 来设置其大小
tmp_table_size :
它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，默认:
mysql> show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp/ |
+---------------+-------+
优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句，并且你有很多内存，增大tmp_table_size(和max_heap_table_size)的值。这个变量不适用与用户创建的内存表(memory table).
你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数（Created_tmp_disk_tables和Created_tmp_tables），一般的比例关系是:
Created_tmp_disk_tables/Created_tmp_tables<5%
max_heap_table_size:
这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#
,但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。
这个变量和tmp_table_size一起限制了内部内存表的大小。
tmp_table_size 和 max_heap_table_size 大小要一致, 且不能分配过大, 建议不超过 128MB
当另一个系统参数 max_heap_table_size 的大小还小于 tmp_table_size 的时候，MySQL 将使用 max_heap_table_size 参数所设置大小作为最大的内存临时表大小，而忽略 tmp_table_size 所设置的值。而且 tmp_table_size 参数从 MySQL 5.1.2 才开始有，之前一直使用 max_heap_table_size
max_tmp_tables:
客户端可以同时打开的临时表的最大数
mysql内存使用的情况
主要是public+private*connections
public主要包括：key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size
private主要包括:read_buffer_size, sort_buffer_size,join_buffer_size, read_rnd_buffer_size, tmp_table_size
如果这些变量设置的太高，很容易引发诸多问题：耗尽内存，使用swap,耗尽地址空间.
2.对内层调优
a.mysql使用内存的上限
b.mysql会为每个连接使用多少内存
c.OS良好运行使用的内存
mysiam键缓存：默认只有一个，但是也可以创建多个，只缓存索引，没有数据(os缓存数据)。
key_buffer_size
show global status like '%key%'
Key_blocks_not_flushed | 0 |
Key_blocks_unused | 53585 |
Key_blocks_used | 0 |
Key_read_requests | 0 |
Key_reads | 0 |
Key_write_requests | 0 |
Key_writes | 0 |
键缓存命中率：=(1 - Key_reads / Key_read_requests) * 100%
键缓存使用率：=
----------------------------------------------------
查询缓存query_cache_szie
query_cache_limit：允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cache
query_cache_min_res_unit：设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小
query_cache_type：控制 Query Cache 功能的开关，可以设置为0(OFF),1(ON)和2(DEMAND)三种，意义分别如下：
0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache
1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用Query Cache
2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache
show global status like '%Qcache%'
Qcache_free_blocks | 1 |
Qcache_free_memory | 33535328 |
Qcache_hits | 1 |
Qcache_inserts | 4 |
Qcache_lowmem_prunes | 0 |
Qcache_not_cached | 86 |
Qcache_queries_in_cache | 1 |
Qcache_total_blocks | 4 |
查询缓存命中率：=(Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%
查询缓存使用率：=
----------------------------------------------------
innodb缓存：缓存索引和数据
innodb_buffer_pool_size
show global status like '%innodb_buffer%'
Innodb_buffer_pool_pages_data | 294 | ----包含数据的页数(脏+干净数据)
Innodb_buffer_pool_pages_dirty | 0 | ----当前的脏数据
Innodb_buffer_pool_pages_flushed | 134 | ----要求清空的缓冲池页数
Innodb_buffer_pool_pages_free | 65240 | ----空页数
Innodb_buffer_pool_pages_misc | 1 | ----
Innodb_buffer_pool_pages_total | 65535 | ----缓冲池总大小
Innodb_buffer_pool_read_ahead_rnd | 0 | -----
Innodb_buffer_pool_read_ahead | 0 |
Innodb_buffer_pool_read_ahead_evicted | 0 |
Innodb_buffer_pool_read_requests | 1637 |
Innodb_buffer_pool_reads | 284 |
Innodb_buffer_pool_wait_free | 0 |
Innodb_buffer_pool_write_requests | 205 |
bp缓存命中率：=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%
bp缓存使用率：=
--------------------------------------------------------------------
线程缓存
thread_cache_size
show global status like '%thread%';
Threads_cached | 2 |
Threads_connected | 1 |
Threads_created | 3 |
Threads_running | 2 |
线程缓存命中率：Thread_cache_hits = (1 - Threads_created / Connections) * 100%
----------------------------------------------------------------------
表缓存
table_cache/table_open_cache
Open_table_definitions | 35 |
Open_tables | 29 |
Opened_table_definitions | 38 |
Opened_tables | 39 |
--------------------------------------------------------------------
binlog缓存
Binlog_cache_disk_use | 0 |
Binlog_cache_use | 0 |
--------------------------------------------------------------------
临时表使用情况
tmp_table_size|max_heap_table_size
Created_tmp_disk_tables | 2 |
Created_tmp_files | 7 |
Created_tmp_tables | 63 |
3.IO调优主要是针对innodb
innodb_log_file_size 256M,512M
innodb_log_buffer_size 2M-16M
innodb_log_at_trx_commit 0,1,2
0,2的区别是，0在mysql进程崩溃是不会有事务丢失，但是在服务器宕机或者掉电可能会有事务丢失。
2在事务提交时做刷新磁盘的动作，0是每1s钟做这个动作。同时2也会没1s钟刷新磁盘
把日志缓存写入日志文件和把日志刷写到持久性存储的区别：
在大多数操作系统中，把缓存写入日志只是简单的把数据从innodb的内存缓冲区移到操作系统的缓冲区，它也在内存中。它实际上不会把数据写入持久性存储。
innodb_flush_method
配置innodb实际与文件系统交互的方式。
fdatasync()，默认方式
0_direct，禁用操作系统缓存，所有读写操作直接到存储设备，在使用该参数的时候通常不应该禁止RAID卡的写入缓存，这是保持良好性能的途径，对随机读写有帮助。
查看没有被清理的旧数据(undo)
------------
TRANSACTIONS
------------
Trx id counter 500
Purge done for trx's n:o < 0 undo n:o < 0
innodb_max_purge_lag
innodb_purge_threads
sync_binlog
控制如何把二进制写入磁盘，默认值是0
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
innodb_io_capacity
参数为innodb_io_capacity，默认值为200
在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity数值的5%。
在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity
4.innodb并发调优
innodb_thread_concurrency,它限制了一次有多少线程进入内核，0表示不限制进入内核的数量，禁用线程并发检查，使InnoDB按照请求的需求, 创造尽可能多的线程
默认推荐的值是 (cpu的数量+磁盘数量)x2 （我的理解，对于raid10的磁盘阵列，应该是磁盘总数/2)
5.排序优化
max_length_for_sort_data
如果需要排序列的总大小加上order by列的大小超过了max_length_for_sort_data定义的字节数，mysql就好使用双路排序。
当出现blob和text列，会使用双路排序，(可以使用substring把这些列转换一下，可以使用单路排序)
6.通过show global status需要关注的信息
慢查询
连接数
key_buffer使用情况
innodb_buffer使用情况
临时表
open table情况
线程使用情况
查询缓存
排序情况
表扫描情况
