MySQL Performance Tuning: Tips, Scripts and Tools

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: With MySQL, common configuration mistakes can cause serious performance problems.

With MySQL, common configuration mistakes can cause serious performance problems. In fact, if you mis-configure just one of the many config parameters, it can cripple performance! (see examples) Of course, the performance of MySQL is often tied greatly to the efficiency of your MySQL queries. It’s important to ensure that your performance issues are not due to poorly written MySQL queries. You can use MySQL’s slow query log, log_queries_not_using_indexes or APM tools which offer MySQL performance monitoring such as AppdynamicsPanoptaSematextDatadogInstrumentalOracle (MySQL Enterprise)and other APM tools.

MySQL tuning is quite an expansive topic. I won’t try to place any recommended config lines, values or settings here. Be wary, very wary of those. This post assumes that you’ve already optimized your queries and seek guidance with selecting the best performance config options (my.cnf) for MySQL. This can vary greatly case by case as there’s no one-size-fits-all advice. Therefore, also included below, are additional links to popular free MySQL tuning scripts and tools.

Popular scripts and tools available for MySQL performance tuning.CLICK TO TWEET

Stay up to date with the latest MySQL server versions

With each new version of MySQL released, there’s substantial performance and feature enhancements over previous versions. So the most important advice would be to upgrade, upgrade, upgrade. Have a look at some version performance comparisons here.

If you are seeking additional features or flexibility you may already be using MariaDB, which is an enhanced drop-in replacement for MySQL Server. Or, you may prefer Percona’s flavor of MySQL which is another improved compatible version of MySQL to consider. If you have seen notable improvements in using MariaDB or Percona over stock MySQL please share your experience below. I view them all as great options. I’ve used mostly MySQL and MariaDB and don’t have much experience with Percona but much like MariaDB I’ve have heard good things.

Related:   Tuning MySQL: my.cnf, avoid this common pitfall!

 

MySQL Performance Tuning Advice

Before continuing please have a look at the following MySQL performance tuning articles: Tuning MySQL: my.cnf, avoid this common pitfall! and Avoid This When Tuning MySQL Query Cache for Performance.

Other than the tuning scripts listed below, try to avoid online advice unless its via mysql.com* OR references directly Oracle’s MySQL documentation. You will notice that both blog posts linked to just above references and/or quotes MySQL’s docs. The bottom line is, there’s a ton of conflicting advice and opinions online. I guess my advice here should be to always crosscheck your config changes with MySQL’s official documentation. That includes everything I say here. In fact, when venturing to change MySQL’s defaults, unless you can bet your house or car on your proposed changes, its best to leave it as is. When there’s doubt… stick with defaults. As for changes, always base your changes on benchmarks, comparisons and time-tested firsthand data.

 

Selecting MySQL Storage Engine

This is simple, use InnoDB and avoid MyISAM when possible. For these reasons:

  • InnoDB has better crash recovery.
  • InnoDB has row-level locking, MyISAM can only do full table-level locking.
  • Like MyISAM, InnoDB now has FULLTEXT search indexes as of MySQL 5.6
  • InnoDB supports transactions, foreign keys and relationship constraints, MyISAM does not.
Related:   3rd DDoS attack today, beginning of a new era

 

MySQL Performance Tuning Scripts

You cannot replace Professional MySQL tuning with scripts. Scripts serve as basic guides, sometimes spot on, but most times loose guides which will only solve the most grievous mis-configured parameters. Use them as a starting point. Meaning, before you contact a professional to tune MySQL use these tuning scripts so that at the very least you don’t have any so-called embarrassing config in your my.cnf file. For example, join_buffer_size set to 4GB when the total DB size is less than 1GB.

Now, lets looks at popular scripts and tools available for MySQL performance tuning: MySqlTuner, Tuning-Primer, MySQLreport, phpMyAdmin Advisor and Percona Configuration Wizard for MySQL.

 

MySQLTuner

A script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

Mysqltuner Screenshot from 2017-07-18 13-58-17

MySQLTuner is maintained and indicator collect is increasing week after week supporting a lot of configurations such as Galera Cluster, TokuDB, Performance schema, Linux OS metrics, InnoDB, MyISAM, Aria, etc. – MySQLTuner on Github.

 

Tuning-Primer

This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…” to produce sane recomendations for tuning server variables. It is compatable with all versions of MySQL 3.23 and higher (including 5.1).

Tuning-Primer Screenshot from 2017-07-18 14-11-52

The original script is no longer updated. I’ve been using this Tuning-primer version on Github which fully supports MariaDB.

 

phpMyAdmin Advisor

The Advisor system provides recommendations on server variables by analyzing MySQL status variables.

Related:   Monitoring PHP Performance and Diagnosing Bottlenecks

phpMyAdmin Advisor

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. Visit: phpmyadmin.

 

Mysqlreport from Percona’s Toolkit

Mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.

mysqlreport percona Screenshot from 2017-07-18 14-53-15

The Guide To Understanding mysqlreport (PDF) | Percona acquired Mysqlreport and you can find it as part of Percona’s Toolkit.

 

Percona Configuration Wizard for MySQL

From their website: “Apply Percona best practices to achieve better MySQL database performance and avoid the time, complexity, and risk of customizing a my.cnf configuration on your own. Simply copy and paste the results of the Percona Configuration Wizard for MySQL into your my.cnf file. Tens of thousands of MySQL users have already used this tool to improve their MySQL performance. When you complete the wizard, your configuration files are saved for future use and you can easily share them with colleagues. Registration is required but your information will not be shared with third parties.”

Percona Tools for MySQL Percona Configuration Wizard for MySQL_

Percona Configuration Wizard for Mysql  – a tool to help you generate my.cnf files based on your server’s hardware and other characteristics.

There are also a few free real-time monitoring tools such as innotopmytop (old), mtop (older). Fork anyone? Have a look also at Percona Monitoring and Management (free), Monyog (not free) or the other paid tools mentioned at the top.

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
JavaScript 关系型数据库 MySQL
MySQL · myrocks · 相关tools介绍
概述 MyRocks提供了丰富的tools,如sst_dump, mysql_ldb等,这些工具对我们的运维和分析问题非常有用。 sst_dump 可以导出sst中的数据和属性信息。 sst_dump --help sst_dump --file=<data_dir_OR_sst_file> .
3357 0
|
8天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
85 0
|
10天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误(errno: 39)
docker MySQL删除数据库时的错误(errno: 39)
67 0
|
3天前
|
存储 SQL 关系型数据库
【MySQL】数据库基础 -- 详解
【MySQL】数据库基础 -- 详解
|
10天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
51 0
|
10天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(上)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
55 0
|
1天前
|
关系型数据库 分布式数据库 数据库
【阿里云云原生专栏】云原生时代的数据库选型:阿里云RDS与PolarDB对比分析
【5月更文挑战第24天】阿里云提供RDS和PolarDB两种数据库服务。RDS是高性能的在线关系型数据库,支持MySQL等引擎,适合中小规模需求;而PolarDB是分布式数据库,具备高扩展性和性能,适用于大规模数据和高并发场景。RDS与PolarDB在架构、性能、弹性伸缩、成本等方面存在差异,开发者应根据具体需求选择。示例代码展示了如何通过CLI创建RDS和PolarDB实例。
181 0
|
2天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
22 2
|
3天前
|
存储 数据可视化 关系型数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库
|
3天前
|
关系型数据库 MySQL 数据库
如何在MySQL中查看已创建的数据库列表?
【5月更文挑战第22天】如何在MySQL中查看已创建的数据库列表?
11 1