需要有關 my.cnf 的幫助：32GB RAM 和 3GB 的 InnoDB 表

我有一個 Joomla 應用程序，它一直在關閉我的 MySQL 實例。它使用過多的 CPU 並在不使用索引的情況下執行過多的查詢，查詢非常大的表並且正在使用未優化的查詢。

我昨天已將所有表轉換為 InnoDB，並根據 MySQLTuner 提出的建議在 my.cnf 中應用了一些優化，從那時起，在我看來，該站點更加穩定，CPU 密集度不高，MySQL 已經停止下降，並且該實例性能更高（直到該站點的使用者負載更多，但無論如何我仍然需要一些幫助來改進 my.cnf 文件並提高速度）。

使用 MyISAM 的站點還可以，因為它們很小，但我仍然需要InnoDB的幫助來提高速度。

我的.cnf

[mysqld]
innodb_file_per_table=1
default-storage-engine=InnoDB
max_allowed_packet=268435456
#open_files_limit=1024000

default-storage-engine=MyISAM
innodb_file_per_table=1

#thread_cache_size=5

table_definition_cache=1000
query_cache_size=128M
table_open_cache=300
connect_timeout=120

innodb-buffer-pool-size=7GB
innodb_buffer_pool_instances=7

performance_schema=1
query_cache_type=0 #seems to me thats ignoring this =0 and working anyway because Qcache is hitting etc

join_buffer_size=200M
tmp_table_size=100M
max_heap_table_size=100M
thread_cache_size=1
performance_schema=ON


max_connections=90

mysqlTuner.pl 結果

[root@hawk ~]# perl mysqltuner.pl 
>>  MySQLTuner 1.7.0 - Major Hayden <major@mhtx.net>


[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.23-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in MyISAM tables: 384M (Tables: 1262)
[--] Data in **InnoDB tables: 3G (Tables: 4043)**
[--] Data in MEMORY tables: 0B (Tables: 60)
[OK] Total fragmented tables: 0

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 8h 9m 18s (46M q [1K qps], 264K conn, TX: 137G, RX: 14G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 31.2G
[--] Max MySQL memory    : 25.4G
[--] Other process memory: 2.6G
[--] Total buffers: 7.5G global + 202.7M per thread (90 max threads)
[--] P_S Max memory usage: 85M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 25.6G (81.98% of installed RAM)
[OK] Maximum possible memory usage: 25.4G (81.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/46M)
[!!] Highest connection usage: 100%  (91/90)
[OK] Aborted connections: 0.28%  (744/264918)
[!!] 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: 44.6% (35M cached / 79M selects)
[!!] Query cache prunes per day: 737807
[OK] Sorts requiring temporary tables: 0% (7 temp sorts / 780K sorts)
[!!] Joins performed without indexes: 179795
[!!] Temporary tables created on disk: 56% (1M on disk / 2M total)
[!!] Thread cache hit rate: 49% (134K created / 264K connections)
[!!] Table cache hit rate: 0% (300 open / 75K opened)
[OK] Open file limit used: 1% (15/1K)
[OK] Table locks acquired immediately: 100% (20M immediate / 20M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 85.1M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.1.23-MariaDB)


-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 7.0G/4.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.33928571428571 %): 48.0M * 2/7.0G should be equal 25%
[OK] InnoDB buffer pool instances: 7
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (17217820855 hits/ 17217969376 total)
[OK] InnoDB Write log efficiency: 90.75% (2413696 hits/ 2659622 total)
[OK] InnoDB log waits: 0.00% (0 waits / 245926 writes)


-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
   Control warning line(s) into /var/lib/mysql/hawk.sobrehost.com.br.err file
   Control error line(s) into /var/lib/mysql/hawk.sobrehost.com.br.err file
   Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
   Restrict Host for user@% to user@SpecificDNSorIp
   MySQL started within last 24 hours - recommendations may be inaccurate
   Reduce or eliminate persistent connections to reduce connection usage
   Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
   Increasing the query_cache size over 128M may reduce performance
   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 which have no LIMIT clause
   Increase table_open_cache gradually to avoid file descriptor limits
   Read this before increasing table_open_cache over 64
   Beware that open_files_limit (1024) variable 
   should be greater than table_open_cache (300)
   Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
   max_connections (> 90)
   wait_timeout (< 28800)
   interactive_timeout (< 28800)
   query_cache_type (=0)
   query_cache_size (> 128M) [see warning above]
   join_buffer_size (> 200.0M, or always use indexes with joins)
   tmp_table_size (> 100M)
   max_heap_table_size (> 100M)
   thread_cache_size (> 1)
   table_open_cache (> 300)
   innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=3G) if possible.

查詢記憶體

MariaDB [(none)]>  SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10442    |
| Qcache_free_memory      | 74619672 |
| Qcache_hits             | 40528186 |
| Qcache_inserts          | 8696587  |
| Qcache_lowmem_prunes    | 277279   |
| Qcache_not_cached       | 929978   |
| Qcache_queries_in_cache | 12458    |
| Qcache_total_blocks     | 36968    |
+-------------------------+----------+
8 rows in set (0.00 sec)

