Mysql

MariaDB 佔用 100-200% CPU

  • July 6, 2021

伺服器規格

RAM     CPU         STORAGE     
80 GB   20 Cores    1536 GB SSD 

TRANSFER      NETWORK IN    NETWORK OUT
20 TB         40 Gbps       8000 Mbps

這是顯示狀態的結果:

show status:

Aborted_clients 2   
Aborted_connects    0   
Access_denied_errors    0   
Acl_column_grants   0   
Acl_database_grants 0   
Acl_function_grants 0   
Acl_procedure_grants    0   
Acl_proxy_users 2   
Acl_role_grants 0   
Acl_roles   0   
Acl_table_grants    0   
Acl_users   9   
Aria_pagecache_blocks_not_flushed   0   
Aria_pagecache_blocks_unused    15706   
Aria_pagecache_blocks_used  2   
Aria_pagecache_read_requests    13  
Aria_pagecache_reads    4   
Aria_pagecache_write_requests   6   
Aria_pagecache_writes   0   
Aria_transaction_log_syncs  0   
Binlog_commits  5624    
Binlog_group_commits    5624    
Binlog_group_commit_trigger_count   0   
Binlog_group_commit_trigger_lock_wait   0   
Binlog_group_commit_trigger_timeout 0   
Binlog_snapshot_file    mariadb-bin.000017  
Binlog_snapshot_position    2460550 
Binlog_bytes_written    0   
Binlog_cache_disk_use   0   
Binlog_cache_use    5628    
Binlog_stmt_cache_disk_use  0   
Binlog_stmt_cache_use   0   
Busy_time   0.000000    
Bytes_received  510 
Bytes_sent  1262    
Com_admin_commands  0   
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  0   
Com_change_db   1   
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  0   
Com_create_trigger  0   
Com_create_udf  0   
Com_create_user 0   
Com_create_view 0   
Com_dealloc_sql 0   
Com_delete  0   
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    0   
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  0   
Com_insert_select   0   
Com_install_plugin  0   
Com_kill    0   
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  0   
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  2   
Com_set_option  3   
Com_show_authors    0   
Com_show_binlog_events  0   
Com_show_binlogs    0   
Com_show_charsets   0   
Com_show_collations 0   
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   0   
Com_show_create_trigger 0   
Com_show_databases  0   
Com_show_engine_logs    0   
Com_show_engine_mutex   0   
Com_show_engine_status  0   
Com_show_errors 0   
Com_show_events 0   
Com_show_explain    0   
Com_show_fields 0   
Com_show_function_status    0   
Com_show_generic    0   
Com_show_grants 0   
Com_show_keys   0   
Com_show_master_status  0   
Com_show_open_tables    0   
Com_show_plugins    0   
Com_show_privileges 0   
Com_show_procedure_status   0   
Com_show_processlist    0   
Com_show_profile    0   
Com_show_profiles   0   
Com_show_relaylog_events    0   
Com_show_slave_hosts    0   
Com_show_slave_status   0   
Com_show_status 1   
Com_show_storage_engines    0   
Com_show_table_status   0   
Com_show_tables 0   
Com_show_triggers   0   
Com_show_variables  3   
Com_show_warnings   0   
Com_shutdown    0   
Com_signal  0   
Com_start_all_slaves    0   
Com_start_slave 0   
Com_stmt_close  0   
Com_stmt_execute    0   
Com_stmt_fetch  0   
Com_stmt_prepare    0   
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   0   
Com_update  0   
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 11180   
Cpu_time    0.000000    
Created_tmp_disk_tables 0   
Created_tmp_files   6   
Created_tmp_tables  4   
Delayed_errors  0   

