Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Why do you need so many connections? –  ypercube Feb 26 '13 at 12:49

1 Answer 1

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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