DBA-70-day15


第十二章节 MySQL 工具应用及全面优化

一、 PT(percona-toolkits)工具的应用:

1. pt工具安装

[root@master ~]# yum install -y  percona-toolkit-3.1.0-2.el7.x86_64.rpm

2. 常用工具使用介绍

2.1 pt-archiver 归档表

场景:

面试题: 亿级的大表,delete批量删除100w左右数据。

面试题: 定期按照时间范围,进行归档表。

# 重要参数

--limit 100        每次取100行数据用pt-archive处理   

--txn-size  100    设置100行为一个事务提交一次,   

--where 'id<3000'  设置操作条件   

--progress 5000    每处理5000行输出一次处理信息   

--statistics        输出执行过程及最后的操作统计。(只要不加上--quiet,默认情况下pt- archive都会输出执行过程的)   

--charset=UTF8      指定字符集为UTF8—这个最后加上不然可能出现乱码。   

--bulk-delete      批量删除source上的旧数据(例如每次1000行的批量删除操作)

注意:  需要归档表中至少有一个索引,做好是where条件列有索引

使用案例:

1.归档到数据库

db01 [test]>create table test1 like t100w;

pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --dest h=10.0.0.51,D=test,t=test1,u=oldguo,p=123 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 1000 --statistics

2.只清理数据

pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --where 'id<10000' --purge --limit=1 --no-check-charset

3.只把数据导出到外部文件,但是不删除源表里的数据

pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.dat"

2.2 pt-osc

场景: 

  修改表结构、索引创建删除

  不能加快速度,但能减少业务影响(锁)。


面试题 :    

pt-osc工作流程:

1、检查更改表是否有主键或唯一索引,是否有触发器

2、检查修改表的表结构,创建一个临时表,在新表上执行ALTER TABLE语句

create table  bak like t1;

alter table bak add telnum char(11) not null;

3、在源表上创建三个触发器分别对于INSERT UPDATE DELETE操作

create trigger

a

b

c

4、从源表拷贝数据到临时表,在拷贝过程中,对源表的更新操作会写入到新建表中

insert into bak  select * from t1 

5、将临时表和源表rename(需要元数据修改锁,需要短时间锁表)

6、删除源表和触发器,完成表结构的修改。

pt-osc工具限制

1、源表必须有主键或唯一索引,如果没有工具将停止工作

2、如果线上的复制环境过滤器操作过于复杂,工具将无法工作

3、如果开启复制延迟检查,但主从延迟时,工具将暂停数据拷贝工作

4、如果开启主服务器负载检查,但主服务器负载较高时,工具将暂停操作

5、当表使用外键时,如果未使用--alter-foreign-keys-method参数,工具将无法执行

6、只支持Innodb存储引擎表,且要求服务器上有该表1倍以上的空闲空间。

pt-osc之alter语句限制

1、不需要包含alter table关键字,可以包含多个修改操作,使用逗号分开,如"drop clolumn c1, add column c2 int"

2、不支持rename语句来对表进行重命名操作

3、不支持对索引进行重命名操作

4、如果删除外键,需要对外键名加下划线,如删除外键fk_uid, 修改语句为"DROP FOREIGN KEY _fk_uid"

pt-osc之命令模板

## --execute表示执行

## --dry-run表示只进行模拟测试

## 表名只能使用参数t来设置,没有长参数

pt-online-schema-change \

--host="127.0.0.1" \

--port=3358 \

--user="root" \

--password="root@root" \

--charset="utf8" \

--max-lag=10 \

--check-salve-lag='xxx.xxx.xxx.xxx' \

--recursion-method="hosts" \

--check-interval=2 \

--database="testdb1" \

  t="tb001" \

--alter="add column c4 int" \

--execute

例子:

pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51 --alter "add column state int not null default 1" D=test,t=t100w --print --execute

pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51 --alter "add index idx(num)" D=test,t=t100w --print --execute

2.3 pt-table-checksum

场景:  校验主从数据一致性

2.3.1 创建数据库

Create database pt CHARACTER SET utf8;

创建用户checksum并授权

GRANT ALL ON *.* TO 'checksum'@'10.0.0.%' IDENTIFIED BY 'checksum';

flush privileges;

2.3.2 参数:

