FreeBSD swap usage with mysql

Question

For the past few months, on a few different servers with similar FreeBSD versions and specs, I've been experiencing high swap usage with MySQL while seemingly having plenty of free RAM.

• FreeBSD 11.2-RELEASE-p8 (Also was happening in p2)
• mysql-server 5.6.43 (Also was happening in 5.6.40)
• Hosted in vmware vcloud.
• MySQL datadir is in a zvol, with vfs.zfs.arc_max="512M"
• 4 vcpu and 8GB RAM.

It doesn't use swap immediately. It takes a couple weeks of incrementally using more swap until none remains. This particular server hasn't reached max swap yet, but it's getting there.

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 

Eventually out of swap messages start hitting /var/log/messages every 10 or so minutes.

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 

What can cause mysql to repeatedly allocate swap while having plenty of free memory?

Edit: Output of 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 

Answer 1

Analysis...

Observations:

• Version: 5.6.43-log
• 8 GB of RAM
• Uptime = 14d 21:34:59
• You are not running on Windows.
• Running 64-bit version
• You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Alas, nothing jumped out as the answer to your swapping problem. Anyway, here's my comments:

Set long_query_time=1 and turn on the slowlog. You have some slow queries that are likely to get worse as the tables get bigger. (16% of SELECTs are table scans.)

slave_skip_errors = 1022 1050 1060 1061 1062 1146 1169 -- That's sweeping a lot of things under the rug!

Doing USE dbname 30 times per connection (14 times per second)? What's going on?

Increase thread_cache_size to 20.

Since you don't seem to be using MyISAM, I wonder why you are doing any REPAIRs.

Is some 3rd party software doing SHOW TABLES every second? There is a bit of overhead in this.

Details and other observations:

( innodb_buffer_pool_size / _ram ) = 4096M / 8192M = 50.0% -- % of RAM used for InnoDB buffer_pool

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 4,358,689 / 19741903 = 22.1% -- Write requests that had to hit disk -- Check innodb_buffer_pool_size

( Innodb_dblwr_pages_written/Innodb_pages_written ) = 0/4358689 = 0 -- Seems like these values should be equal?

( 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 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,287,299 / 60 * 512M / 2416485376 = 4,766 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( innodb_flush_method ) = innodb_flush_method = -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_connections ) = 80 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections is too high and various memory settings are high, you could run out of RAM.

( local_infile ) = local_infile = ON -- local_infile = ON is a potential security issue

( Select_scan ) = 3,833,980 / 1287299 = 3 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 3,833,980 / 23827649 = 16.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( binlog_error_action ) = binlog_error_action = IGNORE_ERROR -- What to do if the binlog cannot be written. -- IGNORE_ERROR is the default for backwards compatibility, but ABORT_SERVER is recommended.

( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( slave_skip_errors ) = slave_skip_errors = 1022 1050 1060 1061 1062 1146 1169 -- Which error cases to ignore -- It would be better to change the code than sweep problems under the rug.

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( Com_change_db / Connections ) = 17,594,185 / 586795 = 30 -- Database switches per connection -- (minor) Consider using "db.table" syntax

( Com_change_db ) = 17,594,185 / 1287299 = 14 /sec -- Probably comes from USE statements. -- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.

( Threads_created / Connections ) = 16,681 / 586795 = 2.8% -- Rapidity of process creation

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Abnormally large:

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 

Abnormal strings:

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 

Answer 2

Rate Per Second=RPS - Suggestions to consider for your my.cnf [mysqld] section

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)