需要更新 MySQL 數據庫的建議（軟體和硬體）

我有三個生產中的 MySQL 數據庫（5.7），大約 160GB 的數據。最大的表大約 20GB，最大的模式大約 40GB。我認為所有的表都是 innodb 表。目前我不知道MySQL伺服器機器的配置。我被要求購買一台新伺服器來替換舊伺服器。這個數據庫真的很慢，不能容納超過 100 個連接。我使用此查詢來獲取 max_memory：

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( 
   @@read_buffer_size
   + @@read_rnd_buffer_size
   + @@sort_buffer_size
   + @@join_buffer_size
   + @@binlog_cache_size
   + @@thread_stack
   + @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

結果它給了我 54.7040(GB)。我認為這可能接近伺服器的物理 RAM 大小？我覺得奇怪的一件事是 innodb_buffer_pool_size 只有 4GB 左右。使用此查詢：SHOW VARIABLES LIKE 'innodb_buffer_pool%';

結果是。

innodb_buffer_pool_instances    1
innodb_buffer_pool_size 4294967296

我認為這是錯誤的配置。我對嗎？我打算購買一個具有 512 GB RAM 的 64 核 CPU 來託管它。我計劃將它們從 MySQL 5.7 升級到 8.0。這會成為一個巨大的性能提升嗎？你們認為我應該在新伺服器上將三個數據庫合併為一個還是仍然託管三個數據庫實例？如果我將它們組合成一個數據庫。我需要設置的合理 buffer_pool_size 是多少。我是數據庫新手，所以我的問題可能很愚蠢。感謝您的幫助和建議！

更新：附上show global status

Aborted_clients, 24213
Aborted_connects, 0
Binlog_cache_disk_use, 268
Binlog_cache_use, 2245790
Binlog_stmt_cache_disk_use, 0
Binlog_stmt_cache_use, 0
Bytes_received, 45415670865
Bytes_sent, 77195756974
Com_admin_commands, 39693518
Com_assign_to_keycache, 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, 5
Com_alter_tablespace, 0
Com_analyze, 0
Com_begin, 0
Com_binlog, 0
Com_call_procedure, 0
Com_change_db, 72471
Com_change_master, 0
Com_check, 0
Com_checksum, 0
Com_commit, 1719583
Com_create_db, 0
Com_create_event, 0
Com_create_function, 0
Com_create_index, 0
Com_create_procedure, 0
Com_create_server, 0
Com_create_table, 175
Com_create_trigger, 0
Com_create_udf, 0
Com_create_user, 0
Com_create_view, 0
Com_dealloc_sql, 0
Com_delete, 8943
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_server, 0
Com_drop_table, 176
Com_drop_trigger, 0
Com_drop_user, 0
Com_drop_view, 0
Com_empty_query, 0
Com_execute_sql, 0
Com_flush, 6
Com_grant, 0
Com_ha_close, 0
Com_ha_open, 0
Com_ha_read, 0
Com_help, 0
Com_insert, 2376493
Com_insert_select, 2
Com_install_plugin, 0
Com_kill, 9757
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, 351
Com_replace_select, 0
Com_reset, 0
Com_resignal, 0
Com_revoke, 0
Com_revoke_all, 0
Com_rollback, 67359939
Com_rollback_to_savepoint, 0
Com_savepoint, 0
Com_select, 66889461
Com_set_option, 132188966
Com_signal, 0
Com_show_authors, 0
Com_show_binlog_events, 0
Com_show_binlogs, 21
Com_show_charsets, 5090
Com_show_collations, 1344
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, 20611
Com_show_create_trigger, 0
Com_show_databases, 207
Com_show_engine_logs, 0
Com_show_engine_mutex, 0
Com_show_engine_status, 2
Com_show_events, 0
Com_show_errors, 0
Com_show_fields, 12249
Com_show_function_status, 27
Com_show_grants, 6
Com_show_keys, 361
Com_show_master_status, 19
Com_show_open_tables, 0
Com_show_plugins, 0
Com_show_privileges, 0
Com_show_procedure_status, 27
Com_show_processlist, 381
Com_show_profile, 0
Com_show_profiles, 0
Com_show_relaylog_events, 0
Com_show_slave_hosts, 0
Com_show_slave_status, 3
Com_show_status, 8077
Com_show_storage_engines, 14
Com_show_table_status, 11
Com_show_tables, 838
Com_show_triggers, 82
Com_show_variables, 13196
Com_show_warnings, 0
Com_slave_start, 0
Com_slave_stop, 0
Com_stmt_close, 536
Com_stmt_execute, 536
Com_stmt_fetch, 0
Com_stmt_prepare, 536
Com_stmt_reprepare, 0
Com_stmt_reset, 0
Com_stmt_send_long_data, 0
Com_truncate, 0
Com_uninstall_plugin, 0
Com_unlock_tables, 3
Com_update, 174401
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
Connections, 33107958
Created_tmp_disk_tables, 15056
Created_tmp_files, 3067
Created_tmp_tables, 76714
Delayed_errors, 0
Delayed_insert_threads, 0
Delayed_writes, 0
Flush_commands, 5
Handler_commit, 42098418
Handler_delete, 50581
Handler_discover, 0
Handler_prepare, 8244948
Handler_read_first, 187940
Handler_read_key, 210889806
Handler_read_last, 2562
Handler_read_next, 235534577
Handler_read_prev, 944581109
Handler_read_rnd, 1984245
Handler_read_rnd_next, 4128983877
Handler_rollback, 32895675
Handler_savepoint, 0
Handler_savepoint_rollback, 0
Handler_update, 35296124
Handler_write, 31003805
Innodb_buffer_pool_pages_data, 262063
Innodb_buffer_pool_pages_dirty, 181
Innodb_buffer_pool_pages_flushed, 1073914
Innodb_buffer_pool_pages_free, 0
Innodb_buffer_pool_pages_misc, 81
Innodb_buffer_pool_pages_total, 262144
Innodb_buffer_pool_read_ahead_rnd, 0
Innodb_buffer_pool_read_ahead, 573918785
Innodb_buffer_pool_read_ahead_evicted, 465869
Innodb_buffer_pool_read_requests, 1583154591
Innodb_buffer_pool_reads, 6918613
Innodb_buffer_pool_wait_free, 0
Innodb_buffer_pool_write_requests, 23935481
Innodb_data_fsyncs, 4531875
Innodb_data_pending_fsyncs, 1
Innodb_data_pending_reads, 0
Innodb_data_pending_writes, 0
Innodb_data_read, 3315879936
Innodb_data_reads, 580850961
Innodb_data_writes, 5054458
Innodb_data_written, 46713856
Innodb_dblwr_pages_written, 536957
Innodb_dblwr_writes, 18531
Innodb_have_atomic_builtins, ON
Innodb_log_waits, 0
Innodb_log_write_requests, 3243758
Innodb_log_writes, 4481087
Innodb_os_log_fsyncs, 4496724
Innodb_os_log_pending_fsyncs, 1
Innodb_os_log_pending_writes, 0
Innodb_os_log_written, 3918738944
Innodb_page_size, 16384
Innodb_pages_created, 47610
Innodb_pages_read, 580851446
Innodb_pages_written, 536957
Innodb_row_lock_current_waits, 0
Innodb_row_lock_time, 8749
Innodb_row_lock_time_avg, 11
Innodb_row_lock_time_max, 114
Innodb_row_lock_waits, 771
Innodb_rows_deleted, 50581
Innodb_rows_inserted, 4969966
Innodb_rows_read, 2753561383
Innodb_rows_updated, 582607
Innodb_truncated_status_writes, 0
Key_blocks_not_flushed, 0
Key_blocks_unused, 6698
Key_blocks_used, 261
Key_read_requests, 2125947
Key_reads, 36
Key_write_requests, 198899
Key_writes, 0
Last_query_cost, 0.000000
Max_used_connections, 94
Not_flushed_delayed_rows, 0
Open_files, 3
Open_streams, 0
Open_table_definitions, 400
Open_tables, 43
Opened_files, 208257
Opened_table_definitions, 87043
Opened_tables, 92483
Performance_schema_cond_classes_lost, 0
Performance_schema_cond_instances_lost, 0
Performance_schema_file_classes_lost, 0
Performance_schema_file_handles_lost, 0
Performance_schema_file_instances_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_table_handles_lost, 0
Performance_schema_table_instances_lost, 0
Performance_schema_thread_classes_lost, 0
Performance_schema_thread_instances_lost, 0
Prepared_stmt_count, 0
Qcache_free_blocks, 0
Qcache_free_memory, 0
Qcache_hits, 0
Qcache_inserts, 0
Qcache_lowmem_prunes, 0
Qcache_not_cached, 0
Qcache_queries_in_cache, 0
Qcache_total_blocks, 0
Queries, 304460265
Questions, 303968470
Rpl_status, AUTH_MASTER
Select_full_join, 967
Select_full_range_join, 0
Select_range, 38133
Select_range_check, 0
Select_scan, 228931
Slave_heartbeat_period, 0.000
Slave_open_temp_tables, 0
Slave_received_heartbeats, 0
Slave_retried_transactions, 0
Slave_running, OFF
Slow_launch_threads, 0
Slow_queries, 2533
Sort_merge_passes, 1465
Sort_range, 32838342
Sort_rows, 1875805
Sort_scan, 9814
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_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, 
Table_locks_immediate, 36266242
Table_locks_waited, 0
Tc_log_max_pages_used, 0
Tc_log_page_size, 0
Tc_log_page_waits, 0
Threads_cached, 3
Threads_connected, 27
Threads_created, 8926
Threads_running, 4
Uptime, 159095
Uptime_since_flush_status, 159095

更新 2：感謝 Rick James，這是節目變數。我現在無法訪問慢日誌。但我會看看你的網站並了解它。

auto_increment_increment    1
auto_increment_offset   1
autocommit  ON
automatic_sp_privileges ON
back_log    50
basedir C:\Program Files\MySQL\MySQL Server 5.5\
big_tables  OFF
binlog_cache_size   32768
binlog_direct_non_transactional_updates OFF
binlog_format   ROW
binlog_stmt_cache_size  32768
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  C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\
collation_connection    utf8mb4_general_ci
collation_database  utf8_general_ci
collation_server    utf8_general_ci
completion_type NO_CHAIN
concurrent_insert   AUTO
connect_timeout 10
datadir C:\ProgramData\MySQL\MySQL Server 5.5\data\
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_storage_engine  InnoDB
default_week_format 0
delay_key_write ON
delayed_insert_limit    100
delayed_insert_timeout  300
delayed_queue_size  1000
div_precision_increment 4
engine_condition_pushdown   ON
error_count 0
event_scheduler OFF
expire_logs_days    0
external_user   
flush   OFF
flush_time  1800
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    C:\ProgramData\MySQL\MySQL Server 5.5\data\PC.log
group_concat_max_len    1024
have_compress   YES
have_crypt  NO
have_csv    YES
have_dynamic_loading    YES
have_geometry   YES
have_innodb YES
have_ndbcluster NO
have_openssl    DISABLED
have_partitioning   YES
have_profiling  YES
have_query_cache    YES
have_rtree_keys YES
have_ssl    DISABLED
have_symlink    YES
hostname    Vibrant7-PC
identity    0
ignore_builtin_innodb   OFF
init_connect    
init_file   
init_slave  
innodb_adaptive_flushing    ON
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size 33554432
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 4294967296
innodb_change_buffering all
innodb_checksums    ON
innodb_commit_concurrency   0
innodb_concurrency_tickets  500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir    
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   OFF
innodb_flush_log_at_trx_commit  1
innodb_flush_method 
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_io_capacity  200
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  16777216
innodb_log_file_size    1073741824
innodb_log_files_in_group   2
innodb_log_group_home_dir   .\
innodb_max_dirty_pages_pct  75
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files   300
innodb_purge_batch_size 20
innodb_purge_threads    0
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  4
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_spin_wait_delay  6
innodb_stats_method nulls_equal
innodb_stats_on_metadata    ON
innodb_stats_sample_pages   8
innodb_strict_mode  OFF
innodb_support_xa   ON
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   25
innodb_thread_sleep_delay   10000
innodb_use_native_aio   ON
innodb_use_sys_malloc   ON
innodb_version  1.1.8
innodb_write_io_threads 4
insert_id   0
interactive_timeout 28800
join_buffer_size    131072
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 C:\Program Files\MySQL\MySQL Server 5.5\share\
lc_time_names   en_US
license GPL
local_infile    ON
lock_wait_timeout   31536000
log OFF
log_bin ON
log_bin_trust_function_creators OFF
log_error   C:\ProgramData\MySQL\MySQL Server 5.5\data\PC.err
log_output  FILE
log_queries_not_using_indexes   OFF
log_slave_updates   OFF
log_slow_queries    OFF
log_warnings    1
long_query_time 10.000000
low_priority_updates    OFF
lower_case_file_system  ON
lower_case_table_names  1
max_allowed_packet  1048576
max_binlog_cache_size   18446744073709547520
max_binlog_size 1073741824
max_binlog_stmt_cache_size  18446744073709547520
max_connect_errors  10
max_connections 100
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads  20
max_join_size   18446744073709551615
max_length_for_sort_data    1024
max_long_data_size  1048576
max_prepared_stmt_count 16382
max_relay_log_size  0
max_seeks_for_key   4294967295
max_sort_length 1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    4294967295
metadata_locks_cache_size   1024
min_examined_row_limit  0
multi_range_count   256
myisam_data_pointer_size    6
myisam_max_sort_file_size   107374182400
myisam_mmap_size    18446744073709551615
myisam_recover_options  OFF
myisam_repair_threads   1
myisam_sort_buffer_size 2147483648
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
named_pipe  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   OFF
open_files_limit    2670
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
performance_schema  OFF
performance_schema_events_waits_history_long_size   10000
performance_schema_events_waits_history_size    10
performance_schema_max_cond_classes 80
performance_schema_max_cond_instances   1000
performance_schema_max_file_classes 50
performance_schema_max_file_handles 32768
performance_schema_max_file_instances   10000
performance_schema_max_mutex_classes    200
performance_schema_max_mutex_instances  1000000
performance_schema_max_rwlock_classes   30
performance_schema_max_rwlock_instances 1000000
performance_schema_max_table_handles    100000
performance_schema_max_table_instances  50000
performance_schema_max_thread_classes   50
performance_schema_max_thread_instances 1000
pid_file    C:\ProgramData\MySQL\MySQL Server 5.5\data\PC.pid
plugin_dir  C:\Program Files\MySQL\MySQL Server 5.5\lib\plugin\
port    3306
preload_buffer_size 32768
profiling   OFF
profiling_history_size  15
protocol_version    10
proxy_user  
pseudo_thread_id    40772511
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    0
query_cache_type    ON
query_cache_wlock_invalidate    OFF
query_prealloc_size 8192
rand_seed1  0
rand_seed2  0
range_alloc_block_size  4096
read_buffer_size    65536
read_only   OFF
read_rnd_buffer_size    262144
relay_log   
relay_log_index 
relay_log_info_file relay-log.info
relay_log_purge ON
relay_log_recovery  OFF
relay_log_space_limit   0
report_host 
report_password 
report_port 3306
report_user 
rpl_recovery_rank   0
secure_auth OFF
secure_file_priv    
server_id   1
shared_memory   OFF
shared_memory_base_name MYSQL
skip_external_locking   ON
skip_name_resolve   OFF
skip_networking OFF
skip_show_database  OFF
slave_compressed_protocol   OFF
slave_exec_mode STRICT
slave_load_tmpdir   C:\Windows\TEMP
slave_net_timeout   3600
slave_skip_errors   OFF
slave_transaction_retries   10
slave_type_conversions  
slow_launch_time    2
slow_query_log  OFF
slow_query_log_file C:\ProgramData\MySQL\MySQL Server 5.5\data\PC-slow.log
socket  MySQL
sort_buffer_size    262144
sql_auto_is_null    OFF
sql_big_selects ON
sql_big_tables  OFF
sql_buffer_result   OFF
sql_log_bin ON
sql_log_off OFF
sql_low_priority_updates    OFF
sql_max_join_size   18446744073709551615
sql_mode    STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_notes   ON
sql_quote_show_create   ON
sql_safe_updates    ON
sql_select_limit    18446744073709551615
sql_slave_skip_counter  0
sql_warnings    OFF
ssl_ca  
ssl_capath  
ssl_cert    
ssl_cipher  
ssl_key 
storage_engine  InnoDB
stored_program_cache    256
sync_binlog 0
sync_frm    ON
sync_master_info    0
sync_relay_log  0
sync_relay_log_info 0
system_time_zone    Pacific Daylight Time
table_definition_cache  400
table_open_cache    256
thread_cache_size   8
thread_concurrency  10
thread_handling one-thread-per-connection
thread_stack    262144
time_format %H:%i:%s
time_zone   SYSTEM
timed_mutexes   OFF
timestamp   1630002865
tmp_table_size  543162368
tmpdir  C:\Windows\TEMP
transaction_alloc_block_size    8192
transaction_prealloc_size   4096
tx_isolation    REPEATABLE-READ
unique_checks   ON
updatable_views_with_limit  YES
version 5.5.21-log
version_comment MySQL Community Server (GPL)
version_compile_machine x86
version_compile_os  Win64
wait_timeout    28800
warning_count   0

全球狀態和變數分析：

觀察：

• 版本：5.5.21-日誌
• 512 GB 記憶體
• 正常執行時間 = 1d 20:11:35
• 您正在 Windows 上執行。
• 執行 32 位版本
• 您似乎完全（或大部分）執行 InnoDB。

更重要的問題：

MySQL 5.5 已經很老了；建議你升級。建議升級到 5.6，然後是 5.7。之後是 8.0，這是一個更重要的升級。

嚴重問題： 您似乎正在執行 32 位版本的 MySQL。它不能使用超過 4GB，並且將 buffer_pool 設置為 4G 可能會導致崩潰。升級時，請務必獲得 64 位版本。您將無法使用超過 4GB 的 512GB；多麼浪費！

增加innodb_buffer_pool_size（如前所述）

如果磁碟是SSD，可以增加幾個IO設置；見下文

似乎有大量的 ROLLBACK；檢查這個。請記住，某些回滾可能是由斷開連接或中止引起的。

最好有innodb_file_per_table = ON，至少在創建大表時。

每秒 249 個“管理”命令（佔查詢的 13%）——這相當多；到底是怎麼回事？

SET被執行的頻率大約是 的兩倍SELECT。到底是怎麼回事？

max_connections = 100和Max_used_connections is 94。系統已接近耗盡連接。

細節和其他觀察：

( innodb_buffer_pool_size ) = 4,096M / 524288M = 0.78%– InnoDB buffer_pool 使用的 RAM 百分比 – 設置為可用 RAM 的 70% 左右。（太低效率低；太高風險交換。）

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 4096M / 1 = 4096MB– 每個 buffer_pool 實例的大小。– 一個實例至少應為 1GB。在非常大的 RAM 中，有 16 個實例。

( innodb_io_capacity ) = 200– 沖洗時，使用這麼多的 IOP。– 讀取可能緩慢或尖刺。

( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 580,851,446 / 1583154591 = 36.7%– 必須命中磁碟的讀取請求 – 增加 innodb_buffer_pool_size（現在為 4294967296）

( Innodb_log_writes ) = 4,481,087 / 159095 = 28 /sec

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 159,095 / 60 * 1024M / 3918738944 = 726– InnoDB 日誌輪換之間的分鐘數從 5.6.8 開始，可以動態更改；請務必同時更改 my.cnf。– （輪換間隔 60 分鐘的建議有些武斷。）調整 innodb_log_file_size（現在為 1073741824）。（不能在 AWS 中更改。）

( innodb_flush_method ) = innodb_flush_method = – InnoDB 應該如何要求作業系統寫入塊。建議使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 以避免雙重緩衝。（至少對於 Unix。）有關 O_ALL_DIRECT 的警告，請參閱 chrischandler

( Com_rollback ) = 67,359,939 / 159095 = 423 /sec– InnoDB 中的回滾。– 回滾頻率過高可能表明應用程序邏輯效率低下。

( Handler_rollback ) = 32,895,675 / 159095 = 206 /sec ——為什麼有這麼多回滾？

( innodb_file_per_table ) = innodb_file_per_table = OFF– 將每個文件放在自己的表空間中 – （輕度推薦，尤其是大表）

( innodb_additional_mem_pool_size ) = 32M– （在 5.6.3 中棄用，在 5.7.4 中刪除。） – 不需要高值。

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 580851446 + 536957 ) / 159095 / 200 = 1827.2%– 如果 > 100%，需要更多的 io_capacity。– 如果驅動器可以處理，則增加 innodb_io_capacity（現在為 200）。

( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。

( innodb_stats_on_metadata ) = innodb_stats_on_metadata = ON- 觸摸統計數據時重新分析表格。– ON 可能會減慢某些 SHOW 和 information_schema 訪問，尤其是在 5.1 和 5.5 中

( innodb_strict_mode ) = innodb_strict_mode = OFF– 更早發現一些細微的錯誤。– OFF 留下一些警告作為警告；ON 使它們出錯。

( sync_binlog ) = 0– 使用 1 來增加安全性，以 I/O 為代價 =1 可能會導致大量“查詢結束”；=0 可能會導致“binlog 在不可能的位置”並在崩潰中失去事務，但速度更快。0 對 Galera 來說是可以的。

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON– 通常應該是 ON。– 在某些情況下，OFF 更好。另請參見 innodb_adaptive_hash_index_parts（5.7.9 之後）和 innodb_adaptive_hash_index_partitions（MariaDB 和 Percona）。ON 涉及罕見的崩潰（錯誤 73890）。10.5.0 決定預設關閉。

( Handler_rollback/Questions ) = 32,895,675/303968470 = 10.8%– 回滾/查詢 – 為什麼有這麼多 ROLLBACK？

( _64bit ) = _64bit = 32– 32 位或 64 位 mysqld – 現在很少執行 32 位。可能會遇到一些限制，尤其是在 Windows 上。

( character_set_server ) = character_set_server = utf8 – 將 character_set_server（現在是 utf8）設置為 utf8mb4 可以幫助解決字元集問題。那是未來的預設值。

( local_infile ) = local_infile = ON – local_infile (now ON) = ON 是一個潛在的安全問題

( bulk_insert_buffer_size ) = 8 / 524288M = 0.00%– 用於多行插入和載入數據的緩衝區 – 太大可能會威脅 RAM 大小。太小可能會阻礙此類操作。

( tmp_table_size ) = 518M– 限制用於支持 SELECT 的MEMORY臨時表的大小 – 減少 tmp_table_size（現在為 543162368）以避免記憶體不足。也許不超過64M。

( Com_rollback / (Com_commit + Com_rollback) ) = 67,359,939 / (1719583 + 67359939) = 97.5%– Rollback : Commit ratio – 回滾成本很高；更改應用邏輯

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (2376493 + 174401 + 8943 + 351) / 1719583 = 1.49– 每個送出的語句（假設所有 InnoDB） – 低：可能有助於在事務中將查詢分組；高：長期交易使各種事情緊張。

( Com_admin_commands ) = 39,693,518 / 159095 = 249 /sec – 為什麼有這麼多的 DDL 語句？

( Com_admin_commands / Queries ) = 39,693,518 / 304460265 = 13.0%– “管理員”命令的查詢百分比。 - 這是怎麼回事？

( Com_set_option / Com_select ) = 132,188,966 / 66889461 = 197.6% – 做更多的 SET 而不是 SELECT 似乎是“錯誤的”。

( Com__biggest ) = Com__biggest = Com_set_option– 哪個“Com_”指標最大。– 通常是 Com_select（現在是 66889461）。如果是別的東西，那麼它可能是一個草率的平台，或者可能是別的東西。

( log_slow_queries ) = log_slow_queries = OFF– 是否記錄慢查詢。（5.1.29、5.6.1之前）

( slow_query_log ) = slow_query_log = OFF– 是否記錄慢查詢。(5.1.12)

( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2

( back_log ) = 50– （自 5.6.6 起自動調整大小；基於 max_connections） – 提高到 min(150, max_connections (現在為 100)) 在進行大量連接時可能會有所幫助。

( Connections ) = 33,107,958 / 159095 = 208 /sec– Connections – 增加wait_timeout（現在是28800）；使用池化？

你有一半的查詢記憶體。您應該同時設置 query_cache_type = OFF 和 query_cache_size = 0 。（根據傳言）QC 程式碼中有一個“錯誤”，除非您關閉這兩個設置，否則某些程式碼會保持打開狀態。

異常小：

10 * read_buffer_size = 0.6MB
Innodb_data_written = 293 /sec
Open_tables = 43

異常大：

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 3,654
Com_commit + Com_rollback = 434 /sec
Com_kill = 0.061 /sec
Com_set_option = 830 /sec
Com_show_charsets = 0.032 /sec
Com_show_databases = 4.7 /HR
Com_show_storage_engines = 0.32 /HR
Innodb_buffer_pool_read_ahead = 3607 /sec
Innodb_data_reads = 3650 /sec
Innodb_log_writes / Innodb_log_write_requests = 138.1%
Innodb_pages_read = 3650 /sec
Innodb_pages_read + Innodb_pages_written = 3654 /sec
Sort_range = 206 /sec
innodb_thread_concurrency = 25
net_buffer_length / max_allowed_packet = 1.6%

異常字元串：

datetime_format = %Y-%m-%d, %H:%i:%s
ft_boolean_syntax = +, -><()~*:&
have_crypt = NO
innodb_fast_shutdown = 1
lower_case_file_system = ON
lower_case_table_names = 1
sql_safe_updates = ON
version_compile_machine = x86

引用自：https://dba.stackexchange.com/questions/298578