mysql通⽤配置表设计_mysql-通⽤优化配置详解
mysql-通⽤优化配置详解
此处只是将通⽤优化配置项进⾏解释说明，再具体的优化需要根据⾃⼰业务需求进⾏相应配置
transaction_isolation = READ-COMMITTED
解读：事务隔离级别：Oracle、SQL Server等商业数据库默认级别为READ-COMMITTED，MySQL默认为REPEATABLE-READ，它利⽤⾃⾝独有的Gap Lock解决了"幻读"，但也因次相⽐于READ-COMMITTED级别的Record Lock，REPEATABLE-READ的事务并发插⼊性能受到很⼤的限制
设置：隔离级别的选择取决实际的业务需求(安全和性能的权衡)。如果不是⾦融、电信等事务级别要求很⾼的业务，建议设置成：transaction_isolation = READ-COMMITTED
innodb_buffer_pool_size = xx[M|G]
解读：InNoDB缓冲池⼤⼩，它决定mysql可以在内存中缓存多少数据和索引，⽽不是每次都从磁盘上读取
smart原则中的r指的是目标应该具有下面哪个特征
设置：如果是专⽤mysql服务器，⼀般设置为操作系统内存的75%，但⾄少保留2G内存⽤于操作系统维护和MySQL异常事务处理
innodb_buffer_pool_instances = xx
解读：InnoDB缓冲池实例个数，InnoDB缓冲池是通过⼀整个链表的⽅式来管理页⾯(段、簇、页)的，由于互斥锁的存在(保护链表中的页⾯)，⾼并发事务下，页⾯的读取和写⼊就需要锁的竞争和等待。通过设置innodb_buffer_pool_instances，将⼀整个链表划分为多个，每个缓冲池实例管理⾃⼰的页⾯和互斥，从⽽提⾼效率
设置：如果缓冲池⽐较⼤(8G以上)，可以按照innodb_buffer_pool_size / innodb_buffer_pool_instances = 1G进⾏设置，但如果缓冲池特别⼤(32G以上)，可以按照每个实例2~3G进⾏划分，实例数不是越多越好，多实例代表多线程，线程的开销(CPU、MEM)也得考虑
innodb_log_file_size = xxM
解读：InnoDB⽇志⽂件⼤⼩(Redo Log)，它将事务对InnoDB表的修改记录保存在ib_logfile0、ib_logfile1中。innodb_log_file_size越⼤，缓冲池中的脏数据需要检查点(checkpoint)进⾏刷盘的频率就越少，从⽽减少磁盘IO来降低⾼并发负载造成的峰值。但⽇志⽂件也不是越⼤约好，由于内存中脏
数据刷盘的频率减少，⼀旦数据库发⽣异常崩溃，数据库重启时从innodb_log_file中读取数据进⾏恢复的时间越长
设置：⼀般选取业务⾼峰期⼀个⼩时的⽇志量作为标准，计算过程如下
]# mysql -uroot -p -e "show engine innodb status\G"|grep "Log sequence number" \
> && sleep 60 \
> && mysql -uroot -p -e "show engine innodb status\G"|grep "Log sequence number"
#输出
Log sequence number 149949388055
Log sequence number 149959622102
#计算
(149949388055 - 149959622102) / 1024 / 1024 = 10M
10 / 60 * 3600 = 600M
#解释
Log sequence number代表InnoDB运⾏⾄今写⼊⽇志的总字节数，两次打印之间线程休眠60秒
得到⼀分钟之内事务⽇志记录的总量10M，再转换成⼀个⼩时的总量600M
python语言可以做什么因为'ib_logfile0、ib_logfile1'两个⽂件循环写⼊，⼀个⽂件为300M
最终，innodb_log_file_size=300M
innodb_flush_log_at_trx_commit = x
解读：InnoDB事务⽇志刷盘时机
当 x = 0 时，事务提交到⽇志缓冲区，后台Write线程每隔⼀秒将缓冲区的⽇志写⼊系统缓冲区，实际写⼊物理⽇志⽂件的时机取决于操作系统
linux是单用户操作系统当 x = 1 时，事务提交到⽇志缓冲区，Master线程同步将缓冲区的⽇志直接写⼊物理⽇志⽂件，这完全符合InnoDB ACID事务标准，数据不会丢失
当 x = 2 时，事务提交到系统缓冲区，Master线程每隔⼀秒将系统缓冲区的⽇志写⼊物理⽇志⽂件
设置：安全性(1>2>0)速度(0>2>1)，根据实际业务需求选择合理的刷盘时机
- innodb_flush_log_at_trx_commit = 0
transaction --> innodb_log_buffer -- 每隔1s --> os_cache -- 操作系统控制 --> innodb_log_file
- innodb_flush_log_at_trx_commit = 1mysql是什么系统
transaction --> innodb_log_buffer -- 每次 --> innodb_log_file
- innodb_flush_log_at_trx_commit = 2
transaction --> os_cache -- 每隔1s --> innodb_log_file
innodb_file_per_table = [ON|OFF]
解读：InnoDB独⽴表空间，innodb_file_per_table = ON表⽰每张表在独⽴的物理⽂件中(.ibd)存储数据和索引，innodb_file_per_table = OFF表⽰所有表都共享表空间即⼀个物理⽂件(ibdata1)。如果通过drop/truncate table操作，独⽴表空间的物理存储会⽴即被回收(删除/初始化)，⽽共享表空间不会被回收且只会⼀直增⼤
设置：innodb_file_per_table = ON，但需要注意的是，独⽴表空间只存储数据和索引，如回滚⽇志缓冲(Undo Log)、插⼊索引缓冲(Insert Buffer)、⼆次写缓冲(Doublewrite Buffer)等还是放在共享表空间
vps搭建网站教程query_cache_size = 0
解读：查询缓存⼤⼩，它是为了在追踪表的数据未发⽣变化时，本次查询命中之前的查询语句，从⽽跳过解析、优化、执⾏阶段，直接返回缓冲池中的数据。但实际在OLTP系统中，极少能命中查询缓存(前提是数据库中的数据变化频率很⼩)，因为⼀旦数据有变则缓存失效。且因为查询缓存会跟踪所有表的变化，它也会成为整个数据库的瓶颈(资源竞争点)
设置：query_cache_size = 0，同时配合设置query_cache_type = 0，MySQL5.7.20以上、MySQL8.0会直接弃⽤所有查询缓存配置项
max_connections = xxx
解读：最⼤连接数，当max_connections设置太⼩时(默认151)，MySQL可能会报错Too many connections。当max_connections设置太⼤时(1000以上)，操作系统可能忙于线程间的切换⽽失去响应
设置：每个连接都会消耗⼀定内存，计算过程如下：
# 计算
mysql> SELECT CONCAT(ROUND(SUM(VARIABLE_VALUE)/(1024*1024)),'M') AS 'per_connection'
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN ('read_buffer_size', 'read_rnd_buffer_size', 'sort_buffer_size',
'join_buffer_size', 'thread_stack', 'max_allowed_packet', 'net_buffer_length');
+----------------+
| per_connection |
+----------------+
| 17M |
+----------------+
# 连接
mysql> SHOW STATUS LIKE 'threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 99 |
+-------------------+-------+
# 内存
$ top -c|grep mysqld
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
24411 mysql 20 0 8327348 6.458g 7680 S 36.7 85.4 831:56.14 /usr/sbin/mysqld --daemonize ...
# 校验
5 + 17 * 99 / 1024 = 6.64g，其中innodb_buffer_pool_size设置为5G，
总和6.64g约等于通过top命令显⽰的mysqld进程6.458g，
java php pythonMySQL所占操作系统内存的精确计算与本公式有所出⼊，但与实际相差甚微，可⽤于实际⽣产环境计算
注意：
1.performance_schema.global_variables 5.7.6版本以后才有此参数
thread_cache_size = xx
解读：线程缓存⼤⼩，客户端连接时，MySQL会为每个连接分配⼀个线程，通过设置thread_cache_size = N，MySQL可以重复利⽤保存在缓存中的N个线程，从⽽改善频繁的线程创建销毁的开销。同时，应⽤如果有连接池复⽤设置，那也会改善MySQL Server的线程开销
设置：可以通过监控" SHOW STATUS LIKE 'threads_connected' "的数量，确定每天的平均连接数
sync_binlog = x
解读：⼆进制事务⽇志刷盘时机，需要配合log-bin选项才能记录⼆进制⽇志。区别于InnoDB事务⽇志，⼆进制事务⽇志是针对整个MySQL Server的，⽽InnoDB事务⽇志只针对InnoDB存储引擎。⼆进制事务⽇志的作⽤⼀是⽤于主从复制，⼆是⽤于数据恢复。区别于InnoDB事务⽇志恢复，⼆进制事务⽇志是⽤于误操作的数据恢复，⽽InnoDB事务⽇志是⽤于InnoDB存储引擎的崩溃恢复
当0时，将由操作系统控制binlog_cache的刷盘时机
当1时，所有事务开始、提交阶段，都会同步写⼊磁盘，这是最安全的⽅式。如果设置innodb_flush_log_at_trx_commit = 1,
sync_binlog = 1，这是使⽤InnoDB事务最安全可靠的⽅式
当N时，事务每提交N次，同步写⼊⼀次⼆进制⽇志。
设置：如果MySQL是单机，可以考虑sync_binlog=0；如果是主从，且每秒事务并发量低，考虑sync_binlog=1；事务并发量很⾼，考虑sync_binlog=N，N的选取可以通过统计业务正常时期的OPS
设置：如果MySQL是单机，可以考虑sync_binlog=0；如果是主从，且每秒事务并发量低，考虑sync_binlog=1；事务并发量很⾼，考虑sync_binlog=N，N的选取可以通过统计业务正常时期的OPS
- sync_binlog = 0
transaction --> binlog_cache -- 操作系统控制 --> innodb_log_file
- sync_binlog = 1
transaction --> binlog_cache -- 每次提交前后 --> innodb_log_file
- sync_binlog = N
transaction --> binlog_cache -- 每N次提交 --> innodb_log_file
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论