mysql 参数调整_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

阅读(339) | 评论(0) | 转发(0) |