实例讲解数据库优化

从网上去搜数据库优化基本都是从SQL层次进行优化的,很少有提及到数据库本身的实例优化。就算有也都是基于某个特定数据库的实例优化,本文涵盖目前市面上所有主流数据库的实例优化(Oralce、MySQL、POSTGRES、达梦),按照文章的配置能够将你数据库性能用到80%或以上。

数据库优化方法论

这部分为理论知识,不感兴趣的同学可以直接跳到后面参数配置部分。

数据库优化目标

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

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

指标

1. SQL平均响应时间变短

a. 优化前:数据库平均响应时间500ms

b. 优化目标:数据库平均响应时间200ms

2. 数据库服务器CPU占用率变少

a. 优化前:数据库高峰期CPU使用率70%

b. 优化目标:数据库高峰期CPU使用率50%

3. 数据库服务器IO使用率变低

a. 优化前:数据库IO WAIT为30%

b. 优化目标:数据库IO WAIT低于10%

数据库优化误区

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

1. 优化之前一定要深入了解数据库内部原理

优化是有“套路”的,照着这些“套路”你也可以很好的完成数据库优化

2. 不断调整数据库参数就可以最终实现优化

有时候设计不合理怎么调整参数都不行

3. 不断调整操作系统参数就可以最终实现优化

同上

4. 数据库性能由应用、数据库架构决定,与应用开发关系不大

恰恰相反,应用开发的关系很大

5. 必须要做读写分离,必须要弄分库分表

数据量级只有达到一定的比例才有必要做读写分离,分表分库,否则徒增复杂度。一般来说Oracle的单表量级可以达到1亿,MySQL到1000万~2000万

数据库优化流程

完整的数据库优化流程如下:

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

数据库实例优化

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

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

a. 先将事务写到日志文件RedoLog(WAL),将随机写优化成顺序写

b. 加一层缓存结构Buffer,将每次写优化成顺序写

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

数据库参数优化

主流数据库架构都有如下的共同点:

数据缓存 SQL解析区 排序内存 REDO及UNDO 锁、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%。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏营旗的小记录

基于 SpringCloud 微服务架构的广告系统(第二部分:广告投放、微服务调用与断路器)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

7520
来自专栏码农阿宇

数据同步的一些思考与改进

闲的没事,自己写了个小网站,搭建在自己国外的VPS上,VPS内存极小(512M),而且还要跑点别的(你懂的),内存更紧张巴巴. 改造之前小网站用到了时髦的Red...

7020
来自专栏有三AI

【知识图谱】人工智能技术最重要基础设施之一,知识图谱你该学习的东西

互联网时代,人类在与自然和社会的交互中生产了异常庞大的数据,这些数据中包含了大量描述自然界和人类社会客观规律有用信息。如何将这些信息有效组织起来,进行结构化的存...

7800
来自专栏小詹同学

写文章不会起标题 ?爬取虎嗅5万篇文章告诉你

摘要: 不少时候,一篇文章能否得到广泛的传播,除了文章本身实打实的质量以外,一个好的标题也至关重要。本文爬取了虎嗅网建站至今共 5 万条新闻标题内容,助你找到起...

6310
来自专栏cwl_Java

Java基础-JDBC

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

6530
来自专栏Java3y

《吊打面试官》系列-Redis哨兵、持久化、主从、手撕LRU

Redis在互联网技术存储方面使用如此广泛,几乎所有的后端技术面试官都要在Redis的使用和原理方面对小伙伴们进行360°的刁难。作为一个在互联网公司面一次拿一...

7520
来自专栏Lambda

第2章 SaaS-HRM- 数据库设计

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

11920
来自专栏营旗的小记录

基于 SpringCloud 微服务架构的广告系统(第三部分:索引构建与检索、binlog更新、Kafka投递)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

10230
来自专栏happyJared

Docker 命令

通过 docker rmi [image] (等价于 docker image rm [image])删除镜像,不过首先要确保这个镜像没有被容器引用(可以通过标...

8020
来自专栏好好学java的技术栈

面试必会的 MySQL 中的事务问题!

事务(Transaction) 是并发控制的基本单位。所谓的事务,它是一个操作序列,这些操作要么都 执行,要么都不执行,它是一个不可分割的工作单位。事务是数据库...

8430

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励