mysql - Mysql内存使用量不断增长，直到OOM和系统终止/重新启动

Question

我在Ubuntu 16.04.4、64位，4GB RAM和SSD（这是虚拟机）下安装了MySQL 5.7.27。我正在观察mysqld中的常驻内存增长，直到没有更多可用资源并且操作系统终止/重新启动mysqld为止。

背景知识-该系统用于从远程位置收集信息，并且从每个位置收集1个实体。每个实体可能具有数百个数据点。对于每个远程位置，都有两个数据库，它们支持两种情况：每个位置都是每个客户（但一个客户可能有成千上万个这样的位置），在某些情况下，可能从每个客户收集成千上万个实体偏远的地点。这两个数据库对应于原始数据收集和“处理”摘要。此外，每个客户还有一个额外的数据库，用于记录管理员以及有关每个远程位置的其他数据。因此，总DB数为C *（1 + 2L）-客户C，位置L。 2L的第一个DB有32个表，最小大小为2MB（但是我至少有一个示例，它为5.9GB-在当前样本集中没有）-在每个收集事件开始时“清除”；第二个有12个表，当前样本集中的典型大小为1MB（尽管每个收集事件都会累加）。

每个远程位置定期（例如每周一次或每月一次）执行一次远程收集。每次收集后，将运行一个过程，将来自所有2L数据库的数据汇总到一个统一的数据库中。

在有问题的系统中，C（1 + 2L）刚好超过5000。运行汇总过程时，mysqld常​​驻内存（使用top查看）从大约800MB增长到不足3GB。汇总过程完成，然后可能会在不久后再次运行。该过程将完全完成，并正确释放查询结果并关闭数据库连接。该应用程序是在本地运行的PHP 7.2.24 CLI（因此，使用套接字而不是TCP）。

我已经尝试过“调整”：

open_files_limit = 11000
table_open_cache = 5200

innodb_lru_scan_depth = 128
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1073741824

（请参见 open_files_limit， table_open_cache， innodb_lru_scan_depth， innodb_flush_log_at_trx_commit， innodb_buffer_pool_size）

这些要么没有明显变化，要么恶化了内存使用量的增长速度。我还在三个实例上运行了 show engine performance_schema status;：常驻内存分别为2459MB，2478MB和2910MB。在这三个样本之间，我仅看到以下指标之间的差异：

                                2459MB       2478MB        2910MB

(digest_hash).count             4676 ->      4837 ->       4958
(filename_hash).count         121118 ->    121139 ->     121214
(table_share_hash).count       27229 ->     27247 ->      27276
mutex_instances.count          29696 ->     30720 ->      31744
mutex_instances.memory       3801088 ->   3932160 ->    4063232
performance_scheme.memory  426293432 -> 426424504 -> 4266886648
rwlock_instances.count        400384 ->    400384 ->     401408
rwlock_instances.memory     51249152 ->  51249152 ->   51380224

我的期望是，在每次汇总运行结束时，将释放mysqld中的“临时”缓冲区空间，或者在该过程的下一次运行中简单地重用它们，因为它实际上是在进行相同的查询序列（和非常相似的更新）。内存占用量增长的事实向我暗示，要么mysqld中存在内存泄漏，要么是与某些故障情况（连接，锁，互斥锁？）相关的故意“缓冲区”至少是造成内存问题的一部分。增长。我建议使用后者，因为我将研究在总结过程的后续运行之间mysqld中将保留哪些内容。

我也尝试过运行mysqltuner.pl（请参见 http://mysqltuner.com），但这也会导致内存增长问题，这无疑与数据库的数量有关。但是，如果我指的是 https://dev.mysql.com/doc/refman/5.7/en/database-count-limit.html，无论MySQL对数据库和表的数量有无限制，这里的数字对mysqld本身都不重要，即使它们对底层OS也是如此。我承认我对服务器在不是非常高规格的系统上每秒处理超过1000 SELECTS印象深刻。

