Mysql 的my.cnf 针对不同类型数据库应用的推荐配置项的具体参数对比

最新推荐文章于 2023-07-04 10:21:07 发布

Naola2001

最新推荐文章于 2023-07-04 10:21:07 发布

阅读量3.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： WEB服务文章标签： mysql 数据库 buffer concurrency cache socket

本文链接：https://blog.csdn.net/naola2001/article/details/7740615

WEB服务专栏收录该内容

7 篇文章

订阅专栏

本文对比了从小型到庞大应用规模的MySQL配置文件(my-small.cnf, my-medium.cnf, my-large.cnf, my-huge.cnf)的变化。主要关注了不同规模下配置参数如key_buffer_size, table_open_cache等的变化。

Text Compare
Produced: 2012-7-12 15:12:07

Mode: All, With Context, Ignoring Unimportant
Left file: my-small.cnf (小型应用)
Right file: my-medium.cnf （中型应用）

	=

[client]		[client]

port = 3306		port = 3306
socket = /tmp/mysql.sock		socket = /tmp/mysql.sock




[mysqld]		[mysqld]
port = 3306		port = 3306
socket = /tmp/mysql.sock		socket = /tmp/mysql.sock
skip-locking		skip-locking
key_buffer_size = 16K	<>	key_buffer_size = 16M
max_allowed_packet = 1M	=	max_allowed_packet = 1M
table_open_cache = 4	<>	table_open_cache = 64
sort_buffer_size = 64K		sort_buffer_size = 512K
		net_buffer_length = 8K
read_buffer_size = 256K	=	read_buffer_size = 256K
read_rnd_buffer_size = 256K	<>	read_rnd_buffer_size = 512K
net_buffer_length = 2K		myisam_sort_buffer_size = 8M
thread_stack = 128K
	=

	-+	log-bin=mysql-bin
	=

	-+	binlog_format=mixed
	=



server-id = 1		server-id = 1


[mysqldump]		[mysqldump]
quick		quick
max_allowed_packet = 16M		max_allowed_packet = 16M

[mysql]		[mysql]
no-auto-rehash		no-auto-rehash



[myisamchk]		[myisamchk]
key_buffer_size = 8M	<>	key_buffer_size = 20M
sort_buffer_size = 8M		sort_buffer_size = 20M
		read_buffer = 2M
		write_buffer = 2M
	=
[mysqlhotcopy]		[mysqlhotcopy]
interactive-timeout		interactive-timeout

Text Compare
Produced: 2012-7-12 15:18:30

Mode: All, With Context, Ignoring Unimportant
Left file: my-medium.cnf （中型应用）
Right file: my-large.cnf （大型应用）


[client]		[client]

port = 3306		port = 3306
socket = /tmp/mysql.sock		socket = /tmp/mysql.sock




[mysqld]		[mysqld]
port = 3306		port = 3306
socket = /tmp/mysql.sock		socket = /tmp/mysql.sock
skip-locking		skip-locking
key_buffer_size = 16M	<>	key_buffer_size = 256M
max_allowed_packet = 1M	=	max_allowed_packet = 1M
table_open_cache = 64	<>	table_open_cache = 256
sort_buffer_size = 512K		sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 256K		read_buffer_size = 1M
read_rnd_buffer_size = 512K		read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 8M		myisam_sort_buffer_size = 64M
		thread_cache_size = 8
		query_cache_size= 16M
	=
	-+	thread_concurrency = 8
	=

log-bin=mysql-bin		log-bin=mysql-bin


binlog_format=mixed		binlog_format=mixed




server-id = 1		server-id = 1


[mysqldump]		[mysqldump]
quick		quick
max_allowed_packet = 16M		max_allowed_packet = 16M

[mysql]		[mysql]
no-auto-rehash		no-auto-rehash



[myisamchk]		[myisamchk]
key_buffer_size = 20M	<>	key_buffer_size = 128M
sort_buffer_size = 20M		sort_buffer_size = 128M
read_buffer = 2M	=	read_buffer = 2M
write_buffer = 2M		write_buffer = 2M

[mysqlhotcopy]		[mysqlhotcopy]
interactive-timeout		interactive-timeout

Text Compare
Produced: 2012-7-12 15:19:38

Mode: All, With Context, Ignoring Unimportant
Left file: my-large.cnf （大型应用）
Right file: my-huge.cnf （庞大应用）


[client]		[client]

port = 3306		port = 3306
socket = /tmp/mysql.sock		socket = /tmp/mysql.sock




[mysqld]		[mysqld]
port = 3306		port = 3306
socket = /tmp/mysql.sock		socket = /tmp/mysql.sock
skip-locking		skip-locking
key_buffer_size = 256M	<>	key_buffer_size = 384M
max_allowed_packet = 1M	=	max_allowed_packet = 1M
table_open_cache = 256	<>	table_open_cache = 512
sort_buffer_size = 1M		sort_buffer_size = 2M
read_buffer_size = 1M		read_buffer_size = 2M
read_rnd_buffer_size = 4M		read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M	=	myisam_sort_buffer_size = 64M
thread_cache_size = 8		thread_cache_size = 8
query_cache_size= 16M	<>	query_cache_size = 32M
	=
thread_concurrency = 8		thread_concurrency = 8


log-bin=mysql-bin		log-bin=mysql-bin


binlog_format=mixed	+-
	=



server-id = 1		server-id = 1



[mysqldump]		[mysqldump]
quick		quick
max_allowed_packet = 16M		max_allowed_packet = 16M

[mysql]		[mysql]
no-auto-rehash		no-auto-rehash



[myisamchk]		[myisamchk]
key_buffer_size = 128M	<>	key_buffer_size = 256M
sort_buffer_size = 128M		sort_buffer_size = 256M
read_buffer = 2M	=	read_buffer = 2M
write_buffer = 2M		write_buffer = 2M

[mysqlhotcopy]		[mysqlhotcopy]
interactive-timeout		interactive-timeout