FreeBSD 与 mysql 的交换使用情况

FreeBSD 与 mysql 的交换使用情况

在过去的几个月中,在几台具有类似 FreeBSD 版本和规格的不同服务器上,我一直遇到 MySQL 的高交换使用率,尽管似乎有大量的可用 RAM。

  • FreeBSD 11.2-RELEASE-p8(也发生在 p2 中)
  • mysql-server 5.6.43(5.6.40 中也发生过此问题)
  • 托管在 vmware vcloud 中。
  • MySQLdatadir位于 zvol 中,vfs.zfs.arc_max="512M"
  • 4 vcpu 和 8GB RAM。

它不会立即使用交换空间。需要几周的时间才能逐渐使用更多的交换空间,直到没有剩余交换空间。此特定服务器尚未达到最大交换空间,但正在接近。

last pid:  7054;  load averages:  0.53,  0.44,  0.40                                         up 14+20:16:49  13:28:17
32 processes:  1 running, 31 sleeping
CPU:  0.0% user,  0.0% nice,  0.0% system,  0.0% interrupt,  100% idle
Mem: 233M Active, 1966M Inact, 1994M Laundry, 1326M Wired, 533M Buf, 412M Free
ARC: 189M Total, 47M MFU, 74M MRU, 288K Anon, 5185K Header, 62M Other
     63M Compressed, 208M Uncompressed, 3.28:1 Ratio
Swap: 6144M Total, 4684M Used, 1460M Free, 76% Inuse

  PID USERNAME    THR PRI NICE   SIZE    RES STATE   C   TIME    WCPU COMMAND
  125 root          2  20    0 22348K  5628K select  1  15:47   0.05% vmtoolsd
 7053 root          1  20    0  7920K  3600K CPU2    2   0:00   0.04% top
93525 root          1  20    0   154M   113M nanslp  1   0:36   0.02% php
 1140 mysql        30  20    0  8720M  3519M select  0 344:58   0.02% mysqld
  681 root          1  20    0 77684K 31444K select  1   5:52   0.01% snmpd
  452 root          1  20    0  9184K  1048K select  2   1:23   0.01% devd
 6958 dfairley      1  20    0 13164K  7968K select  0   0:00   0.01% sshd
93513 root          1  20    0   104M 23360K kqread  2   0:05   0.01% php-fpm
  711 root          1  20    0 12712K 12808K select  2   1:11   0.00% ntpd
  619 root          1  20    0  6416K  2004K select  0   0:36   0.00% syslogd
 1179 root          1  20    0 10456K  5204K select  1   0:20   0.00% sendmail
 6949 daemon        1  41    0   118M 63328K accept  0   0:53   0.00% php-fpm
 6954 daemon        1  31    0   116M 62008K accept  0   0:38   0.00% php-fpm
 7002 daemon        1  35    0   114M 56476K accept  2   0:21   0.00% php-fpm
 1186 root          1  20    0  6468K   800K nanslp  0   0:05   0.00% cron
 6999 daemon        1  44    0   114M 54684K accept  2   0:04   0.00% php-fpm
  401 root          1  42    0  6548K  1992K select  2   0:01   0.00% dhclient
 1182 smmsp         1  20    0 10456K  3352K pause   2   0:00   0.00% sendmail
  447 _dhcp         1  20    0  6548K  2048K select  0   0:00   0.00% dhclient
 1176 root          1  20    0 12852K  5168K select  2   0:00   0.00% sshd
  738 mysql         1  52    0  7064K     0K wait    2   0:00   0.00% <sh>
 6959 dfairley      1  20    0  7416K  3784K pause   3   0:00   0.00% csh
 6956 root          1  23    0 13164K  7920K select  0   0:00   0.00% sshd
 7052 root          1  20    0  7612K  3796K select  1   0:00   0.00% sudo
 1237 root          1  52    0  6408K  1796K ttyin   2   0:00   0.00% getty
 1238 root          1  52    0  6408K  1796K ttyin   2   0:00   0.00% getty
 1239 root          1  52    0  6408K  1796K ttyin   0   0:00   0.00% getty
 1236 root          1  52    0  6408K  1796K ttyin   3   0:00   0.00% getty
 1241 root          1  52    0  6408K  1796K ttyin   3   0:00   0.00% getty
 1242 root          1  52    0  6408K  1796K ttyin   1   0:00   0.00% getty
 1235 root          1  52    0  6408K  1796K ttyin   1   0:00   0.00% getty
 1240 root          1  52    0  6408K  1796K ttyin   3   0:00   0.00% getty