--[no]check-replication-filters:是否检查复制的过滤器,默认是yes,建议启用不检查模式。

--databases | -d:指定需要被检查的数据库,多个库之间可以用逗号分隔。

--[no]check-binlog-format:是否检查binlog文件的格式,默认值yes。建议开启不检查。因为在默认的row格式下会出错。

--replicate`:把checksum的信息写入到指定表中。

--replicate-check-only:只显示不同步信息

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test --tables=t1 h=10.0.0.51,u=checksum,p=checksum,P=3306

#!/bin/bash

date >> /root/db/checksum.log

pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases test --tables t1 -u'checksum' -p'checksum' -h'10.0.0.51' >> /tmp/checksum.log

date >> /root/db/checksum.log

2.4 pt-table-sync

主要参数介绍

--replicate :指定通过pt-table-checksum得到的表.

--databases : 指定执行同步的数据库。

--tables :指定执行同步的表,多个用逗号隔开。

--sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。

h= :服务器地址,命令里有2个ip,第一次出现的是Master的地址,第2次是Slave的地址。

u= :帐号。

p= :密码。

--print :打印,但不执行命令。

--execute :执行命令。

pt-table-sync --replicate=pt.checksums --databases test  --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3306 h=10.0.0.52,u=checksum,p=checksum,P=3306 --print

pt-table-sync --replicate=pt.checksums --databases test  --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3306 h=10.0.0.52,u=checksum,p=checksum,P=3306 --execute

2.5 pt-duplicate-key-checker

作用:检查数据库重复索引

pt-duplicate-key-checker --database=test  h='10.0.0.51' --user=oldguo --password=123

2.6 pt-kill 语句

场景: 无法正常kill的连接。

常用参数说明

--daemonize  放在后台以守护进程的形式运行;

--interval  多久运行一次,单位可以是s,m,h,d等默认是s –不加这个默认是5秒

--victims 默认是oldest,只杀最古老的查询。这是防止被查杀是不是真的长时间运行的查询,他们只是长期等待 这种种匹配按时间查询,杀死一个时间最高值。

--all 杀掉所有满足的线程

--kill-query      只杀掉连接执行的语句,但是线程不会被终止

--print              打印满足条件的语句

--busy-time 批次查询已运行的时间超过这个时间的线程;

--idle-time 杀掉sleep 空闲了多少时间的连接线程,必须在--match-command sleep时才有效—也就是匹配使用 -- –match-command 匹配相关的语句。

----ignore-command 忽略相关的匹配。 这两个搭配使用一定是ignore-commandd在前 match-command在后,

--match-db cdelzone 匹配哪个库

command有:Query、Sleep、Binlog Dump、Connect、Delayed insert、Execute、Fetch、Init DB、Kill、Prepare、Processlist、Quit、Reset stmt、Table Dump

例子:

### 杀掉空闲链接sleep 5秒的 SQL 并把日志放到/home/pt-kill.log文件中

/usr/bin/pt-kill  --user=用户名 --password=密码 --match-command Sleep  --idle-time 5 --victim all --interval 5 --kill --daemonize -S /tmp/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

### 查询SELECT 超过1分钟

/usr/bin/pt-kill --user=用户名 --password=密码 --busy-time 60  --match-info "SELECT|select" --victim all --interval 5 --kill --daemonize -S  -S /tmp/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

### Kill掉 select IFNULl.*语句开头的SQL

pt-kill --user=用户名 --password=密码 --victims all --busy-time=0 --match-info="select IFNULl.*" --interval 1 -S /tmp/mysqld.sock --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

### kill掉state Locked

/usr/bin/pt-kill --user=用户名 --password=密码  --victims all --match-state='Locked' --victim all --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

### kill掉 a库,web为10.0.0.11的链接

pt-kill  --user=用户名 --password=密码 --victims all  --match-db='a' --match-host='10.0.0.11' --kill --daemonize --interval 10  -S /tmp/mysqld.sock  --pid=/tmp/ptkill.pid --print-log=/tmp/pt-kill.log &

### 指定哪个用户kill

pt-kill  --user=用户名 --password=密码 --victims all --match-user='root' --kill  --daemonize --interval 10 -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &

### kill掉 command query | Execute

pt-kill --user=用户名 --password=密码 --victims all  --match-command= "query|Execute" --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &

7. 显示主从结构:pt-slave-find

[root@db01 tmp]#  pt-slave-find -h10.0.0.51  -P3306 -uchecksum -pchecksum

10.0.0.51

Version        5.7.28-log

Server ID      51

Uptime          27:57 (started 2020-05-15T13:24:15)

Replication    Is not a slave, has 1 slaves connected, is not read_only

Filters       

Binary logging  ROW

Slave status   

Slave mode      STRICT

Auto-increment  increment 1, offset 1

InnoDB version  5.7.28

+- 10.0.0.52

  Version        5.7.28-log

  Server ID      52

  Uptime          28:18 (started 2020-05-15T13:23:54)

  Replication    Is a slave, has 0 slaves connected, is not read_only

  Filters       

  Binary logging  ROW

  Slave status    0 seconds behind, running, no errors

  Slave mode      STRICT

  Auto-increment  increment 1, offset 1

  InnoDB version  5.7.28

[root@db01 tmp]#

8. 监控主从延时

# pt-heartbeat

主库:

pt-heartbeat --user=oldguo --ask-pass --host=10.0.0.51 --port=3306 --create-table -D test  --interval=1 --update --replace --daemonize

从库:

pt-heartbeat --user=oldguo --ask-pass --host=10.0.0.52 --port=3306  -D test  --table=heartbeat --monitor

9. # pt-show-grants

作用:  用户和权限信息迁移。

pt-show-grants -h10.0.0.51  -P3306  -uchecksum -pchecksum

-- Grants dumped by pt-show-grants

-- Dumped from server 10.0.0.51 via TCP/IP, MySQL 5.7.28-log at 2020-05-15 17:11:06

-- Grants for 'checksum'@'10.0.0.%'

CREATE USER IF NOT EXISTS 'checksum'@'10.0.0.%';

ALTER USER 'checksum'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*E5E390AF1BDF241B51D9C0DBBEA262CC9407A2DF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

GRANT ALL PRIVILEGES ON *.* TO 'checksum'@'10.0.0.%';

-- Grants for 'mysql.session'@'localhost'

CREATE USER IF NOT EXISTS 'mysql.session'@'localhost';

ALTER USER 'mysql.session'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;

GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';

GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';

GRANT SUPER ON *.* TO 'mysql.session'@'localhost';

-- Grants for 'mysql.sys'@'localhost'

CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';

ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;

GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';

GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';

GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';

-- Grants for 'repl'@'10.0.0.%'

CREATE USER IF NOT EXISTS 'repl'@'10.0.0.%';

ALTER USER 'repl'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

-- Grants for 'root'@'10.0.0.%'

CREATE USER IF NOT EXISTS 'root'@'10.0.0.%';

ALTER USER 'root'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%';

-- Grants for 'root'@'localhost'

CREATE USER IF NOT EXISTS 'root'@'localhost';

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

二、 MySQL全面优化

1.优化哲学

1.1 为什么优化?

为了获得成就感?

为了证实比系统设计者更懂数据库?

为了从优化成果来证实优化者更有价值?

但通常事实证实的结果往往会和您期待相反!

优化有风险,涉足需谨慎!

1.2 优化风险

优化不总是对一个单纯的环境进行!还很可能是一个复杂的已投产的系统。

优化手段本来就有很大的风险,只不过你没能力意识到和预见到!

任何的技术可以解决一个问题,但必然存在带来一个问题的风险!

对于优化来说解决问题而带来的问题控制在可接受的范围内才是有成果。

保持现状或出现更差的情况都是失败!

稳定性和业务可持续性通常比性能更重要!

优化不可避免涉及到变更,变更就有风险!

优化使性能变好,维持和变差是等概率事件!

优化不能只是数据库管理员担当风险,但会所有的人分享优化成果!

所以优化工作是由业务需要驱使的!!!

1.3 谁参与优化

数据库管理员

业务部门代表

应用程序架构师

应用程序设计人员

应用程序开发人员

硬件及系统管理员

存储管理员

1.4 优化方向

安全优化(业务持续性)

性能优化(业务高效性)

1.5 优化的思路

基础设施: 存储、主机和操作系统:

    主机架构稳定性

    I/O规划及配置

    Swap

    OS内核参数

    网络问题

数据库实例优化:(数据库设计、参数)

    内存

    数据库结构(物理&逻辑)

    实例配置

业务层面:(Index,lock,session)

schema 设计

    应用程序稳定性和性能

    SQL语句性能

    串行访问资源

    性能欠佳会话管理

架构优化:

高可用

读写分离

分布式

NoSQL

面试题: 你对数据库系统都做过哪些优化

2. 优化工具的使用

2.1 系统层面的

OS层面你关注哪些指标?

CPU \ MEM  \IO

2.1.1 CPU : 进程和线程CPU利用

top

top 都要看什么?

%Cpu(s):  0.1 us,  0.1 sy,  0.0 ni, 99.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st

us : 用户程序,工作期间占用的cpu的占比实时状态。

CPU干正事的时间。

sy : 系统程序? 工作期间占用的cpu的占比实时状态。

内核态的程序运行时占用的cpu半分比。

资源的监控,分配,回收,维护等工作。

=================================

如果MySQL数据库服务器,SYS高可能是什么问题导致的?

1. 高并发,会话过多。

2. 锁。

3. 大表全表扫描。

4. 参数配置。

=================================

id : 空闲

wa : cpu花在等待上的时间百分比

等待谁?IO

================

高并发,大事务。

raid 存储规划问题

缓存过小

全表扫描较多

随机IO过多

================

%CPU 某程序对于CPU使用的总占比。

ps  -ef |grep mysqld ---> 1446

[root@db01 ~]# top -Hp 1446  ----> 12222(THREAD_OS_ID)----> PS.threads ---> thread_id(SQL) ---> PS.events_statements_history

select  SQL_TEXT  from events_statements_history where  THREAD_ID=28

2.1.2 MEM

KiB Mem :  8155004 total,  7631840 free,  354772 used,  168392 buff/cache    7573204 avail Mem

KiB Swap:  2097148 total,  2097148 free,        0 used.   


2.1.3 IO

[root@db01 ~]# iostat -dk 1

[root@db01 ~]# dd if=/dev/zero of=/mnt/bigfile bs=1M count=2048

2.2 数据库优化工具

    show status 

    show variables

    show index 

    show processlist

    show slave status

    show engine innodb status

    desc /explain

    slowlog

    扩展类深度优化:

    pt系列

    mysqlslap

    sysbench

    information_schema

    performance_schema

    sys

3. 优化思路分解

3.1 硬件优化建议

3.1.1 主机

# 服务器品牌

真实的硬件(X86 PC Server): DELL R系列 ,华为,浪潮,HP,联想(IBM)

云产品:ECS、数据库RDS、DRDS、polarDB

IBM 小型机 P6  570  595  P7 720  750 780    P8

# CPU根据数据库类型

OLTP  : 在线事务处理 IO密集型,线上系统,OLTP主要是IO密集型的业务,高并发 ,E系列(至强),主频相对低,核心数量多

OLAP  :  CPU密集型:数据分析数据处理,OLAP,cpu密集型的,需要CPU高计算能力(i系列,IBM power系列 I 系列的,主频很高,核心少 )

# 内存

建议2-4倍cpu核心数量 (ECC)

# 磁盘选择

SATA-III  , SAS  ,  Fc  , SSD(sata), pci-e  ssd , Flash

db01 [(none)]>show variables like '%innodb_io%'

    -> ;

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| innodb_io_capacity    | 200  |

| innodb_io_capacity_max | 2000  |

+------------------------+-------+

2 rows in set (0.00 sec)

db01 [(none)]>

innodb_io_capacity  磁盘类型

200-500      :    普通 SATA III

500-2000      :    SAS - SAS*6 raid

2000- 5000    :    SSD 

5000-8000    :    Flash

# 主机 RAID卡的 BBU(Battery Backup Unit)关闭

案例 1 : 有规律的一段时间,会产生性能低谷

场景:

每隔 28-90天 一段时间的性能低谷( 1s - 3s+)

在MySQL日常运行期间,业务上反映,每隔一段时间会出现性能下降的情况。

经过监控排查,业务的并发QPS平均值在1000-1200左右。并不是太高。

平时的slowlog有部分慢查询,但放到MySQL端直接执行都比较快。

原因1:  有规律

raid卡充放电(28-90天),WB-WT转换

Raid卡 Consistency Check 一致性检查 (168H) , Patrol Read(168H) 巡读

建议联系: 系统管理员 关闭相应功能

了解一下: LSI卡的管理命令 , MegaCli命令

原因2:

统计信息过旧,导致索引失效

# 存储

根据存储数据种类的不同,选择不同的存储设备

配置合理的RAID级别(raid5、raid10、热备盘) 

r0 :条带化 ,性能高

r1 :镜像,安全

r5 :校验+条带化,安全较高+性能较高(读),写性能较低 (适合于读多写少)

r10:安全+性能都很高,最少四块盘,浪费一半的空间(高IO要求)

避免使用LVM技术。

断电数据损坏,修复复杂。

过度条带化会增加IOPS压力

# 网络

1、硬件买好的(单卡单口)

2、网卡绑定(bonding),交换机堆叠

以上问题,提前规划好。

3.2 操作系统优化

# Swap调整

swap 被使用的策略 ,通过以下参数控制

cat /proc/sys/vm/swappiness

C7 : 30    使用量 70%

C6 : 60    使用量 40%

echo 0 >/proc/sys/vm/swappiness  的内容改成0(临时)

vim /etc/sysctl.conf

#添加:

vm.swappiness=0(永久)

sysctl -p

这个参数决定了Linux是倾向于使用swap,还是倾向于释放文件系统cache。

在内存紧张的情况下,数值越低越倾向于释放文件系统cache。

当然,这个参数只能减少使用swap的概率,并不能避免Linux使用swap。

# IO调度策略

centos 7 默认是deadline ,比较适合于机械类的磁盘.

cat  /sys/block/sda/queue/scheduler

说明: 如果是SSD或者flash ,我们建议电梯(noop)的调度方式.

#临时修改为deadline(centos6)

echo deadline >/sys/block/sda/queue/scheduler

vi /boot/grub/grub.conf

更改到如下内容:

kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

IO :

    raid

    no lvm

    ext4或xfs

    ssd

    IO调度策略

提前规划好以上所有问题,减轻MySQL优化的难度。

3. MySQL参数优化测试

虚拟机vm12.5,OS centos 6.9(系统已优化),cpu*4(I5 4440 3.1GHZ),MEM*4GB ,HardDisk:SSD

模拟数据库数据

drop database if exists oldboy;

create database oldboy charset utf8mb4 collate utf8mb4_bin;

use oldboy;

create table t_100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);

delimiter //

create  procedure rand_data(in num int)

begin

declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

declare str2 char(2);

declare str4 char(4);

declare i int default 0;

while i<num do

set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));

set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));

set i=i+1;

insert into t_100w values (i,floor(rand()*num),str2,str4,now());

end while;

end;

//

delimiter ;

插入100w条数据:

call rand_data(10000000);

commit;

mysqlslap --defaults-file=/etc/my.cnf \

--concurrency=100 --iterations=1 --create-schema='test' \

--query="select * from test.t100w where k2='FGCD'" engine=innodb \

--number-of-queries=2000 -uoldguo -p123 -verbose

4. 优化细节:

4.1 Max_connections *****

(1)简介

Mysql的最大连接数,如果服务器的并发请求量比较大,可以调高这个值,当然这是要建立在机器能够支撑的情况下,因为如果连接数越来越多,mysql会为每个连接提供缓冲区,就会开销的越多的内存,所以需要适当的调整该值,不能随便去提高设值。

(2)判断依据

show variables like 'max_connections';

    +-----------------+-------+

    | Variable_name  | Value |

    +-----------------+-------+

    | max_connections | 151  |

    +-----------------+-------+

show status like 'Max_used_connections';

    +----------------------+-------+

    | Variable_name        | Value |

    +----------------------+-------+

    | Max_used_connections | 101  |

    +----------------------+-------+

(3)修改方式举例

vim /etc/my.cnf

Max_connections=1024

补充:

    1.开启数据库时,我们可以临时设置一个比较大的测试值

    2.观察show status like 'Max_used_connections';变化

    3.如果max_used_connections跟max_connections相同,

    那么就是max_connections设置过低或者超过服务器的负载上限了,

    低于10%则设置过大.

案例二:

连接数设置不生效的问题,214问题。

*  soft  nofile  65536

*  hard  nofile  65536

5.2 back_log ***

(1)简介

mysql能暂存的连接数量,当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用,如果mysql的连接数据达到max_connections时候,新来的请求将会被存在堆栈中,等待某一连接释放资源,该推栈的数量及back_log,如果等待连接的数量超过back_log,将不被授予连接资源。

back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中,只有如果期望在一个短时间内有很多连接的时候需要增加它

(2)判断依据

show full processlist

发现大量的待连接进程时,就需要加大back_log或者加大max_connections的值

(3)修改方式举例

vim /etc/my.cnf

back_log=1024

5.3 wait_timeout和interactive_timeout ****

(1)简介

wait_timeout:指的是mysql在关闭一个非交互的连接之前所要等待的秒数

interactive_timeout:指的是mysql在关闭一个交互的连接之前所需要等待的秒数,比如我们在终端上进行mysql管理,使用的即使交互的连接,这时候,如果没有操作的时间超过了interactive_time设置的时间就会自动的断开,默认的是28800,可调优为7200。

wait_timeout:如果设置太小,那么连接关闭的就很快,从而使一些持久的连接不起作用

(2)设置建议

如果设置太大,容易造成连接打开时间过长,在show processlist时候,能看到很多的连接 ,一般希望wait_timeout尽可能低

(3)修改方式举例

wait_timeout=120

interactive_timeout=7200

长连接的应用,为了不去反复的回收和分配资源,降低额外的开销。

一般我们会将wait_timeout设定比较小,interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接,那么这个值可以不需要调整。

另外还可以使用类外的参数弥补。

案例三:MySQL 连接长时间(7200和1200秒)无法释放

场景: MySQL 5.7  , DELL730 E5-2650  96G内存  1主2从

Keepalive + LVS + 1主 2从

处理方法:

ipvsadmin -l -timeout 

Timeout (tcp  tcpfin  udp ):  90 120 300

net.ipv4.tcp_keepalive_time = 60 

5.4 key_buffer_size *****

(1)简介

key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度

《1》此参数与myisam表的索引有关

《2》临时表的创建有关(多表链接、子查询中、union)

    在有以上查询语句出现的时候,需要创建临时表,用完之后会被丢弃

    临时表有两种创建方式:

                        内存中------->key_buffer_size

                        磁盘上------->ibdata1(5.6)

                                      ibtmp1 (5.7)


注:key_buffer_size只对myisam表起作用,即使不使用myisam表,但是内部的临时磁盘表是myisam表,也要使用该值。

可以使用检查状态值created_tmp_disk_tables得知:

mysql> show status like "created_tmp%";

+-------------------------+-------+

| Variable_name          | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 0    |

| Created_tmp_files      | 6    |

| Created_tmp_tables      | 1    |

+-------------------------+-------+

3 rows in set (0.00 sec)

mysql>

通常地,我们习惯以

Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables)

Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)

或者已各自的一个时段内的差额计算,来判断基于内存的临时表利用率。所以,我们会比较关注 Created_tmp_disk_tables 是否过多,从而认定当前服务器运行状况的优劣。

Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)

控制在5%-10%以内

看以下例子:

在调用mysqldump备份数据时,大概执行步骤如下:

180322 17:39:33      7 Connect    root@localhost on

7 Query      /*!40100 SET @@SQL_MODE='' */

7 Init DB    guo

7 Query      SHOW TABLES LIKE 'guo'

7 Query      LOCK TABLES `guo` READ /*!32311 LOCAL */

7 Query      SET OPTION SQL_QUOTE_SHOW_CREATE=1

7 Query      show create table `guo`

7 Query      show fields from `guo`

7 Query      show table status like 'guo'

7 Query      SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`

