MySQL如何充分利用系统资源?
1.如果采取“先把数据放在内存,然后集中写入磁盘”的办法,可以节省 CPU 资源和磁盘读取的时间,但是也会面临系统故障时会丢失数据的风险;
相反,如果每次都写入磁盘,数据最安全,但是频繁的磁盘读写,会导致系统效率低下。这就需要我们提升优化资源配置的能力。
2. 调整系统参数 InnoDB_flush_log_at_trx_commit
默认的值是 1,意思是每次提交事务的时候,都把数据写入日志,并把日志写入磁盘。
2 表示,每次提交事务的时候都将数据写入日志,但是日志每间隔 1 秒写入磁盘。
这样做的好处是数据安全性最佳,不足之处在于每次提交事务,都要进行磁盘写入的操作。
在大并发的场景下,过于频繁的磁盘读写会导致 CPU 资源浪费,系统效率变低。
参数的值改成了 2。这样一来,就不用每次提交事务的时候都启动磁盘读写了,
在大并发的场景下,可以改善系统效率,降低 CPU 使用率。即便出现故障,损失的数据也比较小。
3. 调整系统参数 InnoDB_buffer_pool_size
InnoDB 存储引擎使用缓存来存储索引和数据。这个值越大,可以加载到缓存区的索引和数据量就越多,需要的磁盘读写就越少。
50%
4. 调整系统参数 InnoDB_buffer_pool_instances
将 InnoDB 的缓存区分成几个部分,这样一来,就可以提高系统的并行处理能力,因为可以允许多个进程同时处理不同部分的缓存区。
把 InnoDB_buffer_pool_instances 的值修改为 64,意思就是把 InnoDB 的缓存区分成 64 个分区,这样就可以同时有多个进程进行数据操作,CPU 的效率就高多了。
利用监控信息诊断问题
performance_schema.events_statements_history_long
先用下面的语句,来看一下哪些查询消耗的时间多。这个表的数据量很大
mysql> SELECT -> TRUNCATE(TIMER_WAIT / 1000000000000, 6) AS duration, -- 计算查询的时长,单位是微微秒,需要转换成秒 -> sql_text, -> EVENT_ID -> FROM -> performance_schema.events_statements_history_long -> WHERE -> TRUNCATE(TIMER_WAIT / 1000000000000, 6) <> 0 -> AND sql_text IS NOT NULL -> ORDER BY TRUNCATE(TIMER_WAIT / 1000000000000, 6) DESC -> LIMIT 1,2; +----------+---------------------------------+----------+ | duration | sql_text | EVENT_ID | +----------+---------------------------------+----------+ | 137.2529 | select count(*) from demo.trans | 17 | | 137.2420 | select count(*) from demo.trans | 907 | +----------+---------------------------------+----------+ 2 rows in set (0.00 sec)
用一个例子来演示会更加清晰