Linux.中国 - 开源社区

 找回密码
 骑士注册

QQ登录

微博登录


MySQL my.cnf参数配置优化详解

2014-9-27 19:48    评论: 24 收藏: 11 分享: 10    

#*** MyISAM 相关选项

# 关键词缓冲的大小, 一般用来缓冲MyISAM表的索引块。不要将其设置大于你可用内存的30%,因为一部分内存同样被OS用来缓冲行数据,甚至在你并不使用MyISAM 表的情况下,你也需要仍旧设置起 8-64M 内存由于它同样会被内部临时磁盘表使用。

key_buffer_size = 128M

# 用来做MyISAM表全表扫描的缓冲大小。当全表扫描需要时,在对应线程中分配。

read_buffer_size = 8M

# 当在排序之后,从一个已经排序好的序列中读取行时,行数据将从这个缓冲中读取来防止磁盘寻道。如果你增高此值,可以提高很多ORDER BY的性能。当需要时由每个线程分配。

read_rnd_buffer_size = 64M

# MyISAM 使用特殊的类似树的cache来使得突发插入(这些插入是,INSERT … SELECT, INSERT … VALUES (…), (…), …, 以及 LOAD DATA INFILE) 更快。此变量限制每个进程中缓冲树的字节数。设置为 0 会关闭此优化。为了最优化不要将此值设置大于 “key_buffer_size”。当突发插入被检测到时此缓冲将被分配。

bulk_insert_buffer_size = 256M

# 此缓冲当MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一个空表中引起重建索引时被分配。这在每个线程中被分配.所以在设置大值时需要小心。

myisam_sort_buffer_size = 256M

# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE)。如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)。

myisam_max_sort_file_size = 10G

# 如果被用来更快的索引创建索引所使用临时文件大于制定的值,那就使用键值缓冲方法。这主要用来强制在大表中长字串键去使用慢速的键值缓冲方法来创建索引。

myisam_max_extra_sort_file_size = 10G

# 如果一个表拥有超过一个索引,MyISAM 可以通过并行排序使用超过一个线程去修复他们。这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择。

myisam_repair_threads = 1

# 自动检查和修复没有适当关闭的 MyISAM 表。

myisam_recover

# 默认关闭 Federated

skip-federated

# *** BDB 相关选项 ***

# 如果你运行的MySQL服务有BDB支持但是你不准备使用的时候使用此选项。这会节省内存并且可能加速一些事。

skip-bdb

# *** INNODB 相关选项 ***

# 如果你的MySQL服务包含InnoDB支持但是并不打算使用的话,使用此选项会节省内存以及磁盘空间,并且加速某些部分

#skip-innodb

# 附加的内存池被InnoDB用来保存 metadata 信息。如果InnoDB为此目的需要更多的内存,它会开始从OS这里申请内存。由于这个操作在大多数现代操作系统上已经足够快,你一般不需要修改此值。SHOW INNODB STATUS 命令会显示当先使用的数量。

innodb_additional_mem_pool_size = 64M

# InnoDB使用一个缓冲池来保存索引和原始数据,不像 MyISAM。这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少。在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%。不要设置过大,否则。,由于物理内存的竞争可能导致操作系统的换页颠簸。注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,所以不要设置的太高。

innodb_buffer_pool_size = 6G

# InnoDB 将数据保存在一个或者多个数据文件中成为表空间。如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了。其他情况下,每个设备一个文件一般都是个好的选择。你也可以配置InnoDB来使用裸盘分区 – 请参考手册来获取更多相关内容。

innodb_data_file_path = ibdata1:10M:autoextend

# 设置此选项如果你希望InnoDB表空间文件被保存在其他分区。默认保存在MySQL的datadir中。

#innodb_data_home_dir =

# 用来同步IO操作的IO线程的数量。此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好。

innodb_file_io_threads = 4

