☰

如何使用 performance_sc 找出使用最多 CPU 的 mySQL 查询

tag-icon • tag-icon linux mysql redhat cpu-usage performance-tuning
如何使用 performance_sc 找出使用最多 CPU 的 mySQL 查询

52893 TID 在我的服务器中占用了 100% 的 CPU

如何找到与该 TID 对应的查询？对我来说，该表是空的

答案1

MySQL 和 MariaDB 可选择创建“slowlog”文件或表。

设置：

⚈  Set long_query_time = 1 -- Preferrable in my.cnf We may change that threshold up or down later, but this is a reasonable start.
⚈  Set up the slowlog to be captured to FILE.
⚈  Turn on (the details of this vary with the Version)
⚈  Wait at least 24 hours.

将 slow_log 写入文件 - 这是首选，因为有以下压缩工具：

   log_output = FILE
   slow_query_log = ON
   slow_query_log_file = (fullpath to some file)
   long_query_time = 1
   log_slow_admin_statements = ON
   log_queries_not_using_indexes = OFF

笔记：

⚈  log_output can be TABLE to write to mysql.slow_log, or FILE,TABLE to write both
⚈  slow_query_log_file has a default; is not needed for TABLE
⚈  long_query_time is a float, and can be as low as 0, but that gets verbose; default is a 'useless' 10
⚈  admin statements tend to be slow but infrequent
⚈  not_using_indexes is mostly clutter; simply worry about those that are slow
⚈  If running on a Slave, consider using log_slow_slave_statements
⚈  Beginning with 8.0.14, also have log_slow_extra = ON

其他选项（取决于版本；不完整）：

   log_slow_rate_limit=100
   log_slow_rate_type=query
   log_slow_verbosity=full
   slow_query_log_always_write_time=1
   slow_query_log_use_global_control=all
   innodb_monitor_enable=all
   userstat=1

为我收集结果（最好使用 FILE）：

⚈  Digest the results using either of these:

`pt-query-digest`  -- from Percona.com
`mysqldumpslow -s t`

⚈  Grab the first few (perhaps 5) queries. They will be sorted by (frequency * avg-time), which is the most useful.
⚈  Provide SHOW CREATE TABLE -- for each table
⚈  Provide EXPLAIN SELECT ... -- for each query

分析结果：通常（并非总是），我可以就加快每个查询提供具体的建议：

⚈  Add a particular index (often 'composite')
⚈  Reformulate the query (a simple case is not hiding an indexed column in a function; more complex involves adding/removing subqueries)
⚈  Recommend schema change
⚈  Possibly even an architectural change (EAV is a common problem)

答案2

SHOW FULL PROCESSLIST; 如果进程仍在运行，可能会更有用。将列出完整的查询。

相关内容