Not the answer you need?
Register and ask your own question!

High CPU utilization and can't figure out why

bjohnstonokcbjohnstonokc EntrantInactive User Role Beginner
New to this forum but wanted to run this by you guys and see if you see something I am missing. I have been charged with administering our Ecommerce site that utilizes MySQL on RDS as it's backend. I am fairly new to MySQL and have been up and down the config file and can't find any other config that may help. The issue is we have an average of 3-5 K users on the site that generates on average 500-700+ connection threads to the DB, at the peak times the CPU on the DB instance pushes 90+%. I think something is misconfigured that is causing the CPU spikes. The DB instance specs are below and attached are the configs on the MySQL instance. Any guidance would be appreciated, even if it's 'That's normal'

Amazon RDS 4xLarge instance
16 vcpu
122 GB RAM
MySQL 5.6
75 GB DB

Many thanks in advance!!!



Variable_name
Value


auto_increment_increment
1


auto_increment_offset
1


autocommit
ON


automatic_sp_privileges
ON


avoid_temporal_upgrade
OFF


back_log
900


basedir
/rdsdbbin/mysql/


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
utf8


character_set_connection
utf8


character_set_database
utf8


character_set_filesystem
binary


character_set_results
utf8


character_set_server
utf8


character_set_system
utf8


character_sets_dir
/rdsdbbin/mysql-5.6.29.R1/share/charsets/


collation_connection
utf8_general_ci


collation_database
utf8_general_ci


collation_server
utf8_general_ci


completion_type
NO_CHAIN


concurrent_insert
AUTO


connect_timeout
10


core_file
OFF


datadir
/rdsdbdata/db/


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
ON


expire_logs_days
0


explicit_defaults_for_timestamp
ON


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
/rdsdbdata/log/general/mysql-general.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
YES


have_profiling
YES


have_query_cache
YES


have_rtree_keys
YES


have_ssl
YES


have_symlink
YES


host_cache_size
1114


hostname
ip-10-3-1-143


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
5


innodb_buffer_pool_load_abort
OFF


innodb_buffer_pool_load_at_startup
OFF


innodb_buffer_pool_load_now
OFF


innodb_buffer_pool_size
96545538048


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
/rdsdbdata/db/innodb


innodb_disable_sort_file_cache
OFF


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
ON


innodb_flush_log_at_timeout
1


innodb_flush_log_at_trx_commit
0


innodb_flush_method
O_DIRECT


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
2000


innodb_io_capacity_max
4000


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
134217728


innodb_log_files_in_group
2


innodb_log_group_home_dir
/rdsdbdata/log/innodb


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
27647


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
64


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_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.29


innodb_write_io_threads
64


insert_id
0


interactive_timeout
28800


join_buffer_size
1048576


keep_files_on_create
OFF


key_buffer_size
3350528


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
/rdsdbbin/mysql-5.6.29.R1/share/


lc_time_names
en_US


license
GPL


local_infile
ON


lock_wait_timeout
31536000


locked_in_memory
OFF


log_bin
ON


log_bin_basename
/rdsdbdata/log/binlog/mysql-bin-changelog


log_bin_index
/rdsdbdata/log/binlog/mysql-bin-changelog.index


log_bin_trust_function_creators
OFF


log_bin_use_v1_row_events
OFF


log_error
/rdsdbdata/log/error/mysql-error.log


log_output
TABLE


log_queries_not_using_indexes
OFF


log_slave_updates
ON


log_slow_admin_statements
OFF


log_slow_slave_statements
OFF


log_throttle_queries_not_using_indexes
0


log_warnings
1


long_query_time
10


low_priority_updates
OFF


lower_case_file_system
OFF


lower_case_table_names
0


master_info_repository
TABLE


master_verify_checksum
OFF


max_allowed_packet
16777216


max_binlog_cache_size
18446700000000000000


max_binlog_size
134217728


max_binlog_stmt_cache_size
18446700000000000000


max_connect_errors
1000000


max_connections
10230


max_delayed_threads
20


max_digest_length
1024


max_error_count
64


max_heap_table_size
3353600


max_insert_delayed_threads
20


max_join_size
18446700000000000000


max_length_for_sort_data
1024


max_prepared_stmt_count
16382


max_relay_log_size
0


max_seeks_for_key
18446700000000000000


max_sort_length
1024


max_sp_recursion_depth
0


max_tmp_tables
32


max_user_connections
0


max_write_lock_count
18446700000000000000


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
9223370000000000000


myisam_mmap_size
18446700000000000000


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
65535


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
/rdsdbdata/log/mysql-3306.pid


plugin_dir
/rdsdbbin/mysql-5.6.29.R1/lib/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
7640720


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
1048576


read_only
OFF


read_rnd_buffer_size
524288


relay_log
/rdsdbdata/log/relaylog/relaylog


relay_log_basename
/rdsdbdata/log/relaylog/relaylog


relay_log_index
/rdsdbdata/log/relaylog/relaylog.index


relay_log_info_file
relay-log.info


relay_log_info_repository
TABLE


relay_log_purge
ON


relay_log_recovery
ON


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
/tmp/


server_id
273721596


server_id_bits
32


server_uuid
e95c3806-2c68-11e4-a9c7-0212ef3c339e


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
/rdsdbdata/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
OFF


slow_query_log_file
/rdsdbdata/log/slowquery/mysql-slowquery.log


socket
/tmp/mysql.sock


sort_buffer_size
4194304


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
ON


sql_select_limit
18446700000000000000


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
1


sync_frm
ON


sync_master_info
10000


sync_relay_log
10000


sync_relay_log_info
10000


system_time_zone
UTC


table_definition_cache
12336


table_open_cache
27647


table_open_cache_instances
16


thread_cache_size
2300


thread_concurrency
10


thread_handling
one-thread-per-connection


thread_stack
262144


time_format
%H:%i:%s


time_zone
UTC


timed_mutexes
OFF


timestamp
1480370987


tmp_table_size
3354432


tmpdir
/rdsdbdata/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.29-log


version_comment
MySQL Community Server (GPL)


version_compile_machine
x86_64


version_compile_os
Linux


wait_timeout
28800


warning_count
0

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.