1

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="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/"

Since there is still a lot of memory available for the cache and most of the scripts are data queries

Improve this question
4
  • 1
    Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report - for Windows version, - github.com/pmachapman/mysqltuner - F) SHOW ENGINE INNODB STATUS; G) SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Aug 31, 2020 at 22:05
  • @WilsonHauck I've added your request for more information as an answer to the Asking query performance questions over on Database Meta Stackexchange. Feel free to add further details to the Meta answer as you see fit. This will allow you to post a "Please read this information regarding MySQL performance issues" to future questions.
    – John K. N.
    Sep 1, 2020 at 9:48
  • What is the total size of all the .MYI files? How much RAM do you have? Were you using the IGNORE LEAVES option?
    – Rick James
    Sep 2, 2020 at 5:25
  • Thank you for the SGS and SGV etc posted. Additional information request. Post on pastebin.com (as a complimentary guest) and share the links. After 24 hours of uptime, from your SSH login root, Text results of: D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report - for Windows version, - github.com/pmachapman/mysqltuner for a more complete workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Sep 5, 2020 at 11:25

4 Answers 4

Reset to default
0

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.

Improve this answer
0

The server running this MySQL service has 160 GB of ram with an Intel Xeon E5-2697 v3 processor (28 cores)

The HD that is the databases is a Dell 3.2TB, NVMe, Mixed Use Express Flash, HHHL AIC, PM1725b, CK.

The biggest part of the executed processes are selections and insertions.

SHOW GLOBAL STATUS WHERE VALUE <> '0' AND VALUE <> '';

  • Aborted_clients: 57220
  • Aborted_connects: 3131
  • Bytes_received: 36040947615
  • Bytes_sent: 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_collations: 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
  • Compression: OFF
  • Connections: 2127954
  • Created_tmp_disk_tables: 5655433
  • Created_tmp_files: 28847
  • Created_tmp_tables: 30958954
  • Flush_commands: 1
  • Handler_delete: 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
  • Handler_update: 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
  • Last_query_cost: 0.000000
  • Max_used_connections: 4901
  • Open_files: 17552
  • Open_table_definitions: 19900
  • Open_tables: 10900
  • Opened_files: 32509588
  • Opened_table_definitions: 1185539
  • Opened_tables: 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
  • Queries: 138783204
  • Questions: 111070732
  • Rpl_status: AUTH_MASTER
  • Select_full_join: 420042
  • Select_full_range_join: 112592
  • Select_range: 2109146
  • Select_range_check: 1
  • Select_scan: 26162353
  • Slave_heartbeat_period: 0.000
  • Slave_running: OFF
  • Slow_queries: 1137
  • Sort_merge_passes: 15302
  • Sort_range: 54713565
  • Sort_rows: 1038439499
  • Sort_scan: 4494304
  • Ssl_session_cache_mode: NONE
  • Table_locks_immediate: 116689300
  • Table_locks_waited: 859932
  • Threads_cached: 1535
  • Threads_connected: 2294
  • Threads_created: 10539
  • Threads_running: 5
  • Uptime: 71170
  • Uptime_since_flush_status: 71170
Improve this answer
0

