mysql的调整

来源：互联网发布：php reset current 编辑：程序博客网时间：2024/04/29 10:42

/etc/my.cnf

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock
default-character-set = utf8

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[mysqld]
# default-character-set = utf8

port = 3306
socket = /tmp/mysql.sock

basedir = /www/mysql
datadir = /www/mysql/data
open_files_limit = 4096 (记得改linux的默认值 /etc/security/limits.conf * - nofile 8012,加大table_cache值后,如果遇到描述符不够的问题,增大此值,)

max_connections = 3000 (如果经常出现 too many connections的错语提示,表示需要加在此值)
max_connect_errors = 1024 (同一个IP最大错误连接数,就是说如果连接失败,并且超过这个数字后,MYSQL将拒绝这个IP再次连接,如果发现是网络原因,最后应用程序无法连接数据库,只需要在数据库上flush hosts就行用'mysqladmin flush-hosts')

table_cache = 512 (指定表高速缓存的大小,通过检查峰值时间的状态值open_tables 和 opened_tables可以决定是否需要增加table_cache的值,如果你发现open_tables等于table_cache的什,并且opened_tables在不停的增长,那你就需要加大table_cache的值了,但是不能盲目地设置成很大的值,否则可能会造成文件描述符不足,造成性能不稳定或失败 show status like 'open%';)
max_allowed_packet = 16M ( 常通过MySQL的"load data local infile"语句将一个文本文件中的内容导入到数据库中，这样速度会很快，但今天发现如果文本的大小超过1M时，出现异常：“Packets larger than max_allowed_packet are not allowed MySQL安装目录下的my.ini文件中的[mysqld]段中的＂max_allowed_packet = 1M＂，如更改为16M(如果没有这行内容，增加一行)，保存，重起MySQL服务。现在可以load大于1M的文件了。)
skip-locking (external-locking = FALSE)

sort_buffer_size = 2M (排序查询操作所能使用的缓冲区大小)

join_buffer_size = 2M (联合查询操作所能使用的缓冲区大小)

read_buffer_size = 1M
read_rnd_buffer_size = 16M 排序缓冲相关参数

thread_cache_size = 32 (如果应用时有大量并发连接,并且可以看到threads-created变量讯速增长需要调高一些可以设置成300)
thread_stack = 256K

thread_concurrency = 4 (CUP的个数*2)

# query_prealloc_size = 128k-->64K 无需解释设置错误，这样设置的结果Query cache数据将给人错误的参数指导
# query_alloc_block_size = 64k-->128K 无需解释设置错误，这样设置的结果Query cache数据将给人错误的参数指导