7 Query      UNLOCK TABLES

7 Quit

其中,有一步是:show fields from `guo`。从slow query记录的执行计划中,可以知道它也产生了 Tmp_table_on_disk。

所以说,以上公式并不能真正反映到mysql里临时表的利用率,有些情况下产生的 Tmp_table_on_disk 我们完全不用担心,因此没必要过分关注 Created_tmp_disk_tables,但如果它的值大的离谱的话,那就好好查一下,你的服务器到底都在执行什么查询了。

(3)配置方法

key_buffer_size=64M

5.5 query_cache_size ***

(1)简介:

查询缓存简称QC,主要缓存SQL语句hash值+执行结果。

10条语句,经常做查询。

案例四 : 开QC ,导致性能降低。 QPS ,TPS降低。

没开起的时候。QPS 2000 TPS 500

开了之后直接降低到 800,200

为什么呢?

分区表。Query Cache 不支持。

5.6 sort_buffer_size ***

(1)简介:

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速

ORDER BY

GROUP BY

distinct

union

(2)配置依据

Sort_Buffer_Size并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。

列如:500个连接将会消耗500*sort_buffer_size(2M)=1G内存

(3)配置方法

修改/etc/my.cnf文件,在[mysqld]下面添加如下:

sort_buffer_size=1M

