时间：   |   分类：   | 字数： 1440 字 | 阅读约： 3分钟 | 阅读次数：

系统配置优化

数据库优化维度有四个: 硬件升级, 系统配置, 表结构设计, sql语句及索引

一. 保证从内存中读取数据

MySQL会在内存中保存一定的数据，通过LRU算法将不常访问的数据保存在硬盘文件中。

尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。

扩大innodb_buffer_pool_size，能够全然从内存中读取数据。最大限度降低磁盘操作。

show global status like'innodb_buffer_pool_pages_%';

Innodb_buffer_pool_pages_data	282
Innodb_buffer_pool_pages_dirty	0
Innodb_buffer_pool_pages_flushed	187
Innodb_buffer_pool_pages_free	7909
Innodb_buffer_pool_pages_misc	0               #0 表示已经被用光
Innodb_buffer_pool_pages_total	8191

#innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。

如果是专用的MySQL Server可以禁用SWAP

关闭所有交换设备和文件.

​ swapoff -a

二. 数据预热

默认情况，仅仅有某条数据被读取一次，才会缓存在 innodb_buffer_pool。

所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。

  1. 对于InnoDB数据库，进行数据预热的脚本是

    SELECT DISTINCT
	CONCAT( 'SELECT ', ndxcollist, ' FROM ', db, '.', tb, ' ORDER BY ', ndxcollist, ';' ) SelectQueryToLoadCache 
FROM
	(
	SELECT ENGINE
		,
		table_schema db,
		table_name tb,
		index_name,
		GROUP_CONCAT( column_name ORDER BY seq_in_index ) ndxcollist 
	FROM
		(
		SELECT
			B.ENGINE,
			A.table_schema,
			A.table_name,
			A.index_name,
			A.column_name,
			A.seq_in_index 
		FROM
			information_schema.statistics A
			INNER JOIN ( SELECT ENGINE, table_schema, table_name FROM information_schema.TABLES WHERE ENGINE = 'InnoDB' ) B USING ( table_schema, table_name ) 
		WHERE
			B.table_schema NOT IN ( 'information_schema', 'mysql' ) 
		ORDER BY
			table_schema,
			table_name,
			index_name,
			seq_in_index 
		) A 
	GROUP BY
		table_schema,
		table_name,
		index_name 
	) AA 
ORDER BY
	db,
	tb;

    将该脚本保存为: loadtomem.sql

  2. 执行命令

    mysql -uroot -proot -AN < /root/loadtomem.sql > /root/loadtomem.sql
#保存路径可自定义

  3. 在需要数据预热时,如重启数据库

    mysql -uroot < /root/loadtomem.sql > /dev/null 2>&1

三. 降低磁盘写入次数

  • 增大redolog，减少落盘次数

    innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

  • 通用查询日志、慢查询日志可以不开 ，bin-log开

    生产中不开通用查询日志，遇到性能问题开慢查询日志

  • 写redolog策略 innodb_flush_log_at_trx_commit设置为0或2

    0 : 每隔1s写入文件并通知文件系统flush到磁盘, 可能会造成1秒数据丢失

    1: 每次事务提交都会触发 log thread 触发写文件并持久化到磁盘

    2: 每次事务结束,写文件,但因文件系统有缓存,并不一定完全写到磁盘

    如果不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者事务都非常小，都能够用 0或者 2 来减少磁盘操作。

四. 提高磁盘读写性能

使用SSD或者内存磁盘

0%