# 如果你发现InnoDB表空间损坏,设置此值为一个非零值可能帮助你导出你的表。从1开始并且增加此值知道你能够成功的导出表。

#innodb_force_recovery=1

# 在InnoDb核心内的允许线程数量。最优值依赖于应用程序,硬件以及操作系统的调度方式。过高的值可能导致线程的互斥颠簸。

innodb_thread_concurrency = 16

# 如果设置为1,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上,这提供了完整的ACID行为。如果你愿意对事务安全折衷, 并且你正在运行一个小的事务,你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O。0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘。2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上。

innodb_flush_log_at_trx_commit = 2

#(说明:如果是游戏服务器,建议此值设置为2;如果是对数据安全要求极高的应用,建议设置为1;设置为0性能最高,但如果发生故障,数据可能会有丢失的危险!默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,这是很费时的。特别是使用电池供电缓存(Battery backed up cache)时。设成2对于很多运用,特别是从MyISAM表转过来的是可以的,它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘,所以你一般不会丢失超过1-2秒的更新。设成0会更快一点,但安全方面比较差,即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。)

# 加速InnoDB的关闭。这会阻止InnoDB在关闭时做全清除以及插入缓冲合并。这可能极大增加关机时间,但是取而代之的是InnoDB可能在下次启动时做这些操作。

#innodb_fast_shutdown

# 用来缓冲日志数据的缓冲区的大小。当此值快满时, InnoDB将必须刷新数据到磁盘上。由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)

innodb_log_buffer_size = 16M

# 在日志组中每个日志文件的大小。你应该设置日志文件总合大小到你缓冲池大小的25%~100%。来避免在日志文件覆写上不必要的缓冲池刷新行为。不论如何,请注意一个大的日志文件大小会增加恢复进程所需要的时间。

innodb_log_file_size = 512M

# 在日志组中的文件总数。通常来说2~3是比较好的。

innodb_log_files_in_group = 3

# InnoDB的日志文件所在位置。默认是MySQL的datadir。你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能。

#innodb_log_group_home_dir

# 在InnoDB缓冲池中最大允许的脏页面的比例。如果达到限额,InnoDB会开始刷新他们防止他们妨碍到干净数据页面。这是一个软限制,不被保证绝对执行。

innodb_max_dirty_pages_pct = 90

# InnoDB用来刷新日志的方法。表空间总是使用双重写入刷新方法。默认值是 “fdatasync”, 另一个是 “O_DSYNC”。

#innodb_flush_method=O_DSYNC

# 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久。InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务。如果你使用 LOCK TABLES 指令,或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎。那么一个死锁可能发生而InnoDB无法注意到。这种情况下这个timeout值对于解决这种问题就非常有帮助。

innodb_lock_wait_timeout = 120

[mysqldump]

# 不要在将内存中的整个结果写入磁盘之前缓存。在导出非常巨大的表时需要此项。

quick

max_allowed_packet = 32M

[mysql]

no-auto-rehash

# 仅仅允许使用键值的 UPDATEs 和 DELETEs 。

#safe-updates

[isamchk]

key_buffer = 2048M

sort_buffer_size = 2048M

read_buffer = 32M

write_buffer = 32M

[myisamchk]

key_buffer = 2048M

sort_buffer_size = 2048M

read_buffer = 32M

write_buffer = 32M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

# 增加每个进程的可打开文件数量。警告:确认你已经将全系统限制设定的足够高!打开大量表需要将此值设大。
open-files-limit = 8192

12
查看其它分页:

发表评论


最新评论

我也要发表评论

