暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 聊聊数据库：SQL运维~配置篇

聊聊数据库：SQL运维~配置篇

逸鹏说道 2019-04-04
177


  • 1.安全相关配置

  • 2.内存相关

  • 3.IO相关参数

  • 4.其他服务器参数

  • 扩展工具： pt-config-diff

  • 扩展：常见存储引擎

  • Memory存储引擎

上节回顾：聊聊数据库：SQL运维~先导篇

1.6.3.MySQL配置参数

建议：优先从数据库设计和SQL优化着手，然后才是配置优化和存储引擎的选择，最后才是硬件提升

设计案例： 列太多
不行， 关联太多
也不行（10个以内），不恰当的 分区表
，使用了外键

分区表：一个服务器下，逻辑上还是一个表，物理存储上分成了多个表（类似于SQLServer的水平分库）

PS：分库分表：物理和逻辑上都拆分成多个表了

之前讲环境的时候简单说了下最基础的

  1. [mysqld]
  2. # 独立表空间: 每一个表都有一个.frm表描述文件，还有一个.ibd文件
  3. innodb_file_per_table=on
  4. # 不对连接进行DNS解析(省时)
  5. skip_name_resolve=on
  6. # 配置sql_mode
  7. sql_mode='strict_trans_tables'

然后说 SQL_Mode
的时候简单说了下 全局参数
和 会话参数
的设置方法：MySQL的SQL_Mode修改小计

  • 全局参数设置： setglobal参数名=参数值;

    • 只对新会话有效，重启后失效

  • 会话参数设置： set[session]参数名=参数值

    • 只对当前会话有效，其他会话不影响

这边继续说下其他几个影响较大的配置参数：（对于开发人员来说，简单了解即可，这个是DBA的事情了）

1.安全相关配置

  • expire_logs_days
    ：自动清理binlog

    • PS：一般最少保存7天（具体根据业务来）

  • max_allowed_packet
    ：配置MySQL接收包的大小

    • PS：默认太小。如果配置了主从，需要配置成一样大（防止丢包）

  • skip_name_resolve
    ：禁用DNS查找    （这个我们之前说过了，主要是提速）

    • 用 *
      的是没影响的

    • PS：如果启用了，那么进行用户授权时，只能通过 ip
      或者 ip段
      或者 本机host出现过的域名
      进行授权

  • sysdata_is_now
    ：保证sysdate()返回确定性日期

    • 类似的问题还有很多，eg：获取最后一次id的时候（ last_insert_id()
      ）

    • 扩：现在MySQL有了 Mixed
      模式

    • PS：如果主从使用了binlog的 statement
      模式，sysdata的结果会不一样，最后导致数据不一致

  • read_only
    ：一般用户只能读数据，只有root用户可以写：

    • PS：推荐在从库中开启，这样就只接受从主库中的写操作，其它只读

    • 从库授权的时候不要授予超级管理员的权限，不然这个参数相当于废了

  • skip_slave_start
    ：禁用从库( Slave
    )自动恢复

    • MySQL在重启后会自动启用复制，这个可以禁止

    • PS：不安全的崩溃后，复制过去的数据可能也是不安全的（手动启动更合适）

  • sql_mode
    ：设置MySQL的SQL模式    （这个上次说过，默认是宽松的检测，这边再补充几个）

    • PS：防止数据库迁移的时候出错

    • 要求在在分组查询语句中，把所有没有使用聚合函数的列，列出来

    • eg： selectcount(url),namefromfile_recordsgroupbyurl;

    • 使用了name字段，name不是聚合函数，那必须在group by中写一下

    • 最常见，主要对事物型的存储引擎生效，其他的没效果

    • PS：如果插入数据不符合规范，则中断当前操作

    • strict_trans_tables
      ：对所有支持事物类型的表做严格约束      ：

    • no_engine_subtitution
      ：建表的时候指定不可用存储引擎会报错

    • only_full_group_by
      ：检验 groupby
      语句的合法性

    • ansi_quotes
      ：不允许使用双引号来包含字符串

    • PS：生存环境下最好不要修改，容易报错对业务产生影响（严格变宽松没事）

PS：一般 SQL_Mode
是测试环境相对严格（ strict_trans_tables,only_full_group_by,no_engine_subtitution,ansi_quotes
），线上相对宽松（ strict_trans_tables
）

补充说下 sysdate()
和 now()
的区别:（看个案例就懂了）

