开发者社区> 神棍先生> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL Performance Tuning: Tips, Scripts and Tools

简介: 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.

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
TS入门篇 | 详解 TypeScript 数据类型(下)
一、简单基础类型 在说TypeScript数据类型之前,先来看看在TypeScript中定义数据类型的基本语法。 在语法层面,缺省类型注解的 TypeScript 与 JavaScript 完全一致。因此,可以把 TypeScript 代码的编写看作是为 JavaScript 代码添加类型注解。
83 0
TypeScript躬行记(1)——数据类型
  TypeScript不仅支持JavaScript所包含的数据类型,还额外扩展了许多实用的数据类型,例如枚举、空值、任意值等。
38 0
Drools决策表的使用
Drools决策表的使用
126 0
如何找到SAP ECC事务码升级到S4HANA后对应的新事务码
如何找到SAP ECC事务码升级到S4HANA后对应的新事务码
39 0
异地多活数据流基础设施DRC 双11支持571亿交易额背后的武器
本文将简要介绍我在QCON上做的DRC在异地双活的场景设计和应用分享,另外,我将介绍一下DRC在阿里云的使用场景。
1458 0
各个 C# 版本的主要特性、发布日期和发布方式(C# 1.0 - 7.3)
原文 各个 C# 版本的主要特性、发布日期和发布方式(C# 1.0 - 7.3) 本文收集各个 C# 版本的主要特性、发布日期和发布方式。 C# 8.0 尚在预览版本 C# 7.3 2018 年 5 月 随 Visual Studio 2017 v15.
1517 0
bbossgroups 2.0-RC版本中如何通过JGroups来实现集群节点间远程服务调用,或者多服务器之间远程服务调用
bbossgroups 2.0-RC中对jgroups已经升级到Jgroups 2.10.0版本,因此对aop中基于JGroups的rpc也做了相应的调整,本文详细讲解新的使用方法: 1.配置文件目录调整: jgroups本身协议配置文件和存放目录(tcp,udp) /bbossaop/resources/org/frameworkset/spi/jgroups/jgroups-tcp.
1095 0
+关注
313
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载