謝謝。

“你無法擺脫性能問題”。話雖如此，這裡有一些調整建議，然後是解決您面臨的問題的其他方法。

“執行緒記憶體命中率：49%（創建 134K / 264K 連接）”和thread_cache_size=1– 如果 *nix，設置為 10。否則程序創建可能會很明顯。

“最高連接使用率：100% (91/90)”——MySQL 之外的東西導致了這種情況。你有連接池嗎？Web 伺服器是否已調整為具有太多連接？什麼網路伺服器？

“每天查詢記憶體修剪次數：737807”——即每秒幾次！這是昂貴的，尤其是query_cache_size在 128M 的情況下。建議降低到50M。更好的是，如果可能的話，更改為並DEMAIND使用適合您的. 通常一個應用程序有一些可記憶體的查詢和許多不可記憶體的查詢。後者使 QC 雜亂無章，但沒有提供任何好處。SQL_CACHE``SQL_NO_CACHE``SELECTs

每當發生任何寫入時都會發生 QC“修剪” ——它會刪除所涉及表的所有QC 條目。因此，QC 越大，所需的時間越長。因此，我建議縮小尺寸。

其餘設置似乎合理。

不要擔心 mysqlTuner 的其余建議。

更多關於轉換為 InnoDB 的技巧在這裡。

不要使用 MyISAM；它正在消失。而且，幾乎在所有情況下，InnoDB 都可以執行得更快。

假設 3GB 包括數據和索引，如通過 所見SHOW TABLE STATUS，緩衝池的 7G 表示在 I/O可能成為問題之前您有足夠的增長空間。

“讓 Mysql 一直停機的 Joomla 應用程序”——MySQL（甚至使用 MyISAM）可能會停止、掛起等，但我懷疑它是否每次都“崩潰”。

“過多的 CPU 使用和過多的沒有索引的查詢、非常大的表和未優化的查詢”——找到最差的查詢之一，提供EXPLAIN SELECT ...，並為所SHOW CREATE TABLE涉及的每個表​​提供。解決方案可能就像添加“複合”索引一樣簡單。而且，由於索引在 MyISAM 和 InnoDB 中的工作方式不同，這需要（至少）在轉換為 InnoDB 之後完成。（開始一個新問題，以便我們可以專注於該查詢。）

我猜你想確保 my.cnf 設置是否正確，沒有任何錯誤或錯誤的設置。是的，有道理。Rick 還添加了一些要點來幫助您根據伺服器配置 my.cnf，這是可以遵循的良好實踐。

但我認為這個問題是。

嘿，我有一個問題，我需要一個解決方案！！！

如果實際問題得到解決，我想您可能已經找到了解決方案。

MySQL 在過去 24 小時內啟動 - 建議可能不准確

如果您已經看到調諧器免責聲明，它可能是不准確的，因為輸出不知道更好的統計歷史記錄以確保統計數據是正確的。

讓我們採取一些小步驟來看看它是否有幫助：

1. 數據庫重新啟動是突然的，但它一直執行良好，直到上次（XDATE），或者您可能會在 Mysql 圖表中看到統計資訊（如果您有的話）並查看此重新啟動何時開始。指出那個日期，看看是否有任何部署在那個日期被推送。並嘗試看看是否可以回滾。如果它可以繼續進行，然後繼續監控。
2. 看看是什麼原因導致重啟？是因為最大連接數嗎？或者一個特定的查詢正在執行？
3. 如果很難確定確切的原因。嘗試將讀者和作者分開。如果應用程序允許，則寫入主設備並讀取從設備。我假設它是一個框架。不確定它是否對您來說是一個簡單的選擇。但值得一試。
4. 但是一旦（3）完成後，您將對大師有清晰的認識
5. 可以說重啟仍然存在於 Master 上。查看您在哪個小時重新啟動。如果查詢相同並且並發是瓶頸。然後嘗試閱讀並查看這些變數的組合是否設置正確。

innodb_thread_concurrency

innodb_flush_neighbors -> 如果 SSD 不應該啟用

innodb_log_file_size -> 按推薦配置

innodb_io_capacity -> 值得驗證磁碟 IO，看看 IO 等待磁碟尋軌是否正常。嘗試查看其在 Mysql 上的突發 IOP 是否因伺服器上給定的容量而結束。

innodb_flush_log_at_trx_commit -> 如果一致性是一種權衡，您可以將其設置為 0/2。但是，如果您需要一致性，那麼您需要確保在每次送出時都將磁碟 IOP 分配給突發 fsync。推薦的是1。

6. 可以說重新啟動在從屬設備上。我懷疑很多。然後開始調整查詢以選擇索引或在需要時創建索引。架構中的任何更改都應在 Master 上執行，以便架構可以保持一致。
7. 當你確定你已經調整了 slave 上的查詢時。您可以每週在較小的查詢集中放回 Master。如果您不想，請刪除奴隸。

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