建议: 尽量排序能够使用索引更好。

5.7 max_allowed_packet *****

(1)简介:

mysql根据配置文件会限制,server接受的数据包大小。

(2)配置依据:

有时候大的插入和更新会受max_allowed_packet参数限制,导致写入或者更新失败,更大值是1GB,必须设置1024的倍数

(3)配置方法:

max_allowed_packet=32M

5.8 join_buffer_size ***

select a.name,b.name from a join b on a.id=b.id where xxxx

用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

尽量在SQL与方面进行优化,效果较为明显。

优化的方法:在on条件列加索引,至少应当是有MUL索引

建议: 尽量能够使用索引优化更好。

5.9 thread_cache_size = 16 *****

(1)简介

服务器线程缓存,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.

(2)配置依据

通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。

设置规则如下:1GB 内存配置为8,2GB配置为16,3GB配置为32,4GB或更高内存,可配置更大。

服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

试图连接到MySQL(不管是否连接成功)的连接数

mysql>  show status like 'threads_%';

+-------------------+-------+

| Variable_name    | Value |

+-------------------+-------+

| Threads_cached    | 8    |

| Threads_connected | 2    |

| Threads_created  | 4783  |

| Threads_running  | 1    |

+-------------------+-------+

4 rows in set (0.00 sec)

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。