query_cache_size = 32M (400M 通常设置32M 到512M之间, 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多,需要清理，flush query cache。 show status like 'qcac%'; qcache_free_blocks 数目大可能有碎片; qcache_free_memory 缓存中的空闲内存; qcache_hits:每次查询在缓存中命中时是就增大; qcache_inserts:每次插入一个查询时就增大,qcache_not_cached:不适合进行缓存的查询数量;qcache_queries_in_cache:当前缓存的查询数量)
query_cache_limit = 2M (只有小于此设定值的结果才缓冲，此设置用来保护查询缓冲，防止一个极大的结果集将其他所有的查询都覆盖)
query_cache_min_res_unit = 2k (query_cache_min_res_unit默认值是4KB。如果你有大量返回小结果数据的查询，默认数据块大小可能会导致内存碎片，显示为大量空闲内存块。由于缺少内存，内存碎片会强制查询缓存从缓存内存中修整（删除）查询。这时，你应该减少query_cache_min_res_unit变量的值。空闲块和由于修整而移出的查询的数量通过Qcache_free_blocks和Qcache_lowmem_prunes变量的值给出。如果大量查询返回大结果（检查 Qcache_total_blocks和Qcache_queries_in_cache状态变量），你可以通过增加query_cache_min_res_unit变量的值来提高性能。但是，注意不要使它变得太大

query_cache_type = 1 (打开查询缓冲，为1是使用缓冲，2是除非使用SQL_CACHE才进行缓冲)

default-storage-engine = MyISAM
default_table_type = MyISAM (创建新表时作为默认使用的表类型)

transaction_isolation = READ-COMMITTED (设定事务隔离级别,可用的级别 READ-UNCOMMITED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE)

tmp_table_size = 246M (内存中临时表的最大大小,如果一个表增长到比此值更大,将会自动转换为基于磁盘的表)
max_heap_table_size = 246M (独立的内存表所充许的最大容量,此选项为了防止意外创建一个超大的内存表导致永尽所有的内存资源)

log_long_format (未按索引查询的也记录下来)
log_slow_queries = ON 启用慢查询
log_slow_queries = slow-log
long_query_time = 4
expire_logs_days = 7(设置日志过期的天数,过了指定的天数后,日志将会自动删除)#

#log-bin = /www/mysql/binlog (如果不需要配置mysql复制,不建议启用,开启log-bin 会使性能下降46%)
#binlog_cache_size = 4M (二进制缓存日志大小设置其可以使用的内存大小)
#binlog_format = MIXED binlog的格式也有三种：STATEMENT，ROW，MIXED
#max_binlog_cache_size = 8M ( binlog_cache_size 来设置其可以使用的内存大小，同时通过 max_binlog_cache_size 限制其最大大小（当单个事务过大的时候 MySQL 会申请更多的内存）。当所需内存大于 max_binlog_cache_size 参数设置的时候，MySQL 会报错：“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”。)
#max_binlog_size = 512M

key_buffer_size = 384M (如果只使用MYIASM表,把它的值设置为可用内存的30%-40%, 查看key-read和key-read-requests的对比值,越低越好 1:100 1:1000 1:10000)

bulk_insert_buffer_size = 64M   (使用多个值表的 INSERT 语句或者 LOAD DATA 命令，可以大大缩减客户端与数据库之间的连接、语法分析等消耗，使得效率比分开执行的单个 INSERT 语句快很多，相关的命令我们会在 SQL 优化详细介绍。如果多值的 INSERT 或者 LOAD DATA 是往一个非空的数据表里增加记录，也可以通过调整bulk_insert_buffer_size 参数来提高数据插入的效率，这个参数设置的是 bulk insert 的缓存大小，默认是 8M )
interactive_timeout =200     对后续起的交互链接有效；
wait_timeout=200    对当前交互链接有效；

#skip-innodb (默认启用了innodb存储引擎,建议关闭,节约内存开销
#skip-bdb mysql4.0无此选项如果你运行的MYSQL服务有BDB支持,但是你不准备使用的时候使用些选项,这会节省内存并且可能加速一些事.

myisam_sort_buffer_size = 128M 这在每个线程中被分配,所在在设置大值时需要小心,此缓冲当 mysql需要在repair,optimize,alter 及load data infile到一个空表中引起重建索引时被分配.

myisam_max_sort_file_size = 10G MYSQL重建索引时所充许的最大临时文件的大小
myisam_max_extra_sort_file_size = 10G 主要用来强制在大表中长字串键去使用慢速的键值缓冲方法来创建索引
myisam_repair_threads = 1 对于拥有多个CPU及大量内存情况的用户,是一个很好的选择.
myisam_recover 自劝检查和修复没有适当关闭的MyISAM

skip-name-resolve
# master-connect-retry = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396

key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M

# Replication Master Server (default)
# binary logging is required for replication
log-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# binary logging - not required for slaves, but recommended
#log-bin

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /www/mysql/var/
#innodb_data_file_path = ibdata1:10M:autoextend,.
#innodb_log_group_home_dir = /www/mysql/var/
#innodb_log_arch_dir = /www/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

是有时还是连接不上，用netstat -a查看：
TCP web:4299             web:3306             TIME_WAIT
  TCP web:4300             web:3306             TIME_WAIT
  TCP web:4301             web:3306             TIME_WAIT
  TCP web:4302             web:3306             TIME_WAIT
  TCP web:4303             web:3306             TIME_WAIT
  TCP web:4304             web:3306             TIME_WAIT
  TCP web:4306             web:3306             TIME_WAIT
  TCP web:4308             web:3306             TIME_WAIT
  TCP web:4309             web:3306             TIME_WAIT
  TCP web:4310             web:3306             TIME_WAIT
  TCP web:4311             web:3306             TIME_WAIT
  TCP web:4312             web:3306             TIME_WAIT
  TCP web:4313             web:3306             TIME_WAIT
  TCP web:4314             web:3306             TIME_WAIT
  TCP web:4315             web:3306             TIME_WAIT
请问这是什么问题，该如何解决？

长连接没有释放的原因，设置两个参数：
interactive_timeout =200     对后续起的交互链接有效；
wait_timeout=200    对当前交互链接有效；