MySQL Forums
Forum List  »  Performance

Re: Mysql 5.5 memory issues.
Posted by: Alex Titley
Date: October 02, 2020 01:16PM

Hi Thanks for the reply:

(i)

'MyISAM', '369.14 GB', '141.60 GB', '510.74 GB'
'CSV', '0.00 GB', '0.00 GB', '0.00 GB'
'MEMORY', '0.00 GB', '0.00 GB', '0.00 GB'
'TOTALS', '369.14 GB', '141.60 GB', '510.74 GB'

(ii) Show Variables;
'auto_increment_increment', '1'
'auto_increment_offset', '1'
'autocommit', 'ON'
'automatic_sp_privileges', 'ON'
'back_log', '600'
'basedir', '/usr'
'big_tables', 'OFF'
'binlog_cache_size', '131072'
'binlog_direct_non_transactional_updates', 'OFF'
'binlog_format', 'STATEMENT'
'binlog_stmt_cache_size', '131072'
'bulk_insert_buffer_size', '34359738368'
'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'latin1'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'
'character_sets_dir', '/usr/share/mysql/charsets/'
'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_swedish_ci'
'collation_server', 'latin1_swedish_ci'
'completion_type', 'NO_CHAIN'
'concurrent_insert', 'AUTO'
'connect_timeout', '60'
'datadir', '/home/mysql/'
'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', '3'
'external_user', ''
'flush', 'OFF'
'flush_time', '0'
'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', '/home/mysql/rhdbserver15.log'
'group_concat_max_len', '1024'
'have_compress', 'YES'
'have_crypt', 'YES'
'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', 'rhdbserver15'
'identity', '0'
'ignore_builtin_innodb', 'OFF'
'init_connect', ''
'init_file', ''
'init_slave', ''
'insert_id', '0'
'interactive_timeout', '28800'
'join_buffer_size', '268435456'
'keep_files_on_create', 'OFF'
'key_buffer_size', '85899345920'
'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', '/usr/share/mysql/'
'lc_time_names', 'en_US'
'license', 'GPL'
'local_infile', 'ON'
'lock_wait_timeout', '31536000'
'locked_in_memory', 'OFF'
'log', 'OFF'
'log_bin', 'ON'
'log_bin_trust_function_creators', 'OFF'
'log_error', '/var/log/mysqld.log'
'log_output', 'FILE'
'log_queries_not_using_indexes', 'OFF'
'log_slave_updates', 'OFF'
'log_slow_queries', 'OFF'
'log_warnings', '0'
'long_query_time', '10.000000'
'low_priority_updates', 'OFF'
'lower_case_file_system', 'OFF'
'lower_case_table_names', '1'
'max_allowed_packet', '33554432'
'max_binlog_cache_size', '18446744073709547520'
'max_binlog_size', '1073741824'
'max_binlog_stmt_cache_size', '18446744073709547520'
'max_connect_errors', '1000000'
'max_connections', '1000'
'max_delayed_threads', '20'
'max_error_count', '64'
'max_heap_table_size', '1073741824'
'max_insert_delayed_threads', '20'
'max_join_size', '18446744073709551615'
'max_length_for_sort_data', '1024'
'max_long_data_size', '33554432'
'max_prepared_stmt_count', '16382'
'max_relay_log_size', '0'
'max_seeks_for_key', '18446744073709551615'
'max_sort_length', '1024'
'max_sp_recursion_depth', '0'
'max_tmp_tables', '32'
'max_user_connections', '0'
'max_write_lock_count', '18446744073709551615'
'metadata_locks_cache_size', '1024'
'min_examined_row_limit', '0'
'multi_range_count', '256'
'myisam_data_pointer_size', '6'
'myisam_max_sort_file_size', '429496729600'
'myisam_mmap_size', '18446744073709551615'
'myisam_recover_options', 'BACKUP,FORCE'
'myisam_repair_threads', '1'
'myisam_sort_buffer_size', '75161927680'
'myisam_stats_method', 'nulls_unequal'
'myisam_use_mmap', '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', 'ON'
'open_files_limit', '65536'
'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', '/var/run/mysqld/mysqld.pid'
'plugin_dir', '/usr/lib64/mysql/plugin/'
'port', '3306'
'preload_buffer_size', '32768'
'profiling', 'OFF'
'profiling_history_size', '15'
'protocol_version', '10'
'proxy_user', ''
'pseudo_slave_mode', 'OFF'
'pseudo_thread_id', '1270498'
'query_alloc_block_size', '8192'
'query_cache_limit', '1048576'
'query_cache_min_res_unit', '4096'
'query_cache_size', '0'
'query_cache_type', 'OFF'
'query_cache_wlock_invalidate', 'OFF'
'query_prealloc_size', '8192'
'rand_seed1', '0'
'rand_seed2', '0'
'range_alloc_block_size', '4096'
'read_buffer_size', '33554432'
'read_only', 'OFF'
'read_rnd_buffer_size', '33554432'
'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', '/var/lib/mysql-files/'
'server_id', '1'
'skip_external_locking', 'ON'
'skip_name_resolve', 'ON'
'skip_networking', 'OFF'
'skip_show_database', 'OFF'
'slave_compressed_protocol', 'OFF'
'slave_exec_mode', 'STRICT'
'slave_load_tmpdir', '/mark'
'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', 'OFF'
'slow_query_log_file', '/home/mysql/rhdbserver15-slow.log'
'socket', '/home/mysql/mysql.sock'
'sort_buffer_size', '268435456'
'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', 'NO_UNSIGNED_SUBTRACTION,STRICT_TRANS_TABLES,STRICT_ALL_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', '20'
'sync_frm', 'ON'
'sync_master_info', '0'
'sync_relay_log', '0'
'sync_relay_log_info', '0'
'system_time_zone', 'BST'
'table_definition_cache', '409900'
'table_open_cache', '524288'
'thread_cache_size', '200'
'thread_concurrency', '4'
'thread_handling', 'one-thread-per-connection'
'thread_stack', '262144'
'time_format', '%H:%i:%s'
'time_zone', 'SYSTEM'
'timed_mutexes', 'OFF'
'timestamp', '1601666342'
'tmp_table_size', '1073741824'
'tmpdir', '/mark'
'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', 'x86_64'
'version_compile_os', 'Linux'
'wait_timeout', '28800'
'warning_count', '0'

