mysql性能优化学习笔记-参数介绍及优化建议

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql性能优化学习笔记-参数介绍及优化建议

无声胜有声 2016-08-21 16:23:00 浏览630
展开阅读全文

MySQL服务器参数介绍

mysql参数介绍(客户端中执行),尽量只修改session级别的参数。

全局参数(新连接的session才会生效,原有已经连接的session不生效)

  • set global 参数名=参数值;
  • set @@global.参数名 :=参数值;

会话参数

  • set [session] 参数名=参数值;
  • set @@session.参数名 :=参数值;

内存配置相关参数

  • 确定可以使用的内存的上限
  • 确定mysql每个连接使用的内存
sort_buffer_size:需要注意,每个连接分配指定大小的内存;

join_buffer_size;

read_buffer_size;

read_rnd_buffer_size。

上述都为每个进程分别进行设置,所以要注意配置大小。
  • 确定需要为操作系统保留多少内存
  • 如何为缓存池分配内存
Innodb_buffer_pool_size:总内存 - (每个线程所需内存 * 连接数) - 系统保留内存

key_buffer_size:主要用于myisam引擎

IO相关配置参数

Innodb I/O相关配置

  • Innodb_log_file_size:单个日志文件大小
  • Innodb_log_files_in_group:日志文件的个数
  • 事务日志总大小:
Innodb_log_files_in_group * Innodb_log_file_size
  • Innodb_log_buffer_size:日志缓冲区大小
  • Innodb_flush_log_at_trx_commit:日志刷新的频率
0:每秒进行一次log写入cache,并flush log到磁盘。如果mysql关闭或重启会导致至少1秒日志的丢失。

1:『默认』,在每次事务提交执行log写入cache,并flush log到磁盘。保证不会导致事务日志的丢失。

2:【建议】,每次事务提交,执行log数据写入到cache。每秒执行一次flush log到磁盘。
  • Innodb_flush_method=O_DIRECT:Innodb刷新的方式。
  • Innodb_file_per_table=1:表空间。强烈建议启用。
  • Innodb_doublewrite=1:

Myisam I/O相关配置

  • delay_key_write
OFF:每次写操作后刷新新建缓冲区的脏块到磁盘
ON:只对建表是指定了delay_key_write选项的表使用延迟刷新。
ALL:对所有MYISAM表使用延迟键写入。会导致表损坏,使用相关命令进行修复。

安全相关配置参数

  • expire_logs_days指定自动清理binlog的天数
  • max_allowed_packet控制mysql可以接收的包的大小
  • skip_name_resolve禁用DNS查找
  • sysdate_is_now确保sysdate和now返回的日期是一样的。(同一条sql同时使用sysdate返回的结果有可能是不同的,因而建议启用)
  • read_only禁止非super用户写权限:在做主从复制时,建议备库启用这个配置项。
  • skip_slave_start警用slave自动恢复,(从服务器中进行配置)
  • sql_mode设置mysql所使用的sql模式。(建议不要在生产换环境中调整这个值,有可能会导致系统不可用)
strict_trans_tables

no_engine_subtitution

no_zero_date

no_zero_in_date

only_full_group_by:select字句需要全部列出group by字段,否则报错。

其它常用配置参数

  • sync_binlog控制mysql如何向磁盘中刷新binlog
  • tmp_table_size和max_heap_table_size控制内存临时表的大小。两个值应保持一致
  • max_connections允许最大连接数,一般设置为2000都可以。

数据库设计对性能的影响(所有优化的重点,其它的所有优化加起来都未必有数据库设计优化对性能的影响大)

  • 过分的反范式话为表建立太多的列
  • 过多的范式话造成太多表的关联(最多只允许61个表关联)
  • 在OLTP中使用不恰当的分区表
  • 使用外键保证数据的完整性(性能很低,建议不要使用外键约束)

优化建议

性能优化顺序

  • 数据库结构和sql语句优化
  • 数据库引擎的选择和参数配置(不要混合使用存储引擎)
  • 系统选择及优化
  • 硬件升级

网友评论

登录后评论
0/500
评论
无声胜有声
+ 关注