最终,交换消息开始/var/log/messages每隔 10 分钟左右出现一次。

my.cnf

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
default_time_zone=America/Vancouver
port        = 3306
socket      = /tmp/mysql.sock
key_buffer_size = 32M
expire_logs_days=30
join_buffer_size = 1M
table_definition_cache = 1500
table_open_cache = 1500
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
max_allowed_packet = 128M
slave-skip-errors=1022,1050,1060,1061,1062,1169,1146
server-id   = 253051
log-bin=/data/binarylogs/mysql-bin
log-bin-index=/data/binarylogs/mysql-bin
binlog_format=mixed
innodb_log_group_home_dir=/data/mysql/logs
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 512M
innodb_buffer_pool_instances = 4
skip-innodb_doublewrite
innodb_flush_log_at_trx_commit = 2
max_connections=80

[mysqldump]
quick
max_allowed_packet = 128M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

什么原因导致 mysql 在有大量可用内存的情况下重复分配交换?

编辑:输出SHOW GLOBAL STATUS;

Aborted_clients 0
Aborted_connects    0
Binlog_cache_disk_use   368
Binlog_cache_use    1495766
Binlog_stmt_cache_disk_use  0
Binlog_stmt_cache_use   183
Bytes_received  6673111397
Bytes_sent  16919455552
Com_admin_commands  0
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 140
Com_alter_tablespace    0
Com_alter_user  0
Com_analyze 0
Com_begin   682
Com_binlog  0
Com_call_procedure  2
Com_change_db   17594185
Com_change_master   0
Com_check   1815
Com_checksum    0
Com_commit  682
Com_create_db   1
Com_create_event    0
Com_create_function 0
Com_create_index    0
Com_create_procedure    4
Com_create_server   0
Com_create_table    81
Com_create_trigger  78
Com_create_udf  0
Com_create_user 0
Com_create_view 0
Com_dealloc_sql 118
Com_delete  33434
Com_delete_multi    34
Com_do  0
Com_drop_db 1
Com_drop_event  0
Com_drop_function   0
Com_drop_index  0
Com_drop_procedure  8
Com_drop_server 0
Com_drop_table  1
Com_drop_trigger    78
Com_drop_user   0
Com_drop_view   0
Com_empty_query 0
Com_execute_sql 118
Com_flush   1
Com_get_diagnostics 0
Com_grant   0
Com_ha_close    0
Com_ha_open 0
Com_ha_read 0
Com_help    0
Com_insert  2267022
Com_insert_select   1
Com_install_plugin  0
Com_kill    0
Com_load    0
Com_lock_tables 711
Com_optimize    0
Com_preload_keys    0
Com_prepare_sql 118
Com_purge   0
Com_purge_before_date   0
Com_release_savepoint   0
Com_rename_table    0
Com_rename_user 0
Com_repair  1
Com_replace 0
Com_replace_select  0
Com_reset   0
Com_resignal    0
Com_revoke  0
Com_revoke_all  0
Com_rollback    0
Com_rollback_to_savepoint   0
Com_savepoint   0
Com_select  23827649
Com_set_option  1874466
Com_signal  0
Com_show_binlog_events  0
Com_show_binlogs    0
Com_show_charsets   0
Com_show_collations 88
Com_show_create_db  331
Com_show_create_event   0
Com_show_create_func    0
Com_show_create_proc    0
Com_show_create_table   50
Com_show_create_trigger 0
Com_show_databases  2
Com_show_engine_logs    0
Com_show_engine_mutex   0
Com_show_engine_status  1
Com_show_events 7
Com_show_errors 0
Com_show_fields 6349
Com_show_function_code  0
Com_show_function_status    0
Com_show_grants 0
Com_show_keys   2445
Com_show_master_status  0
Com_show_open_tables    0
Com_show_plugins    0
Com_show_privileges 0
Com_show_procedure_code 0
Com_show_procedure_status   0
Com_show_processlist    75
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 38530
Com_show_storage_engines    0
Com_show_table_status   149
Com_show_tables 1258634
Com_show_triggers   3
Com_show_variables  38882
Com_show_warnings   3
Com_slave_start 0
Com_slave_stop  0
Com_stmt_close  118
Com_stmt_execute    118
Com_stmt_fetch  0
Com_stmt_prepare    118
Com_stmt_reprepare  0
Com_stmt_reset  0
Com_stmt_send_long_data 0
Com_truncate    0
Com_uninstall_plugin    0
Com_unlock_tables   709
Com_update  488759
Com_update_multi    294
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 586795
Created_tmp_disk_tables 303745
Created_tmp_files   269
Created_tmp_tables  3267898
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Handler_commit  26737048
Handler_delete  206673
Handler_discover    0
Handler_external_lock   69915146
Handler_mrr_init    0
Handler_prepare 2992470
Handler_read_first  20029611
Handler_read_key    1228486328
Handler_read_last   100115
Handler_read_next   1712542996
Handler_read_prev   1412213
Handler_read_rnd    21965186
Handler_read_rnd_next   3295746264
Handler_rollback    2668
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  800615
Handler_write   76355829
Innodb_buffer_pool_dump_status  not started
Innodb_buffer_pool_load_status  not started
Innodb_buffer_pool_pages_data   252762
Innodb_buffer_pool_bytes_data   4141252608
Innodb_buffer_pool_pages_dirty  78
Innodb_buffer_pool_bytes_dirty  1277952
Innodb_buffer_pool_pages_flushed    4358689
Innodb_buffer_pool_pages_free   4096
Innodb_buffer_pool_pages_misc   5286
Innodb_buffer_pool_pages_total  262144
Innodb_buffer_pool_read_ahead_rnd   0
Innodb_buffer_pool_read_ahead   4428
Innodb_buffer_pool_read_ahead_evicted   20
Innodb_buffer_pool_read_requests    5898171234
Innodb_buffer_pool_reads    245395
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   19741903
Innodb_data_fsyncs  1553378
Innodb_data_pending_fsyncs  0
Innodb_data_pending_reads   0
Innodb_data_pending_writes  0
Innodb_data_read    6139465728
Innodb_data_reads   372118
Innodb_data_writes  5567954
Innodb_data_written 73872195072
Innodb_dblwr_pages_written  0
Innodb_dblwr_writes 0
Innodb_have_atomic_builtins ON
Innodb_log_waits    0
Innodb_log_write_requests   3607495
Innodb_log_writes   1750027
Innodb_os_log_fsyncs    316644
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   2416485376
Innodb_page_size    16384
Innodb_pages_created    26254
Innodb_pages_read   374718
Innodb_pages_written    4358689
Innodb_row_lock_current_waits   0
Innodb_row_lock_time    2560
Innodb_row_lock_time_avg    2
Innodb_row_lock_time_max    89
Innodb_row_lock_waits   1194
Innodb_rows_deleted 99776
Innodb_rows_inserted    2033207
Innodb_rows_read    5579212917
Innodb_rows_updated 689505
Innodb_num_open_files   1500
Innodb_truncated_status_writes  0
Innodb_available_undo_logs  128
Key_blocks_not_flushed  0
Key_blocks_unused   26756
Key_blocks_used 498
Key_read_requests   8896641
Key_reads   55
Key_write_requests  2430085
Key_writes  69
Last_query_cost 0.000000
Last_query_partial_plans    0
Max_used_connections    43
Not_flushed_delayed_rows    0
Open_files  59
Open_streams    0
Open_table_definitions  1500
Open_tables 1500
Opened_files    1225846
Opened_table_definitions    2741
Opened_tables   3734
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  1
Qcache_free_memory  1031432
Qcache_hits 0
Qcache_inserts  0
Qcache_lowmem_prunes    0
Qcache_not_cached   23787035
Qcache_queries_in_cache 0
Qcache_total_blocks 1
Queries 49223617
Questions   46560144
Rsa_public_key   
Select_full_join    39259
Select_full_range_join  0
Select_range    370666
Select_range_check  0
Select_scan 3833980
Slave_heartbeat_period  0.000
Slave_last_heartbeat     
Slave_open_temp_tables  0
Slave_received_heartbeats   0
Slave_retried_transactions  0
Slave_running   OFF
Slow_launch_threads 0
Slow_queries    4
Sort_merge_passes   6
Sort_range  212023
Sort_rows   34405674
Sort_scan   1616766
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  
Table_locks_immediate   34856215
Table_locks_waited  1
Table_open_cache_hits   34862013
Table_open_cache_misses 3681
Table_open_cache_overflows  1819
Tc_log_max_pages_used   0
Tc_log_page_size    0
Tc_log_page_waits   0
Threads_cached  6
Threads_connected   3
Threads_created 16681
Threads_running 1
Uptime  1287299
Uptime_since_flush_status   1287299

