20、MySQL性能慢排查

使用profile工具排查语句

MySQL Profile是一种MySQL SQL查询性能优化工具，它可以帮助用户定位和解决MySQL数据库性能问题。此工具可以帮助用户分析查询的性能，并确定哪些查询需要进一步优化。MySQL Profile可以运行在基于Web的管理中心(例如phpMyAdmin或Adminer)或者作为MySQL命令行客户端的插件。通过使用MySQL Profile，用户可以：

1、追踪查询的执行时间和执行流程，以识别缓慢的部分。

2、分析查询执行计划，以确定数据库是如何查询的。

3、识别查询需要优化的部分。

4、生成报告和视觉化分析，以帮助理解查询性能。

5、监视长时间运行或高负载查询，以及其他资源问题，以及确定需要加强或缩减的部分。

#打开后，会显示语句执行详细的过程
set profiling = ON
#查看语句,注意结果中的query_id值
show profiles ;

MySQL的查询分析器可以记录并提供多种查询性能信息，这些信息对于优化查询执行计划非常有用。以下是MySQL查询分析器记录的主要查询性能信息。
Query_ID：每个查询在查询分析器中都有一个唯一的ID。
Duration：查询执行的总时间，包括解析、编译、优化和执行查询本身所需的时间。
Query：执行的查询语句。
Status：查询的执行状态，例如查询成功或失败。
Rows_sent：查询返回的行数。
Rows_examined：查询中检查的行数。
Rows_affected：查询所影响的行数。
tmp_table：MySQL是否需要使用临时表来完成查询，以及临时表的使用时间和大小。
Lock_time：在执行查询时花费的时间等待锁定。
Rows_read：查询操作所使用的磁盘I/O次数。
Rows_sent：查询返回的数据量在网络上传输所花费的时间。

#显示语句的详细执行步骤和时长
Show profile for query #

#显示cpu使用情况
Show profile cpu for query #

Query_ID：查询的ID。
Duration：查询总时间(单位：秒)。
Query：查询语句。
User_time：查询执行期间花费的用户模式CPU时间(单位：秒)。
System_time：查询执行期间花费的系统模式CPU时间(单位：秒)。
Total_cpu_time：查询执行期间花费的总CPU时间(单位：秒)。
Profile_timestamp：查询性能信息的时间戳。

SHOW PROFILE ALL FOR QUERY query_id：显示命令详细操作过程中的所有性能分析器信息，包括读取和获取数据等。

SHOW PROFILE BLOCK IO FOR QUERY query_id：显示块IO操作的性能分析器信息，例如磁盘读取和写入。

SHOW PROFILE MEMORY FOR QUERY query_id：显示查询的内存使用情况，例如内存分配、释放和重用。

mysql cpu使用率过高排查

1、使用show full processlist 列出所有线程查看慢查询

2、.查询是否有锁问题

show OPEN TABLES where In_use > 0;

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

3、开启慢查询日志

slow_query_log=ON

Mysql 内存使用率过高排查

如果您的MySQL内存使用率高，请按照以下步骤进行排查：

1、确认MySQL优化器参数是否正确 - 查看MySQL配置文件中的my.cnf，查看以下参数设置是否正确：

innodb_buffer_pool_size

query_cache_size

table_open_cache

这些参数应该适当配置，符合您的服务器的硬件规格和数据库负载。

2、检查MySQL进程使用的内存 - 在Linux上，可以通过以下命令查看进程使用的内存：

ps aux | grep mysql

您可以计算RES列的总和，该列显示进程使用的物理内存。

3、检查是否有其他应用程序占用了过多的内存 - 您可能会发现其他应用程序使用了很多内存，从而导致MySQL无法获得足够的内存。您可以通过以下命令检查：

top

此命令将显示服务器上所有进程的性能指标。

4、检查是否有长时间运行的查询 - 长时间运行的查询可能占用了太多的MySQL内存，导致内存使用率过高。您可以通过执行以下查询来查找长时间运行的查询：

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC;

此查询将显示正在运行的所有MySQL进程。

5、检查是否需要优化查询 - 如果您的数据库表很大，或者您的应用程序经常执行大量查询，则可能需要优化查询以提高性能和减少内存使用。您可以使用MySQL Profiler等工具来分析查询并进行优化。

pidstat工具使用

pidstat是一个Linux系统性能分析工具，用于监视进程的CPU使用，内存使用和IO等指标。以下是pidstat的使用示例：

pidstat -u：显示CPU使用情况，包括处理器时间占用率、用户CPU 占用率和内核CPU占用率等信息

pidstat -r：显示内存使用情况，包括进程常驻集、共享内存、虚拟内存和物理内存等指标

pidstat -d：显示磁盘 IO使用情况，包括磁盘读写速率、IOPS和服务时间等信息

pidstat -t：显示每个线程的CPU和内存使用情况

pidstat -p [pid]：指定要监视的进程ID