-1

We configured the my.cnf of our server yesterday. Today comes to a high traffic on our site. The site is going slow as the traffic goes high, and for sometimes get a downtime. We already switch from MyISAM storage engine to InnoDB before. Here are the details on our my.cnf.

[mysqld]
default-storage-engine=InnoDB
query_cache_type=1
innodb_buffer_pool_size=868M
innodb_buffer_pool_instances=1
performance-schema=0
key_buffer_size=2M
max_connections=2000
slow_query_log= ON
innodb_file_per_table
innodb_open_files=1000
innodb_fast_shutdown=0
query_cache_size =50M

We have available 400MB memory, before restarting the server.

And here is the result using top in SSH with 600+ users after a graceful restart.

The server has available 4Gb memory. But still, the website is slow.

Is there anything we should consider in configuring our my.cnf?

UPDATE: SHOW GLOBAL STATUS

Aborted_clients 19
Aborted_connects    784
Binlog_cache_disk_use   0
Binlog_cache_use    0
Binlog_stmt_cache_disk_use  0
Binlog_stmt_cache_use   0
Bytes_received  281808990
Bytes_sent  3828623531
Com_admin_commands  12
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 0
Com_alter_tablespace    0
Com_alter_user  0
Com_analyze 0
Com_begin   0
Com_binlog  0
Com_call_procedure  0
Com_change_db   214618
Com_change_master   0
Com_check   28
Com_checksum    0
Com_commit  0
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    0
Com_create_trigger  0
Com_create_udf  0
Com_create_user 0
Com_create_view 0
Com_dealloc_sql 0
Com_delete  174
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  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_ha_close    0
Com_ha_open 0
Com_ha_read 0
Com_help    0
Com_insert  7736
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_rollback    0
Com_rollback_to_savepoint   0
Com_savepoint   0
Com_select  523567
Com_set_option  175679
Com_signal  0
Com_show_binlog_events  0
Com_show_binlogs    0
Com_show_charsets   0
Com_show_collations 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   111
Com_show_create_trigger 0
Com_show_databases  6
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 35
Com_show_function_code  0
Com_show_function_status    3
Com_show_grants 2
Com_show_keys   27
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   3
Com_show_processlist    345
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 285
Com_show_storage_engines    3
Com_show_table_status   30
Com_show_tables 12
Com_show_triggers   0
Com_show_variables  12
Com_show_warnings   0
Com_slave_start 0
Com_slave_stop  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_truncate    0
Com_uninstall_plugin    0
Com_unlock_tables   0
Com_update  104639
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 216533
Created_tmp_disk_tables 8814
Created_tmp_files   39
Created_tmp_tables  28116
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Handler_commit  592369
Handler_delete  173
Handler_discover    0
Handler_external_lock   1352192
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  9154
Handler_read_key    50340776
Handler_read_last   1262
Handler_read_next   535162039
Handler_read_prev   21301756
Handler_read_rnd    15634807
Handler_read_rnd_next   709087992
Handler_rollback    1
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  119993
Handler_write   30752233
Innodb_buffer_pool_dump_status  not started
Innodb_buffer_pool_load_status  not started
Innodb_buffer_pool_pages_data   46719
Innodb_buffer_pool_bytes_data   765444096
Innodb_buffer_pool_pages_dirty  36
Innodb_buffer_pool_bytes_dirty  589824
Innodb_buffer_pool_pages_flushed    365086
Innodb_buffer_pool_pages_free   78123
Innodb_buffer_pool_pages_misc   3157
Innodb_buffer_pool_pages_total  127999
Innodb_buffer_pool_read_ahead_rnd   0
Innodb_buffer_pool_read_ahead   7874
Innodb_buffer_pool_read_ahead_evicted   0
Innodb_buffer_pool_read_requests    1326800871
Innodb_buffer_pool_reads    37552
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   1056322
Innodb_data_fsyncs  179862
Innodb_data_pending_fsyncs  0
Innodb_data_pending_reads   0
Innodb_data_pending_writes  0
Innodb_data_read    754421760
Innodb_data_reads   46083
Innodb_data_writes  514361
Innodb_data_written 12084665856
Innodb_dblwr_pages_written  365086
Innodb_dblwr_writes 12008
Innodb_have_atomic_builtins ON
Innodb_log_waits    0
Innodb_log_write_requests   115719
Innodb_log_writes   124576
Innodb_os_log_fsyncs    135947
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   115743232
Innodb_page_size    16384
Innodb_pages_created    678
Innodb_pages_read   46041
Innodb_pages_written    365086
Innodb_row_lock_current_waits   0
Innodb_row_lock_time    0
Innodb_row_lock_time_avg    0
Innodb_row_lock_time_max    0
Innodb_row_lock_waits   0
Innodb_rows_deleted 173
Innodb_rows_inserted    7726
Innodb_rows_read    1260491510
Innodb_rows_updated 104897
Innodb_num_open_files   34
Innodb_truncated_status_writes  0
Innodb_available_undo_logs  128
Key_blocks_not_flushed  0
Key_blocks_unused   1612
Key_blocks_used 1674
Key_read_requests   85604696
Key_reads   44517
Key_write_requests  31877162
Key_writes  198650
Last_query_cost 0.000000
Last_query_partial_plans    0
Max_used_connections    19
Not_flushed_delayed_rows    0
Open_files  1100
Open_streams    0
Open_table_definitions  622
Open_tables 657
Opened_files    37448
Opened_table_definitions    622
Opened_tables   664
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  5581
Qcache_free_memory  18170232
Qcache_hits 855928
Qcache_inserts  431128
Qcache_lowmem_prunes    59940
Qcache_not_cached   92231
Qcache_queries_in_cache 12983
Qcache_total_blocks 32769
Queries 2099071
Questions   2098784
Select_full_join    0
Select_full_range_join  0
Select_range    6046
Select_range_check  0
Select_scan 13707
Slave_heartbeat_period  
Slave_last_heartbeat    
Slave_open_temp_tables  0
Slave_received_heartbeats   
Slave_retried_transactions  
Slave_running   OFF
Slow_launch_threads 0
Slow_queries    7
Sort_merge_passes   64
Sort_range  102701
Sort_rows   5793368
Sort_scan   24887
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   663374
Table_locks_waited  0
Table_open_cache_hits   667069
Table_open_cache_misses 664
Table_open_cache_overflows  0
Tc_log_max_pages_used   0
Tc_log_page_size    0
Tc_log_page_waits   0
Threads_cached  13
Threads_connected   6
Threads_created 19
Threads_running 1
Uptime  82693
Uptime_since_flush_status   82693

