1

My replicator MySQL db, hosted by Amazon-RDS-Aurora is performing poorly. The DB is quite powerful so performance shouldn't be an issue.

When I executed

SHOW VARIABLES;

I could not find INNODB_BUFFER_POOL_SIZE. How could this be? I suspect this parameter is related to the poor performance.

Here are my config variables:

  • Variable_name Value
  • aurora_lab_mode OFF
  • aurora_load_from_s3_role
  • aurora_max_alter_table_log_entries 1000
  • aurora_max_connections_limit 16000
  • aurora_select_into_s3_role
  • aurora_server_id tableau
  • aurora_use_key_prefetch ON
  • aurora_version 1.17.2
  • auto_increment_increment 1
  • auto_increment_offset 1
  • autocommit ON
  • automatic_sp_privileges ON
  • aws_default_lambda_role
  • aws_default_s3_role
  • awsauthenticationplugin_max_backoff_delay 2000
  • awsauthenticationplugin_max_retry_count 3
  • awsauthenticationplugin_retry_delay 250
  • back_log 650
  • basedir /rdsdbbin/oscar/
  • big_tables OFF
  • bind_address *
  • binlog_cache_size 32768
  • binlog_checksum CRC32
  • binlog_direct_non_transactional_updates OFF
  • binlog_format STATEMENT
  • 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
  • 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 *
  • 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 ON
  • 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 OFF
  • 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 128
  • hostname ip-10-1-1-83
  • identity 0
  • ignore_builtin_innodb OFF
  • ignore_db_dirs
  • ignore_default_storage_engine_errors OFF
  • init_connect
  • init_file
  • init_slave
  • innodb_adaptive_flushing ON
  • innodb_adaptive_flushing_lwm 10
  • innodb_adaptive_hash_index OFF
  • 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_aurora_enable_auto_akp OFF
  • 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 8
  • innodb_buffer_pool_load_abort OFF
  • innodb_buffer_pool_load_at_startup OFF
  • innodb_buffer_pool_load_now OFF
  • innodb_change_buffer_max_size 25
  • innodb_change_buffering none
  • innodb_checksum_algorithm none
  • innodb_checksums OFF
  • 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 1
  • 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 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_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 6000
  • innodb_optimize_fulltext_only OFF
  • innodb_page_size 16384
  • innodb_print_all_deadlocks OFF
  • innodb_purge_batch_size 900
  • innodb_purge_threads 3
  • innodb_random_read_ahead OFF
  • innodb_read_ahead_threshold 56
  • innodb_read_io_threads 16
  • innodb_read_only OFF
  • innodb_replication_delay 0
  • innodb_rollback_on_timeout OFF
  • innodb_rollback_segments 128
  • innodb_shared_buffer_pool_uses_huge_pages ON
  • 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_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_undo_directory .
  • innodb_undo_logs 128
  • innodb_undo_tablespaces 0
  • innodb_use_native_aio OFF
  • innodb_use_sys_malloc ON
  • innodb_version 1.2.10
  • innodb_write_io_threads 4
  • insert_id 0
  • interactive_timeout 28800
  • join_buffer_size 262144
  • keep_files_on_create OFF
  • key_buffer_size 16777216
  • 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/oscar-5.6.10a.200116.0/share/
  • 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 /rdsdbdata/log/error/mysql-error.log
  • log_output TABLE
  • log_queries_not_using_indexes OFF
  • log_slave_updates ON
  • 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 4194304
  • max_binlog_cache_size 1.84467E+19
  • max_binlog_size 134217728
  • max_binlog_stmt_cache_size 1.84467E+19
  • max_connect_errors 100
  • max_connections 3000
  • 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 1.84467E+19
  • max_length_for_sort_data 1024
  • max_prepared_stmt_count 16382
  • max_relay_log_size 0
  • max_seeks_for_key 1.84467E+19
  • max_sort_length 1024
  • max_sp_recursion_depth 0
  • max_tmp_tables 32
  • max_user_connections 0
  • max_write_lock_count 1.84467E+19
  • metadata_locks_cache_size 1024
  • min_examined_row_limit 0
  • multi_range_count 256
  • myisam_data_pointer_size 6
  • myisam_max_sort_file_size 9.22337E+18
  • myisam_mmap_size 1.84467E+19
  • 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,hash_join=on,hash_join_cost_based=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__auto__ 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 52160
  • performance_schema_max_digest_length 1024
  • performance_schema_max_file_classes 50
  • performance_schema_max_file_handles 32768
  • performance_schema_max_file_instances 92616
  • performance_schema_max_mutex_classes 200
  • performance_schema_max_mutex_instances 319090
  • performance_schema_max_rwlock_classes 40
  • performance_schema_max_rwlock_instances 126430
  • performance_schema_max_socket_classes 10
  • performance_schema_max_socket_instances 6050
  • performance_schema_max_stage_classes 150
  • performance_schema_max_statement_classes 177
  • performance_schema_max_table_handles 12000
  • performance_schema_max_table_instances 25000
  • performance_schema_max_thread_classes 50
  • performance_schema_max_thread_instances 6130
  • 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 /rdsdbdata/log/mysql-3306.pid
  • plugin_dir /rdsdbbin/oscar-5.6.10a.200116.0/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 46502
  • query_alloc_block_size 8192
  • query_cache_limit 1048576
  • query_cache_min_res_unit 4096
  • query_cache_size 2459388928
  • 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 262144
  • 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 1000000000
  • report_host
  • report_password
  • report_port 3306
  • report_user
  • require_fk_checks_for_ddl OFF
  • rpl_stop_slave_timeout 31536000
  • secure_auth ON
  • secure_file_priv /tmp/
  • server_audit_cw_upload OFF
  • server_audit_events
  • server_audit_excl_users
  • server_audit_incl_users
  • server_audit_logging OFF
  • server_audit_mode 0
  • server_audit_query_log_limit 1024
  • server_id *
  • server_id_bits 32
  • server_uuid *
  • sha256_password_private_key_path private_key.pem
  • sha256_password_public_key_path public_key.pem
  • skip_external_locking ON
  • skip_name_resolve ON
  • 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 262144
  • sql_auto_is_null OFF
  • sql_big_selects ON
  • sql_buffer_result OFF
  • sql_log_bin ON
  • sql_log_off OFF
  • sql_mode
  • sql_notes ON
  • sql_quote_show_create ON
  • sql_safe_updates OFF
  • sql_select_limit 1.84467E+19
  • sql_slave_skip_counter 0
  • sql_warnings OFF
  • ssl_ca /rdsdbdata/rds-metadata/ca-cert.pem
  • ssl_capath
  • ssl_cert /rdsdbdata/rds-metadata/server-cert.pem
  • ssl_cipher *
  • ssl_crl
  • ssl_crlpath
  • ssl_key /rdsdbdata/rds-metadata/server-key.pem
  • 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 20000
  • table_open_cache 6000
  • table_open_cache_instances 16
  • thread_cache_size 58
  • thread_concurrency 10
  • thread_handling multiple-connections-per-thread
  • thread_stack 262144
  • time_format %H:%i:%s
  • time_zone SYSTEM
  • timed_mutexes OFF
  • timestamp 1531841565
  • tmp_table_size 16777216
  • 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
  • user_disable_external_log OFF
  • version 5.6.10
  • version_comment MySQL Community Server (GPL)
  • version_compile_machine x86_64
  • version_compile_os Linux
  • wait_timeout 28800
  • warning_count 0

  • Unexpected. I also see innodb_version 1.2.10. Raise the question with Amazon support. – Rick James Jul 22 '18 at 18:17
