Mysql

單個查詢的高 CPU 使用率

  • November 1, 2016

我最近從標準共享主機遷移到 Digital Ocean。

雖然我非常享受這種體驗——尤其是學習管理一些（相對）大型數據庫——但我遇到了一個主要問題。

我在 Digital Ocean 上的 Droplet 有 2GB RAM 和 2 個 CPU。我在伺服器上執行的唯一東西是一個相當低調的 PHP 應用程序。但是，此應用程序中的一個頁麵包含一個相當廣泛的查詢，目前返回大約 10k 行。涉及一些連接（並非所有連接都已編入索引）。

每當我點擊此頁面時，載入需要 60 秒。Mysql 的 CPU 使用率飆升至伺服器最大值： 在此處輸入圖像描述

我使用了 SQL Tuner，將表轉換為 InnoDB（根據我閱讀的建議，儘管我最多有 25 個並髮使用者），對 my.cnf 進行了大量更改並進行了測試，並花費了大量時間嘗試來破譯這個問題。但是，我仍然得到相同的結果（以及一些沮喪的使用者）。

這個問題100%是查詢的錯嗎？或者，我是否可以對 my.cnf （或其他任何地方）進行進一步的更改，以幫助減少 CPU 負載並提高載入速度？

感謝您的幫助！

MySQL 版本資訊： 在此處輸入圖像描述

my.cnf，包括 Percona 生成的部分和 SQL Tuner 提出的建議：

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
default_storage_engine         = InnoDB
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 50
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 2048

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 250M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 1000M
join_buffer_size = 250M

# LOGGING #
log_error                      = /var/log/mysql/error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log
long_query_time                = 1


#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

編輯 - 事後看來……我假設它是這個生成的查詢。在 PHPMyAdmin 中執行需要 42 秒。