因此，我应该考虑使用哪些“监视”参数来帮助调试mysql，操作系统或平台，而在这种情况下，哪些（mysql / OS）调试参数最有帮助？我可以采用 Jaws方法（ "We're going to need a bigger boat"），但是如果我遇到的问题是内存泄漏，那么仅添加更多的RAM只会推迟不可避免的事情。

------------编辑里克·詹姆斯（Rick James）的回答（2020/01/13）-
内存-4GB

显示变量； （较少的SSL相关信息）输出：

auto_generate_certs  ON
auto_increment_increment    1
auto_increment_offset   1
autocommit   ON
automatic_sp_privileges  ON
avoid_temporal_upgrade   OFF
back_log    80
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
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     utf8mb4
character_set_connection     utf8mb4
character_set_database   utf8
character_set_filesystem     binary
character_set_results    utf8mb4
character_set_server     utf8
character_set_system     utf8
character_sets_dir   /usr/share/mysql/charsets/
check_proxy_users    OFF
collation_connection     utf8mb4_general_ci
collation_database   utf8_unicode_ci
collation_server     utf8_unicode_ci
completion_type  NO_CHAIN
concurrent_insert    AUTO
connect_timeout 10
core_file    OFF
datadir  /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   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  OFF
expire_logs_days    10
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     /var/lib/mysql/demo.log
group_concat_max_len    1024
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     YES
have_profiling   YES
have_query_cache     YES
have_rtree_keys  YES
have_ssl     YES
have_statement_timeout   YES
have_symlink     YES
host_cache_size 279
hostname    <hidden>
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 134217728
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  
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    50331648
innodb_log_files_in_group   2
innodb_log_group_home_dir    ./
innodb_log_write_ahead_size 8192
innodb_lru_scan_depth   128
innodb_max_dirty_pages_pct  75
innodb_max_dirty_pages_pct_lwm  0
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   5200
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_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.28
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 16777216
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  /usr/share/mysql/
lc_time_names    en_US
license  GPL
local_infile     ON
lock_wait_timeout   31536000
locked_in_memory     OFF
log_bin  OFF
log_bin_basename     
log_bin_index    
log_bin_trust_function_creators  OFF
log_bin_use_v1_row_events    OFF
log_builtin_as_identified_by_password    OFF
log_error    /var/log/mysql/error.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   UTC
log_warnings    2
long_query_time 10
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  16777216
max_binlog_cache_size   1.84467440737095E+019
max_binlog_size 104857600
max_binlog_stmt_cache_size  1.84467440737095E+019
max_connect_errors  100
max_connections 151
max_delayed_threads 20
max_digest_length   1024
max_error_count 64
max_execution_time  0
max_heap_table_size 16777216
max_insert_delayed_threads  20
max_join_size   1.84467440737096E+019
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   1.84467440737096E+019
max_sort_length 1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    1.84467440737096E+019
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   9.22337203685373E+018
myisam_mmap_size    1.84467440737096E+019
myisam_recover_options   BACKUP
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    11000
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 1.84467440737096E+019
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    210
performance_schema_max_mutex_instances  -1
performance_schema_max_prepared_statements_instances    -1
performance_schema_max_program_instances    -1
performance_schema_max_rwlock_classes   50
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/lib/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    8224
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    16777216
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   /var/lib/mysql/demo-relay-bin
relay_log_index  /var/lib/mysql/demo-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     ON
rpl_stop_slave_timeout  31536000
secure_auth  ON
secure_file_priv     /var/lib/mysql-files/
server_id   0
server_id_bits  32
server_uuid <hidden>
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_auto_generate_rsa_keys   ON
sha256_password_private_key_path     private_key.pem
sha256_password_proxy_users  OFF
sha256_password_public_key_path  public_key.pem
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  /var/lib/mysql/demo-slow.log
socket   /var/run/mysqld/mysqld.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     ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_notes    ON
sql_quote_show_create    ON
sql_safe_updates     OFF
sql_select_limit    1.84467440737096E+019
sql_slave_skip_counter  0
sql_warnings     OFF
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     GMT
table_definition_cache  2000
table_open_cache    5200
table_open_cache_instances  16
thread_cache_size   8
thread_handling  one-thread-per-connection
thread_stack    262144
time_format  %H:%i:%s
time_zone    SYSTEM
timestamp   1579095336.07864
tls_version  TLSv1,TLSv1.1,TLSv1.2
tmp_table_size  16777216
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.28-0ubuntu0.16.04.2
version_comment  (Ubuntu)
version_compile_machine  x86_64
version_compile_os   Linux
wait_timeout    28800
warning_count   0

