mysql5.1 my.cnf 优化_MySQL性能优化之优化配置my.cnf文件

[client]

#password= your_password

port= 3306

socket= /opt/mysql/dbdata/mysql.sock

# The MySQL server

[mysqld]

port= 3306

socket= /opt/mysql/dbdata/mysql.sock

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache = 512

#sort_buffer_size = 2M

sort_buffer_size = 256M

#read_buffer_size = 2M

read_buffer_size = 32M

#read_rnd_buffer_size = 8M

read_rnd_buffer_size = 32M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 32M

#开启慢查询日志

log_slow_admin_statements=ON

log_slow_slave_statements=ON

slow_query_log=1

#慢查询日志文件存放路径

slow_query_log_file=/opt/mysql/mysqllog/logfile/slow-query.log

#执行的时间大于多少秒的SQL语句记录到慢查询日志

long_query_time=2

#未使用索引的查询也记录到文件

log_queries_not_using_indexes = 1

#日志记录写到日志文件

log_output='FILE'

#数据连接等待时间，单位秒

interactive_timeout = 1800

wait_timeout = 1800

basedir = /opt/mysql/product

datadir = /opt/mysql/dbdata

max_binlog_size=100M

log_bin=/opt/mysql/mysqllog/binlog/binlog.bin

log-error=/opt/mysql/mysqllog/logfile/mysql-err.log

binlog_format=mixed

expire_logs_days=7

binlog_cache_size=4MB

pid-file = /opt/mysql/dbdata/mysql.pid

default-storage-engine=MyISAM

user = mysql

group_concat_max_len = 10240

max_connections=3000

#(cpu+磁盘)数量的2倍

#thread_concurrency = 8

thread_concurrency = 24

# Point the following paths to a dedicated disk

#tmpdir= /tmp/

#skip-networking

log-bin=mysql-bin

server-id= 1

#server-id = 2

#

# The replication master for this slave - required

#master-host =

#

# The username the slave will use for authentication when connecting

# to the master - required

#master-user =

#

# The password the slave will authenticate with when connecting to

# the master - required

#master-password =

#

# The port the master is listening on.

# optional - defaults to 3306

#master-port =

#

# binary logging - not required for slaves, but recommended

#log-bin=mysql-bin

#

# binary logging format - mixed recommended

binlog_format=mixed

#使用 InnoDB 引擎，可开启以下配置项

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /opt/mysql/dbdata

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /opt/mysql/dbdata

#数据和索引缓冲区的大小

#innodb_buffer_pool_size = 256M

#innodb_buffer_pool_size = 10240M

#数据字段和其他数据结构的大小

#innodb_additional_mem_pool_size = 20M

#innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

#redo日志大小

#innodb_log_file_size = 64M

#innodb_log_file_size = 2560M

#日志缓冲区的大小

#innodb_log_buffer_size = 8M

#innodb_log_buffer_size = 16M

#0 提交事物不写入日志，每秒日志文件写入和flush磁盘

#1 每秒或每次事物提交时，日志文件写入 flush磁盘

#2 每次事物提交时，日志文件写入，每秒flush磁盘

#innodb_flush_log_at_trx_commit = 1

#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

[myisamchk]

#key_buffer_size = 128M

#sort_buffer_size = 128M

#read_buffer = 2M

#write_buffer = 2M

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 32M

write_buffer = 32M

[mysqlhotcopy]

interactive-timeout

[mysqlhotcopy]

interactive-timeout