Threads_connected:代表当前已建立连接的数量,因为一个连接就需要一个线程,所以也可以看成当前被使用的线程数。

Threads_created:代表从最近一次服务启动,已创建线程的数量,如果发现Threads_created值过大的话,表明MySQL服务器一直在创建线程,这也是比较耗cpu SYS资源,可以适当增加配置文件中thread_cache_size值。

Threads_running :代表当前激活的(非睡眠状态)线程数。并不是代表正在使用的线程数,有时候连接已建立,但是连接处于sleep状态。

(3)配置方法:

thread_cache_size=32

整理:

Threads_created  :一般在架构设计阶段,会设置一个测试值,做压力测试。

结合zabbix监控,看一段时间内此状态的变化。

如果在一段时间内,Threads_created趋于平稳,说明对应参数设定是OK。

如果一直陡峭的增长,或者出现大量峰值,那么继续增加此值的大小,在系统资源够用的情况下(内存)

5.10 innodb_buffer_pool_size *****

(1)简介

对于InnoDB表来说,innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。

(2)配置依据:

InnoDB使用该参数指定大小的内存来缓冲数据和索引。

对于单独的MySQL数据库服务器,最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。

(3)配置方法

innodb_buffer_pool_size=2048M

5.11 innodb_flush_log_at_trx_commit ******

