MySQL 數據庫的平均負載非常高

October 10, 2014

我有一個響應我的客戶端應用程序的後端伺服器，它每秒有超過 20k~25k 次命中，這使我的伺服器負載平均非常高，我相信我的 MySQL 配置不合適。

另外，我們的後端系統自帶記憶體系統，但是有些查詢不能記憶體，我的數據庫有26張表（全部用InnoDB引擎），最大的表有60萬行左右。

我的伺服器規格

Processor Name  Common KVM processor
Vendor ID   GenuineIntel
CPU Core: DualCore + 2 thread core
Processor Speed (MHz)   3192.746
Total Memory    5991576 kB
Free Memory 206468 kB
Total Swap Memory   4685816 kB
Free Swap Memory    4516588 kB
Apache 2.2.25   Running
MySQL 5.6.17    Running

作業系統

CentOS release 6.3 (Final)
Kernel \r on an \m

目前 my.cnf 配置

[mysqld]
#------ MySQL Base Config --------
port = 9032
#------ InnoDB Config -----------
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_fast_shutdown=0
innodb_log_buffer_size=256M
innodb_log_file_size=512M
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances=2
query_cache_size=0
#------ Log Slow Queries ---------
#long_query_time=2
#slow_query_log=1
#slow_query_log_file=/etc/mysql_log_files/log-slow-queries.log

MySQL 調諧器結果

&gt;&gt;  MySQLTuner 1.3.0 - Major Hayden &lt;major@mhtx.net&gt;
&gt;&gt;  Bug reports, feature requests, and downloads at http://mysqltuner.com/
&gt;&gt;  Run with '--help' for additional options and output filtering
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
[OK] Currently running supported MySQL version 5.6.17
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: Warning: Using a password on the command line interface can be insecure.
+ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
[--] Data in InnoDB tables: 196M (Tables: 37)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 12

