Load index to cache is not using memory available in the key_buffer_size variable Good afternoon folks,
I have a problem when trying to load the indexes from the MyISAM tables into MySQL memory, even though there is space available in the key_buffer_size variable, it stops loading even though there are still indexes to be loaded.
Has anyone had this problem or know what I can do to solve it?
The server that is running the database is a windows server 2012 (¯_(ツ)_/¯ - and don't want to switch to linux server), MySQL is version 5.5.62 and in this instance I have 1210 databases with the structure of the tables in MyISAM.
Below is the configuration file (my.ini):
  
  [mysqld] 
   port=3306 
   basedir=&quot;C:/Program Files/MySQL/MySQL Server 5.5/&quot;
   datadir=&quot;D:/MySQL/Data/&quot; 
   character-set-server=latin1
   default-storage-engine=myisam
   sql-mode=&quot;STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION&quot;
   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 = &quot;D:/MySQL/Data/slowlog.txt&quot; 
  log_output = TABLE
  connect_timeout=30 
  net_read_timeout=65   
  net_write_timeout=65
  tmpdir=&quot;D:/MySQL/Temp/&quot;

Since there is still a lot of memory available for the cache and most of the scripts are data queries
You have a pretty strange buffer configuration:
max_connections=4500 
. . . . .
key_buffer_size=62G 
read_buffer_size = 200M 
read_rnd_buffer_size = 100M 
sort_buffer_size = 1G 
join_buffer_size = ???  

While key_buffer_size is a global value for all MyISAM indexes across all the databases, (read|read_rnd|sort|join)_buffer_size are per-connection values. That amounts are reserved for each client attached to the mysqld service. The overall RAM consumption is calculated as:
(read_bs + read_rnd_bs + sort_bs + join_bs) * max_connection + key_buffer_size

In your case it seems to be at least 6TB in total that is too optimistic.
First you have to lower your buffer sizes to the reasonable values - 2-8MB each. Second you have to install mysqltuner.pl - a neat tool that analyze both config and status variables and propose a number of reasonable advices.
Third you have to perform a system monitoring for slow queries and on-disk temporary tables and tune your mysql accordingly to your certain needs.