SHOW VARIABLES;

auto_increment_increment    1
auto_increment_offset   1
autocommit  ON
automatic_sp_privileges ON
avoid_temporal_upgrade  OFF
back_log    66
basedir /usr/local/
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    utf8mb4
character_set_connection    utf8mb4
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/local/share/mysql/charsets/
collation_connection    utf8mb4_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci
completion_type NO_CHAIN
concurrent_insert   AUTO
connect_timeout 10
core_file   OFF
datadir /var/db/mysql/
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    30
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/db/mysql/rad-bccfa.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    DISABLED
have_profiling  YES
have_query_cache    YES
have_rtree_keys YES
have_ssl    DISABLED
have_symlink    YES
host_cache_size 208
hostname    rad-bccfa
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    4
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 4294967296
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     
innodb_disable_sort_file_cache  OFF
innodb_doublewrite  OFF
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  2
innodb_flush_method  
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    536870912
innodb_log_files_in_group   2
innodb_log_group_home_dir   /data/mysql/logs
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   1500
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_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  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_tmpdir    
innodb_undo_directory   .
innodb_undo_logs    128
innodb_undo_tablespaces 0
innodb_use_native_aio   OFF
innodb_use_sys_malloc   ON
innodb_version  5.6.43
innodb_write_io_threads 4
insert_id   0
interactive_timeout 28800
join_buffer_size    1048576
keep_files_on_create    OFF
key_buffer_size 33554432
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 /usr/local/share/mysql/
lc_time_names   en_US
license GPL
local_infile    ON
lock_wait_timeout   31536000
locked_in_memory    OFF
log_bin ON
log_bin_basename    /data/binarylogs/mysql-bin
log_bin_index   /data/binarylogs/mysql-bin.index
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events   OFF
log_error   ./rad-bccfa.err
log_output  FILE
log_queries_not_using_indexes   OFF
log_slave_updates   OFF
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  FILE
master_verify_checksum  OFF
max_allowed_packet  134217728
max_binlog_cache_size   18446744073709547520
max_binlog_size 1073741824
max_binlog_stmt_cache_size  18446744073709547520
max_connect_errors  100
max_connections 80
max_delayed_threads 20
max_digest_length   1024
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_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    175950
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  ON
performance_schema_accounts_size    100
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_waits_history_long_size   10000
performance_schema_events_waits_history_size    10
performance_schema_hosts_size   100
performance_schema_max_cond_classes 80
performance_schema_max_cond_instances   3420
performance_schema_max_digest_length    1024
performance_schema_max_file_classes 50
performance_schema_max_file_handles 32768
performance_schema_max_file_instances   7693
performance_schema_max_mutex_classes    200
performance_schema_max_mutex_instances  16480
performance_schema_max_rwlock_classes   40
performance_schema_max_rwlock_instances 9560
performance_schema_max_socket_classes   10
performance_schema_max_socket_instances 180
performance_schema_max_stage_classes    150
performance_schema_max_statement_classes    168
performance_schema_max_table_handles    3000
performance_schema_max_table_instances  12500
performance_schema_max_thread_classes   50
performance_schema_max_thread_instances 260
performance_schema_session_connect_attrs_size   512
performance_schema_setup_actors_size    100
performance_schema_setup_objects_size   100
performance_schema_users_size   100
pid_file    /var/db/mysql/rad-bccfa.pid
plugin_dir  /usr/local/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    588542
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    4194304
read_only   OFF
read_rnd_buffer_size    4194304
relay_log    
relay_log_basename   
relay_log_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  
rpl_stop_slave_timeout  31536000
secure_auth ON
secure_file_priv    /var/db/mysql_secure/
server_id   253051
server_id_bits  32
server_uuid 02376841-ea78-11e5-8675-00505601050e
sha256_password_private_key_path    private_key.pem
sha256_password_public_key_path public_key.pem
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   /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   1022,1050,1060,1061,1062,1146,1169
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/db/mysql/rad-bccfa-slow.log
socket  /tmp/mysql.sock
sort_buffer_size    2097152
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   
ssl_capath   
ssl_cert     
ssl_cipher   
ssl_crl  
ssl_crlpath  
ssl_key  
storage_engine  InnoDB
stored_program_cache    256
sync_binlog 0
sync_frm    ON
sync_master_info    10000
sync_relay_log  10000
sync_relay_log_info 10000
system_time_zone    PST
table_definition_cache  1500
table_open_cache    1500
table_open_cache_instances  1
thread_cache_size   8
thread_concurrency  10
thread_handling one-thread-per-connection
thread_stack    262144
time_format %H:%i:%s
time_zone   America/Vancouver
timed_mutexes   OFF
timestamp   1550099073.579494
tmp_table_size  16777216
tmpdir  /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.43-log
version_comment Source distribution
version_compile_machine amd64
version_compile_os  FreeBSD11.2
wait_timeout    28800
warning_count   0

