Mysql性能优化工具 - tuner-primer使用介绍

Mysql数据库是Web应用程序最常用的数据库之一，但是如果在高负载下使用不当，可能会降低系统性能。因此，优化MySQL数据库服务器的配置至关重要。MySQL性能优化工具如tuner和primer是帮助管理员达到最大化服务器性能的好工具。本文将介绍tuner和primer的基本用法。

1. Mysql性能优化工具 - tuner

tuner是一个Shell脚本，它会从正在运行的MySQL实例中检索一些关键系统参数，然后输出一些建议的更改。tuner使用经验丰富的专业人员开发的一组标准分析规则，这些规则基于服务器规模、负载类型、硬件和其他因素。管理员不需要改变tuner的代码就能使用它。

安装tuner

• 下载tuner：

mkdir -p /opt/tuner && cd $_ && wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

• 更改脚本权限，使其可以执行：

chmod +x /opt/tuner/mysqltuner.pl

运行tuner

• 运行tuner：

cd /opt/tuner && ./mysqltuner.pl

• tuner将输出当前MySQL实例运行的统计信息和分析结果，如下所示：

>> MySQLTuner 1.7.17 - Major Hayden <[e-mail]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 10.3.29-MariaDB-0+deb10u1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ----------------------------------------------------------------------
[--] Log file: /var/log/mysql/mariadb-slow.log(0B)
[OK] Log file /var/log/mysql/mariadb-slow.log exists.
[OK] Log file /var/log/mysql/mariadb-slow.log is readable.
[OK] Log file /var/log/mysql/mariadb-slow.log is not empty.
[OK] Log file /var/log/mysql/mariadb-slow.log is smaller than 32 Mb.
[!!] /var/log/mysql/error.log : !!!!!!1MB (>= 1MB)Log file size seems excessive, should be limited to 25% of buffer pool size 
[!!] /var/lib/mysql/ibdata1: File exists but is not readable..

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +InnoDB +MEMORY +Aria
[--] Data in InnoDB tables: 164.2G (Tables: 484)
[--] Data in MEMORY tables: 2.9M (Tables: 11)
[--] Data in Aria tables: 16.0M (Tables: 10)
[OK] Total fragmented tables: 0

[OK] Up for 15:21:47 (23K q [0.002 qps], 5.2K conn, TX: 929M, RX: 5G)

[--] Uptime / reliability => 15:21:47 / 99%
[--] Uptime since flush_status was reset / Avg uptimes => 89,101s / 89,101s
[--] Total buffers: 5.0G global + 8.2M per thread (200 max threads)
[OK] Maximum reached memory usage: 6.6G (26.41% of installed RAM)
[OK] Maximum possible memory usage: 6.6G (26.41% of installed RAM)
[!!] Slow queries: 1% (273/23K)
[OK] Highest usage of available connections: 29% (59/200)
[OK] Aborted connections: 0.69%  (36/5190)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 34.5% (3K cached / 10K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3 sorts)
[!!] Joins performed without indexes: 19

• tuner所做的更改建议将显示在屏幕下方。

2. Mysql性能优化工具 - primer

primer是一个用PHP写的Web应用程序，它可以使用类似tuner的方式分析正在运行的MySQL实例，并提供建议。不同的是，primer将结果呈现为直观易懂的界面，更容易让管理员查看遗漏，因此，primer是一个不错的辅助选项。

安装primer

• 安装PHP-

apt-get install php-cli php-mysql

• 下载primer：

cd /var/www/html && git clone https://github.com/Grokzen/mysql-primer.git

• 更改MySQL管理员密码：
  在MySQL命令行界面下进行：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

• 配置该应用：

cd /var/www/html/mysql-primer/ && cp includes/config.sample.inc.php includes/config.inc.php

• 更改配置：
  将应用的MySQL凭证设置为刚刚更改的凭证，可以打开用文本编辑器打开配置文件，如vim：

vim /var/www/html/mysql-primer/includes/config.inc.php

## 修改以下几段
define("MYSQL_HOST", "localhost"); 
define("MYSQL_PORT", "3306"); 
define("MYSQL_USER", "root"); 
define("MYSQL_PASS", "新密码");

运行primer

• 打开primer：
  在Web浏览器中访问http://[服务器IP]/mysql-primer/，如http://127.0.0.1/mysql-primer/

• 输入MySQL凭证：
  在访问primer后，将弹出一个窗口，询问您的MySQL凭证。输入您最近更改的MySQL凭证，并单击“提交”。

• primer分析当前运行的MySQL实例：
  完成凭证验证后，primer将显示您的MySQL服务器的运行状态和性能建议列表。

示例

示例1

管理员运行tuner并遵循tuner提供的建议进行调整。tuner建议修改MySQL安装实例的InnoDB磁盘I/O设置，因此管理员在MySQL配置中添加以下行：

innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=4G
innodb_io_capacity=4000

示例2

管理员运行primer并遵循primer提供的建议进行调整。primer建议增加服务器的最大连接数，因此管理员通过以下方式调整：

• 在/etc/mysql/mysql.conf.d/mysqld.cnf中添加以下行：

max_connections=10000

• 重新启动MySQL：

sudo systemctl restart mysql