显示全球状态； （较少的SSL相关信息和任何com _ * = 0值）输出：

Aborted_clients 0
Aborted_connects    4
Binlog_cache_disk_use   0
Binlog_cache_use    0
Binlog_stmt_cache_disk_use  0
Binlog_stmt_cache_use   0
Bytes_received  120596117
Bytes_sent  423055987
Com_admin_commands  1422
Com_change_db   1969
Com_delete  26
Com_delete_multi    5
Com_flush   5
Com_insert  543
Com_lock_tables 106103
Com_select  290121
Com_set_option  3952
Com_show_charsets   4
Com_show_fields 1756
Com_show_keys   3
Com_show_plugins    1
Com_show_status 2317
Com_show_tables 4
Com_show_variables  10
Com_unlock_tables   106103
Com_update  119462
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 53918
Created_tmp_disk_tables 7205
Created_tmp_files   6
Created_tmp_tables  26682
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Handler_commit  516258
Handler_delete  605
Handler_discover    0
Handler_external_lock   1131715
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  251891
Handler_read_key    1450835
Handler_read_last   0
Handler_read_next   46912240
Handler_read_prev   0
Handler_read_rnd    333804
Handler_read_rnd_next   77706400
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  20612
Handler_write   139820
Innodb_buffer_pool_dump_status   Dumping of buffer pool not started
Innodb_buffer_pool_load_status   Buffer pool(s) load completed at 200110 15:44:11
Innodb_buffer_pool_resize_status     
Innodb_buffer_pool_pages_data   7744
Innodb_buffer_pool_bytes_data   126877696
Innodb_buffer_pool_pages_dirty  0
Innodb_buffer_pool_bytes_dirty  0
Innodb_buffer_pool_pages_flushed    10525
Innodb_buffer_pool_pages_free   128
Innodb_buffer_pool_pages_misc   319
Innodb_buffer_pool_pages_total  8191
Innodb_buffer_pool_read_ahead_rnd   0
Innodb_buffer_pool_read_ahead   2174
Innodb_buffer_pool_read_ahead_evicted   552
Innodb_buffer_pool_read_requests    92861248
Innodb_buffer_pool_reads    230500
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   490867
Innodb_data_fsyncs  6682
Innodb_data_pending_fsyncs  0
Innodb_data_pending_reads   0
Innodb_data_pending_writes  0
Innodb_data_read    3812250112
Innodb_data_reads   257019
Innodb_data_writes  36166
Innodb_data_written 301705728
Innodb_dblwr_pages_written  5776
Innodb_dblwr_writes 1121
Innodb_log_waits    0
Innodb_log_write_requests   24009
Innodb_log_writes   23298
Innodb_os_log_fsyncs    3651
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   34017280
Innodb_page_size    16384
Innodb_pages_created    388
Innodb_pages_read   232676
Innodb_pages_written    10525
Innodb_row_lock_current_waits   0
Innodb_row_lock_time    0
Innodb_row_lock_time_avg    0
Innodb_row_lock_time_max    0
Innodb_row_lock_waits   0
Innodb_rows_deleted 605
Innodb_rows_inserted    33914
Innodb_rows_read    125144394
Innodb_rows_updated 20612
Innodb_num_open_files   5200
Innodb_truncated_status_writes  0
Innodb_available_undo_logs  128
Key_blocks_not_flushed  0
Key_blocks_unused   13396
Key_blocks_used 3
Key_read_requests   6
Key_reads   3
Key_write_requests  0
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    14
Max_used_connections_time   10/01/20 15:50
Not_flushed_delayed_rows    0
Ongoing_anonymous_transaction_count 0
Open_files  0
Open_streams    0
Open_table_definitions  3464
Open_tables 5200
Opened_files    337104
Opened_table_definitions    140571
Opened_tables   76095
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 0
Qcache_free_blocks  1
Qcache_free_memory  16760152
Qcache_hits 0
Qcache_inserts  0
Qcache_lowmem_prunes    0
Qcache_not_cached   184049
Qcache_queries_in_cache 0
Qcache_total_blocks 1
Queries 687716
Questions   684326
Rsa_public_key  <removed>
Select_full_join    2827
Select_full_range_join  319
Select_range    56255
Select_range_check  1525
Select_scan 156904
Slave_open_temp_tables  0
Slow_launch_threads 0
Slow_queries    0
Sort_merge_passes   0
Sort_range  61935
Sort_rows   116417
Sort_scan   99600
Table_locks_immediate   458
Table_locks_waited  0
Table_open_cache_hits   381570
Table_open_cache_misses 76095
Table_open_cache_overflows  70888
Tc_log_max_pages_used   0
Tc_log_page_size    0
Tc_log_page_waits   0
Threads_cached  7
Threads_connected   3
Threads_created 14
Threads_running 1
Uptime  424288
Uptime_since_flush_status   424288