答案1

分析...

观察结果:

  • 版本:5.6.43-log
  • 8 GB RAM
  • 正常运行时间 = 14 天 21:34:59
  • 您没有在 Windows 上运行。
  • 运行 64 位版本
  • 您似乎正在运行全部(或大部分)InnoDB。

更重要的问题:

唉,没有任何东西可以解决你的交换问题。无论如何,以下是我的评论:

设置 long_query_time=1 并打开 slowlog。您有一些慢查询,随着表变大,这些查询可能会变得更糟。(16% 的 SELECT 是表扫描。)

slave_skip_errors = 1022 1050 1060 1061 1062 1146 1169 -- 这是扫除很多地毯下的东西!

每个连接执行USE dbname30 次(每秒 14 次)?发生了什么?

增加thread_cache_size至 20。

由于您似乎没有使用 MyISAM,我想知道您为什么要这样做REPAIRs

是否有第三方软件SHOW TABLES每秒都在做这件事?这会产生一些开销。

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

( innodb_buffer_pool_size / _ram ) = 4096M / 8192M = 50.0%-- 用于 InnoDB 缓冲池的 RAM 百分比

( innodb_lru_scan_depth ) = 1,024 -- “InnoDB:page_cleaner:预期循环花费 1000ms...” 可以通过降低 lru_scan_depth 来修复

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 4,358,689 / 19741903 = 22.1%-- 必须到达磁盘的写入请求 -- 检查 innodb_buffer_pool_size

