Mysql memory usage increasing until it hits the RAM limit and starts using swap
Posted by: Ravi Malghan
Date: December 11, 2018 10:23AM
Date: December 11, 2018 10:23AM
I have a mysql database server that has 128G of RAM. mysql starts with 7G and keeps increasing until hits the RAM limit and starts swapping.
Couple of things about my data
1. I'm running MySQL 5.7.22 on RHEL7.4. My server has 128GB RAM and 16GB swap space and this is a VM.
2. I'm using only InnoDB.
3. the mysql datadir is currently about 451G. I have two tables that are rather big. The file sizes to those tables correspond to 251G and 152G. These tables are not partitioned. Rest of the tables not that big. Corresponding file sizes are less than 10G.
4. A web application server connects to this database and at any given time, there are about 150-200 connections.
5. I have two slaves replicating from the master. I disabled one of them today to rule out replication causing any issues.
6. my innodb-buffer-pool-size is 105G and innodb_buffer_pool_instances is 105.
7. from show global status: full table scans(Select_scan): 766226, uptime: 352571, % of selects doing full table scans: 766226/115652330 (0.66%), aborted clients: 38, number of connections: 353006
my my.cnf, show global status and show variables are shown below.
my SHOW VARIABLES is shown below
my show global status is shown below
Couple of things about my data
1. I'm running MySQL 5.7.22 on RHEL7.4. My server has 128GB RAM and 16GB swap space and this is a VM.
2. I'm using only InnoDB.
3. the mysql datadir is currently about 451G. I have two tables that are rather big. The file sizes to those tables correspond to 251G and 152G. These tables are not partitioned. Rest of the tables not that big. Corresponding file sizes are less than 10G.
4. A web application server connects to this database and at any given time, there are about 150-200 connections.
5. I have two slaves replicating from the master. I disabled one of them today to rule out replication causing any issues.
6. my innodb-buffer-pool-size is 105G and innodb_buffer_pool_instances is 105.
7. from show global status: full table scans(Select_scan): 766226, uptime: 352571, % of selects doing full table scans: 766226/115652330 (0.66%), aborted clients: 38, number of connections: 353006
my my.cnf, show global status and show variables are shown below.
default-storage-engine = InnoDB # MyISAM # key-buffer-size = 32M myisam-recover-options = FORCE,BACKUP # SAFETY # max-allowed-packet = 128M max-connect-errors = 1000000 sysdate-is-now = 1 # DATA STORAGE # datadir = /export/apps/var/lib/mysql/ log-bin = /export/apps/var/lib/mysql/mysql-bin binlog_format = row expire-logs-days = 14 sync-binlog = 1 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 4096 max_prepared_stmt_count = 1048576 group_concat_max_len = 1048576 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 1024M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 105G innodb_buffer_pool_instances = 105 user=moog character-set-server=utf8 collation-server=utf8_general_ci init-connect='SET NAMES utf8' innodb_autoinc_lock_mode=0 # Semisynchronous Replication # http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html # uncomment next line on MASTER ;plugin-load=rpl_semi_sync_master=semisync_master.so # uncomment next line on SLAVE ;plugin-load=rpl_semi_sync_slave=semisync_slave.so # Others options for Semisynchronous Replication ;rpl_semi_sync_master_enabled=1 ;rpl_semi_sync_master_timeout=10 ;rpl_semi_sync_slave_enabled=1 # Moogsoft MySQL 5.6 optimizations performance_schema=OFF default_tmp_storage_engine=MyISAM sql-mode=NO_ENGINE_SUBSTITUTION [mysqld_safe] log-error=/export/apps/var/log/mysqld.log pid-file=/export/apps/var/run/mysqld/mysqld.pid [client] default-character-set=utf8 [mysql] # CLIENT # port = 3306 socket = /export/apps/var/lib/mysql/mysql.sock
my SHOW VARIABLES is shown below
auto_increment_increment: 1 auto_increment_offset: 1 autocommit: ON automatic_sp_privileges: ON avoid_temporal_upgrade: OFF back_log: 150 basedir: /export/apps/moogsoft/dist/6.5.0/cots/mysql/ big_tables: OFF bind_address: * binlog_cache_size: 32768 binlog_checksum: CRC32 binlog_direct_non_transactional_updates: OFF binlog_error_action: ABORT_SERVER binlog_format: ROW binlog_group_commit_sync_delay: 0 binlog_group_commit_sync_no_delay_count: 0 binlog_gtid_simple_recovery: ON binlog_max_flush_queue_time: 0 binlog_order_commits: ON binlog_row_image: FULL binlog_rows_query_log_events: OFF binlog_stmt_cache_size: 32768 binlog_transaction_dependency_history_size: 25000 binlog_transaction_dependency_tracking: COMMIT_ORDER block_encryption_mode: aes-128-ecb bulk_insert_buffer_size: 8388608 character_set_client: utf8 character_set_connection: utf8 character_set_database: utf8 character_set_filesystem: binary character_set_results: utf8 character_set_server: utf8 character_set_system: utf8 character_sets_dir: /export/apps/moogsoft/dist/6.5.0/cots/mysql/share/charsets/ check_proxy_users: OFF collation_connection: utf8_general_ci collation_database: utf8_general_ci collation_server: utf8_general_ci completion_type: NO_CHAIN concurrent_insert: AUTO connect_timeout: 10 core_file: OFF datadir: /export/apps/moogdata/var/lib/mysql/ date_format: %Y-%m-%d datetime_format: %Y-%m-%d %H:%i:%s default_authentication_plugin: mysql_native_password default_password_lifetime: 0 default_storage_engine: InnoDB default_tmp_storage_engine: MyISAM default_week_format: 0 delay_key_write: ON delayed_insert_limit: 100 delayed_insert_timeout: 300 delayed_queue_size: 1000 disabled_storage_engines: disconnect_on_expired_password: ON div_precision_increment: 4 end_markers_in_json: OFF enforce_gtid_consistency: OFF eq_range_index_dive_limit: 200 error_count: 0 event_scheduler: OFF expire_logs_days: 14 explicit_defaults_for_timestamp: OFF 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: /export/apps/moogdata/var/lib/mysql/plv-fs7-a003.log group_concat_max_len: 1048576 gtid_executed_compression_period: 1000 gtid_mode: OFF gtid_next: AUTOMATIC gtid_owned: gtid_purged: have_compress: YES have_crypt: YES have_dynamic_loading: YES have_geometry: YES have_openssl: DISABLED have_profiling: YES have_query_cache: YES have_rtree_keys: YES have_ssl: DISABLED have_statement_timeout: YES have_symlink: DISABLED host_cache_size: 628 hostname: plv-fs7-a003 identity: 0 ignore_builtin_innodb: OFF ignore_db_dirs: init_connect: SET NAMES utf8 init_file: init_slave: innodb_adaptive_flushing: ON innodb_adaptive_flushing_lwm: 10 innodb_adaptive_hash_index: ON innodb_adaptive_hash_index_parts: 8 innodb_adaptive_max_sleep_delay: 150000 innodb_api_bk_commit_interval: 5 innodb_api_disable_rowlock: OFF innodb_api_enable_binlog: OFF innodb_api_enable_mdl: OFF innodb_api_trx_level: 0 innodb_autoextend_increment: 64 innodb_autoinc_lock_mode: 0 innodb_buffer_pool_chunk_size: 134217728 innodb_buffer_pool_dump_at_shutdown: ON innodb_buffer_pool_dump_now: OFF innodb_buffer_pool_dump_pct: 25 innodb_buffer_pool_filename: ib_buffer_pool innodb_buffer_pool_instances: 64 innodb_buffer_pool_load_abort: OFF innodb_buffer_pool_load_at_startup: ON innodb_buffer_pool_load_now: OFF innodb_buffer_pool_size: 120259084288 innodb_change_buffer_max_size: 25 innodb_change_buffering: all innodb_checksum_algorithm: crc32 innodb_checksums: ON innodb_cmp_per_index_enabled: OFF innodb_commit_concurrency: 0 innodb_compression_failure_threshold_pct: 5 innodb_compression_level: 6 innodb_compression_pad_pct_max: 50 innodb_concurrency_tickets: 5000 innodb_data_file_path: ibdata1:12M:autoextend innodb_data_home_dir: innodb_deadlock_detect: ON innodb_default_row_format: dynamic innodb_disable_sort_file_cache: OFF innodb_doublewrite: ON innodb_fast_shutdown: 1 innodb_file_format: Barracuda innodb_file_format_check: ON innodb_file_format_max: Barracuda innodb_file_per_table: ON innodb_fill_factor: 100 innodb_flush_log_at_timeout: 1 innodb_flush_log_at_trx_commit: 1 innodb_flush_method: O_DIRECT innodb_flush_neighbors: 1 innodb_flush_sync: ON innodb_flushing_avg_loops: 30 innodb_force_load_corrupted: OFF innodb_force_recovery: 0 innodb_ft_aux_table: innodb_ft_cache_size: 8000000 innodb_ft_enable_diag_print: OFF innodb_ft_enable_stopword: ON innodb_ft_max_token_size: 84 innodb_ft_min_token_size: 3 innodb_ft_num_word_optimize: 2000 innodb_ft_result_cache_limit: 2000000000 innodb_ft_server_stopword_table: innodb_ft_sort_pll_degree: 2 innodb_ft_total_cache_size: 640000000 innodb_ft_user_stopword_table: innodb_io_capacity: 200 innodb_io_capacity_max: 2000 innodb_large_prefix: ON innodb_lock_wait_timeout: 50 innodb_locks_unsafe_for_binlog: OFF innodb_log_buffer_size: 16777216 innodb_log_checksums: ON innodb_log_compressed_pages: ON innodb_log_file_size: 1073741824 innodb_log_files_in_group: 2 innodb_log_group_home_dir: ./ innodb_log_write_ahead_size: 8192 innodb_lru_scan_depth: 1024 innodb_max_dirty_pages_pct: 75.000000 innodb_max_dirty_pages_pct_lwm: 0.000000 innodb_max_purge_lag: 0 innodb_max_purge_lag_delay: 0 innodb_max_undo_log_size: 1073741824 innodb_monitor_disable: innodb_monitor_enable: innodb_monitor_reset: innodb_monitor_reset_all: innodb_numa_interleave: OFF innodb_old_blocks_pct: 37 innodb_old_blocks_time: 1000 innodb_online_alter_log_max_size: 134217728 innodb_open_files: 4096 innodb_optimize_fulltext_only: OFF innodb_page_cleaners: 4 innodb_page_size: 16384 innodb_print_all_deadlocks: OFF innodb_purge_batch_size: 300 innodb_purge_rseg_truncate_frequency: 128 innodb_purge_threads: 4 innodb_random_read_ahead: OFF innodb_read_ahead_threshold: 56 innodb_read_io_threads: 4 innodb_read_only: OFF innodb_replication_delay: 0 innodb_rollback_on_timeout: OFF innodb_rollback_segments: 128 innodb_sort_buffer_size: 1048576 innodb_spin_wait_delay: 6 innodb_stats_auto_recalc: ON innodb_stats_include_delete_marked: OFF innodb_stats_method: nulls_equal innodb_stats_on_metadata: OFF innodb_stats_persistent: ON innodb_stats_persistent_sample_pages: 20 innodb_stats_sample_pages: 8 innodb_stats_transient_sample_pages: 8 innodb_status_output: OFF innodb_status_output_locks: OFF innodb_strict_mode: ON innodb_support_xa: ON innodb_sync_array_size: 1 innodb_sync_spin_loops: 30 innodb_table_locks: ON innodb_temp_data_file_path: ibtmp1:12M:autoextend innodb_thread_concurrency: 0 innodb_thread_sleep_delay: 10000 innodb_tmpdir: innodb_undo_directory: ./ innodb_undo_log_truncate: OFF innodb_undo_logs: 128 innodb_undo_tablespaces: 0 innodb_use_native_aio: ON innodb_version: 5.7.22 innodb_write_io_threads: 4 insert_id: 0 interactive_timeout: 28800 internal_tmp_disk_storage_engine: InnoDB join_buffer_size: 262144 keep_files_on_create: OFF key_buffer_size: 33554432 key_cache_age_threshold: 300 key_cache_block_size: 1024 key_cache_division_limit: 100 keyring_operations: ON large_files_support: ON large_page_size: 0 large_pages: OFF last_insert_id: 0 lc_messages: en_US lc_messages_dir: /export/apps/moogsoft/dist/6.5.0/cots/mysql/share/ lc_time_names: en_US license: GPL local_infile: ON lock_wait_timeout: 31536000 locked_in_memory: OFF log_bin: ON log_bin_basename: /export/apps/moogdata/var/lib/mysql/mysql-bin log_bin_index: /export/apps/moogdata/var/lib/mysql/mysql-bin.index log_bin_trust_function_creators: OFF log_bin_use_v1_row_events: OFF log_builtin_as_identified_by_password: OFF log_error: /export/apps/moogdata/var/log/mysqld.log log_error_verbosity: 3 log_output: FILE log_queries_not_using_indexes: OFF log_slave_updates: OFF log_slow_admin_statements: OFF log_slow_slave_statements: OFF log_statements_unsafe_for_binlog: ON log_syslog: OFF log_syslog_facility: daemon log_syslog_include_pid: ON log_syslog_tag: log_throttle_queries_not_using_indexes: 0 log_timestamps: SYSTEM log_warnings: 2 long_query_time: 10.000000 low_priority_updates: OFF lower_case_file_system: OFF lower_case_table_names: 0 master_info_repository: FILE master_verify_checksum: OFF max_allowed_packet: 134217728 max_binlog_cache_size: 18446744073709547520 max_binlog_size: 1073741824 max_binlog_stmt_cache_size: 18446744073709547520 max_connect_errors: 1000000 max_connections: 500 max_delayed_threads: 20 max_digest_length: 1024 max_error_count: 64 max_execution_time: 0 max_heap_table_size: 33554432 max_insert_delayed_threads: 20 max_join_size: 18446744073709551615 max_length_for_sort_data: 1024 max_points_in_geometry: 65536 max_prepared_stmt_count: 1048576 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 metadata_locks_hash_instances: 8 min_examined_row_limit: 0 multi_range_count: 256 myisam_data_pointer_size: 6 myisam_max_sort_file_size: 9223372036853727232 myisam_mmap_size: 18446744073709551615 myisam_recover_options: BACKUP,FORCE myisam_repair_threads: 1 myisam_sort_buffer_size: 8388608 myisam_stats_method: nulls_unequal myisam_use_mmap: OFF mysql_native_password_proxy_users: OFF net_buffer_length: 16384 net_read_timeout: 30 net_retry_count: 10 net_write_timeout: 60 new: OFF ngram_token_size: 2 offline_mode: OFF old: OFF old_alter_table: OFF old_passwords: 0 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,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on optimizer_trace: enabled=off,one_line=off optimizer_trace_features: greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on optimizer_trace_limit: 1 optimizer_trace_max_mem_size: 16384 optimizer_trace_offset: -1 parser_max_mem_size: 18446744073709551615 performance_schema: OFF performance_schema_accounts_size: 0 performance_schema_digests_size: 0 performance_schema_events_stages_history_long_size: 0 performance_schema_events_stages_history_size: 0 performance_schema_events_statements_history_long_size: 0 performance_schema_events_statements_history_size: 0 performance_schema_events_transactions_history_long_size: 0 performance_schema_events_transactions_history_size: 0 performance_schema_events_waits_history_long_size: 0 performance_schema_events_waits_history_size: 0 performance_schema_hosts_size: 0 performance_schema_max_cond_classes: 0 performance_schema_max_cond_instances: 0 performance_schema_max_digest_length: 0 performance_schema_max_file_classes: 0 performance_schema_max_file_handles: 0 performance_schema_max_file_instances: 0 performance_schema_max_index_stat: 0 performance_schema_max_memory_classes: 0 performance_schema_max_metadata_locks: 0 performance_schema_max_mutex_classes: 0 performance_schema_max_mutex_instances: 0 performance_schema_max_prepared_statements_instances: 0 performance_schema_max_program_instances: 0 performance_schema_max_rwlock_classes: 0 performance_schema_max_rwlock_instances: 0 performance_schema_max_socket_classes: 0 performance_schema_max_socket_instances: 0 performance_schema_max_sql_text_length: 0 performance_schema_max_stage_classes: 0 performance_schema_max_statement_classes: 0 performance_schema_max_statement_stack: 0 performance_schema_max_table_handles: 0 performance_schema_max_table_instances: 0 performance_schema_max_table_lock_stat: 0 performance_schema_max_thread_classes: 0 performance_schema_max_thread_instances: 0 performance_schema_session_connect_attrs_size: 0 performance_schema_setup_actors_size: 0 performance_schema_setup_objects_size: 0 performance_schema_users_size: 0 pid_file: /export/apps/moogdata/var/run/mysqld/mysqld.pid plugin_dir: /export/apps/moogsoft/cots/mysql/lib/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: 352403 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 range_optimizer_max_mem_size: 8388608 rbr_exec_mode: STRICT read_buffer_size: 131072 read_only: OFF read_rnd_buffer_size: 262144 relay_log: relay_log_basename: /export/apps/moogdata/var/lib/mysql/plv-fs7-a003-relay-bin relay_log_index: /export/apps/moogdata/var/lib/mysql/plv-fs7-a003-relay-bin.index relay_log_info_file: relay-log.info relay_log_info_repository: FILE relay_log_purge: ON relay_log_recovery: OFF relay_log_space_limit: 0 report_host: report_password: report_port: 3306 report_user: require_secure_transport: OFF rpl_stop_slave_timeout: 31536000 secure_auth: ON secure_file_priv: NULL server_id: 1 server_id_bits: 32 server_uuid: 4873ca07-95d1-11e8-9ae6-0050569f2825 session_track_gtids: OFF session_track_schema: ON session_track_state_change: OFF session_track_system_variables: time_zone,autocommit,character_set_client,character_set_results,character_set_connection session_track_transaction_info: OFF sha256_password_proxy_users: OFF show_compatibility_56: OFF show_create_table_verbosity: OFF show_old_temporals: OFF skip_external_locking: ON skip_name_resolve: OFF skip_networking: OFF skip_show_database: OFF slave_allow_batching: OFF slave_checkpoint_group: 512 slave_checkpoint_period: 300 slave_compressed_protocol: OFF slave_exec_mode: STRICT slave_load_tmpdir: /tmp slave_max_allowed_packet: 1073741824 slave_net_timeout: 60 slave_parallel_type: DATABASE slave_parallel_workers: 0 slave_pending_jobs_size_max: 16777216 slave_preserve_commit_order: OFF slave_rows_search_algorithms: TABLE_SCAN,INDEX_SCAN slave_skip_errors: OFF slave_sql_verify_checksum: ON slave_transaction_retries: 10 slave_type_conversions: slow_launch_time: 2 slow_query_log: OFF slow_query_log_file: /export/apps/moogdata/var/log/mysql-slow.log socket: /export/apps/moogdata/var/lib/mysql/mysql.sock sort_buffer_size: 262144 sql_auto_is_null: OFF sql_big_selects: ON sql_buffer_result: OFF sql_log_bin: ON sql_log_off: OFF sql_mode: 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_crl: ssl_crlpath: ssl_key: stored_program_cache: 256 super_read_only: OFF sync_binlog: 1 sync_frm: ON sync_master_info: 10000 sync_relay_log: 10000 sync_relay_log_info: 10000 system_time_zone: EST table_definition_cache: 4096 table_open_cache: 4096 table_open_cache_instances: 16 thread_cache_size: 50 thread_handling: one-thread-per-connection thread_stack: 262144 time_format: %H:%i:%s time_zone: SYSTEM timestamp: 1544544038.114917 tls_version: TLSv1,TLSv1.1 tmp_table_size: 33554432 tmpdir: /tmp transaction_alloc_block_size: 8192 transaction_allow_batching: OFF transaction_isolation: REPEATABLE-READ transaction_prealloc_size: 4096 transaction_read_only: OFF transaction_write_set_extraction: OFF tx_isolation: REPEATABLE-READ tx_read_only: OFF unique_checks: ON updatable_views_with_limit: YES version: 5.7.22-log version_comment: MySQL Community Server (GPL) version_compile_machine: x86_64 version_compile_os: el7 wait_timeout: 28800 warning_count: 0
my show global status is shown below
Aborted_clients: 38 Aborted_connects: 5 Binlog_cache_disk_use: 4124761 Binlog_cache_use: 5861660 Binlog_stmt_cache_disk_use: 0 Binlog_stmt_cache_use: 67574 Bytes_received: 59138986898 Bytes_sent: 530596463801 Com_admin_commands: 35963531 Com_assign_to_keycache: 0 Com_alter_db: 0 Com_alter_db_upgrade: 0 Com_alter_event: 0 Com_alter_function: 0 Com_alter_instance: 0 Com_alter_procedure: 0 Com_alter_server: 0 Com_alter_table: 0 Com_alter_tablespace: 0 Com_alter_user: 0 Com_analyze: 0 Com_begin: 0 Com_binlog: 0 Com_call_procedure: 30007167 Com_change_db: 11063333 Com_change_master: 0 Com_change_repl_filter: 0 Com_check: 0 Com_checksum: 0 Com_commit: 6181870 Com_create_db: 0 Com_create_event: 0 Com_create_function: 0 Com_create_index: 0 Com_create_procedure: 0 Com_create_server: 0 Com_create_table: 36412 Com_create_trigger: 0 Com_create_udf: 0 Com_create_user: 0 Com_create_view: 2 Com_dealloc_sql: 46 Com_delete: 21488 Com_delete_multi: 3 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: 62322 Com_drop_trigger: 0 Com_drop_user: 0 Com_drop_view: 0 Com_empty_query: 0 Com_execute_sql: 46 Com_explain_other: 0 Com_flush: 0 Com_get_diagnostics: 0 Com_grant: 0 Com_ha_close: 0 Com_ha_open: 0 Com_ha_read: 0 Com_help: 0 Com_insert: 23777467 Com_insert_select: 187339 Com_install_plugin: 0 Com_kill: 3 Com_load: 0 Com_lock_tables: 0 Com_optimize: 0 Com_preload_keys: 0 Com_prepare_sql: 46 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: 1 Com_replace_select: 303 Com_reset: 0 Com_resignal: 0 Com_revoke: 0 Com_revoke_all: 0 Com_rollback: 5751979 Com_rollback_to_savepoint: 0 Com_savepoint: 0 Com_select: 115368019 Com_set_option: 23343767 Com_signal: 0 Com_show_binlog_events: 0 Com_show_binlogs: 0 Com_show_charsets: 0 Com_show_collations: 0 Com_show_create_db: 0 Com_show_create_event: 0 Com_show_create_func: 0 Com_show_create_proc: 2788 Com_show_create_table: 1 Com_show_create_trigger: 0 Com_show_databases: 5 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: 2673 Com_show_function_code: 0 Com_show_function_status: 0 Com_show_grants: 0 Com_show_keys: 0 Com_show_master_status: 0 Com_show_open_tables: 0 Com_show_plugins: 0 Com_show_privileges: 0 Com_show_procedure_code: 0 Com_show_procedure_status: 0 Com_show_processlist: 13 Com_show_profile: 0 Com_show_profiles: 0 Com_show_relaylog_events: 0 Com_show_slave_hosts: 0 Com_show_slave_status: 0 Com_show_status: 16 Com_show_storage_engines: 0 Com_show_table_status: 0 Com_show_tables: 6 Com_show_triggers: 0 Com_show_variables: 627 Com_show_warnings: 1816 Com_show_create_user: 0 Com_shutdown: 0 Com_slave_start: 0 Com_slave_stop: 0 Com_group_replication_start: 0 Com_group_replication_stop: 0 Com_stmt_execute: 35092606 Com_stmt_close: 30409543 Com_stmt_fetch: 0 Com_stmt_prepare: 30424599 Com_stmt_reset: 0 Com_stmt_send_long_data: 0 Com_truncate: 0 Com_uninstall_plugin: 0 Com_unlock_tables: 0 Com_update: 13801071 Com_update_multi: 11408 Com_xa_commit: 0 Com_xa_end: 0 Com_xa_prepare: 0 Com_xa_recover: 0 Com_xa_rollback: 0 Com_xa_start: 0 Com_stmt_reprepare: 14916 Connection_errors_accept: 0 Connection_errors_internal: 0 Connection_errors_max_connections: 0 Connection_errors_peer_address: 0 Connection_errors_select: 0 Connection_errors_tcpwrap: 0 Connections: 352218 Created_tmp_disk_tables: 489945 Created_tmp_files: 18298 Created_tmp_tables: 30397368 Delayed_errors: 0 Delayed_insert_threads: 0 Delayed_writes: 0 Flush_commands: 1 Handler_commit: 164180042 Handler_delete: 1540124 Handler_discover: 0 Handler_external_lock: 363121437 Handler_mrr_init: 0 Handler_prepare: 76870832 Handler_read_first: 1001966 Handler_read_key: 14364081720 Handler_read_last: 5637 Handler_read_next: 238716528603 Handler_read_prev: 18095734 Handler_read_rnd: 18155362 Handler_read_rnd_next: 10902947702 Handler_rollback: 15509 Handler_savepoint: 0 Handler_savepoint_rollback: 0 Handler_update: 928105331 Handler_write: 2637249215 Innodb_buffer_pool_dump_status: Dumping of buffer pool not started Innodb_buffer_pool_load_status: Buffer pool(s) load completed at 181207 9:15:39 Innodb_buffer_pool_resize_status: Innodb_buffer_pool_pages_data: 7251622 Innodb_buffer_pool_bytes_data: 118810574848 Innodb_buffer_pool_pages_dirty: 4884 Innodb_buffer_pool_bytes_dirty: 80019456 Innodb_buffer_pool_pages_flushed: 20329076 Innodb_buffer_pool_pages_free: 66956 Innodb_buffer_pool_pages_misc: 20558 Innodb_buffer_pool_pages_total: 7339136 Innodb_buffer_pool_read_ahead_rnd: 0 Innodb_buffer_pool_read_ahead: 107714 Innodb_buffer_pool_read_ahead_evicted: 0 Innodb_buffer_pool_read_requests: 98185985554 Innodb_buffer_pool_reads: 5845940 Innodb_buffer_pool_wait_free: 0 Innodb_buffer_pool_write_requests: 739684384 Innodb_data_fsyncs: 11936945 Innodb_data_pending_fsyncs: 0 Innodb_data_pending_reads: 0 Innodb_data_pending_writes: 0 Innodb_data_read: 103355748864 Innodb_data_reads: 6248322 Innodb_data_writes: 28597724 Innodb_data_written: 813356351488 Innodb_dblwr_pages_written: 20145708 Innodb_dblwr_writes: 2086282 Innodb_log_waits: 0 Innodb_log_write_requests: 294235578 Innodb_log_writes: 6087075 Innodb_os_log_fsyncs: 6106003 Innodb_os_log_pending_fsyncs: 0 Innodb_os_log_pending_writes: 0 Innodb_os_log_written: 150224264192 Innodb_page_size: 16384 Innodb_pages_created: 1856385 Innodb_pages_read: 6227743 Innodb_pages_written: 20329076 Innodb_row_lock_current_waits: 0 Innodb_row_lock_time: 11303943 Innodb_row_lock_time_avg: 18 Innodb_row_lock_time_max: 51767 Innodb_row_lock_waits: 622765 Innodb_rows_deleted: 884649 Innodb_rows_inserted: 34609353 Innodb_rows_read: 252699469534 Innodb_rows_updated: 68302123 Innodb_num_open_files: 153 Innodb_truncated_status_writes: 0 Innodb_available_undo_logs: 128 Key_blocks_not_flushed: 0 Key_blocks_unused: 26780 Key_blocks_used: 2720 Key_read_requests: 3379877 Key_reads: 10837 Key_write_requests: 712345 Key_writes: 0 Locked_connects: 0 Max_execution_time_exceeded: 0 Max_execution_time_set: 0 Max_execution_time_set_failed: 0 Max_used_connections: 180 Max_used_connections_time: 2018-12-07 22:03:52 Not_flushed_delayed_rows: 0 Ongoing_anonymous_transaction_count: 0 Open_files: 43 Open_streams: 0 Open_table_definitions: 278 Open_tables: 2742 Opened_files: 234387 Opened_table_definitions: 62607 Opened_tables: 33927 Performance_schema_accounts_lost: 0 Performance_schema_cond_classes_lost: 0 Performance_schema_cond_instances_lost: 0 Performance_schema_digest_lost: 0 Performance_schema_file_classes_lost: 0 Performance_schema_file_handles_lost: 0 Performance_schema_file_instances_lost: 0 Performance_schema_hosts_lost: 0 Performance_schema_index_stat_lost: 0 Performance_schema_locker_lost: 0 Performance_schema_memory_classes_lost: 0 Performance_schema_metadata_lock_lost: 0 Performance_schema_mutex_classes_lost: 0 Performance_schema_mutex_instances_lost: 0 Performance_schema_nested_statement_lost: 0 Performance_schema_prepared_statements_lost: 0 Performance_schema_program_lost: 0 Performance_schema_rwlock_classes_lost: 0 Performance_schema_rwlock_instances_lost: 0 Performance_schema_session_connect_attrs_lost: 0 Performance_schema_socket_classes_lost: 0 Performance_schema_socket_instances_lost: 0 Performance_schema_stage_classes_lost: 0 Performance_schema_statement_classes_lost: 0 Performance_schema_table_handles_lost: 0 Performance_schema_table_instances_lost: 0 Performance_schema_table_lock_stat_lost: 0 Performance_schema_thread_classes_lost: 0 Performance_schema_thread_instances_lost: 0 Performance_schema_users_lost: 0 Prepared_stmt_count: 33 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: 796983903 Questions: 103279196 Select_full_join: 70140 Select_full_range_join: 3 Select_range: 513351 Select_range_check: 0 Select_scan: 763875 Slave_open_temp_tables: 0 Slow_launch_threads: 0 Slow_queries: 0 Sort_merge_passes: 9206 Sort_range: 111772 Sort_rows: 53412633 Sort_scan: 1877589 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_server_not_after: Ssl_server_not_before: 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: 9609 Table_locks_waited: 0 Table_open_cache_hits: 235364446 Table_open_cache_misses: 2764 Table_open_cache_overflows: 15 Tc_log_max_pages_used: 0 Tc_log_page_size: 0 Tc_log_page_waits: 0 Threads_cached: 6 Threads_connected: 174 Threads_created: 180 Threads_running: 2 Uptime: 351789 Uptime_since_flush_status: 351789
Subject
Written By
Posted
Mysql memory usage increasing until it hits the RAM limit and starts using swap
December 11, 2018 10:23AM
December 11, 2018 10:26AM
December 11, 2018 04:18PM
December 11, 2018 07:38PM
December 11, 2018 08:15PM
April 15, 2019 05:47AM
April 15, 2019 07:36AM
April 15, 2019 09:07AM
April 17, 2019 10:52AM
Sorry, you can't reply to this topic. It has been closed.
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.