linux 2014-12-4 21:34
1
来自 - 美国 的 Firefox/Mac 用户 发表于 2014-12-4 17:50 的评论:
基本上这个硬件配置就属于不适合站点使用,除非非常密集的复杂sql,否则cpu没必要两个,如果是专注于网站,那么肯定是查询居多,那么把花在cpu上的钱换成内存,innodb_buffer设置成机器的60%左右,其他优化基本不用做了。
还有余钱换个ssd也行,把数据存ssd,binlog存sas。
我不赞同你的意见,你说的那是一种情况,而往往是你已经有了多个 CPU,或者多核的 CPU,所以不用也没办法。内存自然是越多越好,而 SSD 也如果有可能就配上。但是这不代表这个配置没有意义,网站也经常有复杂的SQL。不过,别人的配置经验,总是要参考和自己根据自己的情况修改研究的。
回复
来自 - 美国 的 Firefox/Mac 用户 2014-12-4 17:50
基本上这个硬件配置就属于不适合站点使用,除非非常密集的复杂sql,否则cpu没必要两个,如果是专注于网站,那么肯定是查询居多,那么把花在cpu上的钱换成内存,innodb_buffer设置成机器的60%左右,其他优化基本不用做了。
还有余钱换个ssd也行,把数据存ssd,binlog存sas。
回复
夏日漱石的猫 2014-9-28 00:33  新浪微博网友评论
mark
回复
和尚也留长发 2014-9-28 00:33  新浪微博网友评论
Repost
回复
netb2c 2014-9-27 23:40
很详细的介绍,我实例多,开了慢查询日志,
6 回复
-路-易- 2014-9-27 23:33  新浪微博网友评论
mark ,总算有一个详细的介绍参数的了。
回复
山野o 2014-9-27 23:03  新浪微博网友评论
mark
回复
蔚蔚蔚想要尘埃落定 2014-9-27 22:33  新浪微博网友评论
@我的印象笔记
回复
ChelinTsien 2014-9-27 22:33  新浪微博网友评论
@我的印象笔记
回复
Linux中国 2014-9-27 22:33  新浪微博网友评论
回复@纳凉先生:另外,虽然这篇不是我写的,但是我感觉基本上还是靠谱的,所以希望能和你具体探讨下。
回复
Linux中国 2014-9-27 22:33  新浪微博网友评论
回复@纳凉先生:6G 怎么了?总共16G 内存。默认引擎和实际使用的引擎是两回事。
回复
纳凉先生 2014-9-27 22:03  新浪微博网友评论
还有你列出的配置默认引擎是MYISAM 不是innodb.
回复
纳凉先生 2014-9-27 22:03  新浪微博网友评论
还有你列出的配置默认引擎是MYISAM 不是innodb. //@纳凉先生:回复@Linux中国:mysql innodb 的 pool_size默认大小是多少? 就这一项配置产生的问题... //@Linux中国:回复@纳凉先生:有何问题,请指教?
回复
纳凉先生 2014-9-27 22:03  新浪微博网友评论
回复@Linux中国:mysql innodb 的 pool_size默认大小是多少? 就这一项配置产生的问题...
回复
纳凉先生 2014-9-27 22:03  新浪微博网友评论
回复@Linux中国:mysql innodb 的 pool_size默认大小是多少? 就这一项配置产生的问题... //@Linux中国:回复@纳凉先生:有何问题,请指教?
回复
Linux中国 2014-9-27 22:03  新浪微博网友评论
回复@纳凉先生:有何问题,请指教?
回复
宇涵同学 2014-9-27 22:03  新浪微博网友评论
@我的印象笔记
回复
Netemu--撒加 2014-9-27 21:33  新浪微博网友评论
不错
1 回复
unodba 2014-9-27 20:33  新浪微博网友评论
看看
回复
纳凉先生 2014-9-27 20:33  新浪微博网友评论
就凭这文单里描述的 Innodb引擎配置能在生产环境用起来? 误导人就是不好
回复
12下一页
运维世界大会

热点评论

netb2c 2014-9-27 23:40
很详细的介绍,我实例多,开了慢查询日志,
6
Netemu--撒加 2014-9-27 21:33
不错
1
返回顶部

分享到微信朋友圈

打开微信,点击底部的“发现”,
使用“扫一扫”将网页分享至朋友圈。