Answer 1

简短的回答：如果您不进行交换也不崩溃，则无需担心。但是，由于OOM正在杀死它，因此需要将某些内容调低一些。

长答案...

将swappiness更改为1。（它可能默认为60。）

对于仅4GB的RAM，请勿像其他人建议的那样将innodb_buffer_pool_size设置为RAM的75-80％。您将用完RAM。交换性能很糟糕。现在将其保留在1G。并且这假设您在计算机上没有任何其他应用程序在运行。

buffer_pool会增加，但不会缩小。它被重用。它是您正在处理的各种表的数据块和索引的“缓存”。

听起来performance_schema占用了400MB。如果您不使用它，请将其关闭。如果使用它，则将buffer_pool降低到1000M，以便为PS腾出空间。

table_open_cache = 5200-您有数千张桌子吗？也许1000会更安全。

数十甚至数百个数据库都不是问题。 table_open_cache是一个缓存，因此它不必与您拥有的表数一样大。但是，由于当一个表有多个条目时，多个连接正在查看该表，因此它可能太小。

为了进一步分析，请提供此处讨论的信息：http://mysql.rjweb.org/doc.php/mysql_analysis#tuning我将查看“变量”和“全局状态”中是否还有其他线索。

1K qps适度繁忙（〜70％）； 10K变得更加令人兴奋。既然您似乎有一些活动，也许您已经达到了10K？

我不喜欢“使硬件出现问题”。

设置分析

观察结果：

* Version: 5.7.28-0ubuntu0.16.04.2
* 4 GB of RAM
* Uptime = 4d 21:51:28
* Are you sure this was a SHOW GLOBAL STATUS ?
* You are not running on Windows.
* Running 64-bit version
* You appear to be running entirely (or mostly) InnoDB.

更重要的问题：

请勿在InnoDB中使用 LOCK TABLES / UNLOCK TABLES。使用交易。 （或者，让我们讨论一下为什么要使用它们。）

innodb_buffer_pool_size = 1G-如果正在运行许多其他应用程序，则更低-注意：1G会使OOM问题变得更糟，因此在弄清楚OOM问题之前，请勿这样做。

您的系统似乎负载很轻-除了打开的表的数量。您能否详细说明该应用程序的功能？

可能有很多效率低下的查询。设置long_query_time = 1并打开慢速日志。

详细信息和其他观察结果：

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((16M / 0.20 + 128M / 0.70)) / 4096M = 6.4%-应该使大多数可用的ram都可用于缓存。
- http://mysql.rjweb.org/doc.php/memory

( table_open_cache ) = 5,200-要缓存的表描述符的数量
-通常好几百个。

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 76,095 / (381570 + 76095) = 16.6%-table_open_cache的有效性。
-增加table_open_cache（现在为5200）并检查table_open_cache_instances（现在为16）。