( Innodb_dblwr_pages_written/Innodb_pages_written ) = 0/4358689 = 0—— 看起来这些值应该相等?

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 2,416,485,376 / (1287299 / 3600) / 2 / 512M = 0.00629-- 比率 -- (见会议纪要)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,287,299 / 60 * 512M / 2416485376 = 4,766-- InnoDB 日志轮换之间的分钟数从 5.6.8 开始,可以动态更改;请确保也更改 my.cnf。--(轮换之间建议 60 分钟有点武断。)调整 innodb_log_file_size。(无法在 AWS 中更改。)

( innodb_flush_method ) = innodb_flush_method =-- InnoDB 应如何要求操作系统写入块。建议使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 来避免双重缓冲。(至少对于 Unix 而言。)有关 O_ALL_DIRECT 的注意事项,请参阅 chrischandler

( innodb_flush_neighbors ) = 1-- 将块写入磁盘时进行小幅优化。-- 对于 SSD 驱动器使用 0;对于 HDD 使用 1。

( innodb_io_capacity ) = 200-- 磁盘每秒的 I/O 操作数。慢速驱动器为 100;旋转驱动器为 200;SSD 为 1000-2000;乘以 RAID 因子。

( sync_binlog ) = 0-- 使用 1 来增加安全性,但会花费一些 I/O 成本 =1 可能会导致大量的“查询结束”;=0 可能会导致“binlog 处于不可能的位置”并在崩溃时丢失事务,但速度更快。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 是否记录所有死锁。-- 如果您受到死锁的困扰,请启用此功能。注意:如果您有大量死锁,这可能会将大量数据写入磁盘。

