mysql 性能调优
时间：2008-11-22 来源：linux-bin
我喜欢问别人在安装了MySQL之后，其默认的配置应该怎么改。但令人惊讶的是很多人都无法给出一个合理的理由。虽然MySQL有许多可以调整的变量，但对于大多数的情况它们中只有少数有用。只要把这几个设好了，其他的对于多数情况只能起到锦上添花的作用。
key_buffer_size
如 果用MyISAM则非常重要。如果只用MyISAM表，那么设成可用内存的30%到40%。主要决定于索引的数量，数据的多少和读写量。要知道 MyISAM是用系统的缓存来缓存数据的，而数据通常比索引大很多，所以你需要留一部分内存给系统。记得检查是否所有的key_buffer都被用到，经常看到有人把它设成4G，而所有的MYI文件加起来才1G（4G呀，看到的次数还不少，真是羡慕。内存不是这么浪费的）。如果用的MySIAM表很少，那么可以把它设得比较少，但至少要有16-32M，提供给创建的临时表的索引用。 innodb_buffer_pool_size
如 果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。 innodb_additional_pool_size
这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途。 innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间。 innodb_log_buffer_size
默认值对于多数中等写操作和事务短的运用都是可以的。如果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。 innodb_flush_log_at_trx_commit
抱怨Innodb比MyISAM慢100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。 table_cache
打开表的操作代价很高。比如MyISAM表标记MYI头部来标记表在使用中。你不会希望频繁进行此操作，所以最好设置缓存使得能够保存大部分打开的表。它会用到一些系统资源和内存，但对于现在的硬件来说不是问题。对于200个表的运用1024比较好。如果你的连接数多，或者表多则再大一些。我见过超过100000的。 thread_cache
建立连接和断开时的线程的创建和死亡开销很大。我一般把它设为至少16。如果程序中有大量并发连接，而变量Threads_Created（status中可以看到）长得很快，那么会设大一些。主要是让正常操作中不要去创建线程。 query_cache
如果你的程序有大量的读操作而又没有程序级的缓存，那么把它设大一点应该有很大的帮助。但设得太大会减慢速度因为维护的开销很大。32M-512M就可以了。设过之后检查一下是否运行良好。For certain workloads cache hit ratio is lower than would justify having it enabled.（这句看不懂，我怀疑他写错了。一般来说，如果hit ratio比较低说明cache还不够大，可以适当的增加） 注意：这些都是一些全局变量。它们依赖于硬件和存储引擎，而session 的变量更偏重于负荷。如果你只做简单的查询，那么即使你有64G的内存可以浪费也没理由要增加sort_buffer_size。而且这样还可能会降低性能。我一般会把session变量的调整放到第二步，在弄清了负荷以后。 另外MySQL提供的my.cnf的样例不错，可以拿来改改了用
如 果用MyISAM则非常重要。如果只用MyISAM表，那么设成可用内存的30%到40%。主要决定于索引的数量，数据的多少和读写量。要知道 MyISAM是用系统的缓存来缓存数据的，而数据通常比索引大很多，所以你需要留一部分内存给系统。记得检查是否所有的key_buffer都被用到，经常看到有人把它设成4G，而所有的MYI文件加起来才1G（4G呀，看到的次数还不少，真是羡慕。内存不是这么浪费的）。如果用的MySIAM表很少，那么可以把它设得比较少，但至少要有16-32M，提供给创建的临时表的索引用。 innodb_buffer_pool_size
如 果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。 innodb_additional_pool_size
这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途。 innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间。 innodb_log_buffer_size
默认值对于多数中等写操作和事务短的运用都是可以的。如果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。 innodb_flush_log_at_trx_commit
抱怨Innodb比MyISAM慢100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。 table_cache
打开表的操作代价很高。比如MyISAM表标记MYI头部来标记表在使用中。你不会希望频繁进行此操作，所以最好设置缓存使得能够保存大部分打开的表。它会用到一些系统资源和内存，但对于现在的硬件来说不是问题。对于200个表的运用1024比较好。如果你的连接数多，或者表多则再大一些。我见过超过100000的。 thread_cache
建立连接和断开时的线程的创建和死亡开销很大。我一般把它设为至少16。如果程序中有大量并发连接，而变量Threads_Created（status中可以看到）长得很快，那么会设大一些。主要是让正常操作中不要去创建线程。 query_cache
如果你的程序有大量的读操作而又没有程序级的缓存，那么把它设大一点应该有很大的帮助。但设得太大会减慢速度因为维护的开销很大。32M-512M就可以了。设过之后检查一下是否运行良好。For certain workloads cache hit ratio is lower than would justify having it enabled.（这句看不懂，我怀疑他写错了。一般来说，如果hit ratio比较低说明cache还不够大，可以适当的增加） 注意：这些都是一些全局变量。它们依赖于硬件和存储引擎，而session 的变量更偏重于负荷。如果你只做简单的查询，那么即使你有64G的内存可以浪费也没理由要增加sort_buffer_size。而且这样还可能会降低性能。我一般会把session变量的调整放到第二步，在弄清了负荷以后。 另外MySQL提供的my.cnf的样例不错，可以拿来改改了用
相关阅读 更多 +
