文章目录
1 MySQL服务器参数介绍
1.1 MySQL获取配置信息路径
- 命令行参数
mysqlId_safe --datadir=/data/sql_data
- 配置文件
mysqlId --help --verbose | grep -A 1 'Default options' /etc/my.cnf
结果:/etc/my.cnf /etc/mysql/my.cnf /home/mysql/my.cnf ~/.my.cnf
1.2 MySQL配置参数的作用域
- 全局参数
set global 参数名=参数值;
set @@global.参数名 :=参数值;
- 会话参数
set [session] 参数名=参数值;
set @@session.参数名 :=参数值;
1.3 内存配置相关参数
- 确定可以使用的内存的上限
- 确定MySQL的每个连接使用的内存
sort_buffer_size
:排序缓冲区大小,一旦有排序,系统会将参数的最大值分配给线程,尽量设计合理,不然会造成内存溢出。
join_buffer_size
:每个线程连接缓冲区的大小。
read_buffer_size
:当MyISAM读缓存时的大小。
read_rnd_buffer_size
:索引缓冲区大小。 - 确定需要为操作系统保留多少内存
- 如何为缓存池分配内存
Innodb_buffer_pool_size
:定义了Innodb缓存池的大小。尽可能的大。
总内存 - (每个线程所需要的内存 * 连接数) - 系统保留内存
key_buffer_size
:主要用于MyISAM存储引擎。因为MyISAM存储引擎只缓存索引。通过下列命令获取MyISAM索引缓存池的大小:
select sum(index_length) from information_schema.tables where engine='myisam';
尽管所使用的数据表都是Innodb存储引擎,但是还是需要给key_buffer_size
分配一定的空间,因为MySQL数据库系统表默认使用MyISAM存储引擎。
1.4 I/O相关配置参数
Innodb I/O相关配置:
Innodb_log_file_size
Innodb_log_files_in_group
- 事务日志总大小 =
Innodb_log_file_size
*Innodb_log_files_in_group
Innodb_log_buffer_size
Innodb_flush_log_at_trx_commit
控制日志缓冲刷新到磁盘的频率
0:每秒进行一次log写入cache,并flush log到磁盘
1[默认]:在每次事务提交执行log写入cache,并flush log到磁盘
2[建议]:每次事务提交,执行log数据写入到cache,每秒执行一次flush log到磁盘。Innodb_flush_method=O_DIRECT
:通知操作系统不要缓存数据,也不要预读Innodb_file_per_table=1
:控制Innodb如何使用表空间,如果设置为1,Innodb会对每个表建立一个单独的表空间,否则会把所有Innodb的表存储在系统表空间中。Innodb_doublewrite=1
:控制Innodb是否使用双写缓存,主要作用是避免没有写完整所导致的这种数据损坏。
MyISAM I/O相关配置:
delay_key_write
与Innodb_flush_log_at_trx_commit
参数相似。
OFF:每次写操作后刷新键缓冲中的脏块到磁盘
ON:只对在建表时指定了delay_key_write选项的表使用延迟刷新
ALL:对所有MyISAM表都是用延迟键写入
1.5 安全相关配置参数
expire_logs_days
:指定自动清理binlog的天数max_allowed_packet
:控制MySQL可以接收的包的大小skip_name_resolve
:禁用DNS查找sysdate_is_now
:确保sysdate()返回确定性日期read_only
:禁止非super权限的用户写权限skip_slave_start
:禁用Slave自动恢复sql_mode
:设置MySQL所使用的SQL模式strict_trans_tables
:如果给定的数据不能插入到事务型存储引擎中,则会中断当前操作,对非事务引擎的参数是没有任何影响no_engine_subtitution
:保证如果在create table语句中所指定的存储引擎不可用的情况下,不会使用默认的存储引擎来建立表,这保证了我们在建表时,可以使用我们认为MySQL所使用的存储引擎,如果我们指定的存储引擎是不可用的,这个表也建立不成功。no_zero_date
:在严格的SQL模式下,不能把0年0月0日这样的日期写入到表中的日期字段上。no_zero_in_date
:在严格的SQL模式下,不接受部分日期为0的日期。only_full_group_by
:要求在分组查询中,group by语句把所有的没有聚合函数的列全部要列出来,否则sql语句不能执行。
建议不要轻易改动已经使用在生产环境中的sql_mode值,以免造成应用程序出错的问题。
1.6 其它常用配置参数
sync_binlog
:控制MySQL如何向磁盘刷新binlog
0[默认]:MySQL不会主动刷新,由操作系统自己决定什么时候刷新cache到磁盘。
大于0:表示两次刷新到磁盘操作之间的间隔多少次二进制的写操作,通常一次事务就有一次写操作,由此可见,如果我们把值设置为1,事务完成后都会有一个binlog写日志的操作,这样操作是最安全的情况,同时也要进行磁盘的写操作,所以成本也是最高的。建议将参数的值设置为1。tmp_table_size
和max_heap_table_size
:控制Memory存储引擎的内存临时表大小,这两个值的设置尽量保持一致,并且不要设置太大,以免造成内存溢出。max_connections
:控制允许的最大连接数。默认值为100
第二章总结
性能优化顺序
- 数据库结构设计和SQL语句
- 数据库存储引擎的选择和参数配置
- 系统选择及优化
- 硬件升级