Mysql – AWS RDS many connections cause “lock up”

amazon-rdsMySQL

I run a fairly standard LAMP stack with an Amazon RDS database on a t2.small instance. Today I was alerted through Website monitoring that the database seemed to have "locked up" completely. The symptoms I found were:

  • No queries running for a long time when running

      show processlist

  • Apache web server not busy at all

Reviewing the logs configured in Cloudwatch showed at the time of the outage I found that all throughput metrics went to near 0. Connections were up to a spike of 17. The connection limit is configured as per default to InstanceMemory/12MB, so that's about 160 connections. Freeable memory went from 264MB to 234MB.

CPU Credit Balance graph for the RDS instance shows CPU is at 250 consistently, web server is very high as well. There is no specific traffic I can point to, I have picked some urls out of the web server logs and counted request in that hour and compared to other hours. Nothing that sticks out.

How could I diagnose this further?

SHOW VARIABLES

    auto_increment_increment    1
    auto_increment_offset   1
    autocommit  ON
    automatic_sp_privileges ON
    avoid_temporal_upgrade  OFF
    back_log    80
    basedir /rdsdbbin/mysql/
    big_tables  OFF
    bind_address    *
    binlog_cache_size   32768
    binlog_checksum CRC32
    binlog_direct_non_transactional_updates OFF
    binlog_error_action IGNORE_ERROR
    binlog_format   MIXED
    binlog_gtid_simple_recovery OFF
    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
    binlogging_impossible_mode  IGNORE_ERROR
    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  /rdsdbbin/mysql-5.6.27.R1/share/charsets/
    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 /rdsdbdata/db/
    date_format %Y-%m-%d
    datetime_format %Y-%m-%d %H:%i:%s
    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
    disconnect_on_expired_password  ON
    div_precision_increment 4
    end_markers_in_json OFF
    enforce_gtid_consistency    OFF
    eq_range_index_dive_limit   10
    error_count 0
    event_scheduler OFF
    expire_logs_days    0
    explicit_defaults_for_timestamp ON
    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    /rdsdbdata/log/general/mysql-general.log
    group_concat_max_len    1024
    gtid_executed   
    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_symlink    YES
    host_cache_size 278
    hostname    ip-10-6-0-211
    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_max_sleep_delay 150000
    innodb_additional_mem_pool_size 8388608
    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_dump_at_shutdown OFF
    innodb_buffer_pool_dump_now OFF
    innodb_buffer_pool_filename ib_buffer_pool
    innodb_buffer_pool_instances    8
    innodb_buffer_pool_load_abort   OFF
    innodb_buffer_pool_load_at_startup  OFF
    innodb_buffer_pool_load_now OFF
    innodb_buffer_pool_size 1418723328
    innodb_change_buffer_max_size   25
    innodb_change_buffering all
    innodb_checksum_algorithm   innodb
    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    /rdsdbdata/db/innodb
    innodb_disable_sort_file_cache  OFF
    innodb_doublewrite  ON
    innodb_fast_shutdown    1
    innodb_file_format  Antelope
    innodb_file_format_check    ON
    innodb_file_format_max  Antelope
    innodb_file_per_table   ON
    innodb_flush_log_at_timeout 1
    innodb_flush_log_at_trx_commit  1
    innodb_flush_method O_DIRECT
    innodb_flush_neighbors  1
    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 OFF
    innodb_lock_wait_timeout    50
    innodb_locks_unsafe_for_binlog  OFF
    innodb_log_buffer_size  8388608
    innodb_log_compressed_pages ON
    innodb_log_file_size    134217728
    innodb_log_files_in_group   2
    innodb_log_group_home_dir   /rdsdbdata/log/innodb
    innodb_lru_scan_depth   1024
    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_mirrored_log_groups  1
    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   2000
    innodb_optimize_fulltext_only   OFF
    innodb_page_size    16384
    innodb_print_all_deadlocks  OFF
    innodb_purge_batch_size 300
    innodb_purge_threads    1
    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  OFF
    innodb_support_xa   ON
    innodb_sync_array_size  1
    innodb_sync_spin_loops  30
    innodb_table_locks  ON
    innodb_thread_concurrency   0
    innodb_thread_sleep_delay   10000
    innodb_undo_directory   .
    innodb_undo_logs    128
    innodb_undo_tablespaces 0
    innodb_use_native_aio   ON
    innodb_use_sys_malloc   ON
    innodb_version  5.6.27
    innodb_write_io_threads 4
    insert_id   0
    interactive_timeout 28800
    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
    large_files_support ON
    large_page_size 0
    large_pages OFF
    last_insert_id  0
    lc_messages en_US
    lc_messages_dir /rdsdbbin/mysql-5.6.27.R1/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    /rdsdbdata/log/binlog/mysql-bin-changelog
    log_bin_index   /rdsdbdata/log/binlog/mysql-bin-changelog.index
    log_bin_trust_function_creators OFF
    log_bin_use_v1_row_events   OFF
    log_error   /rdsdbdata/log/error/mysql-error.log
    log_output  TABLE
    log_queries_not_using_indexes   OFF
    log_slave_updates   ON
    log_slow_admin_statements   OFF
    log_slow_slave_statements   OFF
    log_throttle_queries_not_using_indexes  0
    log_warnings    1
    long_query_time 10.000000
    low_priority_updates    OFF
    lower_case_file_system  OFF
    lower_case_table_names  0
    master_info_repository  TABLE
    master_verify_checksum  OFF
    max_allowed_packet  33554432
    max_binlog_cache_size   18446744073709547520
    max_binlog_size 134217728
    max_binlog_stmt_cache_size  18446744073709547520
    max_connect_errors  100
    max_connections 150
    max_delayed_threads 20
    max_digest_length   1024
    max_error_count 64
    max_heap_table_size 402653184
    max_insert_delayed_threads  20
    max_join_size   18446744073709551615
    max_length_for_sort_data    1024
    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
    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   0
    open_files_limit    65535
    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,subquery_materialization_cost_based=on,use_index_extensions=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
    performance_schema  OFF
    performance_schema_accounts_size    -1
    performance_schema_digests_size -1
    performance_schema_events_stages_history_long_size  -1
    performance_schema_events_stages_history_size   -1
    performance_schema_events_statements_history_long_size  -1
    performance_schema_events_statements_history_size   -1
    performance_schema_events_waits_history_long_size   -1
    performance_schema_events_waits_history_size    -1
    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 50
    performance_schema_max_file_handles 32768
    performance_schema_max_file_instances   -1
    performance_schema_max_mutex_classes    200
    performance_schema_max_mutex_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_stage_classes    150
    performance_schema_max_statement_classes    168
    performance_schema_max_table_handles    -1
    performance_schema_max_table_instances  -1
    performance_schema_max_thread_classes   50
    performance_schema_max_thread_instances -1
    performance_schema_session_connect_attrs_size   -1
    performance_schema_setup_actors_size    100
    performance_schema_setup_objects_size   100
    performance_schema_users_size   -1
    pid_file    /rdsdbdata/log/mysql-3306.pid
    plugin_dir  /rdsdbbin/mysql-5.6.27.R1/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    5139681
    query_alloc_block_size  8192
    query_cache_limit   1048576
    query_cache_min_res_unit    4096
    query_cache_size    1048576
    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    262144
    read_only   OFF
    read_rnd_buffer_size    524288
    relay_log   /rdsdbdata/log/relaylog/relaylog
    relay_log_basename  /rdsdbdata/log/relaylog/relaylog
    relay_log_index /rdsdbdata/log/relaylog/relaylog.index
    relay_log_info_file relay-log.info
    relay_log_info_repository   TABLE
    relay_log_purge ON
    relay_log_recovery  ON
    relay_log_space_limit   0
    report_host 
    report_password 
    report_port 3306
    report_user 
    rpl_stop_slave_timeout  31536000
    secure_auth ON
    secure_file_priv    /tmp/
    server_id   153211832
    server_id_bits  32
    server_uuid 7558fb76-4974-11e6-b1d8-067680180113
    show_old_temporals  OFF
    simplified_binlog_gtid_recovery 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   /rdsdbdata/tmp
    slave_max_allowed_packet    1073741824
    slave_net_timeout   3600
    slave_parallel_workers  0
    slave_pending_jobs_size_max 16777216
    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 /rdsdbdata/log/slowquery/mysql-slowquery.log
    socket  /tmp/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  /rdsdbdata/rds-metadata/ca-cert.pem
    ssl_capath  
    ssl_cert    /rdsdbdata/rds-metadata/server-cert.pem
    ssl_cipher  EXP1024-RC4-SHA:EXP1024-DES-CBC-SHA:AES256-SHA:AES128-SHA:DES-CBC3-SHA:DES-CBC-SHA:EXP-DES-CBC-SHA:EXP-RC2-CBC-MD5:RC4-SHA:RC4-MD5:EXP-RC4-MD5:NULL-SHA:NULL-MD5:DES-CBC3-MD5:DES-CBC-MD5:EXP-RC2-CBC-MD5:RC2-CBC-MD5:EXP-RC4-MD5:RC4-MD5:KRB5-DES-CBC3-MD5:KRB5-DES-CBC3-SHA:ADH-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:EDH-DSS-DES-CBC3-SHA:ADH-AES256-SHA:DHE-RSA-AES256-SHA:DHE-DSS-AES256-SHA:ADH-AES128-SHA:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA:EXP-KRB5-RC4-MD5:EXP-KRB5-RC2-CBC-MD5:EXP-KRB5-DES-CBC-MD5:KRB5-RC4-MD5:KRB5-DES-CBC-MD5:ADH-RC4-MD5:EXP-ADH-RC4-MD5:DHE-DSS-RC4-SHA:EXP1024-DHE-DSS-RC4-SHA:EXP1024-DHE-DSS-DES-CBC-SHA:EXP-KRB5-RC4-SHA:EXP-KRB5-RC2-CBC-SHA:EXP-KRB5-DES-CBC-SHA:KRB5-RC4-SHA:KRB5-DES-CBC-SHA:ADH-DES-CBC-SHA:EXP-ADH-DES-CBC-SHA:EDH-RSA-DES-CBC-SHA:EXP-EDH-RSA-DES-CBC-SHA:EDH-DSS-DES-CBC-SHA:EXP-EDH-DSS-DES-CBC-SHA
    ssl_crl 
    ssl_crlpath 
    ssl_key /rdsdbdata/rds-metadata/server-key.pem
    storage_engine  InnoDB
    stored_program_cache    256
    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  1400
    table_open_cache    2000
    table_open_cache_instances  16
    thread_cache_size   9
    thread_concurrency  10
    thread_handling one-thread-per-connection
    thread_stack    262144
    time_format %H:%i:%s
    time_zone   UTC
    timed_mutexes   OFF
    timestamp   1507496697.301036
    tmp_table_size  16777216
    tmpdir  /rdsdbdata/tmp
    transaction_alloc_block_size    8192
    transaction_allow_batching  OFF
    transaction_prealloc_size   4096
    tx_isolation    REPEATABLE-READ
    tx_read_only    OFF
    unique_checks   ON
    updatable_views_with_limit  YES
    version 5.6.27-log
    version_comment MySQL Community Server (GPL)
    version_compile_machine x86_64
    version_compile_os  Linux
    wait_timeout    28800
    warning_count   0

Best Answer

Implementing Rick James' suggestion of 600MB for innodb_buffer_pool_size will help.

Changing:

innodb_buffer_pool_instances = 2  # from 8

...could also be helpful.

max_allowed_packet at 32M would not take many active connections to stress RAM capacity of 2GB.

See the 5.7 documentation:

Internally net_buffer_length is dynamically allowed to grow during a session to this limit. Default is 1MB, using session variables can be used to manage specific needs, when required.

Related Question