Linux集群和自动化维1.5.2 利用tuning-primer脚本来调优MySQL数据库

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

1.5.2 利用tuning-primer脚本来调优MySQL数据库


MySQL在线上稳定运行一段时间后,就可以调用MySQL调优脚本tuning-primer.sh来检查参数的设置是否合理,该脚本的下载地址为:

http://www.day32.com/MySQL/tuning-primer.sh。

该脚本使用“SHOW STATUS LIKE…”和“SHOW VARIABLES LIKE…”命令获得MySQL的相关变量和运行状态。然后根据推荐的调优参数对当前的MySQL数据库进行测试。最后根据不同颜色的标识来提醒用户需要注意的各个参数设置。

当前版本会处理如下这些推荐的参数:

Slow Query Log(慢查询日志)

Max Connections(最大连接数)

Worker Threads(工作线程)

Key Buffer(Key缓冲)

Query Cache(查询缓存)

Sort Buffer(排序缓存)

Joins(连接)

Temp Tables(临时表)

Table(Open & Definition)Cache(表缓存)

Table Locking(表锁定)

Table Scans(read_buffer)(表扫描,读缓冲)

InnoDB Status(InnoDB状态)

笔者之前所在公司的主营业务是CPA电子广告平台,公司规模比较小,所以没有配备专业的MySQL DBA,线上的MySQL数据库(四核CPU)服务器问题比较多,用tuning-primer.sh脚本扫描后发现有如下问题:

MySQL数据库有时连接非常慢,严重时会被拖死。

通过show full processlist命令可以发现大量的“unauthenticated user”连接,数据库肯定每次都要响应,所以速度越来越慢,解决方法其实很简单:在mysql.cnf里添加skip-name-resolve,即不启用DNS反向解析。

发生这种情况的原因其实也很简单,MySQL的认证实际上是user+host的形式(也就是说user可以相同),所以MySQL在处理新连接时会试着去解析客户端连接的IP,启用参数skip-name-resolve后MySQL授权的时候就只能使用纯IP的形式了。

数据库在繁忙期间负载很大,长期达到了13,远远超过了系统平均负载4,这个肯定是不正常的。

通过脚本扫描,发现没有新建thread_cache_size,所以加上了thread_cache_size=256,然后重启数据库,数据库的平均负载一下子降到了5~6。

发现数据库里有张new_cheat_id表,读取很频繁,而且长期处于Sending data状态。

怀疑是磁盘I/O压力过大所致,所以操作如下:

explain SELECT count(new_cheat_id)  FROM new_cheat WHERE account_id = '14348612' AND offer_id = '689'\G;

显示结果如下所示:

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: new_cheat

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 2529529

        Extra: Using where

1 row in set (0.00 sec)

上面出现的这种问题很严重,new_cheat没有建好索引,导致每次都要全表扫描2 529 529行记录,严重消耗了服务器的I/O资源,所以立即建好索引,并用show index命令查看了表索引:

show index from new_cheat;

命令显示结果如下所示:

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

| Table     | Non_unique | Key_name   | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

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

| new_cheat |          0 | PRIMARY    |            1 | new_cheat_id | A         |     2577704 |     NULL | NULL   |      | BTREE      |         |

| new_cheat |          1 | ip         |            1 | ip           | A         |     1288852 |     NULL | NULL   |      | BTREE      |         |

| new_cheat |          1 | account_id |            1 | account_id   | A         |     1288852 |     NULL | NULL   |      | BTREE      |         |

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

3 rows in set (0.01 sec)

再来查看explain结果:

explain SELECT count(new_cheat_id)  FROM new_cheat WHERE account_id = '14348612' AND offer_id = '689'\G;

显示结果如下所示:

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: new_cheat

         type: ref

possible_keys: account_id

          key: account_id

      key_len: 4

          ref: const

         rows: 6

        Extra: Using where

1 row in set (0.00 sec)

大家可以发现,加好了索引后,此SQL通过account_id索引直接读取了6条记录(请对比关注rows这行)就获得了查询结果,系统负载由5~6直接降到了3.07~3.66了,这个负载还是能在可接受范围之内的。