( max_connections ) = 80-- 最大连接数(线程)。影响各种分配。-- 如果 max_connections 太高,并且各种内存设置都很高,则可能会耗尽 RAM。

( local_infile ) = local_infile = ON -- local_infile = ON 存在潜在的安全问题

( Select_scan ) = 3,833,980 / 1287299 = 3 /sec-- 全表扫描 -- 添加索引/优化查询(除非它们是小表)

( Select_scan / Com_select ) = 3,833,980 / 23827649 = 16.1%-- 执行全表扫描的选择百分比。(可能会被存储例程欺骗。)-- 添加索引/优化查询

( binlog_error_action ) = binlog_error_action = IGNORE_ERROR-- 如果无法写入 binlog,该怎么办。-- IGNORE_ERROR 是默认的,用于向后兼容,但建议使用 ABORT_SERVER。

( binlog_format ) = binlog_format = MIXED—— STATEMENT/ROW/MIXED。ROW 是首选;它可能成为默认值。

( slave_skip_errors ) = slave_skip_errors = 1022 1050 1060 1061 1062 1146 1169-- 忽略哪些错误情况 -- 修改代码比将问题掩盖起来要好。

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

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

( Com_change_db / Connections ) = 17,594,185 / 586795 = 30-- 每个连接的数据库切换 -- (次要)考虑使用“db.table”语法

( Com_change_db ) = 17,594,185 / 1287299 = 14 /sec-- 可能来自 USE 语句。-- 考虑连接 DB、使用 db.tbl 语法、消除虚假的 USE 语句等。

( Threads_created / Connections ) = 16,681 / 586795 = 2.8%-- 流程创建速度

您已将查询缓存关闭一半。您应该同时设置 query_cache_type = OFF 和 query_cache_size = 0 。据传言,QC 代码中有一个“错误”,除非您关闭这两个设置,否则某些代码会一直处于打开状态。

异常大:

Com_check = 5.1 /HR
Com_create_procedure = 0.011 /HR
Com_create_trigger = 0.22 /HR
Com_drop_procedure = 0.022 /HR
Com_drop_trigger = 0.22 /HR
Com_repair = 0.0028 /HR
Com_show_tables = 0.98 /sec

异常字符串:

optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
version_compile_machine = amd64

答案2

每秒速率 = RPS - 针对 my.cnf [mysqld] 部分的建议

innodb_io_capacity=1800  # from 200 to allow higher IOPS
read_rnd_buffer_size=256K  # from 4M to reduce handler_read_rnd_next RPS of 2560
query_cache_size=0  # from 1M to conserve RAM and QC is OFF already
innodb_lru_scan_depth=100  # from 1024 to conserve CPU cycles every SECOND
thread_cache_size=32  # from 8 to reduce threads_created from 16,681 in 15 days (expensive operation) 

相关内容