show variables:

  • auto_increment_increment: 1
  • auto_increment_offset: 1
  • autocommit: ON
  • automatic_sp_privileges: ON
  • back_log: 50
  • basedir: C:/Program Files/MySQL/MySQL Server 5.5.62/
  • big_tables: OFF
  • binlog_cache_size: 32768
  • binlog_direct_non_transactional_updates: OFF
  • binlog_format: STATEMENT
  • binlog_stmt_cache_size: 32768
  • bulk_insert_buffer_size: 8388608
  • character_set_client: utf8mb4
  • character_set_connection: utf8mb4
  • character_set_database: latin1
  • character_set_filesystem: binary
  • character_set_results: utf8mb4
  • character_set_server: latin1
  • character_set_system: utf8
  • character_sets_dir: C:\Program Files\MySQL\MySQL Server 5.5.62\share\charsets\
  • collation_connection: utf8mb4_general_ci
  • collation_database: latin1_swedish_ci
  • collation_server: latin1_swedish_ci
  • completion_type: NO_CHAIN
  • concurrent_insert: AUTO
  • connect_timeout: 10
  • datadir: D:\MySQL\Data\
  • date_format: %Y-%m-%d
  • datetime_format: %Y-%m-%d %H:%i:%s
  • default_storage_engine: MyISAM
  • default_week_format: 0
  • delay_key_write: ON
  • delayed_insert_limit: 100
  • delayed_insert_timeout: 300
  • delayed_queue_size: 1000
  • div_precision_increment: 4
  • engine_condition_pushdown: ON
  • error_count: 0
  • event_scheduler: ON
  • expire_logs_days: 0
  • external_user:
  • flush: OFF
  • flush_time: 1800
  • foreign_key_checks: ON
  • ft_boolean_syntax: + -><()~*:&|
  • ft_max_word_len: 84
  • ft_min_word_len: 4
  • ft_query_expansion_limit: 20
  • ft_stopword_file: (built-in)
  • general_log: OFF
  • general_log_file: D:\MySQL\Data\dbserver.log
  • group_concat_max_len: 1024
  • have_compress: YES
  • have_crypt: NO
  • have_csv: YES
  • have_dynamic_loading: YES
  • have_geometry: YES
  • have_innodb: DISABLED
  • have_ndbcluster: NO
  • have_openssl: DISABLED
  • have_partitioning: YES
  • have_profiling: YES
  • have_query_cache: YES
  • have_rtree_keys: YES
  • have_ssl: DISABLED
  • have_symlink: YES
  • hostname: ServerDB
  • identity: 0
  • ignore_builtin_innodb: OFF
  • init_connect:
  • init_file:
  • init_slave:
  • insert_id: 0
  • interactive_timeout: 1901
  • join_buffer_size: 4194304
  • keep_files_on_create: OFF
  • key_buffer_size: 118111600640
  • key_cache_age_threshold: 300
  • key_cache_block_size: 1024
  • key_cache_division_limit: 100
  • large_files_support: ON
  • large_page_size: 0
  • large_pages: OFF
  • last_insert_id: 0
  • lc_messages: en_US
  • lc_messages_dir: C:\Program Files\MySQL\MySQL Server 5.5.62\share\
  • lc_time_names: en_US
  • license: GPL
  • local_infile: ON
  • lock_wait_timeout: 31536000
  • log: OFF
  • log_bin: OFF
  • log_bin_trust_function_creators: OFF
  • log_error: D:\MySQL\Data\dbserver.err
  • log_output: TABLE
  • log_queries_not_using_indexes: OFF
  • log_slave_updates: OFF
  • log_slow_queries: ON
  • log_warnings: 2
  • long_query_time: 10.000000
  • low_priority_updates: OFF
  • lower_case_file_system: ON
  • lower_case_table_names: 1
  • max_allowed_packet: 67108864
  • max_binlog_cache_size: 18446744073709547520
  • max_binlog_size: 1073741824
  • max_binlog_stmt_cache_size: 18446744073709547520
  • max_connect_errors: 1000000
  • max_connections: 4900
  • max_delayed_threads: 20
  • max_error_count: 64
  • max_heap_table_size: 134217728
  • max_insert_delayed_threads: 20
  • max_join_size: 18446744073709551615
  • max_length_for_sort_data: 1024
  • max_long_data_size: 67108864
  • max_prepared_stmt_count: 16382
  • max_relay_log_size: 0
  • max_seeks_for_key: 4294967295
  • max_sort_length: 4096
  • max_sp_recursion_depth: 0
  • max_tmp_tables: 32
  • max_user_connections: 0
  • max_write_lock_count: 4294967295
  • metadata_locks_cache_size: 1024
  • min_examined_row_limit: 0
  • multi_range_count: 256
  • myisam_data_pointer_size: 6
  • myisam_max_sort_file_size: 2146435072
  • myisam_mmap_size: 18446744073709551615
  • myisam_recover_options: OFF
  • myisam_repair_threads: 1
  • myisam_sort_buffer_size: 17179869184
  • myisam_stats_method: nulls_unequal
  • myisam_use_mmap: OFF
  • named_pipe: OFF
  • net_buffer_length: 16384
  • net_read_timeout: 30
  • net_retry_count: 10
  • net_write_timeout: 60
  • new: OFF
  • old: OFF
  • old_alter_table: OFF
  • old_passwords: OFF
  • open_files_limit: 67583
  • optimizer_prune_level: 1
  • optimizer_search_depth: 62
  • optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
  • performance_schema: OFF
  • performance_schema_events_waits_history_long_size: 10000
  • performance_schema_events_waits_history_size: 10
  • performance_schema_max_cond_classes: 80
  • performance_schema_max_cond_instances: 1000
  • performance_schema_max_file_classes: 50
  • performance_schema_max_file_handles: 32768
  • performance_schema_max_file_instances: 10000
  • performance_schema_max_mutex_classes: 200
  • performance_schema_max_mutex_instances: 1000000
  • performance_schema_max_rwlock_classes: 30
  • performance_schema_max_rwlock_instances: 1000000
  • performance_schema_max_table_handles: 100000
  • performance_schema_max_table_instances: 50000
  • performance_schema_max_thread_classes: 50
  • performance_schema_max_thread_instances: 1000
  • pid_file: D:\MySQL\Data\serverdb.pid
  • plugin_dir: C:\Program Files\MySQL\MySQL Server 5.5.62\lib\plugin\
  • port: 3987
  • preload_buffer_size: 32768
  • profiling: OFF
  • profiling_history_size: 15
  • protocol_version: 10
  • proxy_user:
  • pseudo_slave_mode: OFF
  • pseudo_thread_id: 257434
  • query_alloc_block_size: 8192
  • query_cache_limit: 1048576
  • query_cache_min_res_unit: 4096
  • query_cache_size: 67108864
  • query_cache_type: ON
  • query_cache_wlock_invalidate: OFF
  • query_prealloc_size: 8192
  • rand_seed1: 0
  • rand_seed2: 0
  • range_alloc_block_size: 4096
  • read_buffer_size: 1048576
  • read_only: OFF
  • read_rnd_buffer_size: 262144
  • relay_log:
  • relay_log_index:
  • relay_log_info_file: relay-log.info
  • relay_log_purge: ON
  • relay_log_recovery: OFF
  • relay_log_space_limit: 0
  • report_host:
  • report_password:
  • report_port: 3306
  • report_user:
  • rpl_recovery_rank: 0
  • secure_auth: OFF
  • secure_file_priv: NULL
  • server_id: 0
  • shared_memory: OFF
  • shared_memory_base_name: MYSQL
  • skip_external_locking: ON
  • skip_name_resolve: OFF
  • skip_networking: OFF
  • skip_show_database: OFF
  • slave_compressed_protocol: OFF
  • slave_exec_mode: STRICT
  • slave_load_tmpdir: D:\MySQL\Temp\
  • slave_max_allowed_packet: 1073741824
  • slave_net_timeout: 3600
  • slave_skip_errors: OFF
  • slave_transaction_retries: 10
  • slave_type_conversions:
  • slow_launch_time: 2
  • slow_query_log: ON
  • slow_query_log_file: D:/MySQL/Data/slowlog.txt
  • socket: MySQL
  • sort_buffer_size: 2097152
  • sql_auto_is_null: OFF
  • sql_big_selects: ON
  • sql_big_tables: OFF
  • sql_buffer_result: OFF
  • sql_log_bin: ON
  • sql_log_off: OFF
  • sql_low_priority_updates: OFF
  • sql_max_join_size: 18446744073709551615
  • sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  • sql_notes: ON
  • sql_quote_show_create: ON
  • sql_safe_updates: OFF
  • sql_select_limit: 18446744073709551615
  • sql_slave_skip_counter: 0
  • sql_warnings: OFF
  • ssl_ca:
  • ssl_capath:
  • ssl_cert:
  • ssl_cipher:
  • ssl_key:
  • storage_engine: MyISAM
  • stored_program_cache: 256
  • sync_binlog: 0
  • sync_frm: ON
  • sync_master_info: 0
  • sync_relay_log: 0
  • sync_relay_log_info: 0
  • system_time_zone: E. South America Standard Tim
  • table_definition_cache: 19900
  • table_open_cache: 10900
  • thread_cache_size: 1590
  • thread_concurrency: 10
  • thread_handling: one-thread-per-connection
  • thread_stack: 262144
  • time_format: %H:%i:%s
  • time_zone: SYSTEM
  • timed_mutexes: OFF
  • timestamp: 1599168455
  • tmp_table_size: 134217728
  • tmpdir: D:/MySQL/Temp/
  • transaction_alloc_block_size: 8192
  • transaction_prealloc_size: 4096
  • tx_isolation: REPEATABLE-READ
  • unique_checks: ON
  • updatable_views_with_limit: YES
  • version: 5.5.62-log
  • version_comment: MySQL Community Server (GPL)
  • version_compile_machine: AMD64
  • version_compile_os: Win64
  • wait_timeout: 1901
  • warning_count: 0
Improve this answer
0

Rate Per Second = RPS

Suggestions to consider for your my.ini [mysqld] section,

read_rnd_buffer_size=96K  # from 256K to reduce handler_read_rnd_next RPS of 57,939
key_cache_age_threshold=7200  # from 300 to reduce key_reads RPS of 6,092
key_cache_division_limit=50  # from 100 for Hot/Warm split to reduce key_reads RPS
key_cache_block_size=16384  # from 1024 to reduce RAM management cycles required

You will find these configuration changes will REDUCE CPU busy significantly.

SHOW GLOBAL STATUS LIKE 'key_blocks%' after 2 hours production should show more than 34% used blocks.

Your SHOW GLOBAL STATUS opened_% rates per second could be reduced by raising the limits 10% and making open_tables = open_table_definitions * 1.5 for the count.

For additional assistance, please view my profile, Network profile for contact info and get in touch by Skype, please.

Improve this answer
1
  • @alexandredelcol Have you had an opportunity to implement the suggestions of Sep 5 at 11:57? Please comment on results - positive or negative.
    – Wilson Hauck
    Sep 27, 2020 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.