( innodb_buffer_pool_size ) = 128M-InnoDB数据+索引缓存
-128M（旧的默认值）很小。

( Innodb_buffer_pool_read_ahead_evicted / Innodb_buffer_pool_read_ahead ) = 552 / 2174 = 25.4%-read_ahead的实用程序。
-关闭innodb_random_read_ahead（现在为OFF）。

( Innodb_dblwr_pages_written/Innodb_pages_written ) = 5,776/10525 = 54.9%-似乎这些值应该相等？

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 34,017,280 / (424288 / 3600) / 2 / 48M = 0.00287-比率
-（见会议记录）

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 424,288 / 60 * 48M / 34017280 = 10,462-InnoDB日志轮换之间的分钟数，从5.6.8开始，可以动态更改。一定还要更改my.cnf。
-（建议每转两次间隔60分钟有点随意。）调整innodb_log_file_size（现在为50331648）。 （无法在AWS中更改。）

( innodb_flush_method ) = innodb_flush_method =-InnoDB应该如何要求操作系统写入块。建议使用O_DIRECT或O_ALL_DIRECT（Percona），以避免双重缓冲。 （至少对于Unix。）请参阅chrischandler，以获取有关O_ALL_DIRECT的警告。

( innodb_flush_neighbors ) = 1-将块写入磁盘时的次要优化。
-SSD驱动器使用0; 1个用于HDD。

( innodb_io_capacity ) = 200-磁盘上每秒支持的I / O操作数。对于慢速驱动器为100； 200用于旋转驱动器；适用于SSD的1000-2000；乘以RAID因子。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-是否记录所有死锁。
-如果您遇到死锁困扰，请将其打开。警告：如果您有很多死锁，这可能会在磁盘上写入很多内容。

( character_set_server ) = character_set_server = utf8
-通过将character_set_server（现在为utf8）设置为utf8mb4，可以解决字符集问题。那是将来的默认值。

( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%

( local_infile ) = local_infile = ON
-local_infile（现在为ON）= ON是潜在的安全问题

( Select_scan / Com_select ) = 156,904 / 290121 = 54.1%-执行全表扫描的选择的百分比。 （可能会被存储例程欺骗。）
-添加索引/优化查询

( innodb_autoinc_lock_mode ) = 1-Galera：欲望2 – 2 =“交错”； 1 =“连续”是典型的； 0 =“传统”。
-加莱拉渴望2; 2需要BINLOG_FORMAT = ROW或MIXED

( slow_query_log ) = slow_query_log = OFF-是否记录慢速查询。 （5.1.12）

( long_query_time ) = 10-定义“慢速”查询的截止时间（秒）。
-建议2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF-（5.6.11，5.7.1）默认情况下，复制的语句不会显示在慢日志中；这使它们显示出来。
-在慢日志中查看可能干扰从设备读取的写入会很有帮助。

( back_log ) = 80-（自5.6.6起自动调整大小;基于max_connections）
-在进行大量连接时，提高到min（150，max_connections（现在为151））可能会有所帮助。

您有一半的查询缓存。您应该同时设置query_cache_type = OFF和query_cache_size = 0。根据谣言，除非您同时关闭这两个设置，否则QC代码中会存在“错误”，从而使某些代码保持打开状态。

异常小：

Com_insert = 4.6 /HR
Created_tmp_files = 0.051 /HR
Handler_write = 0.33 /sec
Innodb_buffer_pool_bytes_data = 299 /sec
Key_read_requests = 0.051 /HR
Key_reads+Key_writes + Innodb_pages_read+Innodb_pages_written+Innodb_dblwr_writes+Innodb_buffer_pool_pages_flushed = 0.6 /sec
Open_files = 0
Table_locks_immediate = 3.9 /HR

异常大：

Com_unlock_tables = 0.25 /sec
Innodb_num_open_files = 5,200
Open_table_definitions = 3,464
Open_tables = 5,200
performance_schema_max_rwlock_classes = 50

字符串异常：

innodb_fast_shutdown = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
require_secure_transport = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN