MySQL 查询堆积和崩溃

我们有一个拥有大约 30 万活跃用户的应用程序。我们有一个专用的数据库服务器，它具有以下配置。我们面临的问题是，随机地我们的服务器上有很多负载并且它被挂起，我们必须重新启动 mysql 服务才能继续。我们在数据库服务器上安装了 monyog 以监视哪些查询花费了很多时间，但是每当发生崩溃时，它似乎并没有显示任何异常活动，只是查询开始堆积很多，甚至正常的查询也开始花费超过 2 分钟的时间. 据我们所知，我们已经以各种可能的方式优化了我们的数据库，例如向表中添加索引优化查询，最大限度地减少查询数量。

下面是我们的配置

server.cnf
[mysqld]
    innodb_force_recovery = 0
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    datadir=/var/lib/mysql
    skip-external-locking
    performance_schema=ON
    query_cache_limit=1M
    query_cache_size=16M
    query_cache_type=1
    max_user_connections=32000
    max_connections=32000
    interactive_timeout=180
    wait_timeout=180
    connect_timeout=10
    thread_cache_size=200
    max_connect_errors=999999
    max_allowed_packet=512M
    table_open_cache=90000
    innodb_file_per_table=1
    myisam_sort_buffer_size=64M
    tmp_table_size=32M
    max_heap_table_size=32M
    default-storage-engine = innodb

[mysqldump]
    quick
    max_allowed_packet = 16M

[mysql]
no-auto-rehash
safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

限制.conf

*                hard    nofile          199680
*                soft    nofile          16364

应用架构

语言：Php5

MySQL版本

innodb_version  5.6.36-82.2
protocol_version    10
slave_type_conversions  
version 10.1.28-MariaDB
version_comment MariaDB Server
version_compile_machine x86_64
version_compile_os  Linux
version_malloc_library  system jemalloc
version_ssl_library OpenSSL 1.0.1e-fips 11 Feb 2013
wsrep_patch_version wsrep_25.20

它的MariaDB

操作系统：CentOS

cat /proc/内存信息

MemTotal:       131729252 kB
    MemFree:        74397484 kB
    MemAvailable:   97158916 kB
    Buffers:            4884 kB
    Cached:         23181052 kB
    SwapCached:            0 kB
    Active:         53580240 kB
    Inactive:        1336380 kB
    Active(anon):   32095004 kB
    Inactive(anon):   279912 kB
    Active(file):   21485236 kB
    Inactive(file):  1056468 kB
    Unevictable:           0 kB
    Mlocked:               0 kB
    SwapTotal:       1048572 kB
    SwapFree:        1048572 kB
    Dirty:                76 kB
    Writeback:             0 kB
    AnonPages:      31730916 kB
    Mapped:           139488 kB
    Shmem:            644244 kB
    Slab:            1190592 kB
    SReclaimable:     897028 kB
    SUnreclaim:       293564 kB
    KernelStack:       15536 kB
    PageTables:        84744 kB
    NFS_Unstable:          0 kB
    Bounce:                0 kB
    WritebackTmp:          0 kB
    CommitLimit:    66913196 kB
    Committed_AS:   89544808 kB
    VmallocTotal:   34359738367 kB
    VmallocUsed:      550556 kB
    VmallocChunk:   34258290684 kB
    HardwareCorrupted:     0 kB
    AnonHugePages:  18024448 kB
    HugePages_Total:       0
    HugePages_Free:        0
    HugePages_Rsvd:        0
    HugePages_Surp:        0
    Hugepagesize:       2048 kB
    DirectMap4k:      281112 kB
    DirectMap2M:    14270464 kB
    DirectMap1G:    121634816 kB

查询到show status like '%abort%';

Aborted_clients 3098
Aborted_connects    141795
wsrep_local_bf_aborts   0

下面是我的 mysql_error.log 但它只在我重新启动 mysql 服务后显示

2017-10-26 23:36:56 140677528500480 [Note] Plugin 'FEEDBACK' is disabled.
2017-10-26 23:36:56 140677528500480 [Note] Server socket created on IP: '::'.
2017-10-26 23:36:56 140677528500480 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
2017-10-26 23:36:56 140677528500480 [Warning] 'user' entry '@db.dbsuer.com' ignored in --skip-name-resolve mode.
2017-10-26 23:36:56 140677528500480 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
2017-10-26 23:36:56 140677528500480 [Warning] 'proxies_priv' entry '@% [email protected]' ignored in --skip-name-resolve mode.
2017-10-26 23:36:56 140677528058624 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
2017-10-26 23:36:56 140677528500480 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.28-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
2017-10-26 23:36:56 7ff205748b00 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-10-27  2:12:44 140588045855488 [Note] /usr/sbin/mysqld: Normal shutdown
2017-10-27  2:13:04 140588045855488 [Warning] /usr/sbin/mysqld: Forcing close of thread 26950259  user: 'dbuser'

2017-10-27  2:13:04 140588045855488 [Warning] /usr/sbin/mysqld: Forcing close of thread 26950237  user: 'dbuser'

2017-10-27  2:13:04 140588045855488 [Warning] /usr/sbin/mysqld: Forcing close of thread 26908783  user: 'dbuser'

2017-10-27  2:13:04 140588045855488 [ERROR] mysqld: Got an error writing communication packets
2017-10-27  2:13:04 140588045855488 [Warning] /usr/sbin/mysqld: Forcing close of thread 6885924  user: 'root'

2017-10-27  2:13:04 140588045855488 [Warning] /usr/sbin/mysqld: Forcing close of thread 2139  user: 'root'

2017-10-27  2:13:14 140588619900672 [Note] InnoDB: FTS optimize thread exiting.
2017-10-27  2:13:14 140588045855488 [Note] InnoDB: Starting shutdown...
2017-10-27 02:14:54 7fafc7e6b900 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2017-10-27  2:14:54 140392949790976 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2017-10-27  2:14:54 140392949790976 [Note] InnoDB: The InnoDB memory heap is disabled
2017-10-27  2:14:54 140392949790976 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-10-27  2:14:54 140392949790976 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2

SHOW GLOBAL STATUS;

'Aborted_clients', '108'
'Aborted_connects', '5565'
'Access_denied_errors', '0'
'Acl_column_grants', '0'
'Acl_database_grants', '3'
'Acl_function_grants', '0'
'Acl_procedure_grants', '0'
'Acl_proxy_users', '1'
'Acl_role_grants', '0'
'Acl_roles', '0'
'Acl_table_grants', '0'
'Acl_users', '21'
'Aria_pagecache_blocks_not_flushed', '0'
'Aria_pagecache_blocks_unused', '15706'
'Aria_pagecache_blocks_used', '28'
'Aria_pagecache_read_requests', '553397'
'Aria_pagecache_reads', '6463'
'Aria_pagecache_write_requests', '71168'
'Aria_pagecache_writes', '0'
'Aria_transaction_log_syncs', '0'
'Binlog_bytes_written', '0'
'Binlog_cache_disk_use', '0'
'Binlog_cache_use', '0'
'Binlog_commits', '0'
'Binlog_group_commit_trigger_count', '0'
'Binlog_group_commit_trigger_lock_wait', '0'
'Binlog_group_commit_trigger_timeout', '0'
'Binlog_group_commits', '0'
'Binlog_snapshot_file', ''
'Binlog_snapshot_position', '0'
'Binlog_stmt_cache_disk_use', '0'
'Binlog_stmt_cache_use', '0'
'Busy_time', '0.000000'
'Bytes_received', '779126531'
'Bytes_sent', '7848821242'
'Com_admin_commands', '193376'
'Com_alter_db', '0'
'Com_alter_db_upgrade', '0'
'Com_alter_event', '0'
'Com_alter_function', '0'
'Com_alter_procedure', '0'
'Com_alter_server', '0'
'Com_alter_table', '0'
'Com_alter_tablespace', '0'
'Com_analyze', '0'
'Com_assign_to_keycache', '0'
'Com_begin', '0'
'Com_binlog', '0'
'Com_call_procedure', '25'
'Com_change_db', '2186510'
'Com_change_master', '0'
'Com_check', '0'
'Com_checksum', '0'
'Com_commit', '0'
'Com_compound_sql', '0'
'Com_create_db', '0'
'Com_create_event', '0'
'Com_create_function', '0'
'Com_create_index', '0'
'Com_create_procedure', '0'
'Com_create_role', '0'
'Com_create_server', '0'
'Com_create_table', '0'
'Com_create_temporary_table', '2'
'Com_create_trigger', '0'
'Com_create_udf', '0'
'Com_create_user', '0'
'Com_create_view', '0'
'Com_dealloc_sql', '0'
'Com_delete', '164'
'Com_delete_multi', '0'
'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_role', '0'
'Com_drop_server', '0'
'Com_drop_table', '0'
'Com_drop_temporary_table', '4'
'Com_drop_trigger', '0'
'Com_drop_user', '0'
'Com_drop_view', '0'
'Com_empty_query', '0'
'Com_execute_sql', '0'
'Com_flush', '0'
'Com_get_diagnostics', '0'
'Com_grant', '0'
'Com_grant_role', '0'
'Com_ha_close', '0'
'Com_ha_open', '0'
'Com_ha_read', '0'
'Com_help', '0'
'Com_insert', '7530'
'Com_insert_select', '5'
'Com_install_plugin', '0'
'Com_kill', '1'
'Com_load', '0'
'Com_lock_tables', '0'
'Com_optimize', '0'
'Com_preload_keys', '0'
'Com_prepare_sql', '0'
'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', '0'
'Com_replace_select', '23'
'Com_reset', '0'
'Com_resignal', '0'
'Com_revoke', '0'
'Com_revoke_all', '0'
'Com_revoke_role', '0'
'Com_rollback', '0'
'Com_rollback_to_savepoint', '0'
'Com_savepoint', '0'
'Com_select', '2769447'
'Com_set_option', '1685912'
'Com_show_authors', '0'
'Com_show_binlog_events', '0'
'Com_show_binlogs', '0'
'Com_show_charsets', '0'
'Com_show_collations', '19'
'Com_show_contributors', '0'
'Com_show_create_db', '0'
'Com_show_create_event', '0'
'Com_show_create_func', '0'
'Com_show_create_proc', '0'
'Com_show_create_table', '1'
'Com_show_create_trigger', '0'
'Com_show_databases', '19'
'Com_show_engine_logs', '0'
'Com_show_engine_mutex', '0'
'Com_show_engine_status', '19'
'Com_show_errors', '0'
'Com_show_events', '0'
'Com_show_explain', '0'
'Com_show_fields', '2'
'Com_show_function_status', '0'
'Com_show_generic', '0'
'Com_show_grants', '0'
'Com_show_keys', '2'
'Com_show_master_status', '20'
'Com_show_open_tables', '1912'
'Com_show_plugins', '0'
'Com_show_privileges', '0'
'Com_show_procedure_status', '0'
'Com_show_processlist', '329'
'Com_show_profile', '0'
'Com_show_profiles', '0'
'Com_show_relaylog_events', '0'
'Com_show_slave_hosts', '0'
'Com_show_slave_status', '2'
'Com_show_status', '429'
'Com_show_storage_engines', '0'
'Com_show_table_status', '0'
'Com_show_tables', '5'
'Com_show_triggers', '0'
'Com_show_variables', '50'
'Com_show_warnings', '5'
'Com_shutdown', '0'
'Com_signal', '0'
'Com_start_all_slaves', '0'
'Com_start_slave', '0'
'Com_stmt_close', '1703'
'Com_stmt_execute', '2110'
'Com_stmt_fetch', '0'
'Com_stmt_prepare', '2110'
'Com_stmt_reprepare', '0'
'Com_stmt_reset', '0'
'Com_stmt_send_long_data', '0'
'Com_stop_all_slaves', '0'
'Com_stop_slave', '0'
'Com_truncate', '0'
'Com_uninstall_plugin', '0'
'Com_unlock_tables', '6263'
'Com_update', '23649'
'Com_update_multi', '0'
'Com_xa_commit', '0'
'Com_xa_end', '0'
'Com_xa_prepare', '0'
'Com_xa_recover', '0'
'Com_xa_rollback', '0'
'Com_xa_start', '0'
'Compression', 'OFF'
'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', '2102550'
'Cpu_time', '0.000000'
'Created_tmp_disk_tables', '6776'
'Created_tmp_files', '6'
'Created_tmp_tables', '24053'
'Delayed_errors', '0'
'Delayed_insert_threads', '0'
'Delayed_writes', '0'
'Delete_scan', '26'
'Empty_queries', '971314'
'Executed_events', '0'
'Executed_triggers', '0'
'Feature_delay_key_write', '0'
'Feature_dynamic_columns', '0'
'Feature_fulltext', '0'
'Feature_gis', '0'
'Feature_locale', '0'
'Feature_subquery', '4177'
'Feature_timezone', '0'
'Feature_trigger', '0'
'Feature_xml', '0'
'Flush_commands', '1'
'Handler_commit', '1561805'
'Handler_delete', '132151'
'Handler_discover', '3'
'Handler_external_lock', '0'
'Handler_icp_attempts', '258439030'
'Handler_icp_match', '254844311'
'Handler_mrr_init', '0'
'Handler_mrr_key_refills', '0'
'Handler_mrr_rowid_refills', '0'
'Handler_prepare', '0'
'Handler_read_first', '280'
'Handler_read_key', '35395424'
'Handler_read_last', '16'
'Handler_read_next', '9410864982'
'Handler_read_prev', '81650'
'Handler_read_retry', '0'
'Handler_read_rnd', '28068980'
'Handler_read_rnd_deleted', '0'
'Handler_read_rnd_next', '59836719'
'Handler_rollback', '2'
'Handler_savepoint', '0'
'Handler_savepoint_rollback', '0'
'Handler_tmp_update', '694783'
'Handler_tmp_write', '5656429'
'Handler_update', '79769'
'Handler_write', '109219'
'Innodb_available_undo_logs', '128'
'Innodb_background_log_sync', '1115'
'Innodb_buffer_pool_bytes_data', '6600916992'
'Innodb_buffer_pool_bytes_dirty', '267239424'
'Innodb_buffer_pool_dump_status', 'Dumping buffer pool(s) not yet started'
'Innodb_buffer_pool_load_status', 'Loading buffer pool(s) not yet started'
'Innodb_buffer_pool_pages_data', '402888'
'Innodb_buffer_pool_pages_dirty', '16311'
'Innodb_buffer_pool_pages_flushed', '1'
'Innodb_buffer_pool_pages_free', '4154055'
'Innodb_buffer_pool_pages_lru_flushed', '0'
'Innodb_buffer_pool_pages_made_not_young', '0'
'Innodb_buffer_pool_pages_made_young', '320'
'Innodb_buffer_pool_pages_misc', '30513'
'Innodb_buffer_pool_pages_old', '149969'
'Innodb_buffer_pool_pages_total', '4587456'
'Innodb_buffer_pool_read_ahead', '63121'
'Innodb_buffer_pool_read_ahead_evicted', '0'
'Innodb_buffer_pool_read_ahead_rnd', '0'
'Innodb_buffer_pool_read_requests', '2157996469'
'Innodb_buffer_pool_reads', '337111'
'Innodb_buffer_pool_wait_free', '0'
'Innodb_buffer_pool_write_requests', '4816856'
'Innodb_checkpoint_age', '128276390'
'Innodb_checkpoint_max_age', '13914982934'
'Innodb_data_fsyncs', '1213'
'Innodb_data_pending_fsyncs', '0'
'Innodb_data_pending_reads', '0'
'Innodb_data_pending_writes', '0'
'Innodb_data_read', '6583128576'
'Innodb_data_reads', '402049'
'Innodb_data_writes', '32494'
'Innodb_data_written', '144898048'
'Innodb_dblwr_pages_written', '1'
'Innodb_dblwr_writes', '1'
'Innodb_deadlocks', '1'
'Innodb_defragment_compression_failures', '0'
'Innodb_defragment_count', '0'
'Innodb_defragment_failures', '0'
'Innodb_encryption_key_rotation_list_length', '0'
'Innodb_encryption_n_merge_blocks_decrypted', '0'
'Innodb_encryption_n_merge_blocks_encrypted', '0'
'Innodb_encryption_n_rowlog_blocks_decrypted', '0'
'Innodb_encryption_n_rowlog_blocks_encrypted', '0'
'Innodb_encryption_num_key_requests', '0'
'Innodb_encryption_rotation_estimated_iops', '0'
'Innodb_encryption_rotation_pages_flushed', '0'
'Innodb_encryption_rotation_pages_modified', '0'
'Innodb_encryption_rotation_pages_read_from_cache', '0'
'Innodb_encryption_rotation_pages_read_from_disk', '0'
'Innodb_have_atomic_builtins', 'ON'
'Innodb_have_bzip2', 'OFF'
'Innodb_have_lz4', 'OFF'
'Innodb_have_lzma', 'ON'
'Innodb_have_lzo', 'OFF'
'Innodb_have_snappy', 'OFF'
'Innodb_history_list_length', '130'
'Innodb_ibuf_discarded_delete_marks', '0'
'Innodb_ibuf_discarded_deletes', '0'
'Innodb_ibuf_discarded_inserts', '0'
'Innodb_ibuf_free_list', '64803'
'Innodb_ibuf_merged_delete_marks', '428176'
'Innodb_ibuf_merged_deletes', '123181'
'Innodb_ibuf_merged_inserts', '1178'
'Innodb_ibuf_merges', '4385'
'Innodb_ibuf_segment_size', '64805'
'Innodb_ibuf_size', '1'
'Innodb_log_waits', '0'
'Innodb_log_write_requests', '339325'
'Innodb_log_writes', '32398'
'Innodb_lsn_current', '614688828236'
'Innodb_lsn_flushed', '614688813752'
'Innodb_lsn_last_checkpoint', '614560551846'
'Innodb_master_thread_active_loops', '1115'
'Innodb_master_thread_idle_loops', '0'
'Innodb_max_trx_id', '8368506565'
'Innodb_mem_adaptive_hash', '1689900176'
'Innodb_mem_dictionary', '299754541'
'Innodb_mem_total', '78721843200'
'Innodb_mutex_os_waits', '6963'
'Innodb_mutex_spin_rounds', '1490291'
'Innodb_mutex_spin_waits', '1332026'
'Innodb_num_index_pages_written', '0'
'Innodb_num_non_index_pages_written', '32402'
'Innodb_num_page_compressed_trim_op', '0'
'Innodb_num_page_compressed_trim_op_saved', '0'
'Innodb_num_pages_decrypted', '0'
'Innodb_num_pages_encrypted', '0'
'Innodb_num_pages_page_compressed', '0'
'Innodb_num_pages_page_compression_error', '0'
'Innodb_num_pages_page_decompressed', '0'
'Innodb_oldest_view_low_limit_trx_id', '8368503775'
'Innodb_onlineddl_pct_progress', '0'
'Innodb_onlineddl_rowlog_pct_used', '0'
'Innodb_onlineddl_rowlog_rows', '0'
'Innodb_os_log_fsyncs', '1122'
'Innodb_os_log_pending_fsyncs', '0'
'Innodb_os_log_pending_writes', '0'
'Innodb_os_log_written', '144864256'
'Innodb_page_compression_saved', '0'
'Innodb_page_compression_trim_sect1024', '0'
'Innodb_page_compression_trim_sect16384', '0'
'Innodb_page_compression_trim_sect2048', '0'
'Innodb_page_compression_trim_sect32768', '0'
'Innodb_page_compression_trim_sect4096', '0'
'Innodb_page_compression_trim_sect512', '0'
'Innodb_page_compression_trim_sect8192', '0'
'Innodb_page_size', '16384'
'Innodb_pages0_read', '237'
'Innodb_pages_created', '1079'
'Innodb_pages_read', '401809'
'Innodb_pages_written', '1'
'Innodb_purge_trx_id', '8368504055'
'Innodb_purge_undo_no', '0'
'Innodb_read_views_memory', '2600'
'Innodb_row_lock_current_waits', '0'
'Innodb_row_lock_time', '950387'
'Innodb_row_lock_time_avg', '4658'
'Innodb_row_lock_time_max', '41902'
'Innodb_row_lock_waits', '204'
'Innodb_rows_deleted', '132151'
'Innodb_rows_inserted', '56069'
'Innodb_rows_read', '9463155799'
'Innodb_rows_updated', '79769'
'Innodb_s_lock_os_waits', '44208'
'Innodb_s_lock_spin_rounds', '3808201'
'Innodb_s_lock_spin_waits', '738548'
'Innodb_scrub_background_page_reorganizations', '0'
'Innodb_scrub_background_page_split_failures_missing_index', '0'
'Innodb_scrub_background_page_split_failures_out_of_filespace', '0'
'Innodb_scrub_background_page_split_failures_underflow', '0'
'Innodb_scrub_background_page_split_failures_unknown', '0'
'Innodb_scrub_background_page_splits', '0'
'Innodb_secondary_index_triggered_cluster_reads', '306821502'
'Innodb_secondary_index_triggered_cluster_reads_avoided', '0'
'Innodb_system_rows_deleted', '0'
'Innodb_system_rows_inserted', '0'
'Innodb_system_rows_read', '0'
'Innodb_system_rows_updated', '0'
'Innodb_truncated_status_writes', '0'
'Innodb_x_lock_os_waits', '23001'
'Innodb_x_lock_spin_rounds', '7705254'
'Innodb_x_lock_spin_waits', '94569'
'Key_blocks_not_flushed', '0'
'Key_blocks_unused', '214330'
'Key_blocks_used', '4'
'Key_blocks_warm', '0'
'Key_read_requests', '84'
'Key_reads', '4'
'Key_write_requests', '0'
'Key_writes', '0'
'Last_query_cost', '0.000000'
'Master_gtid_wait_count', '0'
'Master_gtid_wait_time', '0'
'Master_gtid_wait_timeouts', '0'
'Max_statement_time_exceeded', '0'
'Max_used_connections', '300'
'Memory_used', '445027144'
'Not_flushed_delayed_rows', '0'
'Open_files', '12'
'Open_streams', '0'
'Open_table_definitions', '263'
'Open_tables', '400'
'Opened_files', '27460'
'Opened_plugin_libraries', '0'
'Opened_table_definitions', '267'
'Opened_tables', '525'
'Opened_views', '0'
'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_locker_lost', '0'
'Performance_schema_mutex_classes_lost', '0'
'Performance_schema_mutex_instances_lost', '0'
'Performance_schema_rwlock_classes_lost', '0'
'Performance_schema_rwlock_instances_lost', '0'
'Performance_schema_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_thread_classes_lost', '0'
'Performance_schema_thread_instances_lost', '0'
'Performance_schema_users_lost', '0'
'Prepared_stmt_count', '0'
'Qcache_free_blocks', '1918'
'Qcache_free_memory', '5902288'
'Qcache_hits', '1209722'
'Qcache_inserts', '1347881'
'Qcache_lowmem_prunes', '943761'
'Qcache_not_cached', '211725'
'Qcache_queries_in_cache', '5647'
'Qcache_total_blocks', '13458'
'Queries', '8782988'
'Questions', '8778832'
'Rows_read', '3758678584'
'Rows_sent', '9669002'
'Rows_tmp_read', '6550750'
'Rpl_status', 'AUTH_MASTER'
'Select_full_join', '9637'
'Select_full_range_join', '3'
'Select_range', '188883'
'Select_range_check', '0'
'Select_scan', '6116'
'Slave_connections', '0'
'Slave_heartbeat_period', '0.000'
'Slave_open_temp_tables', '0'
'Slave_received_heartbeats', '0'
'Slave_retried_transactions', '0'
'Slave_running', 'OFF'
'Slave_skipped_errors', '0'
'Slaves_connected', '0'
'Slaves_running', '0'
'Slow_launch_threads', '0'
'Slow_queries', '24'
'Sort_merge_passes', '0'
'Sort_priority_queue_sorts', '16808'
'Sort_range', '17758'
'Sort_rows', '376035'
'Sort_scan', '10237'
'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', ''
'Subquery_cache_hit', '0'
'Subquery_cache_miss', '5000'
'Syncs', '2'
'Table_locks_immediate', '1581465'
'Table_locks_waited', '0'
'Tc_log_max_pages_used', '0'
'Tc_log_page_size', '4096'
'Tc_log_page_waits', '0'
'Threadpool_idle_threads', '0'
'Threadpool_threads', '0'
'Threads_cached', '118'
'Threads_connected', '118'
'Threads_created', '309'
'Threads_running', '6'
'Update_scan', '5'
'Uptime', '1124'
'Uptime_since_flush_status', '1124'
'wsrep_cluster_conf_id', '18446744073709551615'
'wsrep_cluster_size', '0'
'wsrep_cluster_state_uuid', ''
'wsrep_cluster_status', 'Disconnected'
'wsrep_connected', 'OFF'
'wsrep_local_bf_aborts', '0'
'wsrep_local_index', '18446744073709551615'
'wsrep_provider_name', ''
'wsrep_provider_vendor', ''
'wsrep_provider_version', ''
'wsrep_ready', 'OFF'
'wsrep_thread_count', '0'

我们有 5 个数据库，总共有近 2 亿行。据我所知，我们可以做一些事情，但这需要优化和减少负载。除了任何错误的查询之外，还可以做些什么来防止崩溃？我们曾经遇到过很多错误，例如

too many open files
too many connections 
cannot connect right now

但是通过研究和配置正确的参数，我们已经克服了这些问题，但崩溃已经存在了一段时间，并且变得更加频繁。没有什么太多的了mysql_error.log任何帮助都会很棒！