Mysql
幫我調整 my.conf MySQL
目前，我一直在四處尋找想法並複制一些發現的過程和細節，並在我的伺服器上玩耍，現在似乎相當不錯，但性能有些異常（並非總是如此）……如果你發現有問題我目前的配置可能會降低伺服器的性能而不是改進它，請告訴我並感謝您的閱讀和幫助
我附上了我使用的 conf 文件
你
虛擬化：vmware 作業系統：CentOS Linux 7 (Core) CPE 作業系統名稱：cpe:/o:centos:centos:7 核心：Linux 3.10.0-1127.19.1.el7.x86_64 架構：x86-64
資源
Ram 64GB ECC DDR3 - CPU 2 核 8 個邏輯處理器每核 = 16 - 磁碟 Nvme m.2 500GB 三星 980Pro
MySQL 8.0.22
[mysqld_safe] log_error = /var/log/mysql_error.log [mysqld] # datadir = {{ mysqldir }} datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock pid-file = /var/run/mysqld/mysqld.pid # Datadog general_log = ON general_log_file = /var/log/mysqld.log log_error = /var/log/mysql_error.log slow_query_log = ON slow_query_log_file = /var/log/mysql_slow.log long_query_time = 1 log_output = File log_queries_not_using_indexes = ON local-infile = ON character_set_server = utf8mb4 collation_server = utf8mb4_general_ci default_authentication_plugin = mysql_native_password skip-character-set-client-handshake # current configuration skip-name-resolve = 1 sql-mode = ALLOW_INVALID_DATES ssl-ca = ca.pem ssl-cert = server-cert.pem ssl-key = server-key.pem require_secure_transport = ON # general table_definition_cache = 400000 back_log = 3500 # tune max_seeks_for_key = 32 max_connections = 100 connect_timeout = 5 wait_timeout = 600 net_read_timeout = 60 max_allowed_packet = 160M thread_cache_size = 128 bulk_insert_buffer_size = 16M tmp_table_size = 16M max_heap_table_size = 16M sort_buffer_size = 4M read_buffer_size = 2M join_buffer_size = 512k read_rnd_buffer_size = 512K key_buffer_size = 6M myisam_recover_options = BACKUP myisam_sort_buffer_size = 16M table_open_cache = 400000 table_open_cache_instances = 8 concurrent_insert = 2 max_binlog_size = 100M thread_stack = 4M max_prepared_stmt_count = 1000000 performance_schema = ON binlog_expire_logs_seconds = 864000 innodb_log_files_in_group = 2 innodb_undo_log_truncate = OFF innodb_doublewrite = 1 innodb_flush_log_at_trx_commit = 0 innodb_max_dirty_pages_pct = 90 innodb_max_dirty_pages_pct_lwm = 10 innodb_page_cleaners = 4 innodb_adaptive_max_sleep_delay = 10000 innodb_lru_scan_depth = 128 innodb_io_capacity = 40000 innodb_io_capacity_max = 80000 innodb_purge_threads = 4 innodb_flushing_avg_loops = 4 innodb_thread_concurrency = 0 innodb_buffer_pool_instances = 32 innodb_log_file_size = 4G innodb_log_buffer_size = 3G innodb_buffer_pool_size = 30G innodb_change_buffer_max_size = 50 innodb_buffer_pool_chunk_size = 512M innodb_file_per_table = 1 innodb_open_files = 600 innodb_flush_method = O_DIRECT_NO_FSYNC innodb_flush_neighbors = 0 innodb_write_io_threads = 64 innodb_read_io_threads = 64 innodb_use_native_aio = 1 innodb_adaptive_hash_index = 0 innodb_stats_persistent = 1 innodb_adaptive_flushing = 1 innodb_monitor_enable = '%'更新
我一直在通過日誌文件和大量閱讀逐步評估我的流程和設置，到目前為止，這個設置執行得非常好，而且比以前的設置要快……現在插入、刪除和讀取從幾個小時開始甚至 1 分鐘沒有 CPU 峰值或 RAM 期待你們認為我可以調整的內容 - 謝謝
[mysqld_safe] log_error = /var/log/mysql_error.log [mysqld] # datadir = {{ mysqldir }} datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock pid-file = /var/run/mysqld/mysqld.pid # Datadog #general_log = ON #general_log_file = /var/log/mysqld.log log_error = /var/log/mysql_error.log slow_query_log = ON slow_query_log_file = /var/log/mysql_slow.log long_query_time = 1 log_output = File log_queries_not_using_indexes = ON local-infile = ON character_set_server = utf8mb4 collation_server = utf8mb4_general_ci default_authentication_plugin = mysql_native_password skip-character-set-client-handshake # current configuration skip-name-resolve = 1 sql-mode = ALLOW_INVALID_DATES ssl-ca = ca.pem ssl-cert = server-cert.pem ssl-key = server-key.pem require_secure_transport = ON # general #table_definition_cache = 400000 #back_log = 3500 # tune #max_seeks_for_key = 32 max_connections = 100 #connect_timeout = 5 #wait_timeout = 600 #net_read_timeout = 60 #max_allowed_packet = 160M #thread_cache_size = 128 bulk_insert_buffer_size = 256M tmpdir = /dev/shm tmp_table_size = 256M max_heap_table_size = 256M sort_buffer_size = 50M read_buffer_size = 4M join_buffer_size = 3M read_rnd_buffer_size = 8M innodb_sort_buffer_size = 50M key_buffer_size = 50M #myisam_recover_options = BACKUP #myisam_sort_buffer_size = 16M table_open_cache = 40000 #table_open_cache_instances = 8 #concurrent_insert = 2 #max_binlog_size = 100M #thread_stack = 4M #max_prepared_stmt_count = 1000000 performance_schema = 1 binlog_expire_logs_seconds = 864000 innodb_log_files_in_group = 2 #innodb_undo_log_truncate = OFF #innodb_doublewrite = 0 #innodb_flush_log_at_trx_commit = 0 #innodb_max_dirty_pages_pct = 90 #innodb_max_dirty_pages_pct_lwm = 10 #innodb_page_cleaners = 4 #innodb_adaptive_max_sleep_delay = 10000 innodb_lru_scan_depth = 5196 innodb_io_capacity = 1000 #innodb_io_capacity_max = 2000 #innodb_purge_threads = 4 #innodb_flushing_avg_loops = 4 innodb_thread_concurrency = 0 innodb_buffer_pool_instances = 2 innodb_log_file_size = 256M innodb_log_buffer_size = 32M innodb_buffer_pool_size = 2G #innodb_change_buffer_max_size = 50 #innodb_buffer_pool_chunk_size = 512M #innodb_file_per_table = 1 #innodb_open_files = 600 innodb_flush_method = O_DIRECT_NO_FSYNC #innodb_flush_neighbors = 0 innodb_write_io_threads = 16 innodb_read_io_threads = 16 #innodb_use_native_aio = 1 innodb_adaptive_hash_index = 1 #innodb_stats_persistent = 1 #innodb_adaptive_flushing = 1 innodb_monitor_enable = '%' #innodb_dedicated_server = ON
我有一個好消息要告訴你
方面#1
您可以只使用一個選項來調整 MySQL 8.0 的某些方面：innodb_dedicated_server。
innodb_dedicated_server選項將自動調整四 (4) 個東西
- 小於 1G：128M（innodb_dedicated_server 為 OFF 時的預設值）
- 小於或 = 4G：檢測到的物理 RAM * 0.5
- 大於 4G：檢測到的物理 RAM * 0.75
- 小於 1G：48M（innodb_dedicated_server 為 OFF 時的預設值）
- 小於4G：128M
- 小於<=8G：512M
- <=16G：1024M
- 大於16G：2G
該變數還設置以下內容：
- 如果該設置在系統上可用，則設置為 O_DIRECT_NO_FSYNC。
- 如果沒有，將其設置為預設的 InnoDB 刷新方法
innodb_log_file_in_group（從 MySQL 8.0.14 開始）：日誌組中的日誌文件數。InnoDB 以循環方式寫入文件。預設（和推薦）值為 2。文件的位置由 innodb_log_group_home_dir 指定。日誌文件的總大小 (innodb_log_file_size * innodb_log_files_in_group) 最高可達 512GB。
方面#2
您現在可以將innodb_read_io_threads和innodb_write_io_threads降低到 16。
您應該定期執行SHOW ENGINE INNODB STATUS \G
檢查表達式
Pending normal aio，你會看到類似Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,如果任何一個零經常變得非常高（如 16-32），則增加其中一個或兩個。
方面#3
您的innodb_log_buffer_size需要像 32M 而不是 3G。
方面#4
你的innodb_adaptive_hash_index應該啟用
方面#5
所有其他調整方面都與 MySQL 5.7 相同。您可以參考這個 Percona 部落格了解這些方面 => MySQL 5.7 Performance Tuning After Installation。您還可以參考 DBA StackExchange 中的其他文章。您絕對應該從 MySQL 文件中閱讀 innodb 的所有選項。
祝研究愉快！