1

It's good that innodb_buffer_pool_size is not there.

Even if you could manipulate its value, best practices for Aurora says you should leave it alone. Please notice what the Best Practices Documentation says under the subheading Default parameter values and their importance:

Certain DB instance parameters contain variables or formulas, in which the value is determined by constants. Examples are the instance’s size and memory footprint, network port for the instance, and its allocated storage. It’s best to leave these unchanged, as they adjust automatically whenever an instance scale-up or down operation is performed.

For example, Aurora DB parameter innodb_buffer_pool_size defaults to:

{DBInstanceClassMemory*3/4}

DBInstanceClassMemory is a variable that is set to your instance’s memory size in GiB.

Example: For a db.r4.xlarge instance with 30.5 GiB of memory, this value is 20,090,716,160 bytes or 18.71 GiB.

Suppose that we decide to set this parameter to a fixed value, say to 18,000,000,000 bytes, and later we run a scale-down operation to db.r4.large, which has half as much memory (15.2 GiB). In this case, we will likely then encounter an out-of-memory condition on the database engine after modifying it, and the instance can’t properly start.

Since MySQL 5.7 allows a user with SUPER privilege to dynamically resize the Buffer Pool, there is no point to setting it to a fixed value. Just let Amazon deal with it. This gives Aurora the edge over RDS with a Buffer Pool that scale with memory supply-and-demand.

I am aware the question is tagged with mysql-5.6. This means the InnoDB Buffer Pool cannot be resized dynamically (requires a restart of the instance to take effect). The principle I mentioned for MySQL 5.7 still applies so that changing innodb_buffer_pool_size would amount to changing the Instance Class. Memory needs would still be met without endangering the Instance to OOM conditions.

0

Try:

SHOW GLOBAL VARIABLES like 'INNODB_BUFFER%';

Another way is to select the variable:

select @@INNODB_BUFFER_POOL_SIZE;
  • @ColinMac How much RAM is on the replication server? Suggestion to consider for my.cnf [mysqld] section innodb_lru_scan_depth=128 # from 1024 to conserve CPU every second and max_connections=reasonable limit # from 3000 to conserve RAM. SHOW GLOBAL STATUS LIKE 'max_used_connections' to get an idea of a more reasonable number. Please view my profile, Network Profile for contact info and to get FREE My Top 10 Global Variable Suggestions for your specific server. – Wilson Hauck Jul 23 '18 at 18:20

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.