SHOW VARIABLES;

auto_increment_increment    1
auto_increment_offset   1
autocommit  ON
automatic_sp_privileges ON
avoid_temporal_upgrade  OFF
back_log    450
basedir /usr/
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   STATEMENT
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    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_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/lib/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    0
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/lib/mysql/xxx-xxx-xxx.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 703
hostname    xxx-xxx-xxx.secureserver.net
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    1
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 2097152000
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  ON
innodb_fast_shutdown    0
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  1
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    50331648
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
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   1000
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   ON
innodb_use_sys_malloc   ON
innodb_version  5.6.41
innodb_write_io_threads 4
insert_id   0
interactive_timeout 28800
join_buffer_size    262144
keep_files_on_create    OFF
key_buffer_size 2097152
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/share/mysql/
lc_time_names   en_US
license GPL
local_infile    ON
lock_wait_timeout   31536000
locked_in_memory    OFF
log_bin OFF
log_bin_basename    
log_bin_index   
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events   OFF
log_error   ./xxx-xxx-xxx.secureserver.net.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  4194304
max_binlog_cache_size   18446744073709547520
max_binlog_size 1073741824
max_binlog_stmt_cache_size  18446744073709547520
max_connect_errors  100
max_connections 2000
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    10000
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  OFF
performance_schema_accounts_size    -1
performance_schema_digests_size -1
performance_schema_events_stages_history_long_size  -1
performance_schema_events_stages_history_size   -1
performance_schema_events_statements_history_long_size  -1
performance_schema_events_statements_history_size   -1
performance_schema_events_waits_history_long_size   -1
performance_schema_events_waits_history_size    -1
performance_schema_hosts_size   -1
performance_schema_max_cond_classes 80
performance_schema_max_cond_instances   -1
performance_schema_max_digest_length    1024
performance_schema_max_file_classes 50
performance_schema_max_file_handles 32768
performance_schema_max_file_instances   -1
performance_schema_max_mutex_classes    200
performance_schema_max_mutex_instances  -1
performance_schema_max_rwlock_classes   40
performance_schema_max_rwlock_instances -1
performance_schema_max_socket_classes   10
performance_schema_max_socket_instances -1
performance_schema_max_stage_classes    150
performance_schema_max_statement_classes    168
performance_schema_max_table_handles    -1
performance_schema_max_table_instances  -1
performance_schema_max_thread_classes   50
performance_schema_max_thread_instances -1
performance_schema_session_connect_attrs_size   -1
performance_schema_setup_actors_size    100
performance_schema_setup_objects_size   100
performance_schema_users_size   -1
pid_file    /var/lib/mysql/xx-xxx-xxx.secureserver.net.pid
plugin_dir  /usr/lib64/mysql/plugin/
port    3306
preload_buffer_size 32768
profiling   ON
profiling_history_size  15
protocol_version    10
proxy_user  
pseudo_slave_mode   OFF
pseudo_thread_id    216678
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    52428800
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    131072
read_only   OFF
read_rnd_buffer_size    262144
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    
server_id   0
server_id_bits  32
server_uuid f4178a-374-11e7-09f-01813919
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   OFF
slave_sql_verify_checksum   ON
slave_transaction_retries   10
slave_type_conversions  
slow_launch_time    2
slow_query_log  ON
slow_query_log_file /var/lib/mysql/xxx.xxx.xxx-slow.log
socket  /var/lib/mysql/mysql.sock
sort_buffer_size    262144
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    +08
table_definition_cache  1400
table_open_cache    2000
table_open_cache_instances  1
thread_cache_size   28
thread_concurrency  10
thread_handling one-thread-per-connection
thread_stack    262144
time_format %H:%i:%s
time_zone   SYSTEM
timed_mutexes   OFF
timestamp   1539659217.500644
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.41-log
version_comment MySQL Community Server (GPL)
version_compile_machine x86_64
version_compile_os  Linux
wait_timeout    28800
warning_count   0
  • Neither memory nor cpu seems to be maxed out, so your app might be crappy or your network or IO, how should we know... – eckes Oct 3 '18 at 2:26
  • @eckes So there is nothing wrong with our my.cnf configuration? – xkev Oct 3 '18 at 2:53
  • There is not really a way to tell you what the right setting is for your scenario. You need to find out what is slow and then why. You can for sure increase the buffer cache more, but if it helps — who knows. – eckes Oct 3 '18 at 2:57
  • 1
    SHOW ENGINE INNODB STATUS / SHOW GLOBAL STATUS. On settings long_query_time should be changed from default to something like 1(second) or less otherwise your slow query log could be rather limited. – danblack Oct 3 '18 at 9:24
  • what does 'Active Users` mean for DB load? How does it related. – danblack Oct 3 '18 at 9:25
