優化 MySQL 伺服器性能
我在 Vultr 有一個具有 8GB Ram 和 4vCPU 的 VPS 伺服器，我希望獲得優化的 mysql 設置。我有大約 170 個 InnoDB 表的數據庫，但它們大多不包含大量數據。有 2 個 MyISAM 表，但是每個表有 150k 行，我想增加到 350k。
這裡的挑戰是我在表格中使用長/緯度數據進行地理計算，每次計算都必須經過完整的 150k 行，確定哪些是最近的（5 英里內）距離，而不是只給出該數據. 這顯然非常耗資源。因此，從 150k 行增加到 200k 行意味著查詢需要多花 1 秒，而增加到 350k 行每個查詢需要額外 3-4 秒。在這個階段我不能更新查詢本身，但只能盡可能地改進 mysql。
我已經開始更新我的 MySQL 設置並大大提高了性能。以下是tuning-primer 的輸出：
MySQL 版本 5.5.55-0ubuntu0.14.04.1 x86_64
正常執行時間 = 2 天 12 小時 1 分 56 秒 qps = 61 總問題 = 2665033 已連接執行緒 = 3
SLOW QUERIES 慢查詢日誌未啟用。目前 long_query_time = 10.000000 秒。2665054 中有 0 個耗時超過 10.000000 秒。完成您的 long_query_time 似乎很好
BINARY UPDATE LOG 未啟用二進制更新日誌。您將無法進行時間點恢復參見http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
工作執行緒 目前 thread_cache_size = 8 目前thread_cached = 5 目前threads_per_sec = 0 歷史thread_per_sec = 0 您的thread_cache_size 沒問題
MAX CONNECTIONS 目前 max_connections = 151 目前threads_connected = 3 Historic max_used_connections = 9 使用的連接數是配置最大值的5%。您使用的 max_connections 不到 10%。降低 max_connections 有助於避免過度分配記憶體請參閱“記憶體使用”部分以確保您沒有過度分配
INNODB STATUS 目前 InnoDB 索引空間 = 35 M 目前 InnoDB 數據空間 = 56 M 目前 InnoDB 緩衝池空閒 = 25 % 目前 innodb_buffer_pool_size = 128 M 根據您的 innodb 索引佔用的空間，將這個值增加到最多可能是安全的系統總記憶體的 2 / 3
記憶體使用最大記憶體分配：4.48 G 配置的最大每執行緒緩衝區：1.53 G 配置的最大全域緩衝區：4.39 G 配置的最大記憶體限制：5.92 G 物理記憶體：7.79 G 最大記憶體限制似乎在可接受的規范范圍內
KEY BUFFER 目前 MyISAM 索引空間 = 79 M 目前 key_buffer_size = 256 M 密鑰記憶體未命中率為 1 : 9866 Key buffer free ratio = 79 % 您的 key_buffer_size 似乎沒問題
QUERY CACHE 查詢記憶體已啟用 目前 query_cache_size = 4.00 G 目前 query_cache_used = 889 M 目前 query_cache_limit = 4 M 目前查詢記憶體 記憶體填充率 = 21.71 % 目前 query_cache_min_res_unit = 4 K 您的 query_cache_size 似乎太高了。也許您可以在其他地方使用這些資源 MySQL 不會記憶體大於 query_cache_limit 大小的查詢結果
排序操作 目前 sort_buffer_size = 2 M 目前 read_rnd_buffer_size = 256 K 排序緩衝區似乎沒問題
JOINS 目前 join_buffer_size = 4.00 M 您有 72592 個查詢，其中連接無法正確使用索引 join_buffer_size >= 4 M 不建議您啟用“log-queries-not-using-indexes” 然後在慢查詢日誌。
OPEN FILES LIMIT 目前 open_files_limit = 2209 個文件 如果您使用大量 MyISAM，open_files_limit 通常應設置為 table_cache 的至少 2x-3x。您的 open_files_limit 值似乎很好
TABLE CACHE 目前 table_open_cache = 1024 個表 目前 table_definition_cache = 1024 個表 您總共有 702 個表 您有 1024 個打開的表。目前 table_cache 命中率為 55% ，而 100% 的表記憶體正在使用中 您可能應該增加您的 table_cache
TEMP TABLES 目前 max_heap_table_size = 16 M 目前 tmp_table_size = 16 M 在 162009 個臨時表中，1% 是在磁碟上創建的 創建的磁碟 tmp 表比率似乎很好
TABLE SCANS 目前 read_buffer_size = 3 M 目前表掃描比率 = 66078 : 1 read_buffer_size 似乎沒問題
TABLE LOCKING Current Lock Wait ratio = 1 : 696683 您的表鎖定似乎沒問題
謝謝你的幫助。如果您需要更多資訊，請詢問！升級到 mysql 5.6 或 5.7 有幫助嗎？
解決 lat/lng 性能問題的第 1 步：
INDEX(latitude), INDEX(longitude)如果這不能充分發揮作用，請參閱http://mysql.rjweb.org/doc.php/latlng
SHOW CREATE TABLE如需更多討論，請提供SELECT正在執行表掃描的內容。
innodb_buffer_pool_size = 128 M– 最重要的性能調整是將其設置為可用RAM 的 70%（假設所有表都是 InnoDB）。為什麼你還有 2 個 MyISAM 表？讓我們談談轉換它們。
query_cache_size = 4.00 G——這真的很糟糕。不要將其設置為超過 50M ！
- 讀取性能主要取決於對錶有正確的索引和正確設計的查詢。沒有替代品。
- 本文提供了一些優化 InnoDB 性能的指針 https://www.percona.com/blog/2007/11/01/innodb-performance-optimization-basics/
寫入性能在很大程度上取決於這兩種設置（使用這些設置可以更快地寫入，但有一個與第一個有關的問題。您將在文章中閱讀）。
set global innodb_flush_log_at_trx_commit=2 set global general-log=0