搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > JAVA日知录 > 数据库优化 - 实例参数优化

数据库优化 - 实例参数优化

JAVA日知录 2020-06-28


一个新安装的数据库使用默认的参数配置,那么数据库的性能很难达到生产要求。要想更好的发挥数据库性能,对数据库参数优化是必不可少的。

本文涵盖目前市面上所有主流数据库的优化配置(Oralce、MySQL、POSTGRES、达梦),掌握本文的参数的配置可以让你数据库飞起来,更能让你成为别人眼中的数据库高手!

数据库优化方法论

数据库优化目标

根据使用数据库角色的不同,数据库优化分为以下几个目标:

  • 业务角度(关键用户):
    减少用户页面响应时间
  • 数据库角度(开发):
    减少数据库SQL响应时间
  • 数据库服务器角度(运维):
    充分使用数据库服务器物理资源
    减少数据库服务器CPU使用率
    减少数据库服务器IO使用率
    减少数据库服务器内存使用率

数据库优化指标


要优化数据库,我们得先给本次优化建立一个优化目标,也就是优化指标。一般来说数据库优化可以建立以下几个指标:

  • SQL平均响应时间变短
    • 优化前:数据库平均响应时间500ms
    • 优化目标:数据库平均响应时间200ms
  • 数据库服务器CPU占用率变少
    • 优化前:数据库高峰期CPU使用率70%
    • 优化目标:数据库高峰期CPU使用率50%
  • 数据库服务器IO使用率变低
    • 优化前:数据库IO WAIT为30%
    • 优化目标:数据库IO WAIT低于10%

数据库优化误区

在进行数据库优化的时候可能会有以下几个误区:

  • 优化之前一定要深入了解数据库内部原理
    其实数据库优化是有“套路”的,照着这些“套路”你也可以很好的完成数据库优化
  • 不断调整数据库参数就可以最终实现优化
    有时候设计不合理怎么调整参数都不行
  • 不断调整操作系统参数就可以最终实现优化
    同上
  • 数据库性能由应用、数据库架构决定,与应用开发关系不大
    恰恰相反,数据库性能与应用开发的关系很大
  • 必须要做读写分离,必须要弄分库分表
    数据量级只有达到一定的比例才有必要做读写分离,分表分库,否则徒增复杂度。一般来说Oracle的单表量级可以达到1亿,MySQL到1000万~2000万

数据库优化流程

完整的数据库优化流程如下:数据库优化 - 实例参数优化

首先我们需要尽可能的了解优化问题,收集问题期间系统信息并做好存档。
根据当前系统问题表现制定优化目标并与客户沟通目标达成一致;
通过一系列工具分析系统问题,制定优化方案,方案评审完成后由各负责人员进行实施。
若达到优化目标则编写优化报告,否则需要重新制定优化方案。

数据库实例优化

数据库实例优化大家只需记住三句口诀:日志不能小、缓存足够大、连接要够用。

在数据库事务提交后需要将事务对数据页的修改刷( fsync)到磁盘上,才能保证数据的持久性。这个刷盘,是一个随机写,性能较低,如果每次事务提交都要刷盘,会极大影响数据库的性能。
所有数据库在架构设计中都会采用如下两个优化手法:

  • 先将事务写到日志文件RedoLog(WAL),将随机写优化成顺序写
  • 加一层缓存结构Buffer,将每次写优化成顺序写

所以日志跟缓存对数据库实例尤其重要。而如果连接不够用,数据库会直接抛出异常,系统无法访问,所以也需要对连接数进行优化。

数据库参数优化

主流数据库架构都有如下几个共同关注点:

  • 数据缓存
  • SQL解析区
  • 排序内存
  • redo log及undo log
  • 锁、LATCH、MUTEX
  • 监听及连接
  • 文件读写性能

接下来我们根据不同的数据库调整参数以使数据库达到最佳性能。

ORACLE

参数分类 参数名 参数值 备注
数据缓存 SGA_TAGET、MEMORY_TARGET 物理内存70-80% 越大越好
数据缓存 DB_CACHE_SIZE 物理内存70-80% 越大越好
SQL解析 SHARED_POOL_SIZE 4-16G 不建议设置过大
监听及连接 PROCESSES、SESSIONS、OPEN_CURSORS 根据业务需求设置 一般为业务预估连接数的120%
其他 SESSION_CACHED_CURSORS 大于200 软软解析


MYSQL(INNODB)

参数分类 参数名 参数值 备注
数据缓存 INNODB_BUFFER_POOL_SIZE 物理内存50-80% 一般来说越大性能越好
日志相关 Innodb_log_buffer_size 16-32M 根据运行情况调整
日志相关 sync_binlog 1、100、0 1安全性最好
监听及连接 max_connections 根据业务情况调整 可以预留一部分值
文件读写性能 innodb_flush_log_at_trx_commit 2 安全和性能的折中考虑
其他 wait_timeout,interactive_timeout 28800 避免应用连接定时中断

POSTGRES

参数分类 参数名 参数值 备注
数据缓存 SHARED_BUFFERS 物理内存10-25%
数据缓存 CACHE_BUFFER_SIZE 物理内存50-60%
日志相关 wal_buffer 8-64M 不建议设置过大过小
监听及连接 max_connections 根据业务情况调整 一般为业务预估连接数的120%
其他 maintenance_work_mem 512M或更大
其他 work_mem 8-16M 原始配置1M过小
其他 checkpoint_segments 32或者更大


达梦数据库

参数分类 参数名 参数值 备注
数据缓存 MEMROY_TARGET、MEMROY_POOL 物理内存90%
数据缓存 BUFFER 物理内存60% 数据缓存
数据缓存 MAX_BUFFER 物理内存70% 最大数据缓存
监听及连接 max_sessions 根据业务需求设置 一般为业务预估连接数的120%


小结

数据库的优化手法有很多,比如有换磁盘阵列升级硬件的,有改写SQL脚本添加索引的,还有数据库参数调整优化性能,甚至还可以业务架构来对数据库进行优化。
本文从数据库参数的角度进行调优,大家根据上面几张表中的配置对数据库参数调整后可以让数据库性能达到数据库最佳性能的80%。



看完本文记得
给我个三连哟:
点赞,转发,评论
咱们下期见!

收藏 等于白嫖点赞 才是真情!

End




干货分享



010: 微信交流群






近期热文top



1、

2、

3、




我就知道你“在看”




版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《数据库优化 - 实例参数优化》的版权归原作者「JAVA日知录」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注JAVA日知录微信公众号

JAVA日知录微信公众号:javadaily

JAVA日知录

手机扫描上方二维码即可关注JAVA日知录微信公众号

JAVA日知录最新文章

精品公众号随机推荐