MYSQLチューニング
9813 ワード
PS： Dell R710， E5620、16G 。CentOS 5.6 64 ，MySQL 5.5.x 。 IP 50-100w，PV 100-300w ， InnoDB 。 。
# MySQL 。
# MySQL 。
# MySQL 。
# MySQL 。
# [client] #password = [your_password]
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
# *** ***# # MySQL
# [mysqld]
#
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
# back_log ,
# MySQL .
# ”connection refused” ,
# . # .
# back_log , back_log = 300# TCP/IP .
# mysqld,
#
# mysqld Unix sockets .
# windows
# ( “enable-named-pipe” ) mysql !#skip-networking
# MySQL
# SUPER .
# .
max_connections = 3000# , .
# MySQL ”FLUSH HOSTS”
# .
# “Aborted_connects” .
max_connect_errors = 30# .
# mysqld
# [mysqld_safe] “open-files-limit” 4096
table_cache = 4096# .
# ( !)
# MyISAM
#external-locking
# ( BLOB )
# .
max_allowed_packet = 32M
# binlog SQL cache
# , , .
# binlog binlog
# , .
#
binlog_cache_size = 4M
# .
# . max_heap_table_size = 128M
# ORDER BY GROUP BY
# ,
#
# “Sort_merge_passes” .
# sort_buffer_size = 16M
# (full JOINs ).
# ,
# .
# “Select_full_join”
# ,
join_buffer_size = 16M
# cache
# , cache thread_cache_size,
# cache .
#
# ( , .)
thread_cache_size = 16# .
# thread_concurrency() ( Sun Solaris).
# [CPU ]*(2..4) thread_concurrency
thread_concurrency = 8# SELECT .
# , .
# “Qcache_lowmem_prunes” .
# : ,
# .
query_cache_size = 128M
#
# , .
query_cache_limit = 4M
# .
# , . # ,
# FULLTEXT
ft_min_word_len = 8# memlock() , mysql , swapping out#
#memlock
# ,
# ,
default_table_type = MYISAM
# . .
# MySQL 64K
# UDF
# ,
# .
thread_stack = 512K
# . :
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ
# ( )
# , .
# , .
tmp_table_size = 128M
# .
# (replication) , MASTER
# , .
log-bin=mysql-bin
# (A->B->C),
# B .
# , # .
#log_slave_updates
# . ( )
# . , .
#log
# log . MySQL
# log , .
#log_warnings
# . “long_query_time” .
# log_long_format , .
# . 。
log_slow_queries
# ( ) .
# ”1″, , ( MySQL ).
long_query_time = 6# . # .
#
log_long_format
# MySQL . ,
# , .
# .
# , swapfs/tmpfs
# .
# ”;”
# roud-robin .
#tmpdir = /tmp
# ***
# , 1 2^32-1 .
# master slave .
# “master-host” , 1, ,MySQL master .
server-id = 1# Slave ( master )
# # slave , :
# # 1) CHANGE MASTER TO ( ) -# :
# # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
# # , , master ( 3306).
# # :
# # CHANGE MASTER TO MASTER_HOST=’125.56.12.1′, MASTER_PORT=3306, # MASTER_USER=’joe’, MASTER_PASSWORD=’secret’;
# #
# # 2) . , , ( ,
# master-password slave ),
# slave master.info ,
# master.info , slave , master.info slave .
# , ( ) CHANGE MASTER TO ( )
# # id 2 2^32 �C 1
# ( master )
# master-host . 2
# , #server-id = 2# # master �C #master-host =# # master slave �C
#master-user =# # master slave �C
#master-password =# # master .
# �C 3306
#master-port =# slave . SUPER slave .
# slave master
#read_only
#*** MyISAM
# , MyISAM .
# 30%,
# OS
# MyISAM , 8-64M . key_buffer_size = 128M
# MyISAM .
# , .
read_buffer_size = 8M
# , , .
# , ORDER BY .
#
read_rnd_buffer_size = 64M
# MyISAM cache
# ( ,INSERT … SELECT, INSERT … VALUES (…), (…), …, LOAD DATA
# INFILE) . .
# 0 .
# “key_buffer_size”.
# .
bulk_insert_buffer_size = 256M
# MySQL REPAIR, OPTIMIZE, ALTER LOAD DATA INFILE .
# . .
myisam_sort_buffer_size = 256M
# MySQL ( REPAIR, ALTER TABLE LOAD DATA INFILE).
# , ( )
myisam_max_sort_file_size = 10G
# , .
# .
myisam_max_extra_sort_file_size = 10G
# , MyISAM .
# CPU , . myisam_repair_threads = 1# MyISAM .
myisam_recover
# Federated skip-federated
# *** BDB ***# MySQL BDB . .
skip-bdb
# *** INNODB ***# MySQL InnoDB ,
# ,
#skip-innodb
# InnoDB metadata
# InnoDB , OS .
# , .
# SHOW INNODB STATUS .
innodb_additional_mem_pool_size = 64M
# InnoDB , MyISAM.
# , I/O .
# , 80%# , , .
# 32 2-3.5G ,
# .
innodb_buffer_pool_size = 6G
# InnoDB .
# , .
# . .
# InnoDB �C
innodb_data_file_path = ibdata1:10M:autoextend
# InnoDB .
# MySQL datadir .
#innodb_data_home_dir =# IO IO .
# Unix 4, Windows I/O .
innodb_file_io_threads = 4# InnoDB , .
# 1 .
#innodb_force_recovery=1# InnoDb .
# , . # .
innodb_thread_concurrency = 16# 1 ,InnoDB (fsync) ,
# ACID .
# , , 0 2 I/O # 0 .
# 2 , .
innodb_flush_log_at_trx_commit = 2 （ ： ， 2； ， 1； 0 ， ， ！ 1 （flush） ， 。 （Battery backed up cache） 。 2 ， MyISAM ， 。 flush ， 1-2 。 0 ， ， MySQL 。 2 。）
# InnoDB . InnoDB .
# , InnoDB .
#innodb_fast_shutdown
# .
# , InnoDB .
# , ( )
innodb_log_buffer_size = 16M
# .
# 25%~100%# .
# , . innodb_log_file_size = 512M
# .
# 2~3 .
innodb_log_files_in_group = 3# InnoDB . MySQL datadir.
# RAID1 #innodb_log_group_home_dir
# InnoDB .
# , InnoDB .
# , .
innodb_max_dirty_pages_pct = 90# InnoDB .
# # “fdatasync”, “O_DSYNC”.
#innodb_flush_method=O_DSYNC
# , InnoDB .
# InnoDB .
# LOCK TABLES , InnoDB
# InnoDB .
# timeout .
innodb_lock_wait_timeout = 120[mysqldump]
# . quick
max_allowed_packet = 32M
[mysql] no-auto-rehash
# UPDATEs DELETEs .
#safe-updates
[isamchk]
key_buffer = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
[myisamchk]
key_buffer = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
# .
# : !#
open-files-limit = 8192