Delayed_insert_threads  0   
Delayed_writes  0   
Delete_scan 0   
Empty_queries   0   
Executed_events 0   
Executed_triggers   0   
Feature_delay_key_write 0   
Feature_dynamic_columns 0   
Feature_fulltext    0   
Feature_gis 0   
Feature_locale  1   
Feature_subquery    0   
Feature_timezone    0   
Feature_trigger 0   
Feature_xml 0   
Flush_commands  1   
Handler_commit  0   
Handler_delete  0   
Handler_discover    0   
Handler_external_lock   0   
Handler_icp_attempts    0   
Handler_icp_match   0   
Handler_mrr_init    0   
Handler_mrr_key_refills 0   
Handler_mrr_rowid_refills   0   
Handler_prepare 0   
Handler_read_first  0   
Handler_read_key    0   
Handler_read_last   0   
Handler_read_next   0   
Handler_read_prev   0   
Handler_read_retry  0   
Handler_read_rnd    0   
Handler_read_rnd_deleted    0   
Handler_read_rnd_next   11  
Handler_rollback    0   
Handler_savepoint   0   
Handler_savepoint_rollback  0   
Handler_tmp_update  0   
Handler_tmp_write   7   
Handler_update  0   
Handler_write   0   
Innodb_available_undo_logs  128 
Innodb_background_log_sync  1240    
Innodb_buffer_pool_bytes_data   189562880   
Innodb_buffer_pool_bytes_dirty  5832704 
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   11570   
Innodb_buffer_pool_pages_dirty  356 
Innodb_buffer_pool_pages_flushed    10802   
Innodb_buffer_pool_pages_free   1561166 
Innodb_buffer_pool_pages_lru_flushed    0   
Innodb_buffer_pool_pages_made_not_young 0   
Innodb_buffer_pool_pages_made_young 0   
Innodb_buffer_pool_pages_misc   120 
Innodb_buffer_pool_pages_old    4428    
Innodb_buffer_pool_pages_total  1572856 
Innodb_buffer_pool_read_ahead   9780    
Innodb_buffer_pool_read_ahead_evicted   0   
Innodb_buffer_pool_read_ahead_rnd   0   
Innodb_buffer_pool_read_requests    2159295327  
Innodb_buffer_pool_reads    1780    
Innodb_buffer_pool_wait_free    0   
Innodb_buffer_pool_write_requests   63831   
Innodb_checkpoint_age   385586  
Innodb_checkpoint_max_age   80826164    
Innodb_data_fsyncs  7786    
Innodb_data_pending_fsyncs  0   
Innodb_data_pending_reads   0   
Innodb_data_pending_writes  0   
Innodb_data_read    189469184   
Innodb_data_reads   11603   
Innodb_data_writes  17885   
Innodb_data_written 361596416   
Innodb_dblwr_pages_written  10802   
Innodb_dblwr_writes 141 
Innodb_deadlocks    0   
Innodb_have_atomic_builtins ON  
Innodb_history_list_length  35  
Innodb_ibuf_discarded_delete_marks  0   
Innodb_ibuf_discarded_deletes   0   
Innodb_ibuf_discarded_inserts   0   
Innodb_ibuf_free_list   0   
Innodb_ibuf_merged_delete_marks 0   
Innodb_ibuf_merged_deletes  0   
Innodb_ibuf_merged_inserts  0   
Innodb_ibuf_merges  0   
Innodb_ibuf_segment_size    2   
Innodb_ibuf_size    1   
Innodb_log_waits    0   
Innodb_log_write_requests   8106    
Innodb_log_writes   6784    
Innodb_lsn_current  1309986856  
Innodb_lsn_flushed  1309986730  
Innodb_lsn_last_checkpoint  1309601270  
Innodb_master_thread_active_loops   1156    
Innodb_master_thread_idle_loops 84  
Innodb_max_trx_id   2809740 
Innodb_mem_adaptive_hash    409079776   