MySQL的explain命令可用于SQL语句的查询执行计划(QEP)。这条命令的输出结果能够让我们了解MySQL 优化器是如何执行SQL 语句的。这条命令并没有提供任何调整建议,但它提供的重要信息能够帮助你做出调优决策。

最后要说明一点的是,对于网站来说,MySQL单机优化对整体性能提升的作用毕竟有限,尤其是在MySQL单机写入方面,如果在工作中遇到了那种对MySQL即时写入和读取速度要求很高的场景,建议大家可以多关注分布式的SQL解决方案,例如Hadoop的HBase和AWS的RedShift等分布式SQL系统。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
关系型数据库 MySQL Linux
在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾
以上就是在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾的步骤。这个过程就像是一场接力赛,数据从MySQL数据库中接力棒一样传递到备份文件,再从备份文件传递到其他服务器,最后再传递回MySQL数据库。这样,即使在灾难发生时,我们也可以快速恢复数据,保证业务的正常运行。
111 28
|
28天前
|
关系型数据库 MySQL Linux
查看Linux、Apache、MySQL、PHP版本的技巧
以上就是查看Linux、Apache、MySQL、PHP版本信息的方法。希望这些信息能帮助你更好地理解和使用你的LAMP技术栈。
80 17
|
20天前
|
Ubuntu 关系型数据库 Linux
Linux数据库安装
本文介绍了在CentOS 8.0和Ubuntu 22.04系统上安装、配置和启动MariaDB数据库服务器的详细步骤。包括通过`yum`和`apt`包管理器安装MariaDB服务,启动并检查服务运行状态,设置root用户密码以及连接数据库的基本操作。此外,还展示了如何在Ubuntu上更新软件包列表、安装依赖项,并验证MariaDB的版本和运行状态。通过这些步骤,用户可以成功部署并初始化MariaDB环境,为后续数据库管理与应用开发奠定基础。
37 4
|
29天前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
25天前
|
Oracle 关系型数据库 MySQL
Oracle linux 8 二进制安装 MySQL 8.4企业版
Oracle linux 8 二进制安装 MySQL 8.4企业版
55 1
|
29天前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
2月前
|
自然语言处理 数据库 iOS开发
DBeaver Ultimate Edtion 25.0 Multilingual (macOS, Linux, Windows) - 通用数据库工具
DBeaver Ultimate Edtion 25.0 Multilingual (macOS, Linux, Windows) - 通用数据库工具
141 12
DBeaver Ultimate Edtion 25.0 Multilingual (macOS, Linux, Windows) - 通用数据库工具
|
5月前
|
Python
自动化微信朋友圈:Python脚本实现自动发布动态
本文介绍如何使用Python脚本自动化发布微信朋友圈动态,节省手动输入的时间。主要依赖`pyautogui`、`time`、`pyperclip`等库,通过模拟鼠标和键盘操作实现自动发布。代码涵盖打开微信、定位朋友圈、准备输入框、模拟打字等功能。虽然该方法能提高效率,但需注意可能违反微信使用条款,存在风险。定期更新脚本以适应微信界面变化也很重要。
431 61
自动化微信朋友圈:Python脚本实现自动发布动态
|
2月前
|
SQL 负载均衡 关系型数据库
MySQL复制以及调优
本文介绍了MySQL自带复制方案的实现及其注意事项。复制方案能提供数据备份、负载均衡与分布式数据管理的优势。文章详细描述了复制步骤:主库(master)记录更改到二进制日志,发送同步消息给从库(slave),从库接收后将日志复制到本地并执行。实现复制包括配置主库的server-id和二进制日志、创建复制账号、初始化主库数据、设置从库参数及开启复制。此外,还探讨了三种日志格式(row、statement、mixed)的特点及选择建议,并分析了主从复制延迟的优化方法,如控制事务大小、优化日志传输和多线程还原日志等。最后,文中列出了搭建过程中需要注意的关键点。
|
2月前
|
关系型数据库 Shell 网络安全
定期备份数据库:基于 Shell 脚本的自动化方案
本篇文章分享一个简单的 Shell 脚本,用于定期备份 MySQL 数据库,并自动将备份传输到远程服务器,帮助防止数据丢失。