PS：对于一个语句中调用多个函数中 now()
返回的值是执行时刻的时间，而 sysdate()
返回的是调用该函数的时间

  1. MariaDB [(none)]> select sysdate(),sleep(2),sysdate();
  2. +---------------------+----------+---------------------+
  3. | sysdate()        | sleep(2) | sysdate()        |
  4. +---------------------+----------+---------------------+
  5. | 2019-03-28 09:09:29 |     0 | 2019-03-28 09:09:31  |
  6. +---------------------+----------+---------------------+
  7. 1 row in set (2.001 sec)
  9. MariaDB [(none)]> select now(),sleep(2),now();
  10. +---------------------+----------+---------------------+
  11. | now()           | sleep(2) | now()          |
  12. +---------------------+----------+---------------------+
  13. | 2019-03-28 09:09:33 |     0 | 2019-03-28 09:09:33  |
  14. +---------------------+----------+---------------------+
  15. 1 row in set (2.000 sec)

2.内存相关

  • sort_buffer_size
    ：每个会话使用的排序缓冲区大小

    • PS：每个连接都分配这么多eg：1M，100个连接==>100M（默认是全部）

  • join_buffer_size
    ：每个会话使用的表连接缓冲区大小

    • PS：给每个join的表都分配这么大，eg：1M，join了10个表==>10M

  • binlog_cache_size
    ：每个会话未提交事物的缓冲区大小

  • read_rnd_buffer_size
    ：设置索引缓冲区大小

  • read_buffer_size
    ：对MyISAM全表扫描时缓冲池大小    （一般都是4k的倍数）

    • PS：对临时表操作的时候可能会用到

read_buffer_size
的扩充说明：

现在基本上都是Innodb存储引擎了，大部分的MyISAM的配置就不用管了，但是这个还是需要配置下的

引入下临时表知识扩展：

  1. 系统使用临时表：

  • 不超过16M：系统会使用 Memory
    表

  • 超过限制：使用 MyISAM
    表

  1. 自己建的临时表：（可以使用任意存储引擎）

  • create temporary table tb_name(列名类型类型修饰符,...)

PS：现在知道为啥配置 read_buffer_size
了吧（系统使用临时表的时候，可能会使用 MyISAM
）

3.IO相关参数

主要看看 Innodb
的 IO
相关配置

事物日志：（总大小: Innodb_log_file_size*Innodb_log_files_in_group
）

  • 事物日志大小： Innodb_log_file_size

  • 事物日志个数： Innodb_log_files_in_group

日志缓冲区大小： Innodb_log_buffer_size

一般日志先写到缓冲区中，再刷新到磁盘（一般32M~128M就够了）

日志刷新频率： Innodb_flush_log_at_trx_commit

  • 0：每秒进行一次日志写入缓存，并刷新日志到磁盘（最多丢失1s）

  • 1：每次交执事物就把日志写入缓存，并刷新日志到磁盘（默认）

  • 2：每次事物提交就把日志写入缓存，每秒刷新日志到磁盘（推荐）

刷新方式： Innodb_flush_method=O_DIRECT

关闭操作系统缓存（避免了操作系统和Innodb双重缓存）

如何使用表空间： Innodb_file_per_table=1

为每个innodb建立一个单独的表空间（这个基本上已经成为通用配置了）

是否使用双写缓存： Innodb_doublewrite=1
（避免发生页数据损坏）

  • 默认是开启的，如果出现写瓶颈或者不在意一些数据丢失可以不开启（开启后性能↑↑）

  • 查看是否开启： show variables like'%double%';

设置innodb缓冲池大小： innodb_buffer_pool_size

如果都是innodb存储引擎，这个参数的设置可以这样来算：（一般都是内存的 75%
） 查看命令： showglobalvariables like'innodb_buffer_pool_size';
PS：缓存数据和索引（直接决定了innodb性能） 课后拓展：https://www.cnblogs.com/wanbin/p/9530833.html

innodb缓存池实例的个数： innodb_buffer_pool_instances

PS：主要目的为了减少资源锁增加并发。 每个实例的大小=总大小/实例的个数
一般来说，每个实例大小不能小于1G，而且个数不超过8个

4.其他服务器参数

  • sync_binlog
    ：控制MySQL如何像磁盘中刷新binlog

    • 还是那句话：一般不去管，具体看业务

    • 默认是0，MySQL不会主动把缓存存储到磁盘，而是靠操作系统

    • PS：为了数据安全，建议主库设置为1（效率也容易降低）

  • 控制内存临时表大小： tmp_table_size
     and max_heap_table_size

    • PS：建议保持两个参数一致

  • max_connections
    ：设置最大连接数

    • 默认是100，可以根据环境调节，太大可能会导致内存溢出

  • Sleep
    等待时间    ：一般设置为相同值（通过连接参数区分是否是交互连接）

    • interactive_timeout
      ：设置交互连接的timeout时间

    • wait_timeout
      ：设置非交互连接的timeout时间

