概述：如果MySQL数据库系统有很多用户同时联机的话，很大的可能造成vps主机效率方面的瓶颈，虽然优化mysql服务器，有很多方法，例如SQL优化(使用索引、正则表达式）数据库对象优化（优化表的数据类型、通过拆分提高表的访问效率、逆规则化、使用中间表提高统计查询速度）磁盘I/O优化（使用磁盘阵列、使用虚拟文件卷LVM2或软RAID、使用Symbolic Links分布I/O,禁止操作系统更新文件的atime属性、使用裸设置）应用优化（使用连接池、减少对MYsql的访问、增加CACHE层、负载均衡）但是这些方法要不针对于数据库设置的优化、要不针对于操作系统级别或提升硬件环境的优化，对于我们的vps主机来讲，都用不上，那么最主要的还是优化mysql服务器，而优化mysql服务器本身，就是一个调校/etc/my.cnf的过程
环境：vps linux 主机 centos5 操作系统 使用yum命令安装mysql数据库
我们这里假设我们的主机内存为128M ,数据库使用64M,关于数据库内存的设置，注意，内存的简单计算公式
key_buffer_size+（sort_buffer_size+read_buffer_size)*max_connections，且总量不能高于实际的物理内存量
其他64M给系统使用以及web服务使用
| [mysqld]字段 | ||
| centos5 yum安装默认值 | vps主机特惠型优化值 | 属性含义介绍 |
| user = mysql | user = mysql | mysqld服务启用帐号 |
| port = 3306 （默认值） | port = 3306 | mysql服务启用的端口 |
| socket=/var/lib/mysql/mysql.sock | socket存放位置，已经在/etc/init.d/mysqld设置了，这里还是设置值一下 | |
| basedir=/usr | 已经在/etc/init.d/mysqld设置了 | 主目录 |
| datadir=/var/lib/mysql | datadir=/var/lib/mysql | 数据库资料存放目录，已经在/etc/init.d/mysqld设置了，这里还设置一下， |
| 默认错误日志已经在/etc/init.d/mysqld设置了–log-error=/var/log/mysql.log | log-error=/var/log/mysql.log,这里可以不设置。 | 错误日志设置，它记录了当mysqld启动和停止时，已经服务器在运行过程中发生任何严重错误的相关信息。当数据库出现任何故障导致无法正常使用时，可以先查看此日志。 |
| 默认没有启用二进制日志 | log-bin=/var/log/localhost-bin | 如果不需要配置mysql复制，建议不要启用 |
| expire_logs_days = 7 | 此参数的含义是设置日志的过期天数，过了指定的天数后日志将会自动删除，这样将有利于减少DBA管理日志的工作量 | |
| binlog_cache_size = 32K | binlog_cache_size = 2M | 二进制缓存日志大小，默认值为32K，有点小，我这里设置为2M |
| 默认没有启用慢查询日志 | log-slow-queries=/var/log/slow-queries.log | 启用慢查询日志，将记录所有执行时间超过参数long_query_time(单位：秒）所设置语句的日志，这样可以迅速定位系统SQL瓶颈所在，便于改善mysql性能。 |
| long_query_time = 10 | long_query_time = 2 | 修改long_query_time = 2，超过2秒就是慢查询语句 |
| default-character-set=utf8 | ||
| old_passwords=1 | #old_password=1 | 在MYSQL4.1版本之前，password函数生成的密码为16位，现在新版改进算法，生成的函数值变成41位，所以建议关闭旧版本密码格式，增强数据库的安全性 |
| skip-locking | 避免MySQL的外部锁定，减少出错几率增强稳定性 | |
| skip-name-resolve | 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！ | |
| 默认启用了innodb存储引擎 | skip-innodb | 此存储引擎具有提交、回滚和崩溃恢复能力的事务安全机制，建议关闭，节约内存开销 |
| 默认启用了BDB存储引擎 | skip-bdb | 建议关闭，节约内存开销 |
| #skip-merge | 建议不需要关闭，关闭也基本不节省什么内存 | |
| key_buffer_size = 8M | key_buffer_size = 4M | 索引块缓冲大小,了解更多关于buffer_size，请访问：在vps主机上mysql数据库优化 |
| sort_buffer_size = 2M | sort_buffer_size = 256K | 排序查询操作所能使用的缓冲区大小 该参数对应的分配内存也是每连接独享 |
| join_buffer_size = 2M | join_buffer_size = 128K | 联合查询操作所能使用的缓冲区大小 该参数对应的分配内存也是每连接独享 |
| read_buffer_size = 2M | read_buffer_size = 128K | 读查询操作所能使用的缓冲区大小 |
| bind-address=127.0.0.1 | ||
| max_connections = 100 | max_connections = 100 | 最大连接数,如果在访问网站时经常出现Too Many Connections的错误提 示，则需要增大该参数值 |
| max_connect_errors=100 | max_connect_errors = 20 | 不要设置这么大，20就差不多了,同一个IP地址最大错误连接数目，就是说如果连接失败，并且超过这个数字后，mysql将拒绝这个IP再次连接，这个数字设置的太大没有必要，设置的小一点，可以防止某些恶意攻击,看有的兄弟把这个值设置为10000000，真是汗一个，如果遇上有人搞破坏，撑不到这个数，机器就已经挂了，只能你硬重启，也无从发现问题；设置的小一点，这样应用程序和数据库之间的连接出现问题了，发现后也可以及时的处理。 如果发现是网络原因，最后应用程序无法连接数据库而报错，只需要在数据库上flush hosts就行。 |
| open_files_limit = 1024 | open_files_limit = 600 | 最大可以打开的文件数，可以查看http://imysql.cn/2008_04_18_how_mysql_calculate_open_files文章 |
| back_log = 384 | back_log = 20 | 指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求，该参数生效，主线程花费很短的时间检查连接并且启动一个新线程。back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。 |
| table_cache = 1024 | table_cache = 300 | 缓存表格数，与最大连接数有关 http://www.askwan.com/read.php?4 |
| query_cache_size = 0M | query_cache_size = 2M | 查询缓存的值，了解更多cache_size，请访问：在vps主机上mysql数据库优化 |
| query_cache_limit = 1M | query_cache_limit = 1M | 具体可以查看这篇文章 http://www.17rumen.com/archives/49.html 如果查询结果很大， 也缓存？？？？这个明显是不可能的。MySql 可以设置一个最大的缓存值，当你查询缓存数结果数据超过这个值就不会进行缓存。缺省为1M，也就是超过了1M查询结果就不会缓存 |
| query_cache_type=1 | query_cache_type=1 | 查询缓存类型设置。 |
| query_cache_min_res_unit = 4k | query_cache_min_res_unit = 2k | 默认是4K,最小分配的快大小，可以看看：http://www.phpe.net/mysql_manual/06-9.html |
| myisam_sort_buffer_size = 8M | myisam_sort_buffer_size = 4M | myisam_sort_buffer_size = 4M 默认为8M, |
| thread_cache_size = 32 | thread_cache_size = 10 | 服务器线程缓存,可以看看这篇文章 http://www.sysbus.com/?p=115 http://qroom.blogspot.com/2008/09/query-cache.html |
| tmp_table_size = 64M | tmp_table_size = 512K | mysql 的配置文件中，tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，如果你做很多高级 GROUP BY 查询，增加 tmp_table_size 值。 |
| max_allowed_packet = 16M | max_allowed_packet = 16M | 可以控制其通信缓冲区的最大长度 |
| external-locking = FALSE | external-locking = FALSE | 来避免外部锁，从MySQL 4.0开始，这个选项默认是打开的。在这之前，只有编译支持，也可以在启动文件命令添加：–skip-external-locking |
| thread_concurrency = 8 | thread_concurrency = 8 | 该参数取值为服务器逻辑CPU数量×2，在vps宿主机，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4 × 2 ＝ 8 |
| wait_timeout=28800 | wait_timeout = 20 | 由连接到确定断线的时间，默认是28800（sec)将它改为20秒，此参数可以查看>mysql show processlist;查看的状态来决定。 |
| [mysql] | ||
| no-auto-rehash | ||
| [mysqldump] | ||
| quick | ||
| max_allowed_packet = 16M | ||
| [mysqlhotcopy] | ||
| interactive-timeout | ||
最终整理后，我们的vps主机特惠型使用的/etc/my.cnf如下：
[mysqld]
user=mysql
port=3306
socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
log-error=/var/log/mysqld.log
log-bin=/var/lib/mysql/localhost-bin
expire_logs_days = 7
binlog_cache_size =2M
log-slow-queries=/var/lib/mysql/slow-queries.log
long_query_time = 2
default-character-set=utf8
# old_passwords=1
skip-locking
skip-name-resolve
skip-innodb
skip-bdb
# skip-memory
key_buffer_size = 4M
sort_buffer_size = 256K
join_buffer_size = 128K
read_buffer_size = 128K
bind-address=127.0.0.1
max_connections = 100
max_connect_errors=20
open_files_limit = 600
back_log = 20
table_cache = 300
query-cache-size = 2M
query_cache_limit = 1M
query-cache-type = 1
query_cache_min_res_unit = 2k
myisam_sort_buffer_size = 4M
thread_cache_size = 10
tmp_table_size = 512K
max_allowed_packet = 16M
external-locking = FALSE
thread_concurrency = 2
wait_timeout = 20
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
no-auto-rehash
default-character-set=utf8
[mysqldump]
quick
max_allowed_packet = 16M
[mysqlhotcopy]
interactive-timeout
没有评论:
发表评论