I’m trying to optimize a MySQL Server to be able to serve as many connections as it can.

The server is in AmazonAWS RDS and has currently the following resources:

–7.5 GB memory, 4 ECUs (2 virtual cores with 2 ECUs each), 64-bit platform, High I/O Capacity

I have run some stress test to check how many connections can serve but no matter what changes I make to the configuration, mysql can not serve more than 800. The rest of the processes are dropped.
If someone can help I would be grateful.

These are the variables in the configuration:

connect_timeout=10
default_storage_engine=InnoDB
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_additional_mem_pool_size=2097152
innodb_autoextend_increment=8
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=1
innodb_buffer_pool_size=5882511360
innodb_change_buffering=all
innodb_checksums=ON
innodb_commit_concurrency=0
innodb_concurrency_tickets=500
innodb_data_file_path=ibdata1:10M:autoextend
innodb_data_home_dir=/rdsdbdata/db/innodb
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_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_force_load_corrupted=OFF
innodb_force_recovery=0
innodb_io_capacity=200
innodb_large_prefix=OFF
innodb_locks_unsafe_for_binlog=OFF
innodb_lock_wait_timeout=50
innodb_log_buffer_size=8388608
innodb_log_files_in_group=2
innodb_log_file_size=134217728
innodb_log_group_home_dir=/rdsdbdata/log/innodb
innodb_max_dirty_pages_pct=75
innodb_max_purge_lag=0
innodb_mirrored_log_groups=1
innodb_old_blocks_pct=37
innodb_old_blocks_time=0
innodb_open_files=300
innodb_purge_batch_size=20
innodb_purge_threads=0
innodb_random_read_ahead=OFF
innodb_read_ahead_threshold=56
innodb_read_io_threads=4
innodb_replication_delay=0
innodb_rollback_on_timeout=OFF
innodb_rollback_segments=128
innodb_spin_wait_delay=6
innodb_stats_method=nulls_equal
innodb_stats_on_metadata=ON
innodb_stats_sample_pages=8
innodb_strict_mode=OFF
innodb_support_xa=ON
innodb_sync_spin_loops=30
innodb_table_locks=ON
innodb_thread_concurrency=0
innodb_thread_sleep_delay=10000
innodb_use_native_aio=ON
innodb_use_sys_malloc=ON
innodb_version=1.1.8
innodb_write_io_threads=4
lock_wait_timeout=31536000
lower_case_table_names=1
low_priority_updates=OFF
max_allowed_packet=16777216
max_binlog_cache_size=18446744073709547520
max_binlog_size=134217728
max_binlog_stmt_cache_size=18446744073709547520
max_connections=2000
max_connect_errors=10
max_delayed_threads=20
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_long_data_size=16777216
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
min_examined_row_limit=0
multi_range_count=256
open_files_limit=65535
range_alloc_block_size=4096
read_buffer_size=262144
read_only=OFF
read_rnd_buffer_size=524288
skip_external_locking=ON
skip_name_resolve=OFF
skip_networking=OFF
skip_show_database=OFF
sort_buffer_size=2097152
storage_engine=InnoDB
stored_program_cache=256
sync_binlog=0
sync_frm=ON
sync_master_info=0
sync_relay_log=0
sync_relay_log_info=0
table_definition_cache=400
table_open_cache=2048
thread_cache_size=10240
thread_concurrency=10
thread_handling=one-thread-per-connection
thread_stack=262144
tmp_table_size=16777216
transaction_alloc_block_size=8192
transaction_prealloc_size=4096
wait_timeout=28800
warning_count=0

Back on October 12, 2012, I wrote this post : When should I think about upgrading our RDS MySQL instance based on memory usage?

I gave the following chart

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

These were the default settings based on server model. I don’t know if things have changed since my October post. Just from the looks of the chart, unless you have server model m2-2xlarge, I would expect some performance tuning issues to be a little wonky since you have max_connections set at 2000.

SUGGESTIONS

• Upgrade to server model m2-2xlarge or m2-4xlarge (more connections allowed)
• Get away from RDS and use MySQL in Amazon EC2 (more tuning independence)
• Tune read_buffer_size, sort_buffer_size, join_buffer_size (not much wiggle room now)