Innodb_mem_dictionary   102244264   
Innodb_mem_total    26990346240 
Innodb_mutex_os_waits   2127    
Innodb_mutex_spin_rounds    42261889    
Innodb_mutex_spin_waits 28215967    
Innodb_oldest_view_low_limit_trx_id 0   
Innodb_os_log_fsyncs    6858    
Innodb_os_log_pending_fsyncs    0   
Innodb_os_log_pending_writes    0   
Innodb_os_log_written   7598080 
Innodb_page_size    16384   
Innodb_pages_created    11  
Innodb_pages_read   11559   
Innodb_pages0_read  33  
Innodb_pages_written    10802   
Innodb_purge_trx_id 2809728 
Innodb_purge_undo_no    0   
Innodb_read_views_memory    720 
Innodb_row_lock_current_waits   3   
Innodb_row_lock_time    2331140 
Innodb_row_lock_time_avg    2578    
Innodb_row_lock_time_max    5959    
Innodb_row_lock_waits   904 
Innodb_rows_deleted 0   
Innodb_rows_inserted    868 
Innodb_rows_read    5115273889  
Innodb_rows_updated 11428   
Innodb_system_rows_deleted  0   
Innodb_system_rows_inserted 0   
Innodb_system_rows_read 0   
Innodb_system_rows_updated  0   
Innodb_s_lock_os_waits  91  
Innodb_s_lock_spin_rounds   2773    
Innodb_s_lock_spin_waits    98  
Innodb_truncated_status_writes  0   
Innodb_x_lock_os_waits  11  
Innodb_x_lock_spin_rounds   372 
Innodb_x_lock_spin_waits    5   
Innodb_page_compression_saved   0   
Innodb_page_compression_trim_sect512    0   
Innodb_page_compression_trim_sect1024   0   
Innodb_page_compression_trim_sect2048   0   
Innodb_page_compression_trim_sect4096   0   
Innodb_page_compression_trim_sect8192   0   
Innodb_page_compression_trim_sect16384  0   
Innodb_page_compression_trim_sect32768  0   
Innodb_num_index_pages_written  2906    
Innodb_num_non_index_pages_written  14979   
Innodb_num_pages_page_compressed    0   
Innodb_num_page_compressed_trim_op  0   
Innodb_num_page_compressed_trim_op_saved    0   
Innodb_num_pages_page_decompressed  0   
Innodb_num_pages_page_compression_error 0   
Innodb_num_pages_encrypted  0   
Innodb_num_pages_decrypted  0   
Innodb_have_lz4 ON  
Innodb_have_lzo OFF 
Innodb_have_lzma    OFF 
Innodb_have_bzip2   OFF 
Innodb_have_snappy  OFF 
Innodb_defragment_compression_failures  0   
Innodb_defragment_failures  0   
Innodb_defragment_count 0   
Innodb_onlineddl_rowlog_rows    0   
Innodb_onlineddl_rowlog_pct_used    0   
Innodb_onlineddl_pct_progress   0   
Innodb_secondary_index_triggered_cluster_reads  13139   
Innodb_secondary_index_triggered_cluster_reads_avo...   0   
Innodb_encryption_rotation_pages_read_from_cache    0   
Innodb_encryption_rotation_pages_read_from_disk 0   
Innodb_encryption_rotation_pages_modified   0   
Innodb_encryption_rotation_pages_flushed    0   
Innodb_encryption_rotation_estimated_iops   0   
Innodb_encryption_key_rotation_list_length  0   
Innodb_scrub_background_page_reorganizations    0   
Innodb_scrub_background_page_splits 0   
Innodb_scrub_background_page_split_failures_underf...   0   
Innodb_scrub_background_page_split_failures_out_of...   0   
Innodb_scrub_background_page_split_failures_missin...   0   
Innodb_scrub_background_page_split_failures_unknow...   0   
Innodb_encryption_num_key_requests  0   
Key_blocks_not_flushed  0   
Key_blocks_unused   19586535    
Key_blocks_used 4   
Key_blocks_warm 0   
Key_read_requests   14  
Key_reads   4   
Key_write_requests  0   
Key_writes  0   
Last_query_cost 10.499000   
Master_gtid_wait_count  0   
Master_gtid_wait_time   0   
Master_gtid_wait_timeouts   0   
Max_statement_time_exceeded 0   
Max_used_connections    7   
Memory_used 67544   
Not_flushed_delayed_rows    0   
Open_files  28  
Open_streams    0   
Open_table_definitions  48  