0

Well, it does not seem like a very heavily used server. So, I think that we need to see the slow queries in order to address the "slow down".

*Observations:**

  • Version: 5.6.41-log
  • 4 GB of RAM
  • Uptime = 22:58:13; some GLOBAL STATUS values may not be meaningful yet.
  • Not Windows.
  • Running 64-bit version
  • It appears that you are running both MyISAM and InnoDB.

The More Important Issues:

Suggest you conver the rest of your MyISAM tables to InnoDB. After that, retune the main memory settings using Conversion from MyISAM to InnoDB and Re-tuning

Decrease max_connections to 500

Increase key_buffer_size to 40M

Details and other observations:

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

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (2M / 0.20 + 2000M / 0.70) / 4096M = 70.0% -- Most of available ram should be made available for caching.

( innodb_buffer_pool_size ) = 2000M -- InnoDB Data + Index cache

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 78,123 / 127999 = 61.0% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size is bigger than necessary?

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 365,086 / 1056322 = 34.6% -- Write requests that had to hit disk -- Check innodb_buffer_pool_size

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 765,444,096 / 2000M = 36.5% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 115,743,232 / (82693 / 3600) / 2 / 48M = 0.0501 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 82,693 / 60 * 48M / 115743232 = 599 -- 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.

( 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 ) = 2,000 -- 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

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

( Connections ) = 216,533 / 82693 = 2.6 /sec -- Connections -- Increase wait_timeout; use pooling?

( thread_cache_size / max_connections ) = 28 / 2000 = 1.4%

Abnormally large:

Com_check = 1.2 /HR
Key_write_requests = 385 /sec
back_log = 450
-1

Can you add more details, including which version of MySQL you have and maybe rest of the config?

I can see that your innodb buffer pool is set to just about 868MB even though you have a total of 8GB of memory. If you are only using innodb then about 70-80% of total physical memory is the general rule of thumb, but you might want to go for about 50% since you also seem to have a php server on the same machine.

Also, do you see any errors in the error log?

  • 1
    Welcome to DBA.SE. We appreciate your contribution. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) Ask for clarification to the question in a comment underneath the question instead of in the answer itself (Comment hint: Use comments to ask for more information or suggest improvements. Avoid answering questions in comments.). Questions in your answer give it an impression of incompleteness. (i.e. not really an answer) – John aka hot2use Oct 3 '18 at 5:59
  • I don't have the reputation to add comments to questions. I can build it if you let me answer them. – Hamza Saleem Ahmed Oct 3 '18 at 16:53
  • @HamzaSaleemAhmed MySQL version is 5.6.41 any other configurations are set to default. In error_logs there are just notice on the code like unindexed variables. – xkev Oct 4 '18 at 1:14
  • @xkev If your tables are InnoDB based (you can check using SHOW CREATE TABLE for individual tables or using the information_schema.tables table for all tables). If they are innodb start with increasing your innodb_buffer_pool size to at least 2GB and see if that improves performance. – Hamza Saleem Ahmed Oct 4 '18 at 16:11
  • @HamzaSaleemAhmed Yes, all of the tables are InnoDB type. I already increased the innodb_buffer_pool_size to 2GB but I can't say that the performance improved because the traffic on our site now deceased. – xkev Oct 5 '18 at 2:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.