(1)简介

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,取值分别为0、1、2三个。

0,表示当事务提交时,不做日志写入操作,而是每秒钟将log buffer中的数据写入日志文件并flush&sync磁盘一次;

1,

每次事务的提交都会引起redo日志文件写入、flush&sync磁盘的操作,确保了事务的ACID;

2,每次事务提交引起写入日志文件的动作,但每秒钟完成一次sync磁盘操作。

(2)配置依据

实际测试发现,该值对插入数据的速度影响非常大,设置为2时插入10000条记录只需要2秒,设置为0时只需要1秒,而设置为1时则需要229秒。因此,MySQL手册也建议尽量将插入操作合并成一个事务,这样可以大幅提高速度。

根据MySQL官方文档,在允许丢失最近部分事务的危险的前提下,可以把该值设为0或2。

(3)配置方法

innodb_flush_log_at_trx_commit=1

双1标准中的一个1

5.12 innodb_thread_concurrency ***

(1)简介

此参数用来设置innodb线程的并发数量,默认值为0表示不限制。

(2)配置依据

在官方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参数的设置进行调整。

128  -----> top  cpu 

设置标准:

1、当前系统cpu使用情况,均不均匀

top

2、当前的连接数,有没有达到顶峰

show status like 'threads_%';

show processlist;

(3)配置方法:

innodb_thread_concurrency=8

方法:

    1. 看top ,观察每个cpu的各自的负载情况

    2. 发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值

    3. 一直观察top状态,直到达到比较均匀时,说明已经到位了.

5.1.3 innodb_log_buffer_size

此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,对于较大的事务,可以增大缓存大小。

innodb_log_buffer_size=128M

设定依据:

1、大事务: 存储过程调用 CALL

2、多事务

5.14 innodb_log_file_size = 100M *****

设置 ib_logfile0  ib_logfile1

此参数确定数据日志文件的大小,以M为单位,更大的设置可以提高性能.

innodb_log_file_size = 100M

innodb_log_files_in_group = 3 *****

为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

read_buffer_size = 1M **

MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样,该参数对应的分配内存也是每个连接独享

read_rnd_buffer_size = 1M **

MySql的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。

注:顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据,而辅助索引和主键所在的数据段不同,因此访问方式是随机的。

bulk_insert_buffer_size = 8M **

批量插入数据缓存大小,可以有效提高插入效率,默认为8M

tokuDB    percona

myrocks 

RocksDB

TiDB

MongoDB

binary log *****

双1标准(基于安全的控制):

sync_binlog=1  什么时候刷新binlog到磁盘,每次事务commit

innodb_flush_log_at_trx_commit=1

安全参数 *****

Innodb_flush_method=(O_DIRECT, fsync)

1、fsync    :

(1)在数据页需要持久化时,首先将数据写入OS buffer中,然后由os决定什么时候写入磁盘

(2)在redo buffuer需要持久化时,首先将数据写入OS buffer中,然后由os决定什么时候写入磁盘

但,如果innodb_flush_log_at_trx_commit=1的话,日志还是直接每次commit直接写入磁盘

2、 Innodb_flush_method=O_DIRECT

(1)在数据页需要持久化时,直接写入磁盘

(2)在redo buffuer需要持久化时,首先将数据写入OS buffer中,然后由os决定什么时候写入磁盘

但,如果innodb_flush_log_at_trx_commit=1的话,日志还是直接每次commit直接写入磁盘

最安全模式:

innodb_flush_log_at_trx_commit=1

innodb_flush_method=O_DIRECT

最高性能模式:

innodb_flush_log_at_trx_commit=0

innodb_flush_method=fsync


一般情况下,我们更偏向于安全。

“双一标准”

innodb_flush_log_at_trx_commit=1                ***************

sync_binlog=1                                  ***************

innodb_flush_method=O_DIRECT

6. 参数优化结果

[mysqld]

basedir=/data/mysql

datadir=/data/mysql/data

socket=/tmp/mysql.sock

log-error=/var/log/mysql.log

log_bin=/data/binlog/mysql-bin

binlog_format=row

skip-name-resolve

server-id=52

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

relay_log_purge=0

max_connections=1024

back_log=128

wait_timeout=60

interactive_timeout=7200

key_buffer_size=16M

query_cache_size=64M

query_cache_type=1

query_cache_limit=50M

max_connect_errors=20

sort_buffer_size=2M

max_allowed_packet=32M

join_buffer_size=2M

thread_cache_size=200

innodb_buffer_pool_size=4096M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=32M

innodb_log_file_size=128M

innodb_log_files_in_group=3

binlog_cache_size=2M

max_binlog_cache_size=8M

max_binlog_size=512M

expire_logs_days=7

read_buffer_size=2M

read_rnd_buffer_size=2M

bulk_insert_buffer_size=8M

[client]

socket=/tmp/mysql.sock 


再次压力测试  :

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 216,001评论 6 498
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,210评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 161,874评论 0 351
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,001评论 1 291
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,022评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,005评论 1 295
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,929评论 3 416
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,742评论 0 271
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,193评论 1 309
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,427评论 2 331
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,583评论 1 346
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,305评论 5 342
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,911评论 3 325
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,564评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,731评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,581评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,478评论 2 352