Open_tables 64  
Opened_files    149 
Opened_plugin_libraries 0   
Opened_table_definitions    0   
Opened_tables   0   
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 4   
Qcache_free_blocks  372 
Qcache_free_memory  65739128    
Qcache_hits 10833   
Qcache_inserts  16036   
Qcache_lowmem_prunes    0   
Qcache_not_cached   2213    
Qcache_queries_in_cache 774 
Qcache_total_blocks 1937    
Queries 235946  
Questions   10  
Rows_read   0   
Rows_sent   8   
Rows_tmp_read   7   
Rpl_status  AUTH_MASTER 
Select_full_join    0   
Select_full_range_join  0   
Select_range    0   
Select_range_check  0   
Select_scan 4   
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    0   
Sort_merge_passes   0   
Sort_priority_queue_sorts   0   
Sort_range  0   
Sort_rows   0   
Sort_scan   0   
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 0   
Syncs   6   
Table_locks_immediate   35507   
Table_locks_waited  0   
Tc_log_max_pages_used   0   
Tc_log_page_size    0   
Tc_log_page_waits   0   
Threadpool_idle_threads 0   
Threadpool_threads  0   

Threads_cached  0   
Threads_connected   7   
Threads_created 7   
Threads_running 5   
Update_scan 0   
Uptime  1247    
Uptime_since_flush_status   1247    
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   

MariaDB 數據庫伺服器配置文件。

#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = *
#
# * Fine Tuning
#
max_connections     = 3500
connect_timeout     = 5
wait_timeout        = 600
max_allowed_packet  = 16M
thread_cache_size       = -1
sort_buffer_size    = 40M
bulk_insert_buffer_size = 160M
tmp_table_size      = 320M
max_heap_table_size = 320M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size     = 23G
#open-files-limit   = 2000
table_open_cache    = 400
myisam_sort_buffer_size = 512M
concurrent_insert   = 2
read_buffer_size    = 2M
read_rnd_buffer_size    = 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit       = 128K
query_cache_size        = 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type       = DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings        = 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit    = 1000
log_slow_verbosity  = query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#report_host        = master1
#auto_increment_increment = 2
#auto_increment_offset  = 1
log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog        = 1
expire_logs_days    = 10
max_binlog_size         = 100M
# slaves
#relay_log      = /var/log/mysql/relay-bin
#relay_log_index    = /var/log/mysql/relay-bin.index
#relay_log_info_file    = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode       = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size   = 50M
innodb_buffer_pool_size = 24G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files   = 400
innodb_io_capacity  = 400
innodb_flush_method = O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

[mysqld]
character-set-server = utf8

我已經my.cnf盡可能地調整了我的方法,但 MariaDB 仍然瘋狂地飆升 - 可以在幾秒鐘內從 110% 上升到 200+。我已經盡我所知為我的表格建立了索引,但似乎沒有什麼能平息滯後。有沒有辦法,因為我有 80GB 的 RAM,我可以讓 MariaDB 使用更多的 RAM,因為目前它只使用了 80GB 總量的 9% 左右?

我在這裡感到迷失,因為我所做的一切都不起作用,甚至似乎沒有幫助

在你的 .cnf 內

$$ mysqld $$部分

max_connections = 300  # from 3500 until you get stabilized
thread_cache_size = 100  # from -1  100 CAP is upper limit at this time
key_buffer_size = 32M  # from 23G whoa, likely an accident. only used by ISAM
REMOVE sort_buffer_size   # from 40M  to default
REMOVE read_buffer_size  # from 2M  to default
REMOVE read_rnd_buffer_size  # from 1M  to default  

通常每天只進行 1 次更改,在您的情況下,將它們全部更改以生存。

mysqlcalculator.com可能是您使用的寶貴資源。

請在正常執行 24 小時後重新發布 SHOW GLOBAL STATUS 並添加到您的問題 SHOW GLOBAL VARIABLES 和 SHOW ENGINE INNODB STATUS 以進行進一步分析。

引用自:https://serverfault.com/questions/871585