多年删库跑路、背锅经验告诉我，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不是万精油别都朝里怼，真不合适。
网友评论