简单参数优化案例

参数优化案例：

1、服务器16G内存，峰值最大连接数为500个，使用MyISAM和InnoDB两种引擎。

为操作系统预留20%内存，约3G

2、与线程相关的几个关键参数

sort_buffer_size=2M

read_buffer_size=2M

read_rnd_buffer_size=2M

join_buffer_size=2M

峰值最大将占用500*(2+2+2+2)=4G

3、MyISAM引擎表不多，主要是系统对象

key_buffer_size=16M

key_cache_block_size=64K

myisam_sort_buffer_size=64M

4、剩下的全部分给InnoDB缓存池

innodb_buffer_pool_size=9G

innodb_thread_concurrency=8

innodb_flush_method=O_DIRECT

innodb_log_buffer_size=16M

innodb_flush_log_at_trx_commit=2

经过配置后，MySQL初始化参数文件为

[mysql@mysql1 3306]$ more my.cnf

[client]

port = 3306

socket = /data/mysqldata/3306/mysql.sock

 

#The MySQL server

[mysqld]

port = 3306

user = mysql

socket = /data/mysqldata/3306/mysql.sock

pid-file = /data/mysqldata/3306/mysql.pid

basedir = /usr/local/mysql

datadir = /data/mysqldata/3306/data

tmpdir = /data/mysqldata/3306/tmp

open_files_limit = 10240

explicit_defaults_for_timestamp

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

federated

server_id=1223344556

max_connections = 1000

max_connect_errors = 100000

interactive_timeout = 86400

wait_timeout = 86400

skip-name-resolve

sync_binlog = 0

 

#Buffer

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 2M

join_buffer_size = 2M

net_buffer_length = 16K

max_allowed_packet = 512M

max_heap_table_size = 512M

bulk_insert_buffer_size = 32M

tmp_table_size = 512M

thread_cache_size = 300

query_cache_size = 128M

query_cache_limit = 1M

query_cache_min_res_unit = 4K

key_buffer_size = 16M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

 

#Log

log-bin = /data/mysqldata/3306/binlog/mysql-bin

binlog_cache_size = 32M

max_binlog_cache_size = 512M

max_binlog_size = 512M

#binlog_format = mixed

binlog_stmt_cache_size = 32M

table_open_cache = 2048

binlog_format = statement

log_output = FILE

log-error = /data/mysqldata/3306/mysql-error.log

slow_query_log = 1

slow_query_log_file = /data/mysqldata/3306/slow_query.log

general_log = 0

general_log_file = /data/mysqldata/3306/general_query.log

expire-logs-days = 14

relay-lay = /data/mysqldata/3306/binlog/relay-bin

relay-log-index = /data/mysqldata/3306/binlog/relay-bin.index

 

#InnoDB

innodb_data_file_path = ibdata1:2048M:autoextend

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_buffer_pool_size = 9G

innodb_buffer_pool_instances = -1

innodb_max_dirty_pages_pct = 70

innodb_thread_concurrency = 8

innodb_flush_method = O_DIRECT

innodb_log_buffer_size = 16M

innodb_flush_log_at_trx_commit = 2

 

[mysql]

no-auto-rehash

prompt = (\u@\h) [\d]>\_

default-character-set = gbk

 

重新测试性能：

[mysql@mysql1 ~]$ /tpcc-mysql/tpcc_start -h localhost -d tpcc -u system -p "123456" -w 1 -c 10 -r 10 -l 20 -f /home/mysql/tpcc_mysql.log -t /home/mysql/tpcc_mysql.rtx