-------- Security Recommendations  -------------------------------------------
Warning: Using a password on the command line interface can be insecure.
[OK] All database users have passwords assigned
Warning: Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 38m 40s (2M q [109.747 qps], 633K conn, TX: 2B, RX: 2B)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 416.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 585.9M (10% of installed RAM)
[OK] Slow queries: 0% (881/2M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/97.0K
[OK] Key buffer hit rate: 100.0% (291K cached / 0 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (30 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 678
[!!] Temporary tables created on disk: 49% (284K on disk / 575K total)
[OK] Thread cache hit rate: 84% (97K created / 633K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 0% (20/5K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[!!] InnoDB  buffer pool / data size: 128.0M/196.4M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
   Run OPTIMIZE TABLE to defragment tables for better performance
   MySQL started within last 24 hours - recommendations may be inaccurate
   Reduce or eliminate persistent connections to reduce connection usage
   Adjust your join queries to always utilize indexes
   When making adjustments, make tmp_table_size/max_heap_table_size equal
   Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
   max_connections (&gt; 151)
   wait_timeout (&lt; 28800)
   interactive_timeout (&lt; 28800)
   query_cache_size (&gt;= 8M)
   join_buffer_size (&gt; 256.0K, or always use indexes with joins)
   tmp_table_size (&gt; 16M)
   max_heap_table_size (&gt; 16M)
   innodb_buffer_pool_size (&gt;= 196M)

通過 top 命令載入平均

top - 21:30:55 up  5:31,  1 user,  load average: 113.62, 46.11, 28.78
Tasks: 602 total,  47 running, 555 sleeping,   0 stopped,   0 zombie
Cpu(s): 84.0%us,  5.1%sy,  0.0%ni,  0.0%id,  0.0%wa,  4.5%hi,  5.0%si,  1.4%st
Mem:   5991576k total,  5872992k used,   118584k free,    42204k buffers
Swap:  4685816k total,   170160k used,  4515656k free,  2420740k cached

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
1858 mysql     20   0 3317m 780m 5408 S 115.6 13.3 186:43.13 mysqld
2661 apache    20   0  151m  15m 4944 S  0.9  0.3   0:09.48 httpd
7521 apache    20   0  149m  12m 4964 R  0.9  0.2   0:03.38 httpd
8115 apache    20   0  151m  14m 4544 R  0.9  0.3   0:02.71 httpd
9529 apache    20   0  149m  11m 3960 S  0.9  0.2   0:00.47 httpd
9748 apache    20   0  149m  10m 3880 S  0.9  0.2   0:00.27 httpd
9770 apache    20   0  149m  10m 3908 S  0.9  0.2   0:00.22 httpd
9844 apache    20   0  151m  13m 4128 R  0.9  0.2   0:00.29 httpd
  11 root      20   0     0    0    0 S  0.7  0.0   4:11.22 events/0
3363 apache    20   0  150m  13m 4496 R  0.7  0.2   0:06.18 httpd
6869 apache    20   0  150m  14m 5276 S  0.7  0.2   0:04.88 httpd
6899 apache    20   0  149m  12m 4568 R  0.7  0.2   0:04.57 httpd
7428 apache    20   0  150m  13m 4784 R  0.7  0.2   0:03.37 httpd
7904 apache    20   0  151m  13m 4480 R  0.7  0.2   0:02.15 httpd
8125 apache    20   0  152m  15m 4620 S  0.7  0.3   0:02.43 httpd
8150 apache    20   0  152m  15m 4232 S  0.7  0.3   0:02.71 httpd

關於如何降低這個高負載平均值的任何建議或想法？

PS 我肯定會關閉每個連接，但我不知道為什麼 MySQL 調諧器顯示我的連接使用率最高！

提高性能通常不是一勞永逸的解決方案。但是查看您的 conf 和 mysql Tuner 的結果，您可以執行以下操作
將 innodb_buffer_pool_size 增加很多。這對於 InnoDB 表通常非常重要。在 InnoDB 的 cnf 文件中進一步設置以下兩個參數
sort_buffer_size=2M
join_buffer_size=2M
您可能需要稍微調整這些值並增加它。
確保您的所有連接語句都使用索引，並且由於您使用的是 InnoDB，因此如果您在適當的情況下使用 FOREIGN KEY 約束（MySQL 自動為此創建一個索引）會更好。還要檢查你的慢查詢日誌，看看是否有任何其他查詢需要一些額外的索引。使用 EXPLAIN 幫助進行任何優化
查詢記憶體。如果您使用的是 MyISAM 表，那將是確定的，但使用 InnoDB 會更複雜一些。這完全取決於您通過交易更新數據的頻率。你可以在這裡閱讀更多。您仍然更了解您的應用程序，因此這可能是您想要探索的途徑。

觀察#1
回顧一下你的top輸出
Cpu(s): 84.0%us,  5.1%sy,  0.0%ni,  0.0%id,  0.0%wa,  4.5%hi,  5.0%si,  1.4%st
看到最後一個狀態變數了1.4%st嗎？那是什麼？
根據Linux 中的“top”命令，我們是什麼，sy、ni、id、wa、hi、si 和 st（用於 CPU 使用率）？
st，“竊取時間”，僅與虛擬化環境相關。它表示目前虛擬機無法使用真實 CPU 的時間 - 它被虛擬機管理程序從該虛擬機“偷走”（執行另一個虛擬機，或出於自己的需要）。
觀察#2
您在問題中的 InnoDB 設置將適合裸機或良好的 VM。鑑於前面提到的竊取，您可以縮減其中的一些設置。
由於mysqltuner.pl輸出說明了這一點
[!!] InnoDB  buffer pool / data size: 128.0M/196.4M
你有這麼少的 InnoDB 數據（196.4M）。因此，您不需要更大的緩衝池。
請看一下 InnoDB 架構
當緩衝池為 128M 時，日誌緩衝區是兩倍大 (256M) 哎喲！！！每次日誌緩衝區滿時，它都會刷新到兩個重做日誌文件（ib_logfile0 和 ib_logfile1）中。
建議
由於您只有 6GB 的 RAM，我建議您進行以下設置
innodb_buffer_pool_size=256M
innodb_buffer_pool_instances=1
innodb_log_file_size=64M
innodb_log_buffer_size=32M
innodb_read_io_threads=8
innodb_write_io_threads=8
可選：我會讓虛擬機有 8G 記憶體而不是 6G
可選：您需要具有更多 IOP 的更好 VM。
試一試！！！
更新 2014-10-10 14:24 EDT
再拿一個
Temporary tables created on disk: 49% (284K on disk / 575K total)
這通常是以下情況的有力指標
增加 tmp_table_size
增加 max_heap_table_size
由於您只有 6GB 或 RAM，或者即使您有 8GB，您也應該不理會它們。您應該做的是尋找可以從附加索引中受益的查詢。
請參閱我的文章，了解有關正確索引查詢的各種原因和場景
Oct 17, 2012:合併索引中的列
Jan 05, 2012:我應該如何設計友誼的關係表？
Aug 15, 2011:你如何使用目前的伺服器資源來提高 MySQL 的性能？
Jul 22, 2011:防止複製到臨時表 (sql)

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

MySQL 數據庫的平均負載非常高

觀察#1

觀察#2

建議

試一試！！！

更新 2014-10-10 14:24 EDT

相關問答

用於提高 MySQL (5.7) 性能的多種模式

將 MyISAM 改為 InnoDB，性能損失巨大

批量刪除後是否需要重新索引 mysql 表？

MySQL InnoDB 事務狀態中的“清理”是什麼意思以及如何防止它？

低記憶體使用高 CPU mysqld

為 32GB 混合 nginx/php/mysql 伺服器優化 Percona 5.7 my.cnf

MySQL 數據庫的平均負載非常高

觀察#1

觀察#2

建議

試一試 ！！！

更新 2014-10-10 14:24 EDT

相關問答

用於提高 MySQL (5.7) 性能的多種模式

將 MyISAM 改為 InnoDB，性能損失巨大

批量刪除後是否需要重新索引 mysql 表？

MySQL InnoDB 事務狀態中的“清理”是什麼意思以及如何防止它？

低記憶體使用高 CPU mysqld

為 32GB 混合 nginx/php/mysql 伺服器優化 Percona 5.7 my.cnf

試一試！！！