MYSQL优化

MYSQL优化

1.修改最大连接数


# 查询
show variables like 'max_connections'

# 临时修改
set GLOBAL max_connections=200

# 用户连接数
show global status like 'Max_used_connections'

2.设置查询缓存


#
SHOW VARIABLES LIKE  '%query_cache%'

# 修改缓存可用内存 默认32M 改为64M
set GLOBAL query_cache_size=67108864

# 大于多少的结果不缓存 默认1M 改为2M
set GLOBAL query_cache_limit=2097152

3. 修改临时表内存


# 改为1024M
set GLOBAL tmp_table_size=1073741824;
set GLOBAL max_heap_table_size=1073741824;

4. 开启慢查询记录


log_slow_queries = ON
long_query_time = 3
log-queries-not-using-indexes

#########################################################

MySQL配置文件my.cnf优化:




# 适用于: 4GB 内存, 主要引擎是InnoDB, 支持ACID, 少量并发连接, 大量的查询

[client]

#password	= [your_password]
port		= 3306
socket		= /tmp/mysql.sock


[mysqld]

port		= 3306
socket		= /tmp/mysql.sock


############################################

# 优化参数,其他保持默认
tmp_table_size = 2G
max_heap_table_size = 2G

############################################


# 初始连接数,默认:50
back_log = 50

# 禁止远程连接数据库,默认:关闭
skip-networking

# 最大连接数,默认:100
max_connections = 1000

# 密码错误重试次数,默认:10
max_connect_errors = 10

# 最多允许打开表的数目,默认:2048
table_open_cache = 2048

# 启用外部文件级锁定,默认:禁用
#external-locking

# 单个查询的最高容量,默认:16M
max_allowed_packet = 16M

# 事务日志缓存大小
binlog_cache_size = 1M

# 内存中单表允许的最大值,默认:64M
# max_heap_table_size = 64M

# 全表扫描的缓冲区大小,默认:2M
read_buffer_size = 2M

# 有序查询缓存大小,可以提高order by查询的性能
read_rnd_buffer_size = 16M

# 排序缓冲,默认:8M
sort_buffer_size = 8M

# join语句缓存大小,默认:8M
join_buffer_size = 8M

# 线程数,默认:8
thread_cache_size = 8

# 最好是CPU的2到8倍
thread_concurrency = 8

# 查询缓存大小,默认:64M
query_cache_size = 64M

# 大于此值的结果集将不被缓存,默认:2M
query_cache_limit = 2M

# 全文检索索引的最小单词长度,默认:4
ft_min_word_len = 4

# 如果系统支持memlock函数,最好启动,默认:禁用
#memlock

# 默认储存引擎,默认:MYISAM
default-storage-engine = MYISAM

# 使用的线程堆栈大小,默认:192K
thread_stack = 192K

# 设置默认的事务隔离等级,默认:REPEATABLE-READ
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ

# 内存中的临时表的最大容量,默认:64M
# tmp_table_size = 64M

# 启用二进制日志,默认:启用
log-bin=mysql-bin

# 推荐的二进制日志格式,默认:mixed
binlog_format=mixed

# 允许从服务器日志更新,默认:禁用
#log_slave_updates

# 启用完整的查询日志,开发时启用,生产环境禁用。默认:禁用
#log

# 打印警告到错误日志文件,默认:禁用
#log_warnings

# 记录慢查询,默认:启用
slow_query_log

# 慢查询时间,默认:2
long_query_time = 2


# ***  集群设置


# 服务器ID
server-id = 1

#server-id = 2
#master-host = <hostname>
#master-user = <username>
#master-password = <password>
#master-port = <port>
#read_only

#*** MyISAM 特殊设置


# 键缓冲区的大小,默认:32M
key_buffer_size = 32M

# 优化大量插入操作,要小于key_buffer_size,默认:64M
bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

# *** INNODB 特殊设置

# 禁用Innodb,默认:注释
#skip-innodb

# 附加的内存池,默认:16M
innodb_additional_mem_pool_size = 16M

# InnoDB缓存内存大小,最大可以设置为内存的80%,默认:2G
innodb_buffer_pool_size = 2G

# InnoDB 存储数据到一个或多个数据文件,形成表空间
innodb_data_file_path = ibdata1:10M:autoextend

# Innodb数据文件目录,默认:注释
#innodb_data_home_dir = <directory>

# 异步 IO 操作所使用的 IO 线程数,默认:8
innodb_write_io_threads = 8
innodb_read_io_threads = 8

# 默认:1
#innodb_force_recovery=1

# InnoDB内核允许的线程数量,默认:16
innodb_thread_concurrency = 16

# 自动刷新事务日志,默认:1
innodb_flush_log_at_trx_commit = 1

# 加速 InnoDB 的关闭,默认:注释
#innodb_fast_shutdown

# InnoDB 缓冲日志数据所使用的缓冲区大小,默认:8M
innodb_log_buffer_size = 8M

# 日志组中每个日志文件的大小,默认:256M
innodb_log_file_size = 256M

# 日志组中文件的总数
innodb_log_files_in_group = 3

# InnoDB 日志文件的位置,默认:注释
#innodb_log_group_home_dir

# InnoDB 缓冲池中允许的脏页面的最大百分比,默认:90
innodb_max_dirty_pages_pct = 90

# InnoDB 对日志使用的刷新方法,注释是 "fdatasync", 可选:"O_DSYNC"
#innodb_flush_method=O_DSYNC

# InnoDB 锁超时时间
innodb_lock_wait_timeout = 120


[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick

max_allowed_packet = 16M


[mysql]
no-auto-rehash

# 只允许 UPDATEs 和 DELETEs 操作使用 keys.
#safe-updates


[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M


[mysqlhotcopy]
interactive-timeout


[mysqld_safe]
open-files-limit = 8192


发表评论