扩展工具： pt-config-diff

使用参考： pt-config-diff u=root,p=pass,h=localhost/etc/my.conf

eg：比较配置文件和服务器配置

  1. pt-config-diff /etc/my.cnf  h=localhost --user=root --password=pass
  2. 3 config differences
  3. Variable                  /etc/my.cnf mariadb2
  4. ========================= =========== ========
  5. max_connect_errors        2           100
  6. rpl_semi_sync_master_e... 1           OFF
  7. server_id                 101         102

课后拓展：https://www.cndba.cn/leo1990/article/2789

扩展：常见存储引擎

常见存储引擎：

  1. MyISAM：不支持事物，表级锁

  • 索引存储在内存中，数据放入磁盘

  • 文件后缀： frm、MYD、MYI

  1. Innodb
    ：事物级存储引擎，支持行级锁和事物ACID特性

  • 同时在内存中缓存索引和数据

  • 文件后缀： frm、ibd

  1. Memory
    ：表结构保存在磁盘文件中，表内容存储在内存中

  • Hash索引、B-Tree索引

  • PS：容易丢失数据（重启后数据丢失，表结构依旧存在）

  1. CSV
    ：一般都是作为中间表

  • 以文本方式存储在文件中，不适合大表

  • frm（表结构）、CSV（表内容）、CSM（元数据，eg：表状态、数据量）

  • PS：不支持索引（engine=csv），所有列不能为Null

  • 详细可以查看上次写的文章：小计：协同办公衍生出的需求

  1. Archive：数据归档（压缩）

  • 文件： .frm
    (存储表结构)、 .arz
    (存储数据)

  • 只支持 insert
    和 select
    操作

  • 只允许在自增ID列上加上索引

  • 适合场景：日志类
    （省空间）

  1. Federated：建立远程连接表（性能不怎样，默认禁止）

  • 本地不存储数据（数据全部在远程服务器上）

  • 本地需要保存表结构和远程服务器的连接信息

  • PS：类似于SQLServer的链接服务器

逆天点评：除非你有100%的理由，否则全选 innodb
，特别不建议混合使用

Memory存储引擎

Memory存储引擎：

  1. 支持 Hash
    和 BTree
    两种索引

    • create index ix_nameusingbtree on tb_name(字段,...)

    • Hash索引：等值查找（默认）

    • Btree索引：范围查找

    • PS：不同场景下的不同选择，性能差异很大

  2. 所有字段类型都等同于固定长度，且不支持 Text
    和 Blog
    等大字段类型

    • eg： varchar(100)
      ==等价于==> char(100)

  3. 存储引擎使用表级锁

    • PS：性能不见得比innodb好

  4. 大小由 max_heap_table_size
    决定（默认16M）

    • PS：如果想存大点，就得改参数（对已经存在的表不生效，需要重建才行）

  5. 常用场景（ 数据易丢失，要保证数据可再生
    ）

  • 缓存周期性聚合数据的结果

  • 用于查找或者映射的表（eg：邮编和地区的对应表）

  • 保存数据分析中产生的中间表

PS：现在基本上都是redis了，如果不使用redis的小项目可以考虑（eg：官网、博客...）

文章拓展：

  1. OLAP、OLTP的介绍和比较
  2. https://www.cnblogs.com/hhandbibi/p/7118740.html
  4. now()与sysdate()
  5. http://blog.itpub.net/22664653/viewspace-752576/
  6. https://stackoverflow.com/questions/24137752/difference-between-now-sysdate-current-date-in-mysql
  8. binlog三种模式的区别（row，statement，mixed）
  9. https://blog.csdn.net/keda8997110/article/details/50895171/
  11. MySQL-重做日志 redo log -原理
  12. https://www.cnblogs.com/cuisi/p/6525077.html
  14. 详细分析MySQL事务日志(redo log和undo log)
  15. https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html
  17. innodb_flush_method的性能差异与File I/O
  18. https://blog.csdn.net/melody_mr/article/details/48626685
  20. InnoDB关键特性之double write
  21. https://www.cnblogs.com/geaozhang/p/7241744.html


下节预估：权限、日志篇

数据库
文章转载自逸鹏说道，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论