配置 MySQL 以最小化文件鎖
*** 編輯 *** 8GB 記憶體
顯示變數：https ://pastebin.com/ANAiq1E2
顯示全球狀態：https ://pastebin.com/pwWKRiie
結束編輯
首先，我不是 DBA，所以我可能忽略了一些簡單的事情。
背景故事，我們目前在 VPS 上託管我們所有的客戶帳戶。VPS 有 42 個可用核心、8GB 記憶體和 SSD 儲存。我們的大多數數據庫都來自帶有十幾個 Symfony 應用程序的 CMS（Joomla 或 WordPress）。目前的表數剛剛超過 15K，數據約為 4.2GB。最初的問題始於頁面一次無響應幾秒鐘。在深入研究日誌後，我發現了多個類似這樣的錯誤
2020-11-27T19:50:35.218190Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5187ms. The settings might not be optimal. (flushed=8 and evicted=0, during the time.) 2020-11-27T20:17:32.699558Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4027ms. The settings might not be optimal. (flushed=14 and evicted=0, during the time.)沖洗時間通常高達 20 秒。我開始研究修復它並最終將所有表移動到 InnoDB，增加了 innodb 緩衝池，增加了表記憶體大小，增加了表打開記憶體，並增加了文件限制。這導致文件鎖定錯誤。
2020-11-27T16:49:38.736243Z 378 [ERROR] InnoDB: Unable to lock ./sedashoa_feb2718/zys4l_easyblog_featured.ibd error: 37 2020-11-27 10:49:38 0x7f6009133700 InnoDB: Assertion failure in thread 140050445842176 in file fil0fil.cc line 906我浪費了很多時間來調整設置，因為我第一次就這些問題聯繫我的託管服務提供商時，他們似乎認為這是 MySQL 記憶體使用問題。在將所有內容調整到最大 MySQL 記憶體使用量低於 3GB 並提高表打開記憶體後，我開始收到相同的錯誤：37 崩潰。這一次，當我聯繫託管支持時，他們告訴我我達到了文件操作限制。所以我將表打開記憶體降低到 1000。
我的問題是我應該如何配置 MySQL 以充分利用可用資源？我已經知道我們需要轉移到專用主機或像 Digital Ocean 提供的託管 SQL 伺服器。同時，我應該如何調整以充分利用可用的 ram（圖 4GB 僅用於 MySQL）而不會遇到文件鎖定限制。這是目前的配置。
# Default options are read from the following files in the given order: # /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf #[mysql] #port = 3306 #socket = /var/run/mysqld/mysqld.sock [mysqld] # Required Settings basedir = /usr bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections datadir = /var/lib/mysql default-time-zone = '-5:00' max_allowed_packet = 32M # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet max_connect_errors = 1000000 performance-schema = 1 # turn performance schema on pid_file = /var/run/mysqld/mysqld.pid port = 3306 skip_external_locking #skip_name_resolve #socket = /var/run/mysqld/mysqld.sock # Enable for b/c with databases created in older MySQL/MariaDB versions (e.g. when using null dates) sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES tmpdir = /tmp user = mysql # InnoDB Settings default_storage_engine = InnoDB innodb_buffer_pool_instances = 2 # Use 1 instance per 1GB of InnoDB pool size innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM (working off 4GB or half VM RAM) innodb_lru_scan_depth = 256 # Default 1024 https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 1 # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit innodb_flush_method = O_DIRECT # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method innodb_log_buffer_size = 16M # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_buffer_size innodb_log_file_size = 256M innodb_stats_on_metadata = 0 # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata innodb_use_native_aio = 0 # MySQL will not start with native asynchronous file access enabled #innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file innodb_thread_concurrency = 16 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better # contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check # the overall load produced by MySQL/MariaDB. innodb_read_io_threads = 8 innodb_write_io_threads = 8 # MyISAM Settings - disable cache # query_cache_limit = 4M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x query_cache_size = 0 # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x query_cache_type = 0 # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x key_buffer_size = 10M # UPD low_priority_updates = 1 # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_low_priority_updates concurrent_insert = 2 # Same as auto https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_concurrent_insert # Connection Settings max_connections = 25 # UPD max_user_connections = 24 back_log = 60 thread_cache_size = 100 # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_cache_size thread_stack = 256K # Default value for 64 bit platforms See: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_stack interactive_timeout = 180 wait_timeout = 60 # For MySQL 5.7+ only (disabled by default) #max_execution_time = 30000 # Set a timeout limit for SELECT statements (value in milliseconds). # This option may be useful to address aggressive crawling on large sites, # but it can also cause issues (e.g. with backups). So use with extreme caution and test! # More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time # For MariaDB 10.1.1+ only (disabled by default) #max_statement_time = 30 # The equivalent of "max_execution_time" in MySQL 5.7+ (set above) # The variable is of type double, thus you can use subsecond timeout. # For example you can use value 0.01 for 10 milliseconds timeout. # More info at: https://mariadb.com/kb/en/aborting-statements/ # Buffer Settings join_buffer_size = 512K # UPD read_buffer_size = 1M # UPD read_rnd_buffer_size = 512K # UPD sort_buffer_size = 2M # UPD # Table Settings # In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit # to be overriden (also see comment next to open_files_limit). # E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html # and for MariaDB check: https://mariadb.com/kb/en/library/systemd/ # ***Non crashing, but with slow cache flush *** table_definition_cache = 20000 # UPD https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_definition_cache table_open_cache = 1000 # UPD Total tables on 2020-11-24=15,617 https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_open_cache open_files_limit = 2000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's # open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf # In systemd managed systems this limit must also be set in: # /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and # /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB) # ***Crashes because of VPS op limit *** #table_definition_cache = 20000 #table_open_cache = 20000 #open_files_limit = 40000 # max_heap_table_size and tmp_table_size should be changed together and kept equal max_heap_table_size = 16M tmp_table_size = 16M # Search Settings ft_min_word_len = 3 # Minimum length of words to be indexed for search results # Logging log_error = /var/lib/mysql/mysql_error.log log_queries_not_using_indexes = 1 long_query_time = 5 slow_query_log = 0 # Disabled for production slow_query_log_file = /var/lib/mysql/mysql_slow.log [mysqldump] # Variable reference # For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html # For MariaDB: https://mariadb.com/kb/en/library/mysqldump/ quick quote_names max_allowed_packet = 64M任何想法或建議將不勝感激。
全球狀態和變數分析：
觀察：
- 版本：5.7.32
- 8 GB 記憶體
- 正常執行時間 = 2d 20:53:10
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎完全（或大部分）執行 InnoDB。
更重要的問題：
table_open_cache = 5000（現在1000）
innodb_flush_neighbors = 0
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
max_connections是 25 歲。你已經成功了。例如，將其增加到 100。並調查為什麼會出現如此多的同時連接。
thread_cache_size不應大於max_connections，並且可以更低。啟用慢日誌並擁有
long_query_time=1. 它應該可以幫助您找到低效的查詢。見http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog細節和其他觀察：
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((10M / 0.20 + 2048M / 0.70)) / 8192M = 36.3%– 大部分可用的 ram 應可用於記憶體。– http://mysql.rjweb.org/doc.php/memory
( Opened_tables ) = 5,271,412 / 247990 = 21 /sec– 打開表的頻率 – 增加 table_open_cache (現在 1000)
( Table_open_cache_overflows ) = 5,269,803 / 247990 = 21 /sec– 可能需要增加table_open_cache（現在1000）
( Table_open_cache_misses ) = 5,271,405 / 247990 = 21 /sec– 可能需要增加table_open_cache（現在1000）
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 5,271,405 / (23504604 + 5271405) = 18.3%– table_open_cache 的有效性。– 增加 table_open_cache（現在 1000）並檢查 table_open_cache_instances（現在 16）。
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 36,493 / 131056 = 27.8%– 目前未使用的 buffer_pool 的 Pct – innodb_buffer_pool_size（現在為 2147483648）是否大於所需？
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10– 容量：max/plain – 推薦 2. Max 應該大約等於您的 I/O 子系統可以處理的 IOP。（如果驅動器類型未知，2000/200 可能是合理的一對。）
( innodb_flush_neighbors ) = 1– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。
( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( max_connections ) = 25– 最大連接數（執行緒）。影響各種分配。– 如果 max_connections（現在是 25）太高並且各種記憶體設置太高，您可能會用完 RAM。
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 8192M = 23.3%– FULLTEXT 結果集的字節限制。（可能沒有預先分配，但會增長？） – 降低設置。
( character_set_server ) = character_set_server = latin1– 將 character_set_server（現在是 latin1）設置為 utf8mb4 可以幫助解決字元集問題。那是未來的預設值。
( (Com_show_create_table + Com_show_fields) / Questions ) = (0 + 652913) / 23566357 = 2.8%– 頑皮的框架 – 花費大量精力重新發現模式。– 向第 3 方供應商投訴。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( Created_tmp_disk_tables ) = 1,341,546 / 247990 = 5.4 /sec– 作為複雜 SELECT 的一部分創建磁碟“臨時”表的頻率 – 增加 tmp_table_size（現在為 16777216）和 max_heap_table_size（現在為 16777216）。檢查何時使用 MEMORY 而不是 MyISAM 的臨時表規則。也許較小的模式或查詢更改可以避免 MyISAM。更好的索引和查詢的重新制定更有可能有所幫助。
( Created_tmp_disk_tables / Questions ) = 1,341,546 / 23566357 = 5.7%– 需要磁碟 tmp 表的查詢的百分比。– 更好的索引/沒有斑點/等等。
( Created_tmp_disk_tables / Created_tmp_tables ) = 1,341,546 / 1531462 = 87.6%– 溢出到磁碟的臨時表的百分比 – 可能增加 tmp_table_size（現在為 16777216）和 max_heap_table_size（現在為 16777216）；改進指標；避免斑點等
( Select_scan ) = 4,480,575 / 247990 = 18 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( Select_scan / Com_select ) = 4,480,575 / 18327133 = 24.4%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢
( expire_logs_days ) = 0– 多久自動清除 binlog（經過這麼多天）。被 binlog_expire_logs_seconds 取代。– 太大（或為零）= 消耗磁碟空間；太小 = 需要快速響應網路/機器崩潰。（如果 log_bin（現在關閉）=關閉，則不相關）
( innodb_autoinc_lock_mode ) = 1– Galera: 慾望 2 – 2 = “interleaved”; 1 =“連續”是典型的；0 =“傳統”。– 加萊拉的願望 2；2 需要 BINLOG_FORMAT=ROW 或 MIXED
( slow_query_log ) = slow_query_log = OFF– 是否記錄慢查詢。(5.1.12)
( long_query_time ) = 5– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF– (5.6.11, 5.7.1) 預設情況下，複製的語句不會出現在慢日誌中；這導致他們顯示。– 在慢日誌中查看可能干擾副本讀取的寫入會很有幫助。
( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON– 是否在慢日誌中包含此類。– 這會使慢日誌變得混亂；把它關掉，這樣你就可以看到真正的慢查詢。並減少 long_query_time（現在為 5）以擷取最有趣的查詢。
( back_log ) = 60– （自 5.6.6 起自動調整大小；基於 max_connections） – 提高到 min(150, max_connections (現在為 25)) 在進行大量連接時可能會有所幫助。
( Max_used_connections / max_connections ) = 26 / 25 = 104.0%– 連接的峰值百分比 – 增加 max_connections（現在 25）和/或減少 wait_timeout（現在 180）
( max_connect_errors ) = 1,000,000 = 1.0e+6– 對黑客的小保護。——也許不超過200。
( thread_cache_size / Max_used_connections ) = 100 / 26 = 384.6%
- 讓執行緒記憶體大於您可能的連接數沒有任何優勢。浪費空間是缺點。
異常小：
Open_files = 0 interactive_timeout = 180 wait_timeout = 180異常大：
Com_check = 1.4 /HR Com_delete_multi = 16 /HR Com_rename_table = 0.32 /HR Com_show_databases = 6.5 /HR Com_show_fields = 2.6 /sec Com_show_open_tables = 0.029 /HR Com_show_profile = 9 /HR Com_show_profiles = 0.3 /HR Com_show_status = 1.9 /sec Com_stmt_send_long_data = 24 /HR Open_table_definitions = 15,451 Performance_schema_file_instances_lost = 514 back_log / max_connections = 240.0% max_user_connections = 2.15e+9 table_definition_cache = 20,000 table_open_cache / max_connections = 40異常字元串：
ft_boolean_syntax = + -><()~*: ft_min_word_len = 3 innodb_fast_shutdown = 1 innodb_use_native_aio = OFF low_priority_updates = ON optimizer_trace = enabled=off,one_line=off optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN time_zone = -05:00
每秒速率 = RPS
根據可用數據，建議為您的 my.cnf 考慮
$$ mysqld $$部分
innodb_io_capacity=1900 # from 200 to enable higher IOPS to your SSD devices innodb_max_dirty_pages_pct_lwm=.1 # 0 to expedite writing rows to table storage innodb_max_dirty_pages_pct=.1 # from 75 percent to reduce innodb_buffer_pool_pages_dirty of 1,333 innodb_log_buffer_size=64M # from 16M for ~ 30 minutes before write to log需要評估/修改更多配置項。查看配置文件、網路配置文件以獲取聯繫資訊和免費下載的實用程序腳本以幫助進行性能調整。