Show Global status:

'Aborted_clients', '130632'
'Aborted_connects', '10040'
'Binlog_cache_disk_use', '0'
'Binlog_cache_use', '0'
'Binlog_stmt_cache_disk_use', '1638'
'Binlog_stmt_cache_use', '36068691'
'Bytes_received', '19750787977'
'Bytes_sent', '90821310058'
'Com_admin_commands', '1493626'
'Com_assign_to_keycache', '0'
'Com_alter_db', '0'
'Com_alter_db_upgrade', '0'
'Com_alter_event', '0'
'Com_alter_function', '0'
'Com_alter_procedure', '0'
'Com_alter_server', '0'
'Com_alter_table', '308343'
'Com_alter_tablespace', '0'
'Com_analyze', '0'
'Com_begin', '0'
'Com_binlog', '0'
'Com_call_procedure', '324213'
'Com_change_db', '2482814'
'Com_change_master', '0'
'Com_check', '0'
'Com_checksum', '0'
'Com_commit', '227'
'Com_create_db', '0'
'Com_create_event', '0'
'Com_create_function', '0'
'Com_create_index', '119'
'Com_create_procedure', '0'
'Com_create_server', '0'
'Com_create_table', '323112'
'Com_create_trigger', '0'
'Com_create_udf', '0'
'Com_create_user', '0'
'Com_create_view', '0'
'Com_dealloc_sql', '403982'
'Com_delete', '617771'
'Com_delete_multi', '414'
'Com_do', '0'
'Com_drop_db', '0'
'Com_drop_event', '0'
'Com_drop_function', '0'
'Com_drop_index', '0'
'Com_drop_procedure', '0'
'Com_drop_server', '0'
'Com_drop_table', '524659'
'Com_drop_trigger', '0'
'Com_drop_user', '0'
'Com_drop_view', '0'
'Com_empty_query', '0'
'Com_execute_sql', '403982'
'Com_flush', '9'
'Com_grant', '0'
'Com_ha_close', '0'
'Com_ha_open', '0'
'Com_ha_read', '0'
'Com_help', '0'
'Com_insert', '16243637'
'Com_insert_select', '66134'
'Com_install_plugin', '0'
'Com_kill', '748'
'Com_load', '319'
'Com_lock_tables', '209'
'Com_optimize', '4'
'Com_preload_keys', '0'
'Com_prepare_sql', '403982'
'Com_purge', '0'
'Com_purge_before_date', '0'
'Com_release_savepoint', '0'
'Com_rename_table', '0'
'Com_rename_user', '0'
'Com_repair', '0'
'Com_replace', '10663881'
'Com_replace_select', '17423'
'Com_reset', '0'
'Com_resignal', '0'
'Com_revoke', '0'
'Com_revoke_all', '0'
'Com_rollback', '114'
'Com_rollback_to_savepoint', '0'
'Com_savepoint', '0'
'Com_select', '65945847'
'Com_set_option', '3674863'
'Com_signal', '0'
'Com_show_authors', '0'
'Com_show_binlog_events', '0'
'Com_show_binlogs', '0'
'Com_show_charsets', '1836'
'Com_show_collations', '1078314'
'Com_show_contributors', '0'
'Com_show_create_db', '0'
'Com_show_create_event', '0'
'Com_show_create_func', '20'
'Com_show_create_proc', '8434'
'Com_show_create_table', '882'
'Com_show_create_trigger', '0'
'Com_show_databases', '97'
'Com_show_engine_logs', '0'
'Com_show_engine_mutex', '0'
'Com_show_engine_status', '0'
'Com_show_events', '0'
'Com_show_errors', '0'
'Com_show_fields', '40189'
'Com_show_function_status', '32'
'Com_show_grants', '0'
'Com_show_keys', '46'
'Com_show_master_status', '80'
'Com_show_open_tables', '0'
'Com_show_plugins', '1'
'Com_show_privileges', '0'
'Com_show_procedure_status', '32'
'Com_show_processlist', '22789'
'Com_show_profile', '0'
'Com_show_profiles', '0'
'Com_show_relaylog_events', '0'
'Com_show_slave_hosts', '1'
'Com_show_slave_status', '4628'
'Com_show_status', '27787'
'Com_show_storage_engines', '5'
'Com_show_table_status', '23'
'Com_show_tables', '71'
'Com_show_triggers', '27866'
'Com_show_variables', '1078455'
'Com_show_warnings', '1694810'
'Com_slave_start', '0'
'Com_slave_stop', '0'
'Com_stmt_close', '404917'
'Com_stmt_execute', '405391'
'Com_stmt_fetch', '1111'
'Com_stmt_prepare', '405141'
'Com_stmt_reprepare', '0'
'Com_stmt_reset', '0'
'Com_stmt_send_long_data', '0'
'Com_truncate', '5'
'Com_uninstall_plugin', '0'
'Com_unlock_tables', '209'
'Com_update', '5593606'
'Com_update_multi', '3372415'
'Com_xa_commit', '0'
'Com_xa_end', '0'
'Com_xa_prepare', '0'
'Com_xa_recover', '0'
'Com_xa_rollback', '0'
'Com_xa_start', '0'
'Compression', 'OFF'
'Connections', '1274537'
'Created_tmp_disk_tables', '76726'
'Created_tmp_files', '1639'
'Created_tmp_tables', '10142411'
'Delayed_errors', '0'
'Delayed_insert_threads', '0'
'Delayed_writes', '0'
'Flush_commands', '10'
'Handler_commit', '36068806'
'Handler_delete', '23834213'
'Handler_discover', '0'
'Handler_prepare', '0'
'Handler_read_first', '1677558'
'Handler_read_key', '1538788622'
'Handler_read_last', '164'
'Handler_read_next', '7352693725'
'Handler_read_prev', '21001'
'Handler_read_rnd', '299601942'
'Handler_read_rnd_next', '62556621836'
'Handler_rollback', '7'
'Handler_savepoint', '0'
'Handler_savepoint_rollback', '0'
'Handler_update', '465862608'
'Handler_write', '2876646627'
'Key_blocks_not_flushed', '188'
'Key_blocks_unused', '64912627'
'Key_blocks_used', '5008137'
'Key_read_requests', '6315439281'
'Key_reads', '35807827'
'Key_write_requests', '194330475'
'Key_writes', '70844821'
'Last_query_cost', '0.000000'
'Max_used_connections', '880'
'Not_flushed_delayed_rows', '0'
'Open_files', '7252'
'Open_streams', '0'
'Open_table_definitions', '2713'
'Open_tables', '4459'
'Opened_files', '11344223'
'Opened_table_definitions', '1279646'
'Opened_tables', '666385'
'Performance_schema_cond_classes_lost', '0'
'Performance_schema_cond_instances_lost', '0'
'Performance_schema_file_classes_lost', '0'
'Performance_schema_file_handles_lost', '0'
'Performance_schema_file_instances_lost', '0'
'Performance_schema_locker_lost', '0'
'Performance_schema_mutex_classes_lost', '0'
'Performance_schema_mutex_instances_lost', '0'
'Performance_schema_rwlock_classes_lost', '0'
'Performance_schema_rwlock_instances_lost', '0'
'Performance_schema_table_handles_lost', '0'
'Performance_schema_table_instances_lost', '0'
'Performance_schema_thread_classes_lost', '0'
'Performance_schema_thread_instances_lost', '0'
'Prepared_stmt_count', '0'
'Qcache_free_blocks', '0'
'Qcache_free_memory', '0'
'Qcache_hits', '0'
'Qcache_inserts', '0'
'Qcache_lowmem_prunes', '0'
'Qcache_not_cached', '0'
'Qcache_queries_in_cache', '0'
'Qcache_total_blocks', '0'
'Queries', '122877314'
'Questions', '110002710'
'Rpl_status', 'AUTH_MASTER'
'Select_full_join', '6279'
'Select_full_range_join', '169'
'Select_range', '4200457'
'Select_range_check', '0'
'Select_scan', '5877864'
'Slave_heartbeat_period', '1800.000'
'Slave_open_temp_tables', '0'
'Slave_received_heartbeats', '0'
'Slave_retried_transactions', '0'
'Slave_running', 'OFF'
'Slow_launch_threads', '0'
'Slow_queries', '6954'
'Sort_merge_passes', '38'
'Sort_range', '1141102'
'Sort_rows', '408356936'
'Sort_scan', '242480'
'Ssl_accept_renegotiates', '0'
'Ssl_accepts', '0'
'Ssl_callback_cache_hits', '0'
'Ssl_cipher', ''
'Ssl_cipher_list', ''
'Ssl_client_connects', '0'
'Ssl_connect_renegotiates', '0'
'Ssl_ctx_verify_depth', '0'
'Ssl_ctx_verify_mode', '0'
'Ssl_default_timeout', '0'
'Ssl_finished_accepts', '0'
'Ssl_finished_connects', '0'
'Ssl_session_cache_hits', '0'
'Ssl_session_cache_misses', '0'
'Ssl_session_cache_mode', 'NONE'
'Ssl_session_cache_overflows', '0'
'Ssl_session_cache_size', '0'
'Ssl_session_cache_timeouts', '0'
'Ssl_sessions_reused', '0'
'Ssl_used_session_cache_entries', '0'
'Ssl_verify_depth', '0'
'Ssl_verify_mode', '0'
'Ssl_version', ''
'Table_locks_immediate', '139438117'
'Table_locks_waited', '4970657'
'Tc_log_max_pages_used', '0'
'Tc_log_page_size', '0'
'Tc_log_page_waits', '0'
'Threads_cached', '140'
'Threads_connected', '360'
'Threads_created', '4941'
'Threads_running', '8'
'Uptime', '34174'
'Uptime_since_flush_status', '34174'

(iii)
total used free shared buff/cache available
Mem: 483420 40239 40959 2825 402220 439170
Swap: 4095 14 4081

Options: ReplyQuote


Subject
Views
Written By
Posted
152
October 02, 2020 11:26AM
80
October 02, 2020 12:21PM
Re: Mysql 5.5 memory issues.
76
October 02, 2020 01:16PM
73
October 02, 2020 10:24PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.