SELECT  x.*,
if(fielding_rating_pos2=0,'-',if(TRUNCATE((fielding_rating_pos2+40)/40,
                       0)<1,1,
                                       0)))) as fielding_rating_pos2, if(TRUNCATE((fielding_rating_pos2+40
 )/40,
               0)>5, 5,TRUNCATE((fielding_rating_pos2+40)/40,
if(fielding_rating_pos3=0,'-',if(TRUNCATE((fielding_rating_pos3+40)/40,
                       0)<1,1,
                                       0)))) as fielding_rating_pos3, if(fielding_rating_pos4=0,
'-',
if(TRUNCATE((fielding_rating_pos3+40)/40,0)>5, 5,TRUNCATE((fielding_rating_pos3+40)/40,
       0
 )<1,1,
       0)))) as fielding_rating_pos4, if(TRUNCATE((fielding_rating_pos4+40)/40,
if(fielding_rating_pos5=0,'-',if(TRUNCATE((fielding_rating_pos5+40)/40,
       0)<1,1,
0
 )>5, 5,
0)))) as fielding_rating_pos5, if(fielding_rating_pos6=0,'-',
if(TRUNCATE((fielding_rating_pos6+40)/40, 0)<1,1,if(TRUNCATE((fielding_rating_pos6+40)/40,
0
 )>5,
5,TRUNCATE((fielding_rating_pos6+40)/40,0)))) as fielding_rating_pos6,
TRUNCATE((fielding_rating_pos4+40)/40, 5,TRUNCATE((fielding_rating_pos5+40)/40,
if(fielding_rating_pos7=0,'-',if(TRUNCATE((fielding_rating_pos7+40)/40,
0)<1,1,
0)))) as fielding_rating_pos7, if(TRUNCATE((fielding_rating_pos5+40
 )/40,
0
 )>5,
'-',
0)>5, 5,TRUNCATE((fielding_rating_pos8+40)/40,0)))) as fielding_rating_pos8,
if(fielding_rating_pos9=0,'-',if(TRUNCATE((fielding_rating_pos9+40)/40,
if(TRUNCATE((fielding_rating_pos7+40)/40,0)>5, 5,TRUNCATE((fielding_rating_pos7+40)/40,
0)<1,1,
0)))) as fielding_rating_pos9, if(TRUNCATE((fielding_rating_pos4+40)/40,
if(fielding_rating_pos8=0, if(fielding_ratings_catcher_arm=0,
'-',
0
 )<1,1,
0)>5,5,TRUNCATE((fielding_ratings_catcher_arm+40)/40, 0)))) as fielding_ratings_catcher_arm,
if(fielding_ratings_infield_arm=0, '-',if(TRUNCATE((fielding_ratings_infield_arm+40)/40,
if(TRUNCATE((fielding_rating_pos9+40)/40,0)>5, 5,TRUNCATE((fielding_rating_pos9+40)/40,
0)<1,1,if(TRUNCATE((fielding_ratings_infield_arm+40)/40, 0)>5,
5,TRUNCATE((fielding_ratings_infield_arm+40)/40,0)))) as fielding_ratings_infield_arm,
if(fielding_ratings_outfield_arm=0,'-',if(TRUNCATE((fielding_ratings_outfield_arm+40)/40,
0)<1,1,if(TRUNCATE((fielding_ratings_outfield_arm+40)/40,0)>5,
5,TRUNCATE((fielding_ratings_outfield_arm+40)/40,0)))
 ) as fielding_ratings_outfield_arm FROM  players_fielding as pf,
if(TRUNCATE((fielding_ratings_catcher_arm+40)/40, if(TRUNCATE((fielding_rating_pos8+40)/40,
   ( if(TRUNCATE((fielding_rating_pos8+40)/40, 0)<1,1,if(TRUNCATE((fielding_ratings_catcher_arm+40)/40,
       SELECT  px.player_id,first_name,last_name,position,role,age,porder,
               slot,batting_ratings_talent_contact,batting_ratings_talent_gap,
               batting_ratings_talent_power,batting_ratings_talent_eye,
               batting_ratings_talent_strikeouts,running_ratings_speed,
       running_ratings_stealing,
               if(TRUNCATE((batting_ratings_talent_contact+40)/40, 0)<1,
               1,
                               0)>5,5,TRUNCATE((batting_ratings_talent_contact+40)/40,
                               0))) as batting_ratings_talent_contact,if(TRUNCATE((batting_ratings_talent_gap+40
                 )/40,
       0)<1,1,
       0)>5,5,TRUNCATE((batting_ratings_talent_gap+40)/40,0))
   ) as batting_ratings_talent_gap,
if(TRUNCATE((batting_ratings_talent_power+40)/40,0)<1, 1,if(TRUNCATE((batting_ratings_talent_power+40)/40,
0)>5, running_ratings_baserunning FROM ( SELECT  player_id, 5,
TRUNCATE((batting_ratings_talent_power+40)/40,0))) as batting_ratings_talent_power,
if(TRUNCATE((batting_ratings_talent_eye+40)/40,0)<1,1, if(TRUNCATE((batting_ratings_talent_contact+40)/40,
       if(TRUNCATE((batting_ratings_talent_eye+40)/40,0)>5,5, TRUNCATE((batting_ratings_talent_eye+40)/40,
                               0))) as batting_ratings_talent_eye, if(TRUNCATE((batting_ratings_talent_strikeouts+40)/40,
                                       if(TRUNCATE((batting_ratings_talent_gap+40)/40,
                               0)<1,1,
                                                       0)>5,5,TRUNCATE((batting_ratings_talent_strikeouts+40)/40,
                                               0))) as batting_ratings_talent_strikeouts FROM  players_batting) as ps RIGHT JOIN ( SELECT  first_name,
last_name,position,role,age,
0)<1,1,
0))) as running_ratings_speed, if(TRUNCATE((batting_ratings_talent_strikeouts+40
 )/40,
if(TRUNCATE((running_ratings_stealing+40)/40,0)<1,1,if(TRUNCATE((running_ratings_stealing+40)/40,
       0)>5,5,TRUNCATE((running_ratings_stealing+40)/40,0))
 ) as running_ratings_stealing,
       if(TRUNCATE((running_ratings_baserunning+40)/40,0)<1,
if(TRUNCATE((running_ratings_speed+40)/40, 1,if(TRUNCATE((running_ratings_baserunning+40)/40,
                       0
                         )>5,
               5,
                               0))
 ) as running_ratings_baserunning, pp.* FROM  players as p,
                   ( SELECT  dp2.* FROM if(TRUNCATE((running_ratings_speed+40)/40,
               0
         )>5, 5,
       SELECT  dp1.player_id,
               porder) as porder, slot FROM TRUNCATE((running_ratings_speed+40)/40,
                   ( TRUNCATE((running_ratings_baserunning+40)/40, ( if(porder IS NULL,
                                               999999, SELECT  dp.player_id,slot FROM  ootp_sql_draft_pool as dp LEFT JOIN  amateur_draft as ad  ON dp.player_id=ad.player_id
                                           WHERE  dp.league_id=101
                                             AND  ad.player_id IS NULL) as dp1
                                   LEFT JOIN  ootp_sql_draft_list as dl  ON dp1.player_id=dl.player_id
                                   WHERE  human_manager_id IN (3,-999,NULL)
                                   ORDER BY  porder) as dp2
                           GROUP BY  player_id) as pp
                   WHERE  p.player_id=pp.player_id
                     AND  position!=1) as px  ON ps.player_id=px.player_id
                         ) as x
   WHERE  pf.player_id=x.player_id
   ORDER BY  batting_ratings_talent_contact DESC,batting_ratings_talent_power DESC,
       batting_ratings_talent_eye DESC,last_name,first_name
   LIMIT  0,100

我建議在參與JOIN條件和WHERE子句的列上添加索引。

索引旨在幫助查詢優化器做出更好的選擇。除非您對不應該唯一的列強制執行唯一性，否則您的索引只會有所幫助。

也就是說，您可能希望在索引中強制應該是唯一的列的唯一性，這對優化器有更大的幫助。

請記住，索引（以及使用這些索引的統計資訊）需要定期維護，並且它們會在表更新時添加額外的寫入（少量成本）。這通常比執行查詢所花費的時間要少得多。

引用自：https://dba.stackexchange.com/questions/153803

相關問答

Mysql

為什麼這個 blob 在用作連接字元串而不是準備好的語句時會保存

  • November 20, 2018
檢視問答
Mysql

在 PHP MySQL 中，如何求和或不求和多個列？

  • September 30, 2018
檢視問答
Mysql

連接錯誤 (2054) - mysql_old_password 問題仍未解決

  • May 30, 2018
檢視問答
Mysql

如何為一條記錄顯示多個名稱但不重複記錄？

  • June 28, 2015
檢視問答
Mysql

group_concat 中的重複值

  • July 11, 2014
檢視問答
Mysql

在 SQL 查詢中或通過程式語言使用字元串函式和算術計算？

  • January 14, 2014
檢視問答