mysql 调整参数实例

该语句计算mysql最大占用的内存数
select @@key_buffer_size + @@query_cache_size + @@tmp_table_size
 + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size
 + @@innodb_log_buffer_size
 + @@max_connections * (
 @@read_buffer_size + @@read_rnd_buffer_size
 + @@sort_buffer_size+ @@join_buffer_size
 + @@binlog_cache_size + @@thread_stack
 );

以下是参数实例及调整依据


# The MySQL server
[mysqld]
skip-name-resolve
port = 3306
socket = /tmp/mysql.sock
skip_external_locking
max_allowed_packet = 64M
sort_buffer_size = 16M
join_buffer_size = 16M
net_buffer_length = 1M


 # cpu核的2倍
thread_concurrency=32  


default_character_set=gbk
character_set_server=gbk
default_storage_engine=innodb
innodb_file_per_table
tmpdir=.
table_open_cache = 256
open-files-limit=2048


# show status like "max_used_connections"
max_connections=200    




#wait_timeout=315360000


tmp_table_size=256M
max_heap_table_size=256M


slow_query_log = 1






#  only for myisam    -- start
key_buffer_size = 64k
read_buffer_size = 64k
read_rnd_buffer_size = 64k
myisam_sort_buffer_size = 64K
#  only for myisam    -- end






skip-federated






# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
# (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，命中率越大越好。
# 根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。值可以用以下命令查得：show status like 'Innodb_buffer_pool_read%';


innodb_buffer_pool_size = 10G


# 这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL会记录Warning信息到数据库的error log中，这时候你就知道该调整这个参数大小了。32G内存推荐100M
innodb_additional_mem_pool_size = 100M


# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 500M


# 一般是4-8M,不建议超过32M
innodb_log_buffer_size = 32M


innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16


# 根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大
# 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲. 如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；
query_cache_size = 512M


# 这个值表示可以重新利用保存在缓存中线程的数量   (Connections -  Threads_created) / Connections * 100 %  超过90% 为合理
# show status like 'thread%';   show status like '%connection%';
thread_cache_size = 128


# Here you can see queries with especially long duration
log_slow_queries = D:\\test_analyse\mysql-winx64\data\mysql-slow.log
long_query_time = 2
#log-queries-not-using-indexes
[mysqldump]
quick
#quote-names
max_allowed_packet = 16M


[mysql]
#no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
prompt="\\r:\\m:\\s> "
user = root
password = 123456789
[isamchk]
key_buffer = 128M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M


[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M


[mysqlhotcopy]
interactive-timeout




# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs