如何对生产环境中的数据库进行压测数据库压测工具我们使用的数据库压测工具是sysbench这个工具会帮你在数据库中构建大量的数据可以模拟几千个线程去并发访问你的数据库模拟各种各样的SQL语句去访问你的数据库包括各种事务的提交。# 安装 curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo yum -y install sysbench sysbench --version实操安装MySQL数据库yum install -y docker systemctl start docker systemctl enable docker docker run -d --name mysql -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDRoot123456 \ -e MYSQL_DATABASEtest_db \ mysql:5.7最终你的压测配置就是用户test_user密码Test123456库名test_db构建压力测试表和数据# 准备 sysbench --db-drivermysql --mysql-host127.0.0.1 --mysql-port3306 --mysql-userroot --mysql-passwordRoot123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write preparesysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-userroot --mysql-passwordRoot123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write --db-ps-modedisable run上面我们构造了一个sysbench命令给他加入了很多的参数现在我们来 解释一下这些参数相信很多参数大家自己看到也就大致明白什么意思了--db-driver mysql这个很简单就是说他基于mysql的驱动去连接mysql数据库你要是oracle或者sqlserver那自然就是其他的数据库的驱动了--time 300 这个就是说连续访问300秒--threads 10 这个就是说用10个线程模拟并发访问--report-interva ∣1 这个就是说每隔1秒输出一下压测情况--mysql-host 127.0.0.1 --mysql-port3306 --mysql-user test_user --mysql-password test_user这一大串就是说连接到哪台机器的哪个端口上的MySQL库他的用户名和密码是什么--mysql-db test_db --tables 20 --table_size 1000000 这一串的意思就是说在test_db这个库里构造20个测试表每个测试表里构造100万条测试数据测试表的名字会是类似于sbtest1sbtest2这个样子的oltp_read_write这个就是说执行oltp数据库的读写测试--db-ps-mode disable这个就是禁止ps模式最后有一个prepare意思是参照这个命令的设置去构造出来我们需要的数据库里的数据他会自动创建20个测试表每个表里创建100对数据库进行全方面压测测试数据库的综合读写TPS使用的是oltp_read_write模式大家看命令中最后不是prepare是run了就是运行压测sysbench --db-drivermysql --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write prepare sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write --db-ps-modedisable run测试数据库的只读性能使用的是oltp_read_only模式大家看命令中的oltp_read_write已经变为oltp_read_only了sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_only --db-ps-modedisable run测试数据库的删除性能使用的是oltp_delete模式sysbench --db-driver : mysql --time 300 --threads 10 --report-interval1 --mysql-hos 127.0.0.1 --mysql- port 3306 --mysql-user : test_user --mysql-password Test123456 --mysql-db test_db --tables 20 --table_size 1000000 oltp_delete --db-ps-mode disable run测试数据库的更新索引字段的性能使用的是oltp_update_index模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_update_index --db-ps-modedisable run测试数据库的更新非索引字段的性能使用的是oltp_update_non_index模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_update_non_index --db-ps-modedisable run测试数据库的更新非索引字段的性能使用的是oltp_update_non_index模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysqlport3306 --mysql-user ≡ test_user --mysql-password ≡ Test123456 --mysql-db ≡ test_db --tables 20 --table_size1000000 oltp_update_non_index --db-ps-mode ≡ disable run测试数据库的插入性能使用的是oltp_insert模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysqlport3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table_size1000000 oltp_insert --db-ps-modedisable run测试数据库的写入性能使用的是oltp_write_only模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_write_only --db-ps-modedisable run使用上面的命令sysbench工具会根据你的指令构造出各种各样的SQL语句去更新或者查询你的20张测试表里的数据同时监测出你的数据库的压测性能指标最后完成压测之后可以执行下面的cleanup命令清理数据。sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write --db-ps-modedisable cleanup压测结果分析指标数值含义说明thds10压测并发线程数为 10tps380.99每秒执行 380.99 个事务Transactions Per Secondqps7610.2每秒执行 7610.20 个查询请求Queries Per Second(r/w/o)5132.99/1155.86/1321.35QPS 拆解・读请求5132.99/s・写请求1155.86/s・其他请求1321.35/slat (ms, 95%)21.3395% 的请求延迟 ≤ 21.33 毫秒95 分位响应时间err/s0每秒 0 个请求失败reconn/s0每秒 0 次网络重连在压测完成会出现一下情况SQL statistics: queries performed: read: 0 -- 读请求数纯写入压测0 write: 494508 -- 总写入请求数 other: 247254 -- 提交/回滚等操作 total: 741762 -- 总SQL请求数 transactions: 123627 (412.05 per sec.) -- 总事务数 / TPS412 queries: 741762 (2472.27 per sec.) -- 总查询数 / QPS2472 ignored errors: 0 (0.00 per sec.) -- 无错误 reconnects: 0 (0.00 per sec.) -- 无重连稳定 General statistics: total time: 300.0315s -- 压测总时长5分钟 total number of events: 123627 -- 总处理事件数 Latency (ms): -- 请求延迟单位毫秒 min: 2.70 -- 最小延迟 avg: 24.27 -- 平均延迟优秀 max: 302.69 -- 最大延迟 95th percentile: 62.19 -- 95%请求≤62.19ms sum: 3000010.93 Threads fairness: -- 线程负载均衡 events (avg/stddev): 12362.7000/35.67 -- 线程分配均匀 execution time (avg/stddev): 300.0011/0.00 -- 执行时间一致✅压测场景oltp_write_only 纯写入✅性能指标TPS412 | QPS2472 | 平均延迟 24.27ms✅稳定性0 错误、0 重连、线程负载均匀数据库运行状态优秀我进行压测的这个服务器是2核2G的服务器数据库压测过程中数据库性能观察为什么我们需要去观察数据库性能当我们在压测的过程中如果我们的数据库可以抗住2000QPS这时候CPU负载内存负载等都处于正常范围但是当我们将数据库提升到5000QPS的时候此时网络负载CPU负载都快满了这时候就说明机器到极致了再下去机器要挂了这时候的5000QPS是不正常的。所以在我们压测数据库的时候同时也需要去观察Linux系统的CPU负载网络负载磁盘和网络等压测时如何去观察CPU负载情况我们可以使用top命令去观察linux机器的性能top - 10:17:37 up 53 min, 4 users, load average: 2.99, 1.47, 0.84 当前时间 机器运行时间 4个用户在使用 负载情况1分钟 5分钟 15分钟现在我们来解释一下CPU负载当我们的机器是4核8G的服务器CPU负载值是2就代表CPU使用率是50%有两个CPU使用满了两个CPU空闲压测时如何去观察内存负载情况在我们使用top命令的时候可以看到以下的情况KiB Mem : 2046504 total, 77264 free, 626932 used, 1342308 buff/cache 20GB左右总内存 7G左右空闲内存 6G左右使用内存 1G左右为OS内核缓冲区一般来说内存使用率在80%以内基本上是正常范围。压测时如何去观察磁盘IO情况首先我们需要去安装dstat这个插件yum install -y dstat使用dstat -d去查看磁盘IO的使用情况这个是存储IO吞吐量使用dstat -r这个是查看IOPS的读写也就是随机磁盘读写请求当我们磁盘IO吞吐量达到每秒上百MB就是极限或者随机磁盘读写两三百次这样的情况都不要增加线程的数量了否则磁盘IO负载太高。压测时观察网卡的流量情况可以是dstat -n这个指令去查看网卡接受到的流量多少kb通常来说千兆网卡每秒总流量为100MB左右如何为生成环境中的数据库部署监控系统当我们将数据库与Java系统连接起来的时候是不是后面就只需去监控我们的Java系统了呢不是这样的如果对数据库不做任何监控万一有一天数据库CPU负责特别高到时候挂了都不知道。接下来我们将搭建PromethusGrafana去搭建生产环境下数据库的可监视平台Prometheus 和Grafana是什么Prometheus 就是一个监控数据采集和存储系统可以利用监控数据采集组件mysql_exporter等工具从你指定的MySQL数据库中采取他需要的监控数据然后他自己有一个时序数据库会把采集到的监控数据放入自己的时序数据库中也就是存储在磁盘文件中。当然我们还需要将这些数据以报表的形式展示出来这时候就用上了Grafana一个可视化的监控数据展示系统。这两个组合不仅可以监控我们的mysql数据库同时还可以监控我们的Java系统中间件等实操MySQL 里创建监控账号docker exec -i mysql mysql -uroot -pRoot123456 -e CREATE USER exporter% IDENTIFIED BY Exporter123; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO exporter%; FLUSH PRIVILEGES; 启动 exporterdocker run -d \ --name mysqld_exporter \ -p 9104:9104 \ -v /root/.my.cnf:/.my.cnf \ prom/mysqld-exporter新建Prometheus配置文件mkdir -p /prometheus cat /prometheus/prometheus.yml EOF global: scrape_interval: 15s scrape_configs: - job_name: mysql static_configs: - targets: [172.17.0.1:9104] EOF启动 Prometheusdocker run -d \ --name prometheus \ -p 9090:9090 \ -v /prometheus/prometheus.yml:/etc/prometheus/prometheus.yml \ prom/prometheus启动 Grafanadocker run -d \ --name grafana \ -p 3000:3000 \ grafana/grafana访问地址:Prometheus: http:// 你的 IP:9090Grafana: http:// 你的 IP:3000默认账号admin默认密码adminGrafana 一键导入 MySQL 监控大盘最关键导入官方 MySQL 监控面板如果不行导入这个json{ annotations: { list: [ { builtIn: 1, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, enable: true, hide: false, iconColor: rgba(0, 211, 255, 1), name: Annotations Alerts, target: { limit: 100, matchAny: false, tags: [], type: dashboard }, type: dashboard } ] }, editable: true, fiscalYearStartMonth: 0, graphTooltip: 0, id: 14077, links: [], liveNow: false, panels: [ { collapsed: false, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, gridPos: { h: 1, w: 24, x: 0, y: 0 }, id: 2, panels: [], title: Overview, type: row }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] } }, overrides: [] }, gridPos: { h: 4, w: 6, x: 0, y: 1 }, id: 4, options: { colorMode: value, graphMode: area, justifyMode: auto, orientation: auto, reduceOptions: { calcs: [ lastNotNull ], fields: , values: false }, textMode: auto }, targets: [ { expr: mysql_up{instance~\$instance\}, refId: A } ], title: MySQL Up, type: stat }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: s }, overrides: [] }, gridPos: { h: 4, w: 6, x: 6, y: 1 }, id: 6, options: { colorMode: value, graphMode: area, justifyMode: auto, orientation: auto, reduceOptions: { calcs: [ lastNotNull ], fields: , values: false }, textMode: auto }, targets: [ { expr: mysql_global_status_uptime{instance~\$instance\}, refId: A } ], title: Uptime, type: stat }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 4, w: 6, x: 12, y: 1 }, id: 8, options: { colorMode: value, graphMode: area, justifyMode: auto, orientation: auto, reduceOptions: { calcs: [ lastNotNull ], fields: , values: false }, textMode: auto }, targets: [ { expr: sum(rate(mysql_global_status_queries{instance~\$instance\}[5m])), refId: A } ], title: QPS, type: stat }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 4, w: 6, x: 18, y: 1 }, id: 10, options: { colorMode: value, graphMode: area, justifyMode: auto, orientation: auto, reduceOptions: { calcs: [ lastNotNull ], fields: , values: false }, textMode: auto }, targets: [ { expr: sum(rate(mysql_global_status_commands_total{instance~\$instance\,command\commit\}[5m])), refId: A } ], title: TPS, type: stat }, { collapsed: false, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, gridPos: { h: 1, w: 24, x: 0, y: 5 }, id: 12, panels: [], title: Connections, type: row }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, custom: { axisCenteredZero: false, axisColorMode: text, axisLabel: , axisPlacement: auto, barAlignment: 0, drawStyle: line, fillOpacity: 10, gradientMode: none, hideFrom: { legend: false, tooltip: false, viz: false }, lineInterpolation: linear, lineWidth: 1, pointSize: 5, scaleDistribution: { type: linear }, showPoints: auto, spanNulls: false, stacking: { group: A, mode: none }, thresholdsStyle: { mode: off } }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 8, w: 12, x: 0, y: 6 }, id: 14, options: { legend: { calcs: [], displayMode: list, placement: bottom, showLegend: true }, tooltip: { mode: single, sort: none } }, targets: [ { expr: sum(rate(mysql_global_status_threads_connected{instance~\$instance\}[5m])), legendFormat: Connected, refId: A }, { expr: sum(rate(mysql_global_status_threads_running{instance~\$instance\}[5m])), legendFormat: Running, refId: B } ], title: Connections, type: timeseries }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, custom: { axisCenteredZero: false, axisColorMode: text, axisLabel: , axisPlacement: auto, barAlignment: 0, drawStyle: line, fillOpacity: 10, gradientMode: none, hideFrom: { legend: false, tooltip: false, viz: false }, lineInterpolation: linear, lineWidth: 1, pointSize: 5, scaleDistribution: { type: linear }, showPoints: auto, spanNulls: false, stacking: { group: A, mode: none }, thresholdsStyle: { mode: off } }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 8, w: 12, x: 12, y: 6 }, id: 16, options: { legend: { calcs: [], displayMode: list, placement: bottom, showLegend: true }, tooltip: { mode: single, sort: none } }, targets: [ { expr: sum(rate(mysql_global_status_threads_created{instance~\$instance\}[5m])), legendFormat: Created, refId: A }, { expr: sum(rate(mysql_global_status_threads_cached{instance~\$instance\}[5m])), legendFormat: Cached, refId: B } ], title: Thread Activity, type: timeseries }, { collapsed: false, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, gridPos: { h: 1, w: 24, x: 0, y: 14 }, id: 18, panels: [], title: InnoDB, type: row }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, custom: { axisCenteredZero: false, axisColorMode: text, axisLabel: , axisPlacement: auto, barAlignment: 0, drawStyle: line, fillOpacity: 10, gradientMode: none, hideFrom: { legend: false, tooltip: false, viz: false }, lineInterpolation: linear, lineWidth: 1, pointSize: 5, scaleDistribution: { type: linear }, showPoints: auto, spanNulls: false, stacking: { group: A, mode: none }, thresholdsStyle: { mode: off } }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: bytes }, overrides: [] }, gridPos: { h: 8, w: 12, x: 0, y: 15 }, id: 20, options: { legend: { calcs: [], displayMode: list, placement: bottom, showLegend: true }, tooltip: { mode: single, sort: none } }, targets: [ { expr: mysql_global_variables_innodb_buffer_pool_size{instance~\$instance\}, legendFormat: Buffer Pool Size, refId: A }, { expr: mysql_global_status_innodb_buffer_pool_bytes_data{instance~\$instance\}, legendFormat: Data Bytes, refId: B }, { expr: mysql_global_status_innodb_buffer_pool_bytes_free{instance~\$instance\}, legendFormat: Free Bytes, refId: C } ], title: InnoDB Buffer Pool, type: timeseries }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, custom: { axisCenteredZero: false, axisColorMode: text, axisLabel: , axisPlacement: auto, barAlignment: 0, drawStyle: line, fillOpacity: 10, gradientMode: none, hideFrom: { legend: false, tooltip: false, viz: false }, lineInterpolation: linear, lineWidth: 1, pointSize: 5, scaleDistribution: { type: linear }, showPoints: auto, spanNulls: false, stacking: { group: A, mode: none }, thresholdsStyle: { mode: off } }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 8, w: 12, x: 12, y: 15 }, id: 22, options: { legend: { calcs: [], displayMode: list, placement: bottom, showLegend: true }, tooltip: { mode: single, sort: none } }, targets: [ { expr: sum(rate(mysql_global_status_innodb_data_reads{instance~\$instance\}[5m])), legendFormat: Data Reads, refId: A }, { expr: sum(rate(mysql_global_status_innodb_data_writes{instance~\$instance\}[5m])), legendFormat: Data Writes, refId: B } ], title: InnoDB I/O, type: timeseries } ], refresh: 5s, schemaVersion: 38, style: dark, tags: [mysql], templating: { list: [ { current: { selected: false, text: Prometheus, value: prometheus }, hide: 0, includeAll: false, label: Data Source, multi: false, name: DS_PROMETHEUS, options: [], query: prometheus, queryValue: , regex: , skipUrlSync: false, type: datasource }, { current: { selected: false, text: All, value: $__all }, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, hide: 0, includeAll: true, label: Instance, multi: false, name: instance, options: [], query: label_values(mysql_up, instance), refresh: 1, regex: , skipUrlSync: false, type: query } ] }, time: { from: now-1h, to: now }, timepicker: {}, timezone: , title: MySQL Overview, uid: mysql-overview-fixed, version: 1, weekStart: }进行压测docker exec -i mysql mysql -uroot -pRoot123456 -e CREATE DATABASE test; GRANT ALL PRIVILEGES ON test.* TO exporter%; FLUSH PRIVILEGES; sysbench oltp_read_write \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userexporter \ --mysql-passwordExporter123 \ --mysql-dbtest \ --table-size100000 \ --tables5 \ preparesysbench oltp_read_write \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userexporter \ --mysql-passwordExporter123 \ --mysql-dbtest \ --threads8 \ --time300 \ runBuffer Pool在数据库里的地位在我们对数据库进行增删改查的时候不可能直接更新磁盘上的数据如果对磁盘进行随机读写操作速度相当慢随机一个大磁盘文件随机读写都可能到几百毫秒可能导致数据库每秒就只有处理几百个请求了前面我们也说过我们对数据库进行增删改操作的时候主要是针对内存里都会Buffer Pool中的数据进行的总的来说Buffer Pool是我们数据库学习必须搞懂的核心组件因为增删改操作主要是对内存数据结构中的缓存数据进行执行的。Buffer Pool内存数据结构长什么样如何配置Buffer Pool其本质就是数据库的一个内存组件默认情况下是128MB但是在实际生产环境下这个还是偏小了需要对其调整如果我们的数据库是16核32G就可以分配2GB的内存给他[server] innodb_buffer_pool_size 2147483648数据页MySQL抽象出来的数据单位我们的数据是如何放到Buffer Pool里面的我们知道数据库核心数据模型是表字段行,那我们的数据是一行一行放到Buffer Pool里面的吗明显不是MySQL会抽取出一个抽象的概念数据页将多行数据放到一个数据页中当我们需要去更新一行数据的时候数据库会找到这行数据的数据页磁盘文件把这行数据所在的数据页放到Buffer Pool里面去磁盘上的数据页和Buffer Pool中的缓存页是如何对应起来实际情况下磁盘中的数据页为16KB页也就是说一页数据包含了16kb的内容而Buffer Pool中存放的一个一个的数据页我们通常叫做缓存页缓存页对应都会描述信息是什么缓存页主要是包括数据页所属的表空间数据页的编号这个缓存页在Buffer Pool的地址以及一些别的东西每个缓存页都对应一个描述信息在Buffer Pool中每个缓存页的描述等候放在数据的最前面然后各个缓存页放在最后Buffer Pool中的描述数据大概相当于缓存页大小的5%左右也就是大概800个字左右大小假设buffer pool描述信息大概相当于缓存页的5%左右就是每个描述数据大概800个字节左右假设我们设置的Buffer Pool大小为128MB实际上最终大小会大一些因为要存放每个缓存页的描述数据
MySQL高手第二章
如何对生产环境中的数据库进行压测数据库压测工具我们使用的数据库压测工具是sysbench这个工具会帮你在数据库中构建大量的数据可以模拟几千个线程去并发访问你的数据库模拟各种各样的SQL语句去访问你的数据库包括各种事务的提交。# 安装 curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo yum -y install sysbench sysbench --version实操安装MySQL数据库yum install -y docker systemctl start docker systemctl enable docker docker run -d --name mysql -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDRoot123456 \ -e MYSQL_DATABASEtest_db \ mysql:5.7最终你的压测配置就是用户test_user密码Test123456库名test_db构建压力测试表和数据# 准备 sysbench --db-drivermysql --mysql-host127.0.0.1 --mysql-port3306 --mysql-userroot --mysql-passwordRoot123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write preparesysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-userroot --mysql-passwordRoot123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write --db-ps-modedisable run上面我们构造了一个sysbench命令给他加入了很多的参数现在我们来 解释一下这些参数相信很多参数大家自己看到也就大致明白什么意思了--db-driver mysql这个很简单就是说他基于mysql的驱动去连接mysql数据库你要是oracle或者sqlserver那自然就是其他的数据库的驱动了--time 300 这个就是说连续访问300秒--threads 10 这个就是说用10个线程模拟并发访问--report-interva ∣1 这个就是说每隔1秒输出一下压测情况--mysql-host 127.0.0.1 --mysql-port3306 --mysql-user test_user --mysql-password test_user这一大串就是说连接到哪台机器的哪个端口上的MySQL库他的用户名和密码是什么--mysql-db test_db --tables 20 --table_size 1000000 这一串的意思就是说在test_db这个库里构造20个测试表每个测试表里构造100万条测试数据测试表的名字会是类似于sbtest1sbtest2这个样子的oltp_read_write这个就是说执行oltp数据库的读写测试--db-ps-mode disable这个就是禁止ps模式最后有一个prepare意思是参照这个命令的设置去构造出来我们需要的数据库里的数据他会自动创建20个测试表每个表里创建100对数据库进行全方面压测测试数据库的综合读写TPS使用的是oltp_read_write模式大家看命令中最后不是prepare是run了就是运行压测sysbench --db-drivermysql --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write prepare sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write --db-ps-modedisable run测试数据库的只读性能使用的是oltp_read_only模式大家看命令中的oltp_read_write已经变为oltp_read_only了sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_only --db-ps-modedisable run测试数据库的删除性能使用的是oltp_delete模式sysbench --db-driver : mysql --time 300 --threads 10 --report-interval1 --mysql-hos 127.0.0.1 --mysql- port 3306 --mysql-user : test_user --mysql-password Test123456 --mysql-db test_db --tables 20 --table_size 1000000 oltp_delete --db-ps-mode disable run测试数据库的更新索引字段的性能使用的是oltp_update_index模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_update_index --db-ps-modedisable run测试数据库的更新非索引字段的性能使用的是oltp_update_non_index模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_update_non_index --db-ps-modedisable run测试数据库的更新非索引字段的性能使用的是oltp_update_non_index模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysqlport3306 --mysql-user ≡ test_user --mysql-password ≡ Test123456 --mysql-db ≡ test_db --tables 20 --table_size1000000 oltp_update_non_index --db-ps-mode ≡ disable run测试数据库的插入性能使用的是oltp_insert模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysqlport3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table_size1000000 oltp_insert --db-ps-modedisable run测试数据库的写入性能使用的是oltp_write_only模式sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_write_only --db-ps-modedisable run使用上面的命令sysbench工具会根据你的指令构造出各种各样的SQL语句去更新或者查询你的20张测试表里的数据同时监测出你的数据库的压测性能指标最后完成压测之后可以执行下面的cleanup命令清理数据。sysbench --db-drivermysql --time300 --threads10 --report-interval1 --mysql-host127.0.0.1 --mysql-port3306 --mysql-usertest_user --mysql-passwordTest123456 --mysql-dbtest_db --tables20 --table-size1000000 oltp_read_write --db-ps-modedisable cleanup压测结果分析指标数值含义说明thds10压测并发线程数为 10tps380.99每秒执行 380.99 个事务Transactions Per Secondqps7610.2每秒执行 7610.20 个查询请求Queries Per Second(r/w/o)5132.99/1155.86/1321.35QPS 拆解・读请求5132.99/s・写请求1155.86/s・其他请求1321.35/slat (ms, 95%)21.3395% 的请求延迟 ≤ 21.33 毫秒95 分位响应时间err/s0每秒 0 个请求失败reconn/s0每秒 0 次网络重连在压测完成会出现一下情况SQL statistics: queries performed: read: 0 -- 读请求数纯写入压测0 write: 494508 -- 总写入请求数 other: 247254 -- 提交/回滚等操作 total: 741762 -- 总SQL请求数 transactions: 123627 (412.05 per sec.) -- 总事务数 / TPS412 queries: 741762 (2472.27 per sec.) -- 总查询数 / QPS2472 ignored errors: 0 (0.00 per sec.) -- 无错误 reconnects: 0 (0.00 per sec.) -- 无重连稳定 General statistics: total time: 300.0315s -- 压测总时长5分钟 total number of events: 123627 -- 总处理事件数 Latency (ms): -- 请求延迟单位毫秒 min: 2.70 -- 最小延迟 avg: 24.27 -- 平均延迟优秀 max: 302.69 -- 最大延迟 95th percentile: 62.19 -- 95%请求≤62.19ms sum: 3000010.93 Threads fairness: -- 线程负载均衡 events (avg/stddev): 12362.7000/35.67 -- 线程分配均匀 execution time (avg/stddev): 300.0011/0.00 -- 执行时间一致✅压测场景oltp_write_only 纯写入✅性能指标TPS412 | QPS2472 | 平均延迟 24.27ms✅稳定性0 错误、0 重连、线程负载均匀数据库运行状态优秀我进行压测的这个服务器是2核2G的服务器数据库压测过程中数据库性能观察为什么我们需要去观察数据库性能当我们在压测的过程中如果我们的数据库可以抗住2000QPS这时候CPU负载内存负载等都处于正常范围但是当我们将数据库提升到5000QPS的时候此时网络负载CPU负载都快满了这时候就说明机器到极致了再下去机器要挂了这时候的5000QPS是不正常的。所以在我们压测数据库的时候同时也需要去观察Linux系统的CPU负载网络负载磁盘和网络等压测时如何去观察CPU负载情况我们可以使用top命令去观察linux机器的性能top - 10:17:37 up 53 min, 4 users, load average: 2.99, 1.47, 0.84 当前时间 机器运行时间 4个用户在使用 负载情况1分钟 5分钟 15分钟现在我们来解释一下CPU负载当我们的机器是4核8G的服务器CPU负载值是2就代表CPU使用率是50%有两个CPU使用满了两个CPU空闲压测时如何去观察内存负载情况在我们使用top命令的时候可以看到以下的情况KiB Mem : 2046504 total, 77264 free, 626932 used, 1342308 buff/cache 20GB左右总内存 7G左右空闲内存 6G左右使用内存 1G左右为OS内核缓冲区一般来说内存使用率在80%以内基本上是正常范围。压测时如何去观察磁盘IO情况首先我们需要去安装dstat这个插件yum install -y dstat使用dstat -d去查看磁盘IO的使用情况这个是存储IO吞吐量使用dstat -r这个是查看IOPS的读写也就是随机磁盘读写请求当我们磁盘IO吞吐量达到每秒上百MB就是极限或者随机磁盘读写两三百次这样的情况都不要增加线程的数量了否则磁盘IO负载太高。压测时观察网卡的流量情况可以是dstat -n这个指令去查看网卡接受到的流量多少kb通常来说千兆网卡每秒总流量为100MB左右如何为生成环境中的数据库部署监控系统当我们将数据库与Java系统连接起来的时候是不是后面就只需去监控我们的Java系统了呢不是这样的如果对数据库不做任何监控万一有一天数据库CPU负责特别高到时候挂了都不知道。接下来我们将搭建PromethusGrafana去搭建生产环境下数据库的可监视平台Prometheus 和Grafana是什么Prometheus 就是一个监控数据采集和存储系统可以利用监控数据采集组件mysql_exporter等工具从你指定的MySQL数据库中采取他需要的监控数据然后他自己有一个时序数据库会把采集到的监控数据放入自己的时序数据库中也就是存储在磁盘文件中。当然我们还需要将这些数据以报表的形式展示出来这时候就用上了Grafana一个可视化的监控数据展示系统。这两个组合不仅可以监控我们的mysql数据库同时还可以监控我们的Java系统中间件等实操MySQL 里创建监控账号docker exec -i mysql mysql -uroot -pRoot123456 -e CREATE USER exporter% IDENTIFIED BY Exporter123; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO exporter%; FLUSH PRIVILEGES; 启动 exporterdocker run -d \ --name mysqld_exporter \ -p 9104:9104 \ -v /root/.my.cnf:/.my.cnf \ prom/mysqld-exporter新建Prometheus配置文件mkdir -p /prometheus cat /prometheus/prometheus.yml EOF global: scrape_interval: 15s scrape_configs: - job_name: mysql static_configs: - targets: [172.17.0.1:9104] EOF启动 Prometheusdocker run -d \ --name prometheus \ -p 9090:9090 \ -v /prometheus/prometheus.yml:/etc/prometheus/prometheus.yml \ prom/prometheus启动 Grafanadocker run -d \ --name grafana \ -p 3000:3000 \ grafana/grafana访问地址:Prometheus: http:// 你的 IP:9090Grafana: http:// 你的 IP:3000默认账号admin默认密码adminGrafana 一键导入 MySQL 监控大盘最关键导入官方 MySQL 监控面板如果不行导入这个json{ annotations: { list: [ { builtIn: 1, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, enable: true, hide: false, iconColor: rgba(0, 211, 255, 1), name: Annotations Alerts, target: { limit: 100, matchAny: false, tags: [], type: dashboard }, type: dashboard } ] }, editable: true, fiscalYearStartMonth: 0, graphTooltip: 0, id: 14077, links: [], liveNow: false, panels: [ { collapsed: false, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, gridPos: { h: 1, w: 24, x: 0, y: 0 }, id: 2, panels: [], title: Overview, type: row }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] } }, overrides: [] }, gridPos: { h: 4, w: 6, x: 0, y: 1 }, id: 4, options: { colorMode: value, graphMode: area, justifyMode: auto, orientation: auto, reduceOptions: { calcs: [ lastNotNull ], fields: , values: false }, textMode: auto }, targets: [ { expr: mysql_up{instance~\$instance\}, refId: A } ], title: MySQL Up, type: stat }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: s }, overrides: [] }, gridPos: { h: 4, w: 6, x: 6, y: 1 }, id: 6, options: { colorMode: value, graphMode: area, justifyMode: auto, orientation: auto, reduceOptions: { calcs: [ lastNotNull ], fields: , values: false }, textMode: auto }, targets: [ { expr: mysql_global_status_uptime{instance~\$instance\}, refId: A } ], title: Uptime, type: stat }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 4, w: 6, x: 12, y: 1 }, id: 8, options: { colorMode: value, graphMode: area, justifyMode: auto, orientation: auto, reduceOptions: { calcs: [ lastNotNull ], fields: , values: false }, textMode: auto }, targets: [ { expr: sum(rate(mysql_global_status_queries{instance~\$instance\}[5m])), refId: A } ], title: QPS, type: stat }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 4, w: 6, x: 18, y: 1 }, id: 10, options: { colorMode: value, graphMode: area, justifyMode: auto, orientation: auto, reduceOptions: { calcs: [ lastNotNull ], fields: , values: false }, textMode: auto }, targets: [ { expr: sum(rate(mysql_global_status_commands_total{instance~\$instance\,command\commit\}[5m])), refId: A } ], title: TPS, type: stat }, { collapsed: false, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, gridPos: { h: 1, w: 24, x: 0, y: 5 }, id: 12, panels: [], title: Connections, type: row }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, custom: { axisCenteredZero: false, axisColorMode: text, axisLabel: , axisPlacement: auto, barAlignment: 0, drawStyle: line, fillOpacity: 10, gradientMode: none, hideFrom: { legend: false, tooltip: false, viz: false }, lineInterpolation: linear, lineWidth: 1, pointSize: 5, scaleDistribution: { type: linear }, showPoints: auto, spanNulls: false, stacking: { group: A, mode: none }, thresholdsStyle: { mode: off } }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 8, w: 12, x: 0, y: 6 }, id: 14, options: { legend: { calcs: [], displayMode: list, placement: bottom, showLegend: true }, tooltip: { mode: single, sort: none } }, targets: [ { expr: sum(rate(mysql_global_status_threads_connected{instance~\$instance\}[5m])), legendFormat: Connected, refId: A }, { expr: sum(rate(mysql_global_status_threads_running{instance~\$instance\}[5m])), legendFormat: Running, refId: B } ], title: Connections, type: timeseries }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, custom: { axisCenteredZero: false, axisColorMode: text, axisLabel: , axisPlacement: auto, barAlignment: 0, drawStyle: line, fillOpacity: 10, gradientMode: none, hideFrom: { legend: false, tooltip: false, viz: false }, lineInterpolation: linear, lineWidth: 1, pointSize: 5, scaleDistribution: { type: linear }, showPoints: auto, spanNulls: false, stacking: { group: A, mode: none }, thresholdsStyle: { mode: off } }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 8, w: 12, x: 12, y: 6 }, id: 16, options: { legend: { calcs: [], displayMode: list, placement: bottom, showLegend: true }, tooltip: { mode: single, sort: none } }, targets: [ { expr: sum(rate(mysql_global_status_threads_created{instance~\$instance\}[5m])), legendFormat: Created, refId: A }, { expr: sum(rate(mysql_global_status_threads_cached{instance~\$instance\}[5m])), legendFormat: Cached, refId: B } ], title: Thread Activity, type: timeseries }, { collapsed: false, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, gridPos: { h: 1, w: 24, x: 0, y: 14 }, id: 18, panels: [], title: InnoDB, type: row }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, custom: { axisCenteredZero: false, axisColorMode: text, axisLabel: , axisPlacement: auto, barAlignment: 0, drawStyle: line, fillOpacity: 10, gradientMode: none, hideFrom: { legend: false, tooltip: false, viz: false }, lineInterpolation: linear, lineWidth: 1, pointSize: 5, scaleDistribution: { type: linear }, showPoints: auto, spanNulls: false, stacking: { group: A, mode: none }, thresholdsStyle: { mode: off } }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: bytes }, overrides: [] }, gridPos: { h: 8, w: 12, x: 0, y: 15 }, id: 20, options: { legend: { calcs: [], displayMode: list, placement: bottom, showLegend: true }, tooltip: { mode: single, sort: none } }, targets: [ { expr: mysql_global_variables_innodb_buffer_pool_size{instance~\$instance\}, legendFormat: Buffer Pool Size, refId: A }, { expr: mysql_global_status_innodb_buffer_pool_bytes_data{instance~\$instance\}, legendFormat: Data Bytes, refId: B }, { expr: mysql_global_status_innodb_buffer_pool_bytes_free{instance~\$instance\}, legendFormat: Free Bytes, refId: C } ], title: InnoDB Buffer Pool, type: timeseries }, { datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, fieldConfig: { defaults: { color: { mode: palette-classic }, custom: { axisCenteredZero: false, axisColorMode: text, axisLabel: , axisPlacement: auto, barAlignment: 0, drawStyle: line, fillOpacity: 10, gradientMode: none, hideFrom: { legend: false, tooltip: false, viz: false }, lineInterpolation: linear, lineWidth: 1, pointSize: 5, scaleDistribution: { type: linear }, showPoints: auto, spanNulls: false, stacking: { group: A, mode: none }, thresholdsStyle: { mode: off } }, mappings: [], thresholds: { mode: absolute, steps: [ { color: green, value: null } ] }, unit: short }, overrides: [] }, gridPos: { h: 8, w: 12, x: 12, y: 15 }, id: 22, options: { legend: { calcs: [], displayMode: list, placement: bottom, showLegend: true }, tooltip: { mode: single, sort: none } }, targets: [ { expr: sum(rate(mysql_global_status_innodb_data_reads{instance~\$instance\}[5m])), legendFormat: Data Reads, refId: A }, { expr: sum(rate(mysql_global_status_innodb_data_writes{instance~\$instance\}[5m])), legendFormat: Data Writes, refId: B } ], title: InnoDB I/O, type: timeseries } ], refresh: 5s, schemaVersion: 38, style: dark, tags: [mysql], templating: { list: [ { current: { selected: false, text: Prometheus, value: prometheus }, hide: 0, includeAll: false, label: Data Source, multi: false, name: DS_PROMETHEUS, options: [], query: prometheus, queryValue: , regex: , skipUrlSync: false, type: datasource }, { current: { selected: false, text: All, value: $__all }, datasource: { type: prometheus, uid: ${DS_PROMETHEUS} }, hide: 0, includeAll: true, label: Instance, multi: false, name: instance, options: [], query: label_values(mysql_up, instance), refresh: 1, regex: , skipUrlSync: false, type: query } ] }, time: { from: now-1h, to: now }, timepicker: {}, timezone: , title: MySQL Overview, uid: mysql-overview-fixed, version: 1, weekStart: }进行压测docker exec -i mysql mysql -uroot -pRoot123456 -e CREATE DATABASE test; GRANT ALL PRIVILEGES ON test.* TO exporter%; FLUSH PRIVILEGES; sysbench oltp_read_write \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userexporter \ --mysql-passwordExporter123 \ --mysql-dbtest \ --table-size100000 \ --tables5 \ preparesysbench oltp_read_write \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userexporter \ --mysql-passwordExporter123 \ --mysql-dbtest \ --threads8 \ --time300 \ runBuffer Pool在数据库里的地位在我们对数据库进行增删改查的时候不可能直接更新磁盘上的数据如果对磁盘进行随机读写操作速度相当慢随机一个大磁盘文件随机读写都可能到几百毫秒可能导致数据库每秒就只有处理几百个请求了前面我们也说过我们对数据库进行增删改操作的时候主要是针对内存里都会Buffer Pool中的数据进行的总的来说Buffer Pool是我们数据库学习必须搞懂的核心组件因为增删改操作主要是对内存数据结构中的缓存数据进行执行的。Buffer Pool内存数据结构长什么样如何配置Buffer Pool其本质就是数据库的一个内存组件默认情况下是128MB但是在实际生产环境下这个还是偏小了需要对其调整如果我们的数据库是16核32G就可以分配2GB的内存给他[server] innodb_buffer_pool_size 2147483648数据页MySQL抽象出来的数据单位我们的数据是如何放到Buffer Pool里面的我们知道数据库核心数据模型是表字段行,那我们的数据是一行一行放到Buffer Pool里面的吗明显不是MySQL会抽取出一个抽象的概念数据页将多行数据放到一个数据页中当我们需要去更新一行数据的时候数据库会找到这行数据的数据页磁盘文件把这行数据所在的数据页放到Buffer Pool里面去磁盘上的数据页和Buffer Pool中的缓存页是如何对应起来实际情况下磁盘中的数据页为16KB页也就是说一页数据包含了16kb的内容而Buffer Pool中存放的一个一个的数据页我们通常叫做缓存页缓存页对应都会描述信息是什么缓存页主要是包括数据页所属的表空间数据页的编号这个缓存页在Buffer Pool的地址以及一些别的东西每个缓存页都对应一个描述信息在Buffer Pool中每个缓存页的描述等候放在数据的最前面然后各个缓存页放在最后Buffer Pool中的描述数据大概相当于缓存页大小的5%左右也就是大概800个字左右大小假设buffer pool描述信息大概相当于缓存页的5%左右就是每个描述数据大概800个字节左右假设我们设置的Buffer Pool大小为128MB实际上最终大小会大一些因为要存放每个缓存页的描述数据
