釋放 MySQL 記憶體和性能調整
我是 MySQL 數據庫的新手。我最近安裝了 MySQL 5.7 並配置了 my.ini 文件。請在下面找到該文件的副本。
伺服器是 MySQL 數據庫的專用伺服器，伺服器的配置是：
- 作業系統 = Windows 8.1（64 位），
- RAM = 16GB（可以自動擴展至 32GB），
- 處理器 = Intel Xenon 2.30 GHz（4 個處理器），
- MySQL 數據庫的大小 = 大約。100GB，
- 並發連接數 = 最多 100 個
我的問題是：
- 我最初為 Innodb 緩衝池分配了 12GB RAM。我最多可以為 Innodb 緩衝池分配 24GB，這樣 MySQL 可以在負載過重的情況下使用額外的記憶體。我希望 MySQL 在完成查詢執行/程序後釋放額外的記憶體。我們有什麼辦法可以做到嗎？
- 如果任何變數配置錯誤，我們是否需要在 my.ini 文件中進行任何更改並提高數據庫的性能？
我的.ini 文件：
character-set-server=utf8, default-storage-engine=INNODB, sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION", max_connections=301, query_cache_size=0, table_open_cache=2000, tmp_table_size=333M, thread_cache_size=10, myisam_max_sort_file_size=100G, myisam_sort_buffer_size=4G, key_buffer_size=8M, read_buffer_size=64K, read_rnd_buffer_size=256K, innodb_flush_log_at_trx_commit=1, innodb_log_buffer_size=16M, innodb_buffer_pool_size=12G, innodb_io_capacity = 2000, innodb_read_io_threads = 64, innodb_log_file_size=128M, innodb_thread_concurrency=0, innodb_write_io_threads = 64, innodb_autoextend_increment=64, innodb_buffer_pool_instances=8, innodb_concurrency_tickets=5000, innodb_old_blocks_time=1000, innodb_open_files=300, innodb_stats_on_metadata=0, innodb_file_per_table=1, innodb_checksum_algorithm=0, back_log=80, flush_time=0, join_buffer_size=256K, max_allowed_packet=4M, max_connect_errors=100, open_files_limit=4161, query_cache_type=0, sort_buffer_size=256K, table_definition_cache=1400, binlog_row_event_max_size=8K, sync_master_info=10000, sync_relay_log=10000, sync_relay_log_info=10000, explicit_defaults_for_timestamp=1, innodb_lock_wait_timeout=120, transaction-isolation=READ-COMMITTED, innodb_support_xa=0, autocommit=1, event_scheduler=ON更新：
我們在云網路上。伺服器上的預設 RAM 為 16 GB。可以根據作業系統負載自動增加（最多 32 GB RAM）或減少 RAM。
例如，如果我為
INNODB_BUFFER_POOL_SIZEMySQL 伺服器分配 20GB 記憶體，那麼在重負載/挑選時間期間最多可以使用 20GB 記憶體。但是，我希望 MySQL 在非挑選時間釋放最多 16GB 的記憶體。因此，我們不能對 20GB RAM 收費（即在非挑選時間額外 4GB RAM）。
MySQL 在使用後不會釋放全域緩衝區（
innodb_buffer_pool、key_buffer_size、query_cache_size等）。它將釋放每個執行緒緩衝區（, 等）佔用的記憶體join_buffer_size，sort_buffer_size但僅線上程被刪除後而不是在thread_cache.
innodb_buffer_pool_size可以在 5.7 中動態調整，但這是一個非常侵入性的操作：啟動調整大小操作時，直到所有活動事務都完成後操作才會開始。一旦調整大小操作正在進行，需要訪問緩衝池的新事務和操作必須等到調整大小操作完成。此規則的例外情況是，在對緩衝池進行碎片整理並在操作期間撤回頁面以減小緩衝池大小時，允許並發訪問緩衝池。允許並發訪問的一個缺點是它可能導致在頁面被撤回時可用頁面暫時短缺。
額外的緩衝池值得嗎？此查詢將使您了解InnoDB 緩衝池將使用的目前最大記憶體：
SELECT ENGINE, SUM(DATA_LENGTH)/1024/1024/1024 as DATA_LENGTH_IN_GB, SUM(INDEX_LENGTH)/1024/1024/1024 AS INDEX_LENGTH_IN_GB, SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as TOTAL_IN_GB FROM information_schema.TABLES WHERE ENGINE IN ("InnoDB") AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY ENGINE ORDER BY DATA_LENGTH DESC;但是，這是數據+索引的總 InnoDB 大小。工作集是高峰期最重要的，因此這將包括您最活躍的表/行。如果您的工作集已經適合記憶體，那麼分配更多不會為您帶來太多收益。如果它不適合記憶體，那麼無論高峰/非高峰流量如何，您都需要增加緩衝池。
我建議您閱讀InnoDB 緩衝池的 MySQL 文件的內部詳細資訊部分，作為一個很好的入門讀物。
至於你的其餘配置，我唯一要說的是，你
temp_table_size=333M沒有按照你的想法做，而且可能太高了。記憶體中臨時表的最大大小是 tmp_table_size 和 max_heap_table_size 值中的最小值。
這意味著，在
max_heap_table_size預設為 16MB 的情況下，內部記憶體表將在達到 330MB 之前很久就轉換為磁碟上的臨時文件。其次，您可能已經增加
temp_table_size以避免臨時磁碟表的使用。但是，在某些情況下 MySQL 永遠無法使用記憶體中的臨時表：某些情況會阻止使用記憶體中的臨時表，在這種情況下，伺服器會使用磁碟表來代替：
- 表中存在 BLOB 或 TEXT 列
- GROUP BY 或 DISTINCT 子句中存在大於 512 個字節（對於二進製字元串）或 512 個字元（對於非二進製字元串）的字元串列。（在 MySQL 5.7.3 之前，無論字元串類型如何，限制都是 512 字節。）
- 如果使用 UNION 或 UNION ALL，則 SELECT 列表中存在最大長度大於 512（二進製字元串為字節，非二進製字元串為字元）的任何字元串列
- SHOW COLUMNS 和 DESCRIBE 語句使用 BLOB 作為某些列的類型，因此用於結果的臨時表是磁碟表。
我建議將
temp_table_sizeand設置max_heap_table_size為 32M 或 64M（最大），然後檢查上述某些條件的查詢。如果您無法避免使用磁碟臨時表，希望您的雲提供商將其tmpdir放在 SSD 上。
正如其他人已經回答了您的第一個問題，我將回答第二個問題：
如果任何變數配置錯誤，我們是否需要在 my.ini 文件中進行任何更改並提高數據庫的性能？
在我看來**，您更改了太多設置而沒有任何好處**。在某些情況下，您還將值設置為預設值，這很好 - 但僅繼承預設值可能是有益的，這樣如果它發生更改，您可以在升級時利用。
規格：
tmp_table_size=333M看起來太高了（預設更好）thread_cache_size=10看起來太低（預設更好）innodb_checksum_algorithm=0並不完全安全innodb_lock_wait_timeout=120高於預設值。大多數人將其更改為低於預設值。innodb_support_xa=0已棄用，不應在 MySQL 5.6+ 中使用