MySQL 优化闲聊 - mysql参数常用基础优化

  多年删库跑路、背锅经验告诉我,MySQL这个东西参数真的没啥好优化的,固定套路走一波 sysbench 不会太难看的,最后说到底还是磁盘「云上升配、自建么上Pcie」,平时自己测试玩都比生产猛「都可以压出各种异常,MySQL的日志莫非只有在这个时候有用?」,还哔哔慢「你说是谁的问题」。

优化步骤

  平时优化基本上按照如下顺序来玩即可

  1. SQL优化是基础,Explain是神器

  2. 软件优化,传说中的优化参数

  3. 硬件优化,升配

  4. 架构优化,这个就麻烦了,可能这个时候就要引入各种其他中间件了

  所有表结构和SQL决定了80%的性能了,至关重要,MySQL没有银蛋。

参数优化

  innodb_buffer_pool_size 硬件资源的75%~85%「其实这个东西不是越大越好,例如大查询为主要场景就要掂量着给了,mysql社区版是没有连接池的,一个连接就是独立的内存空间,万一并发一高查询还大直接OOM了,宕机没商量」,如下参数均是5.6为主「5.7和8.0也没好好研究,因为关键的不在一个数据库版本上,参数也较为保守,具体是啥意思自己官方文档看看,MySQL文档太全了。」

  malloc-lib = libjemalloc.so 「高负载下可以基于 Jemalloc 提高稳定性,提高内存管理效率」

  innodb_purge_threads 4 「均按24c的cpu为例」

  innodb_read_io_threads 8 「均按24c的cpu为例」

  innodb_write_io_threads 8 「均按24c的cpu为例」

  innodb_io_capacity_max 「8 Raid 10:400;SSD:5000,Pcie:40000+」

  innodb_io_capacity  「8 Raid 10:200;SSD:2000,Pcie:20000+」

  innodb_max_dirty_pages_pct 70 可以提高如果写入要求巨大

  innodb_flush_log_at_trx_commit = 2 「理论上丢失最后一个事物的提交」

  sync_binlog = 0 「交给磁盘能跑多快跑多快」

  # *** INNODB Specific Options *** 仅供参考
  innodb_data_file_path = ibdata1:512M:autoextend
  innodb_flush_log_at_trx_commit = 2
  innodb_flush_method = O_DIRECT
  innodb_file_per_table = ON
  innodb_buffer_pool_size = 32G
  innodb_buffer_pool_instances = 1
  innodb_log_file_size = 2G
  innodb_log_files_in_group = 4
  innodb_log_buffer_size = 16M
  innodb_undo_logs = 128 # <default: 128 Min:0 Max:128>
  innodb_undo_tablespaces = 8
  innodb_page_size = 16K # <default: 16K Other: 8K 4K>
  innodb_purge_threads = 4 #Page Cleaner Thread
  innodb_read_io_threads = 8
  innodb_write_io_threads = 8
  innodb_io_capacity_max = 5000
  innodb_io_capacity = 2000
  innodb_disable_sort_file_cache = ON #Temporary files O_DIRECT
  innodb_flush_neighbors = 0  #For table data stored on SSD
  innodb_lock_wait_timeout=180
  innodb_max_dirty_pages_pct = 75
  innodb_file_format = Barracuda
  innodb_open_files = 4096
  innodb_strict_mode = 1
  innodb_print_all_deadlocks = 1 #All deadlocks in InnoDB user transactions is recorded in the mysqld error log
  innodb_thread_concurrency = 16
  #Faster Restart
  innodb_buffer_pool_load_at_startup = 1
  innodb_buffer_pool_dump_at_shutdown = 1


  这边引入几个概念

  redo log 写入到 ib_logfile 「多个循坏写入的」,恢复提交事务修改的页,物理日志,保证事务的持久性,为顺序写,innodb_flush_log_at_trx_commit 0 1 2,redo log以512字节存储与磁盘大小一致不需要doublewrite。

  undo log 是这样玩的写入到ibdata表空间中,回滚行记录到某个特定的版本,逻辑日志,帮助回滚&MVCC,随机读写,undo log 伴随着redo log的产生。

  下图很好解释了innodb_flush_log_at_trx_commit 0 1 2的区别,决定性能的关键参数。

  引出一个系统概念:LSN实际上对应日志文件的偏移量,新的LSN=旧的LSN + 写入的日志大小

  Log sequence number(LSN1):当前系统LSN最大值,新的事务日志LSN将在此基础上生成(LSN1+新日志的大小);

  Log flushed up to(LSN2):当前已经写入日志文件的LSN;

  Oldest modified data log(LSN3):当前最旧的脏页数据对应的LSN,写Checkpoint的时候直接将此LSN写入到日志文件;

  Last checkpoint at(LSN4):当前已经写入Checkpoint的LSN;

  因此从理论上来说,只要事务执行速度大于脏页刷盘速度,最终都会触发日志保护机制,进而将事务阻塞,导致MySQL操作挂起。

  IO很高的原因,因为这个时候在不断的刷脏页数据到磁盘。

感悟

  MySQL 不适合暴力写入,理论上其实读操作都是可以优化的,但是写操作除了批量写入,小步快跑方法就不多了「要不这么多分布式数据库能起来,OLTP有 Tidb 国人之光、CockroachDB,OLAP 有 Greenplum、ClickHouse 战斗名族这个可以学习下的」。MySQL不是万精油别都朝里怼,真不合适。

  

作者:Secret_Sun
原文链接:https://www.jianshu.com/p/8e4e49863eef