Mysql
mysql 記憶體不足：殺死程序 - centos linux 版本 7.2.1511 上的 mysql 5.7.16
mysql 5.5從升級到mysql ver 14.14 distrib 5.7.16on後我遇到了問題CentOS Linux release 7.2.1511 (Core)mysql systemd: Stopping user-0.slice. mysql kernel: mysqld invoked oom-killer: gfp_mask=0x280da, order=0, oom_score_adj=0 mysql kernel: mysqld cpuset=/ mems_allowed=0 mysql kernel: CPU: 0 PID: 30238 Comm: mysqld Not tainted 3.10.0-327.36.3.el7.x86_64 #1 mysql kernel: Hardware name: Xen HVM domU, BIOS 4.2.amazon 11/11/2016
dmesg[461124.941170] Out of memory: Kill process 30048 (mysqld) score 980 or sacrifice child [461124.946789] Killed process 30048 (mysqld) total-vm:31170916kB, anon-rss:30313888kB, file-rss:0kB伺服器：
r3.xlarge 4 vCPU and 30.5GB Memory未啟用交換。
max_allowed_packet: 1073741824我需要更改
innodb_log_file_size = 1000M為 25%innodb_buffer_pool_size=12G嗎？任何幫助將非常感激！
/etc/my.cnf[mysql.server] service-startup-timeout = -1 [client] port=3306 [mysql] default-character-set=utf8 [mysqld] relay-log=/mnt/data/mysql/mysqld-relay-bin innodb_flush_method = O_DIRECT tmpdir=/mnt/data/tmp binlog-format=row port = 3306 max_allowed_packet = 1G socket = /var/lib/mysql/mysql.sock user = mysql lower_case_table_names = 1 log_bin_trust_function_creators = 1 slave-net-timeout = 60 log-bin = mysql-bin expire_logs_days=21 server-id = 162 datadir=/mnt/data/mysql character-set-server = utf8 default-storage-engine = INNODB sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=4000 query_cache_size=0 query_cache_limit=0 query_cache_type=0 table_open_cache = 1520 log-error = /var/log/mysqld.log tmp_table_size = 1G max_heap_table_size = 1G thread_cache_size = 120 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 40M innodb_buffer_pool_size=12G innodb_log_file_size = 1000M innodb_thread_concurrency = 8 log-queries-not-using-indexes long_query_time=1 wait_timeout=43200 interactive_timeout=43200 innodb_lock_wait_timeout = 120 group_concat_max_len = 1M innodb-file-per-table = 1 innodb-flush-method = O_DIRECT innodb-thread-concurrency = 0 sync_binlog = 1 slow-query-log = 1 slow-query-log-file = /mnt/data/mysql/mysql_slow_queries.log skip_ssl skip_name_resolve explicit_defaults_for_timestamp = 0 innodb_buffer_pool_instances = 1 innodb_page_cleaners = 1 event_scheduler = ON [mysqld_safe] timezone = UTC
mysqld.service:[Unit] Description=MySQL Server After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql Type=forking PIDFile=/var/run/mysqld/mysqld.pid TimeoutSec=300 PermissionsStartOnly=true ExecStartPre=/bin/mkdir -p /var/run/mysqld ExecStartPre=/bin/chown -R mysql:mysql /mnt/data/ ExecStartPre=/bin/chown -R mysql:mysql /var/lib/mysql/ ExecStartPre=/bin/chown -R mysql:mysql /var/run/mysqld/ ExecStartPre=/usr/bin/mysqld_pre_systemd ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS EnvironmentFile=-/etc/sysconfig/mysql LimitNOFILE=40000 LimitMEMLOCK=infinity Restart=on-failure RestartPreventExitStatus=1 PrivateTmp=false
ulimit -acore file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 121914 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 65535 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 65535 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
mysqltuner[OK] Operating on 64-bit architecture Log file Recommendations [--] Log file: /var/log/mysqld.log(1M) [OK] Log file /var/log/mysqld.log exists [OK] Log file /var/log/mysqld.log is readable. [OK] Log file /var/log/mysqld.log is not empty [OK] Log file /var/log/mysqld.log is smaller than 32 Mb [!!] /var/log/mysqld.log contains 1592 warning(s). [!!] /var/log/mysqld.log contains 6323 error(s). [--] 10 start(s) detected in /var/log/mysqld.log 12 shutdown(s) detected in /var/log/mysqld.log Storage Engine Statistics [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 12K (Tables: 9) [--] Data in InnoDB tables: 7638G (Tables: 3497) [!!] Total fragmented tables: 2 -------- Performance Metrics [--] Up for: 10h 17m 16s (31M q [838.327 qps], 20K conn, TX: 123G, RX: 24G) [--] Reads / Writes: 68% / 32% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 29.6G [--] Max MySQL memory : 17.4G [--] Other process memory: 149.8M [--] Total buffers: 13.0G global + 1.1M per thread (4000 max threads) [--] P_S Max memory usage: 72B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 13.5G (45.72% of installed RAM) [OK] Maximum possible memory usage: 17.4G (59.02% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 1% (379K/31M) [OK] Highest usage of available connections: 10% (423/4000) [OK] Aborted connections: 0.02% (5/20354) [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [!!] Sorts requiring temporary tables: 12% (65K temp sorts / 514K sorts) [!!] Joins performed without indexes: 60429 [OK] Temporary tables created on disk: 12% (121K on disk / 972K total) [OK] Thread cache hit rate: 97% (487 created / 20K connections) [!!] Table cache hit rate: 0% (1K open / 975K opened) [OK] Open file limit used: 0% (78/40K) [OK] Table locks acquired immediately: 100% (31K immediate / 31K locks) [OK] Binlog cache memory access: 99.35% (705658 Memory / 710308 Total) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is enabled. [--] Memory used by P_S: 72B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.3% (1M used / 8M cache) [OK] Key buffer size / total MyISAM indexes: 8.0M/81.0K [OK] Read Key buffer hit rate: 99.7% (68K cached / 194 reads) [!!] Write Key buffer hit rate: 0.0% (32 cached / 32 writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 12.0G/7638.7G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (16.2760416666667 %): 1000.0M * 2/12.0G should be equal 25% [!!] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 96 for 1 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 99.99% (20560413998 hits/ 20562576302 total) [OK] InnoDB Write log efficiency: 96.90% (23550427 hits/ 24303035 total) [OK] InnoDB log waits: 0.00% (0 waits / 752608 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled. -------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled. -------- Connect Metrics --------------------------------------------------------------------------- [--] Connect is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server. -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysqld.log file Control error line(s) into /var/log/mysqld.log file Run OPTIMIZE TABLE to defragment tables for better performance OPTIMIZE TABLE prod.qc_connector_log; -- can free 1479 MB OPTIMIZE TABLE prod.transport_object; -- can free 1038 MB Total freed space after theses OPTIMIZE TABLE : 2517 Mb Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) Restrict Host for user@% to user@SpecificDNSorIp MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Beware that open_files_limit (40000) variable should be greater than table_open_cache (1520) Variables to adjust: sort_buffer_size (> 256K) read_rnd_buffer_size (> 256K) join_buffer_size (> 256.0K, or always use indexes with joins) table_open_cache (> 1520) innodb_buffer_pool_size (>= 7638G) if possible. innodb_log_file_size should be equals to 1/4 of buffer pool size (=3G) if possible. innodb_buffer_pool_instances(=12)有 IOWAIT，AFAIK 這不是崩潰的原因。
SHOW VARIABLES;auto_increment_increment 1 auto_increment_offset 1 autocommit ON automatic_sp_privileges ON avoid_temporal_upgrade OFF back_log 850 basedir /usr/ 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 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 /usr/share/mysql/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 /mnt/data/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 InnoDB 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 ON expire_logs_days 21 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 /mnt/data/mysql/mysql.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 YES host_cache_size 803 hostname mysql identity 0 ignore_builtin_innodb OFF ignore_db_dirs init_connect 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 1 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 1 innodb_buffer_pool_load_abort OFF innodb_buffer_pool_load_at_startup ON innodb_buffer_pool_load_now OFF innodb_buffer_pool_size 12884901888 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 2 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 120 innodb_locks_unsafe_for_binlog OFF innodb_log_buffer_size 41943040 innodb_log_checksums ON innodb_log_compressed_pages ON innodb_log_file_size 1048576000 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_old_blocks_pct 37 innodb_old_blocks_time 1000 innodb_online_alter_log_max_size 134217728 innodb_open_files 1520 innodb_optimize_fulltext_only OFF innodb_page_cleaners 1 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_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.16 innodb_write_io_threads 4 insert_id 0 interactive_timeout 43200 internal_tmp_disk_storage_engine InnoDB join_buffer_size 262144 keep_files_on_create OFF key_buffer_size 8388608 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_bin ON log_bin_basename /mnt/data/mysql/mysql-bin log_bin_index /mnt/data/mysql/mysql-bin.index log_bin_trust_function_creators ON log_bin_use_v1_row_events OFF log_builtin_as_identified_by_password OFF log_error /var/log/mysqld.log log_error_verbosity 3 log_output FILE log_queries_not_using_indexes ON 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 UTC log_warnings 2 long_query_time 1.000000 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 1 master_info_repository FILE master_verify_checksum OFF max_allowed_packet 1073741824 max_binlog_cache_size 18446744073709547520 max_binlog_size 1073741824 max_binlog_stmt_cache_size 18446744073709547520 max_connect_errors 100 max_connections 4000 max_delayed_threads 20 max_digest_length 1024 max_error_count 64 max_execution_time 0 max_heap_table_size 1073741824 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_points_in_geometry 65536 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 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 OFF 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 40000 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 ON performance_schema_accounts_size -1 performance_schema_digests_size 10000 performance_schema_events_stages_history_long_size 10000 performance_schema_events_stages_history_size 10 performance_schema_events_statements_history_long_size 10000 performance_schema_events_statements_history_size 10 performance_schema_events_transactions_history_long_size 10000 performance_schema_events_transactions_history_size 10 performance_schema_events_waits_history_long_size 10000 performance_schema_events_waits_history_size 10 performance_schema_hosts_size -1 performance_schema_max_cond_classes 80 performance_schema_max_cond_instances -1 performance_schema_max_digest_length 1024 performance_schema_max_file_classes 80 performance_schema_max_file_handles 32768 performance_schema_max_file_instances -1 performance_schema_max_index_stat -1 performance_schema_max_memory_classes 320 performance_schema_max_metadata_locks -1 performance_schema_max_mutex_classes 200 performance_schema_max_mutex_instances -1 performance_schema_max_prepared_statements_instances -1 performance_schema_max_program_instances -1 performance_schema_max_rwlock_classes 40 performance_schema_max_rwlock_instances -1 performance_schema_max_socket_classes 10 performance_schema_max_socket_instances -1 performance_schema_max_sql_text_length 1024 performance_schema_max_stage_classes 150 performance_schema_max_statement_classes 193 performance_schema_max_statement_stack 10 performance_schema_max_table_handles -1 performance_schema_max_table_instances -1 performance_schema_max_table_lock_stat -1 performance_schema_max_thread_classes 50 performance_schema_max_thread_instances -1 performance_schema_session_connect_attrs_size 512 performance_schema_setup_actors_size -1 performance_schema_setup_objects_size -1 performance_schema_users_size -1 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 21979 query_alloc_block_size 8192 query_cache_limit 0 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 /mnt/data/mysql/mysqld-relay-bin relay_log_basename /mnt/data/mysql/mysqld-relay-bin relay_log_index /mnt/data/mysql/mysqld-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 /var/lib/mysql-files/ server_id 162 server_id_bits 32 server_uuid 7303dd93-b4bc-11e6-ad68-0af6008454f1 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_old_temporals OFF skip_external_locking ON skip_name_resolve ON 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 /mnt/data/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 ON slow_query_log_file /mnt/data/mysql/mysql_slow_queries.log socket /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 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_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 UTC table_definition_cache 1160 table_open_cache 1520 table_open_cache_instances 16 thread_cache_size 120 thread_handling one-thread-per-connection thread_stack 262144 time_format %H:%i:%s time_zone SYSTEM timestamp 1482351343.091002 tls_version TLSv1,TLSv1.1 tmp_table_size 1073741824 tmpdir /mnt/data/tmp transaction_alloc_block_size 8192 transaction_allow_batching OFF transaction_prealloc_size 4096 transaction_write_set_extraction OFF tx_isolation REPEATABLE-READ tx_read_only OFF unique_checks ON updatable_views_with_limit YES version 5.7.16-log version_comment MySQL Community Server (GPL) version_compile_machine x86_64 version_compile_os Linux wait_timeout 28800 warning_count 0
max_connections=8000——不合理的高。如果您確實獲得了接近 8000 個連接，請在連接到 MySQL 之前對其進行限制tmp_table_size = 1G和– 由於可能為每個（和每個子查詢）分配max_heap_table_size = 1Gtmp 表，因此可能SELECT需要超過 8TB。我建議不超過 1% 的 RAM（每個），直到您發現需要更多。innodb_buffer_pool_size=9G– 15GB RAM 是合理的；那是你有多少？ 如果您急於從反复的崩潰中恢復，這是首先要減少的事情。group_concat_max_len = 1M- 建議只為那些將要發胖的連接設置此項GROUP_CONCATs。- 交換未啟用——交換對性能不利，但被殺死更糟。
swappiness = 1建議在作業系統中啟用交換和設置。如需更多評論，請提供
SHOW VARIABLES;和SHOW GLOBAL STATUS;。
根據問題作者的評論生成的社區 Wiki 答案
問題是
innodb_buffer_pool_instancesMySQL 5.7 上的預設值 8 和 MySQL 5.5 上的預設值 1，在我將其改回 1 後，問題解決了。無論如何，我覺得
max connections太高了，所以我會在開始時將它減少到 4000 並減少一點 innodb 緩衝池大小，或者可能會使用更多記憶體的機器。我知道 4000 很高，但我不想對生產做出巨大的改變，所以我把它從 8000 改為 4000，以後我可以減少更多。
Max_used_connections顯示391，我們配置Hibernate config connection pool size為800。我單獨更改並等待了兩天，數據庫沒有崩潰，但無論如何就像我之前所說的，我使用http://www.mysqlcalculator.com並將配置調整
my.cnf到我在伺服器上實際擁有的記憶體中。從 8000減少max_connections到 4000 為我節省了很多記憶體，我在 zabbix 上看到了。我使用了 32 GB 記憶體的m4.2xlarge
innodb_buffer_pool_size=15，並更改了,max_connections=4000.mysqlcalculator使用舊配置顯示我沒有足夠的記憶體讓 MySQL 正常執行，所以我認為總是需要使用 MySQL calc 檢查配置以了解伺服器上的記憶體使用情況。
澄清一下：我上面寫的調整設置是崩潰問題的解決方案，但中止的連接可能來自應用程序端，我仍然在日誌中看到這些註釋消息，但現在看來一切正常。