將索引載入到記憶體未使用 key_buffer_size 變數中的可用記憶體

各位下午好

嘗試將 MyISAM 表中的索引載入到 MySQL 記憶體時遇到問題，即使 key_buffer_size 變數中有可用空間，即使仍有要載入的索引，它也會停止載入。

有沒有人遇到過這個問題或知道我能做些什麼來解決它？

執行數據庫的伺服器是 windows server 2012 (¯_(ツ)_/¯ - 並且不想切換到 linux 伺服器)，MySQL 是 5.5.62 版，在這種情況下，我有 1210 個數據庫MyISAM 中的表結構。

下面是配置文件（my.ini）：

 
 [mysqld] 
  port=3306 
  basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
  datadir="D:/MySQL/Data/" 
  character-set-server=latin1
  default-storage-engine=myisam
  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  max_connections=4500 
  query_cache_size=48M 
  query_cache_type=1
  tmp_table_size = 1G 
  max_heap_table_size = 256M 
  thread_cache_size=1300

  key_buffer_size=62G 
  read_buffer_size = 200M 
  read_rnd_buffer_size = 100M 
  sort_buffer_size = 1G 
  myisam_sort_buffer_size = 4096M

 skip-innodb 
 max_allowed_packet=16M 
 interactive_timeout=300
 wait_timeout=1700 
 max_sort_length=4096 
 max_connect_errors=1000000
 table_open_cache=13999 
 table_definition_cache=4096 
 log-warnings
 long_query_time=10 
 open_files_limit=65535 
 slow-query-log
 slow_query_log_file = "D:/MySQL/Data/slowlog.txt" 
 log_output = TABLE
 connect_timeout=30 
 net_read_timeout=65   
 net_write_timeout=65
 tmpdir="D:/MySQL/Temp/"

由於記憶體還有很多可用的記憶體，而且大部分腳本都是數據查詢

您有一個非常奇怪的緩衝區配置：

max_connections=4500 
. . . . .
key_buffer_size=62G 
read_buffer_size = 200M 
read_rnd_buffer_size = 100M 
sort_buffer_size = 1G 
join_buffer_size = ???  

Whilekey_buffer_size是所有數據庫中所有 MyISAM 索引的全域值，(read|read_rnd|sort|join)_buffer_size是每個連接的值。該金額是為附加到mysqld服務的每個客戶保留的。總 RAM 消耗計算如下：

(read_bs + read_rnd_bs + sort_bs + join_bs) * max_connection + key_buffer_size

在您的情況下，總容量似乎至少為 6TB，這太樂觀了。

首先，您必須將緩衝區大小降低到合理的值 - 每個 2-8MB。其次，您必須安裝mysqltuner.pl- 一個分析配置和狀態變數並提出許多合理建議的簡潔工具。第三，您必須對慢查詢和磁碟臨時表執行系統監控，並根據您的特定需求調整您的 mysql。

執行此 MySQL 服務的伺服器具有 160 GB 記憶體和 Intel Xeon E5-2697 v3 處理器（28 核）

作為數據庫的 HD 是 Dell 3.2TB、NVMe、Mixed Use Express Flash、HHHL AIC、PM1725b、CK。

執行過程的最大部分是選擇和插入。

顯示 VALUE <> ‘0’ 和 VALUE <> ’’ 的全域狀態；

• 中止客戶端：57220
• Aborted_connects：3131
• 接收字節數：36040947615
• 字節發送：1360556719043
• Com_admin_commands：114591
• Com_alter_table：10367
• Com_begin: 41
• Com_call_procedure：16716
• Com_change_db: 22594
• com_commit: 30
• Com_create_db：7
• Com_create_event: 7
• Com_create_function：53
• Com_create_index：14
• Com_create_procedure：4
• Com_create_table：4770
• Com_create_trigger：83
• Com_create_view：28
• Com_dealloc_sql: 21793
• com_delete: 1364878
• Com_delete_multi: 26171
• Com_drop_event：7
• Com_drop_function: 53
• Com_drop_procedure：2
• Com_drop_table：4944
• Com_drop_trigger：532
• Com_drop_view: 62
• Com_empty_query：3
• Com_execute_sql: 89991
• Com_flush：2
• Com_insert：4851225
• Com_insert_select：145200
• com_kill: 36366
• com_load：1
• Com_lock_tables：4560
• com_optimize：744978
• Com_preload_keys：50829
• Com_prepare_sql：90123
• Com_repair: 1
• com_replace: 12736
• Com_rollback: 12079
• com_select：34041572
• Com_set_option: 4462250
• Com_show_charsets：6
• Com_show_snacks：1543
• Com_show_create_func：425
• Com_show_create_proc：341
• Com_show_create_table：14716
• Com_show_create_trigger：133
• Com_show_databases：19734
• Com_show_events：34
• Com_show_fields：34238
• Com_show_function_status: 57
• Com_show_keys: 14130269
• Com_show_open_tables：5
• Com_show_plugins: 3
• Com_show_procedure_status：57
• Com_show_processlist：37026
• Com_show_status: 113106
• Com_show_storage_engines：8
• Com_show_table_status: 4635
• Com_show_tables：1905693
• Com_show_triggers：4634
• Com_show_variables：2123984
• Com_show_warnings：5
• Com_stmt_close：2801455
• com_stmt_execute：2811106
• Com_stmt_prepare：2802239
• Com_stmt_reset：9114
• Com_stmt_send_long_data：71916
• Com_unlock_tables：4560
• com_update: 9011925
• Com_update_multi: 154020
• 壓縮：關
• 連接：2127954
• Created_tmp_disk_tables：5655433
• Created_tmp_files: 28847
• Created_tmp_tables：30958954
• 刷新命令：1
• 處理程序刪除：568112
• Handler_read_first：6414362
• Handler_read_key：2702557111
• Handler_read_last: 2655
• Handler_read_next：3484601325
• Handler_read_prev: 61269195
• Handler_read_rnd：859438046
• Handler_read_rnd_next：4123461317
• 處理程序更新：301766348
• Handler_write：1405341275
• Key_blocks_unused: 93494875
• Key_blocks_used: 48795048
• Key_read_requests：9930039199
• Key_reads：433621548
• Key_write_requests：99582766
• Key_writes：32531990
• 最後查詢成本：0.000000
• 最大使用連接數：4901
• 打開文件：17552
• 開放表定義：19900
• 開放表：10900
• 已打開文件：32509588
• Opened_table_definitions：1185539
• 已打開表：4164765
• Prepared_stmt_count: 1
• Qcache_free_blocks：1713
• Qcache_free_memory：4755840
• Qcache_hits: 35981931
• Qcache_inserts：26888989
• Qcache_lowmem_prunes：20380999
• Qcache_not_cached：7128110
• Qcache_queries_in_cache：12672
• Qcache_total_blocks: 31375
• 查詢：138783204
• 問題：111070732
• Rpl_status：AUTH_MASTER
• Select_full_join: 420042
• Select_full_range_join: 112592
• 選擇範圍：2109146
• 選擇範圍檢查：1
• 選擇掃描：26162353
• Slave_heartbeat_period：0.000
• 從屬執行：關閉
• 慢查詢：1137
• sort_merge_passes：15302
• 排序範圍：54713565
• 排序行數：1038439499
• 排序掃描：4494304
• ssl_session_cache_mode：無
• Table_locks_immediate：116689300
• Table_locks_waited：859932
• 執行緒記憶體：1535
• Threads_connected：2294
• Threads_created：10539
• 執行緒執行：5
• 正常執行時間：71170
• Uptime_since_flush_status：71170

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