my.cnf 位置
Red Hat / Centos : /etc/my.cnf
Debian: /etc/mysql/my.cnf
my.cnf 配置说明
[client]
port = 3309
socket = /usr/local/mysql/tmp/mysql.sock
[mysqld]
!include /usr/local/mysql/etc/mysqld.cnf
port = 3309
bind-address = 0.0.0.0
server-id = 1
socket = /usr/local/mysql/tmp/mysql.sock
pid-file = /usr/local/mysql/var/mysql.pid
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/var/
tmpdir = /usr/local/mysql/tmp/
slave-load-tmpdir = /usr/local/mysql/tmp/
skip-name-resolve
skip-symbolic-links
skip-slave-start
skip-networking
sysdate-is-now = 1
back_log = 50
max_connections = 1000
max_connect_errors = 10000
open_files_limit = 10240
connect-timeout = 10
wait-timeout = 28800
interactive-timeout = 28800
slave-net-timeout = 600
net_read_timeout = 30
net_write_timeout = 60
net_retry_count = 10
net_buffer_length = 16384
max_allowed_packet = 64M
服务器收到大于 max_allowed_packet 字节的信息包时,将发出“信息包过大”错误,并关闭连接。对于某些客户端,如果通信信息包过大,在执行查询期间,可能会遇到“丢失与 MySQL 服务器的连接”错误。默认值 16M。
table_cache = 512
thread_stack = 192K
thread_cache_size = 20
根据物理内存设置规则如下:
1G —> 8
2G —> 16
3G —> 32
大于3G —> 64
thread_concurrency = 8
query_cache_limit = 2M
query_cache_min_res_unit = 2K
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过 20%,可以用 FLUSH QUERY CACHE 整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size *100%
查询缓存利用率在 25%以下的话说明 query_cache_size 设置的过大,可适当减小;查询缓存利用率在 80%以上而且 Qcache_lowmem_prunes > 50 的话说明 query_cache_size 可能有点小,要不就是碎片太多。查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
query_cache_size = 64M
代码:
> SHOW VARIABLES LIKE '%query_cache%';
> SHOW STATUS LIKE 'Qcache%';如果 Qcache_lowmem_prunes 的值非常大,则表明经常出现缓冲不够的情况;
如果 Qcache_hits 的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲; Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。
memlock
紧张的时候,数据在内存中保持锁定并且防止可能被 swapping out,此选项对于性能有益
default_table_type = InnoDB
default-time-zone = system
character-set-server = utf8
default-storage-engine = InnoDB
tmp_table_size = 512M
max_heap_table_size = 512M
log-bin = mysql-bin
log_slave_updates = 1
log-bin-index = mysql-bin.index
relay-log = relay-log
relay_log_index = relay-log.index
log-warnings = 1
log-error = /usr/local/mysql/log/mysql.err
log_output = FILE
log_slow_queries
slow_query_log = 1
long-query-time = 1
log_long_format
slow_query_log_file = /usr/local/mysql/log/slow.log
log-queries-not-using-indexes
min_examined_row_limit=1000
long-slow-admin-statements
log-slow-slave-statements
general_log = 1
general_log_file = /usr/local/mysql/log/mysql.log
max_binlog_size = 1G
max_relay_log_size = 1G
relay-log-purge = 1
expire_logs_days = 30
binlog_cache_size = 1M
replicate-wild-ignore-table = mysql.%
slave_skip_errors=all
slave_skip_errors选项有四个可用值,分别为:off,all,ErorCode,ddl_exist_errors。
默认情况下该参数值是off,我们可以列出具体的error code,也可以选择all,mysql5.6及MySQL Cluster NDB 7.3以及后续版本增加了参数ddl_exist_errors,该参数包含一系列error code(1007,1008,1050,1051,1054,1060,1061,1068,1094,1146)
一些error code代表的错误如下:
1007:数据库已存在,创建数据库失败
1008:数据库不存在,删除数据库失败
1050:数据表已存在,创建数据表失败
1051:数据表不存在,删除数据表失败
1054:字段不存在,或程序文件跟数据库有冲突
1060:字段重复,导致无法插入
1061:重复键名
1068:定义了多个主键
1094:位置线程ID
1146:数据表缺失,请恢复数据库
1053:复制过程中主服务器宕机
1062:主键冲突 Duplicate entry '%s' for key %d
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 8M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover = 64K
transaction_isolation = REPEATABLE-READ
1.READ UNCOMMITTED-读未提交 2.READ COMMITTE-读已提交 3.REPEATABLE READ -可重复读 4.SERIALIZABLE -串行
skip-innodb
innodb_file_per_table = 1
独立表空间优点:
1.每个表都有自已独立的表空间。
2.每个表的数据和索引都会存在自已的表空间中。
3.可以实现单表在不同的数据库中移动。
4.空间可以回收(除drop table操作处,表空不能自已回收)
缺点:
1.单表增加过大,如超过100G
结论:
共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时,请合理调整:innodb_open_files
innodb_status_file = 1
innodb_open_files = 2048
innodb_additional_mem_pool_size = 100M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_data_home_dir = /usr/local/mysql/var/
innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
0 代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.
2 代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.
innodb_log_buffer_size = 8M
innodb_log_file_size = 500M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /usr/local/mysql/var/
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DSYNC
innodb_force_recovery=1
innodb_fast_shutdown
[mysqldump]
quick
[mysql]
auto-rehash
default-character-set = utf8
connect-timeout = 3
[mysqld_safe]
open-files-limit = 8192
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)