mysql数据库学习-mysql内存IO性能优化

Mysql内存性能优化

1 CPU模式优化

1.7、安装jemalloc,避免原生内存分配器带来的内存碎片问题.

cp $basedir/lib/mysql/libjemalloc.so.1 /usr/lib64/libjemalloc.so

数据库配置文件添加如下

[mysqld_safe]

malloc-lib=/usr/lib64/libjemalloc.so

1.8、修改cpu模式

cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor

2 mysql 数据库8以后得hint

select /*+ set_var(optimizer_switch='index_merge=off') set_var(join_buffer_size=4M) */ c_id from customer limit 1;

3 优化innodb_buffer_pool_size

innodb_buffer_pool_size 决定 InnoDB 存储引擎表数据和索引数据的最大缓存区大小。Innod buffer pool同时为数据块和索引块提供数据缓存，这与Oracle的缓存机制很相似。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O就越少，性能也就越高。在一个专用的数据库服务器上，可以将 80%的物理内存分配给 InnoDB buffer pool，但一定要注意避免设置过大而导致页交换。

innodb缓存池命中率

（1 -innodb buffer pool reads /innodb buffer pool read request）*100

4 优化innodb_log_file_size

一、innodb 重做日志

当更新数据时，innodb 内部的操作流程大致是：

将数据读入 innodb buffer pool，并对相关记录加独占锁；

将 undo 信息写入 undo 表空间的回滚段中；

更改缓存页中的数据，并将更新记录写入 redo buffer中；

提交时，根据 innodb_flush_log_at_trx_commit 的设置，用不同的方式将 redo buffer 中的更新记录刷新到 innodb redo log file 中，然后释放独占锁；

最后，后台 IO 线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中。

innodb_log_buffer_size决定InnoDB重做日志缓存池的大小，默认值是8MB。对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免InnoDB在事务提交前就执行不必要的日志写入磁盘操作。因此，对于会在一个事务中更新、插入或删除大量记录的应用，我们可以通过增大innodb_log_buffer_size来减少日志写磁盘操作，从而提高事务处理的性能。

5 优化thread_cache_size

为加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制MySQL缓存客户服务线程的数量。
可以通过计算线程cache的失效率threads_created/connections来衡量thread_cache_size的设置是否合适。该值越接近1，说明线程cache命中率越低，应考虑适当增加thread_cache_size的值。

[information_schema] 04:40:47>show variables like '%thread_cache_size%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 768 |
+-------------------+-------+
1 row in set (0.00 sec)

[information_schema] 04:38:56>show status like '%threads_created%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 338 |
+-----------------+-------+
1 row in set (0.00 sec)

[information_schema] 04:39:06>show variables like '%connections%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_connections | 10000 |
| max_user_connections | 0 |
+----------------------+-------+
2 rows in set (0.01 sec)

6 优化innodb_lock_wait_timeout的设置

参数innodb_lock_wait_timeout可以控制InnoDB事务等待行锁的时间，默认值是50ms，可以根据需要动态设置。对于需要快速反馈的交互式OLTP应用，可以将行锁等待超时时间调小，以避免事务长时间挂起；对于后台运行的批处理操作，可以将行锁等待超时时间调大，以避免发生大的回滚操作。

7 优化table_open_cache

每一个SQL执行线程至少都要打开1个表缓存，参数table_open_cache控制所有SQL执行线程可打开表缓存的数量。这个参数的值应根据最大连接数 max_connections 以及每个连接执行关联查询中所涉及表的最大个数（用N表示）来设定：

max_connections*N

[information_schema] 04:36:02>show variables like '%table_open_cache%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| table_open_cache | 2048 |
+----------------------------+--------+
3 rows in set (0.01 sec)

[information_schema] 04:36:19>show status like '%open_tables%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Com_show_open_tables | 0 |
| Open_tables | 2048 |
+----------------------+-------+
2 rows in set (0.00 sec)

(greatdb@10.142.44.36)[information_schema] 04:36:24>

在未执行 flush tables命令的情况下，如果MySQL状态变量 opened_tables的值较大，就说明table_open_cache设置得太小，应适当增大。

增大table_open_cache的值，会增加MySQL对文件描述符的使用量，因此，也要注意评估open-files-limit的设置是否够用。

8 合理设置log file size 控制检查点

当日志文件写满后INNODB会自动切换到另一个logfile，其他的logfile要产生检查点，产生大量IO操作，刷脏页（16KB）

show status like '%innodb_os_log_written%';

select sleep(60);

show status like '%innodb_os_log_written%';
在业务高峰运行期间如果 Innodb_log_waits 值为0或接近0， innodb_log_buffer_size 可能太大,可以减少。
set @old_value := 0;
set @new_value := 0;
select VARIABLE_VALUE into @old_value from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_log_waits';
select SLEEP(时间);
select VARIABLE_VALUE into @new_value from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_log_waits';
select @old_value;
select @new_value;

日志设置大小至少为 30* 两次值差量/1024/1024.

9 数据盘禁用atime

/etc/fstab

atime是Linux/Unix系统下的一个文件属性，每当读取文件时，OS都会将读操作发生的时间回写到磁盘上。对于读写频繁的数据库文件来说，记录文件的访问时间一般没有任何用处，反而会增加磁盘系统的负担，影响I/O的性能。因此，可以通过设置文件系统的mount属性，阻止操作系统写atime信息，以减轻磁盘I/O的负担。在Linux下的具体做法如下。

（1）修改文件系统配置文件/etc/fstab，指定noatime选项：

/dev/vg_orasoft/lv_orasoft_u01 u01 ext4 defaults,noatime 0 0

（2）重新mount文件系统：

#mount -oremount /u01

完成上述操作，以后读/u01下文件就不会再写磁盘了。

7 优化back_log

back_log参数控制MySQL监听TCP端口时设置的积压请求栈大小，5.6.6版本以前的默认值是 50，5.6.6版本以后的默认值是 50+（max_connections / 5），但最大不能超过 900。
如果需要数据库在较短时间内处理大量连接请求，可以考虑适当增大back_log的值。

8 子查询调整连接优化

explain update irdb.r_tra_port a set a.line_branch_type = 52101 where obj_id in
(select ORIG_POINT_ID id from irdb.r_topolink where orig_ne_type in (504,501) union select DEST_POINT_ID id from irdb.r_topolink where orig_ne_type in (504,501));

一条update语句优化。

explain
update irdb.r_tra_port b JOIN
(select ORIG_POINT_ID id from irdb.r_topolink where orig_ne_type in (504,501)
union
select DEST_POINT_ID id from irdb.r_topolink where orig_ne_type in (504,501)
) a
on b.obj_id =a.id set b.line_branch_type = 52101