MySQL数据库架构--什么影响了MySQL性能-最终篇

发表于 | 分类于 | | 阅读次数: |
字数统计: 1,625 字 | 阅读时长 ≈ 6 分钟
MySQL服务器参数介绍

加载配置最终起作用的是后面的配置文件覆盖前面的。

Mysql获取配置信息路径

  • 我们可以通过命令行指定配置

    1
    mysqld_safe --datadir=/data/sql_data

  • 我们看下默认配置文件的访问顺序

    1
    2
    3
    [root@hongshaorou ~]#  mysqld --help --verbose | grep -A 1 'Default options'
    Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

MySQL配置参数的作用域

MySQL的配置参数根据作用域的不同可以分为全局参数和会话参数。

  • 全局参数
1
2
3
# 命令要在mysql客户端下执行
set global 参数名=参数值;
set @@global.参数名 := 参数值;
  • 会话参数
1
2
set [session] 参数名=参数值;
set @@session.参数名 := 参数值;

session没有指定的情况下，会使用同名的全局配置。

全局配置完之后，需要退出重新登录才能生效。

内存配置相关参数

内存配置相关参数

  • 确定可以使用的内存的上限

    不要超过物理内存

  • 确定MySQL的每个连接使用的内存

    mysql对一些缓存对每个连接的内存是单独分配的，所以使用的连接数越大，占用的内存也就会越多

    sort_buffer_size: 排序缓存区的尺寸，这个参数定义了mysql每个线程使用的排序缓存区的大小，mysql不是在连接初始化的时候后就给每个缓冲区分配内存，而是在有查询或排序操作时候，才会给每个缓冲区分配内存，然而在mysql出现查询排序的时候，mysql会立即分配这个参数设置的大小内存给缓冲区，而不管该排序是否需要这么大的内存，有的时候会把这个值设置很大，增加查询效率，比如100M，如果有100个连接同时进行排序，那么mysql，就会分配出10g左右的内存，所以很容易造成服务器内存溢出，所以配置这个的时候要注意。

    join_buffer_size: 连接缓冲区的尺寸，这个参数定义了mysql每个线程使用的连接缓冲区的大小，如果一个查询中关联了多张表，就会为每个关联 分配一个连接缓冲，所每以个查询可能会有多个连接缓冲，所以这个参数也不能设置太大

    read_buffer_size: 当对myisam表进行全表扫描时，所分配的读缓冲区的大小， mysql只会在有查询需要的时候才会对该缓存分配内存，同样也会一次性分配该参数的值，这个参数值是4k的倍数

    read_rnd_buffer_size: 索引缓冲区的大小，mysql也是只有在查询的时候，才会给该缓冲区分配内存，会分配内存需要的大小，不会分配指定的的大小

    上面四个参数都是对每个线程生效的，如果有100个连接将会由100倍的内存被分配出去。

  • 确定需要为操作系统保留多少内存

  • 如何为缓存池分配内存

    Innodb_buffer_pool_size: 定义了Innodb所使用的缓冲池的大小，这个参数对Innodb存储引擎性能是影响很大的，Innodb不仅要缓存索引还要缓存数据。Innodb缓存池还帮助写入，这个帮助数据一起顺序的写入到磁盘，性能影响最大，所以必须分配足够的内存。公式=总内存-（每个线程所需要的内存*连接数）-系统保留内存。官方手册中，参数配置值要是服务器内存75%以上

    key_buffer_size： 这个缓冲池存储引擎用于myisam，只会缓冲索引，数据则是依赖操作系统的缓存

    1
    2
    # 我们使用下面的查询 可以知道 myisam 表的索引占得缓存池的大小
    select sum(index_length) from information_schema.tables where engine='myisam';

    即使我们的数据表全是Innodb引擎，上面的这个参数我们还是有必要设置的，因为MySQL的内部表还是使用的是myisam存储引擎。

IO相关配置参数

​ 这部分参数决定了mysql如何同步缓存池中的数据到磁盘上，已修改数据持久化的保存，这些操作对性能的影响非常的大 ，如果需要修改的数据立刻写入到磁盘上，那是很昂贵的操作。

I/O配置和存储引擎是相关的。

Innodb I/O相关配置

Innodb_log_file_size 控制了单个事务日志的大小

Innodb_log_file_in_group 控制了事务日志文件的个数，

事务日志文件总大小=Innodb_log_files_in_group * Innodb_log_file_size

事务日志文件是循环使用的。

事务日志不是每次提交直接写入到日志文件中的，而是先写入到事务日志的缓冲区，然后再刷新到磁盘中。

Innodb_log_buffer_size 控制日志缓冲区的大小。（32M-128M）

Innodb_flush_log_at_trx_commit 刷新事务日志的频率

这个参数有三个选择 ：

0：每秒进行一次log写入到cache 并flush log到磁盘。在事务提交时候不会做任何事情，在MySQL崩溃的时候回至少丢失一秒钟的事务。

1：默认值，在每次事务提交执行log写入cache，并flush log到磁盘。

2：建议值，每次事务提交，执行log数据写入到cache，每秒执行一次flush log到磁盘。与 0 值比较，在MySQL崩溃的时候不会丢失事务。

Innodb_flush_method=O_DIRECT

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可以接收的包的大小（32M）

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

no_zero_date

no_zero_in_date

only_full_group_by

其他配置参数

sync_binlog //控制MySQL如何向磁盘刷新binlog（主DB 建议为1）

tmp_table_size 和 max_heap_table_size //控制内存临时表大小

max_connections //允许的最大链接数

知识就是财富
如果您觉得文章对您有帮助, 欢迎请我喝杯水！