一、数据库优化的思路
(一)、什么时候优化
优化有风险，涉足请谨慎！！
优化一定是由业务需求触发的
(二)、数据库的优化谁来参与
硬件工程师、 网络管理员、系统管理员、开发人员、DBA 、领导
(三)、优化框架
(1)硬件、网络、存储
(2)操作系统、文件系统
(3)数据库实例 (链接层、SQL层、存储引擎层)
(4)业务应用(库、表、索引、SQL语句、锁等)
(5)架构选型(高可用、读写分离、分布式、NoSQL)
(四)、优化工具
1、操作系统和硬件层面
cpu : top 、htop 、sar 、vmstat
mem、swap ：top 、 free 、vmstat 、sar
IO(disk、net) ：iotop、iostat
2、数据库实例
show processlist;
show status ;
show engine innodb status \G
3、高级工具
PT 工具
二、优化细节
(一)、硬件优化
1、硬件选型建议
服务器
MySQL
PC Server
OLTP: 在线事务处理系统。IO密集性，高并发。
CPU ： 志强 16核+
MEM : 64G+
IO :
2、磁盘IO:
硬盘： SAS 、 PCI-E SSD 、Flash
RAID卡： RAID 5 、RAID 10
3、网络IO： 网卡。单口单卡。bound网卡绑定(主备、轮询)。网络交换机要做堆叠
PS：补充
OLAP：计算类的业务。CPU密集型。
CPU: I系列 主频高
阿里(RDS、DRDS 、PolarDB\PolarDB box)
腾讯云：
# Oracle : 小机 (IBM PowerPC\HP小机)、PC Server、Oracle 一体机
阿里
RDS、DRDS 、PolarDB\PolarDB box
Oracle
小机 (IBM PowerPC\HP小机)、PC Server、Oracle 一体机
4、网络设备
网络交换机 ： vlan
光纤交换机 ： zone
5、存储设备
RAID 10
注意： 不要过度条带化。可能导致IOPS过高，甚至被打满
IOPS： 一块硬盘，最高每秒的IO次数。是个定值
(二)、操作系统优化
1、 操作系统及文件系统优化建议
系统工具应用
CPU
利用top命令
%Cpu(s): CPU的平均值使用率，按“1”会展开看到每颗cpu占比。
us ：用户进程占用的CPU时间。我们希望看到这个越多越好。
sy ：系统、内核工作时候占用的CPU比例。资源监控、分配、回收、系统调用。我们希望越低越好。
id ：空闲CPU的时间。
wa ：花在等待上的时间。等资源：处理队列、IO。
数据库服务器
如果： wa很高， 大几率 ： 大事务、全表扫描、随机IO过多、锁等待
锁等待也会导致 sys过高。全表扫描也会导致us过高。
wa ：花在等待上的时间。等资源：处理队列、IO
PS：MySQL 服务器 ，CPU 暴涨，排查思路
# 1. 找 mysql 的进程号
cat /data/3306/data/db01.pid
97798
# 2. 通过 进程找问题线程
top -Hp 97798
OS_thread_ID 24331
# 3. 通过OS thread找到数据库内部线程
select thread_id,PROCESSLIST_ID ,THREAD_OS_ID from performance_schema.threads where THREAD_OS_ID=24331;
+-----------+----------------+--------------+
| thread_id | PROCESSLIST_ID | THREAD_OS_ID |
+-----------+----------------+--------------+
| 82 | 57 | 121855 |
+-----------+----------------+--------------+
# 4. 通过 thread_id找到问题SQL语句
select THREAD_ID ,SQL_TEXT from performance_schema.events_statements_history where THREAD_ID=42;
+-----------+------------------------------------------+
| THREAD_ID | SQL_TEXT |
+-----------+------------------------------------------+
| 82 | select * from test.t100w where k2='FGCD' |
| 82 | select * from test.t100w where k2='FGCD' |
| 82 | select * from test.t100w where k2='FGCD' |
| 82 | select * from test.t100w where k2='FGCD' |
2、MEM(内存)、SWAP优化
MEM 主要看 avail Mem 、 buff/cache，
MySQL内存使用不超过服务器内存的90%
SWAP ： MySQL建议不使用
默认规则
centos7 中默认物理内存70%, 时会使用swap。
centos6 中默认物理内存40% ,时会使用swap。
修改方法
方法一：临时修改
echo 0 >/proc/sys/vm/swappiness
方法二：永久修改
vim /etc/sysctl.conf
#添加
vm.swappiness=0
sysctl -p
3、IO优化
查看IO调度策略
cat /sys/block/sda/queue/scheduler
deadline (适合SAS盘)
noop (适合ssd,flash)
cfq(完全公平调度测策略) (不建议)
修改方法
临时修改
echo deadline >/sys/block/sda/queue/scheduler
永久
vim /boot/grub/grub.conf
#写入如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
4、其他建议
磁盘做raid10
不使用 lvm
建议文件系统使用ext4或xfs
磁盘建议使用ssd
三、mysql实例优化
(一)、连接层
1、连接参数
--skip-networking # 跳过网络TCP连接协议(如果数据与应用放在一起，建议开启此参数)
--skip-grant-tables # 跳过授权表
--skip-name-resolve # 跳过域名解析
修改方法
vim /etc/my.cnf
#[mysqld]中添加
skip-name-resolve
2、单线程连接数
查看连接数
select @@max_connections;
连接数量不足报错
too many connections
设置建议
单节点阈值，3000-4000
设置依据
show processlist; # 当前正在并发的。
show status like '%Max_used_connect%'; #历史最大连接数
2、连接线程回收
select @@wait_timeout;
作用： 非交互式链接，连接超时时间。
select @@interactive_timeout;
作用：交互式链接，连接超时时间
连接数设置不生效时还需设置文件句柄
vim /etc/security/limits.conf
#添加
* soft nofile 65536
* hard nofile 65536
3、会话级别的参数(每个连接都有关)
thread_cache_size=100 #连接缓存大小 (单位：个)
查看
select @@thread_cache_size;
功能
用内存换区CPU时间
设置依据
show status like 'threads_%';
Threads_cached | 8 | # 剩余的个数(过少则添加)
Threads_connected | 2 | # 已经产生的线程
Threads_created | 4783 | # 全新创建的线程个数(过多则添加)
Threads_running | 1 | # 正在运行的个数
内存的剩余量过少则减少
CPU的 SYS过高则减少
设置方法
临时设置
set global thread_cache_size=100;
永久设置
vim /etc/my.cnf
#[mysqld]中添加
thread_cache_size=100
innodb_thread_concurrency #线程并发
查看
select @@innodb_thread_concurrency;
功能
控制线程并发
设置依据
1.
在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，
并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，
例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，
性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，
建议设置innodb_thread_concurrency参数为80，以避免影响性能。
如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多(比如20个虚拟CPU)，
建议通过设置innodb_thread_concurrency 参数为这个值(也可能更低，这取决于性能体现)，
如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。
但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，
你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。
2. show processlist; # 查看当前的并发连接数据队列。
建议： 先设置较小值。如：8 ，不断观察队列
3. 观察 TOP中，每颗CPU的负载情况
设置方法
临时设置
set global innodb_thread_concurrency=8;
永久设置
vim /etc/my.cnf
#[mysqld]中添加
innodb_thread_concurrency=8
key_buffer_size #索引缓冲区
查看
select @@key_buffer_size;
功能
1. MyISAM索引缓冲。
2. 内存临时表缓冲。临时表组成(1、内存 2、缓冲区)
PS：在备份数据库时会大量产生临时表
设置依据
show status like "created_tmp%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 10 | #磁盘所占用的临时表大小
| Created_tmp_files | 6 | #内存文件所占用的临时表大小
| Created_tmp_tables | 100 | #内存表所占用的临时表大小
+-------------------------+-------+
磁盘所占用空间不超过10%(disk / (file+tables))
业务上减少结果集大小。尽量控制查询条件，精细化
设置方法
临时
set global key_buffer_size=7000000;
永久
vim /etc/my.cnf
#[mysqld]中添加
key_buffer_size=7000000
sort_buffer_size #排序缓冲区
join_buffer_size #连接表缓冲区
read_rnd_buffer_size #MRR缓冲区
优化目标： 越小越好。
优化方式： 1. 减少排序结果集 2. 优化索引
(二)、SQL层
1、query_cache_size #查询缓存
查看
select @@query_cache_size;
功能
SQL层查询缓存
设置方法
临时
set global query_cache_size=1572864;
永久
vim /etc/my.cnf
#[mysqld]中添加
query_cache_size=1G
(三)、存储引擎层
1、InnoDB参数
# innodb_adaptive_hash_index (AHI)
# innodb_buffer_pool_instances=4-8个
# innodb_buffer_pool_size =50%-80%，建议不要超过75%
# innodb_file_per_table # 独立表空间
# innodb_data_file_path # 共享表空间，建议2-3个 512M-4G
# innodb_doublewrite # 双写。8.0.20之前在，ibdataN ,2M空间。checkpoint时，分两次，每次1M写入DWB。然后刷脏页到ibd
# innodb_flush_log_at_trx_commit # 双一之一：
1. 每次事务提交，立即触发刷写，立即fsync()将redo落盘。
0. 每秒事务提交，立即触发刷写，立即fsync()将redo落盘。
2. 每次事务提交，立即触发刷写OS cache，每秒fsync()将redo落盘。
# innodb_flush_method
fsync : redo和数据刷盘都经历OS cache，再到磁盘
O_Direct： 数据直接刷盘，日志经历OS cache ，再到磁盘
2、看磁盘类型设置
innodb_io_capacity
innodb_io_capacity_max
3、锁等待时间
innodb_lock_wait_timeout=10
4、redo有关 ，TPS有关
innodb_log_buffer_size=文件倍数
innodb_log_file_size=512M-4G
innodb_log_files_in_group=2-4组
5、checkpoint 相关机制
1. sharp ckpt (必然发生的)
数据库关机触发
2. fuzzy ckpt (可能发生的)
master thread checkpoint
每1秒有可能
每10秒有可能
flush_lru_list checkpoint
innodb_lru_scan_depth=1024
async/sync flush checkpoint
2个redo ,1G ==> 2G
75% 1.5G ---》 可能发生的
90% 1.8G ---》 必然
dirty page too much checkpoint
innodb_max_dirty_pages_pct=75%
6、innodb_open_files #InnoDB能够打开文件句柄。
innodb_temp_data_file_path
7、innodb_temp_data_file_path #临时表空间设置
8、undo 的设置：3-4个,1-4G左右
innodb_max_undo_log_size | 1073741824 |
innodb_undo_directory | ./ |
innodb_undo_log_truncate | OFF |
innodb_undo_logs | 128 |
innodb_undo_tablespaces | 0 |
优化后配置文件详解
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/3312/data
socket=/data/3312/mysql.sock
#server ID 主从环境中各节点不能重复
server_id=7
port=3312
#错误日志路径
log_error=/data/3312/error/mysql_3312.log
#relaylog 日志
relay_log=/data/3312/relaylog/mysql_3312_relay_bin
#二进制日志文件路径
log_bin=/data/3312/binlog/mysql_3312_bin
#RBR模式
binlog_format=row
#二进制日志缓存大小
binlog_cache_size=2M
#最大二进制日志缓存大小
max_binlog_cache_size=8M
#二进制日志大小
max_binlog_size=512M
#二进制日志清空规则:至少大于一轮全备周期
expire_logs_days=15
#关闭自动commit机制
autocommit=0
#--启用gtid类型，否则就是普通的复制架构
gtid-mode=on
#--强制GTID的一致性
enforce-gtid-consistency=true
#--强制从库刷新binlog日志
log-slave-updates=1
#开启逻辑时钟(记录事务的执行顺序,实现从库多线程并行回放)
slave-parallel-type=LOGICAL_CLOCK
#开启多线程,CPU个数的50%
slave-parallel-workers=8
# 以表的方式存储,提高读写性
master_info_repository=TABLE
#以表的方式存储,提高读写性
relay_log_info_repository=TABLE
#从库意外宕机elay_log损坏后,初始化IO线程重新向主库请求,
relay_log_recovery=on
#DUMP_T组提交:"时间
#binlog_group_commit_sync_delay=0.1
# DUMP_T组提交: 事务数
#binlog_group_commit_sync_no_delay_count=100
# 过滤复制:主从环境中从库记录主库的全部二进制,但是sql_T指定库进行relaylog回放
#replicate_do_db=test
#开启慢查询开关
slow_query_log=1
#慢日志文件位置及名字
slow_query_log_file=/data/3312/slow/slow.log
#设定慢查询时间
long_query_time=0.1
#记录不走索引的语句(慢日志)
log_queries_not_using_indexes
#双1事务commit时立即将redo buff(已/位提交事务打标签) 写入到磁盘(redo log) ,写入到磁盘commit事务才算成功
innodb_flush_log_at_trx_commit=1
#双1 只要有事务提交立即将binlog写入到磁盘
sync_binlog=1
#启用O_DIRECT模式
innodb_flush_method=O_DIRECT
#信任目录
secure-file-priv=/mnt
#最大连接
max_connections=1024
#当max_connections满时,允许多少连接等待.太大无用不如设置max_connections值
back_log=128
#无操作断开时间
wait_timeout=60
#有操作断开时间
interactive_timeout=7200
#临时表的缓冲大小(临时表个占总个数5%-10%)
key_buffer_size=16M
#查询select结果的缓冲大小
query_cache_size=64M
#开启query_cache_size
query_cache_type=1
#指定单个查询能够使用的缓冲区大小
query_cache_limit=50M
#最大错误连接数,超过服务器将禁止其登录,直到服务器flush,或重启
max_connect_errors=1000
#排序缓冲,每个会话独享(建立索引效果比此参数更好)
sort_buffer_size=2M
#服务端允许接受的数据包大小
max_allowed_packet=32M
# 联合查询操作所能使用的缓冲区大小,每个会话独享(建立索引效果比此参数更好)
join_buffer_size=2M
#缓存多少个线程(内存换CPU)
thread_cache_size=200
#用于缓冲数据和索引(建议物理内存的50-70,不得超过70%)
innodb_buffer_pool_size=1024M
#redo_log 的buffer大小
innodb_log_buffer_size=32M
#redo_log 大小
innodb_log_file_size=128M
#redo_log 文件个数.文件个数越多,WAL(持久化)越慢消耗IO越低,但是意外down机数据恢复有风险
innodb_log_files_in_group=3
#私有的buffer内存区域(按照会话)
read_buffer_size=2M
#私有的随机读内存区域(按照会话)
read_rnd_buffer_size=2M
#插入数据缓冲大小
bulk_insert_buffer_size=8M
#监控死锁(错误日志中)
innodb_print_all_deadlocks=1
四、业务层面优化建议
(一)、用户权限优化建议
1. 用户要和业务有关
例如：zbx_user zbx_admin ..
2. 密码复杂度
建议3种复杂度，12位以上。
定期进行更改。
3.用户密码专人管理，超级管理员多人持有部分。
4.开发或业务用户，不直接登陆数据库。经过审计平台、堡垒机等机制。
5. 权限最小化。
(二)、SQL规范
1. schema 设计
库： 大小写、业务相关、字符集、校对规则。
表： 一大堆规范、范式。
2. DROP操作避免使用
3. ON-DDL
4. SQL 优化
5. 合理的优化器算法
6. 大事务：批量更新、批量删除、批量的插入，夜里去做，拆分小事务
(三)、索引引用
聚簇索引数字自增
降低索引树高度
联合索引覆盖长度
减少回表。
创建索引规范。
不走索引情况。
慢日志分析。
(四)、锁方面优化
1、锁争用导致的环境
1 通过top详细排查,发现mysqld进程占比达到了700-800%
2 其中有量的CPU是被用作的SYS和WAIT,us处于正常
3 怀疑是MySQL 锁 或者SQL语句出了问题
4 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句
2、排查思路
(1) pt-query-diagest 查看慢日志
(2)查看有没有所等待
show status like 'innodb_row_lock%';
(3)查看那个事务在等待(被阻塞了)
(4)查看锁源事务信息(谁锁的我)
(5)找到锁源的thread_id
(6)找到锁源的SQL语句
3、找到语句后处理
(1)
开发人员描述,此语句是事务挂起导致
我们提出建议是临时kill 会话,最终解决问题
(2)
开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待
临时解决方案,将阻塞事务的会话kill掉.
最终解决方案,修改代码中的业务逻辑
4、锁监控设计到的命令
查看是否有所等待
show status like 'innodb_rows_lock%'
查看被锁的事务
select * from information_schema.innodb_trx;
查看锁情况的详细信息
select * from sys.innodb_lock_waits;
查看当前所有的工作线程
select * from performance_schema.threads;
查看线程的历史命令
select * from performance_schema.events_statements_history;
(五)、架构优化
主从 ：5.7 以上GTID 。
高可用 : MHA 、PXC 、ORCH 、InnoDB CLuster(MGR)
读写分离 : ProxySQL
分布式架构 : Mycat 、 DBLE
扫一扫
1968
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
