Skip to content

Instantly share code, notes, and snippets.

@douglasjarquin
Created March 26, 2012 18:58
Show Gist options
  • Save douglasjarquin/2208690 to your computer and use it in GitHub Desktop.
Save douglasjarquin/2208690 to your computer and use it in GitHub Desktop.
Amazon RDS Performance Tuning Settings
rds-modify-db-parameter-group {param-group-name} \
--parameters="name=character_set_server, value=utf8, method=pending-reboot" \
--parameters="name=collation_server, value=utf8_general_ci, method=pending-reboot" \
--parameters="name=tmp_table_size, value={DBInstanceClassMemory/16}, method=pending-reboot" \
--parameters="name=max_heap_table_size, value={DBInstanceClassMemory/16}, method=pending-reboot" \
--parameters="name=query_cache_type, value=1, method=pending-reboot" \
--parameters="name=query_cache_size, value=131072, method=pending-reboot" \
--parameters="name=table_open_cache, value=2500, method=pending-reboot" \
--parameters="name=join_buffer_size, value={DBInstanceClassMemory/64}, method=pending-reboot" \
--parameters="name=thread_cache_size, value={DBInstanceClassMemory/12582880}, method=pending-reboot" \
--parameters="name=sort_buffer_size, value={DBInstanceClassMemory/64}, method=pending-reboot" \
--parameters="name=innodb_flush_log_at_trx_commit, value=0, method=pending-reboot" \
--parameters="name=innodb_additional_mem_pool_size, value={DBInstanceClassMemory/64}, method=pending-reboot" \
--parameters="name=innodb_max_dirty_pages_pct, value=90, method=pending-reboot" \
--parameters="name=max_allowed_packet, value=33554432, method=pending-reboot" \
--parameters="name=tx_isolation, value=READ-COMMITTED, method=pending-reboot"
@douglasjarquin
Copy link
Author

@douglasjarquin
Copy link
Author

m1.xlarge instances (the 64-bit/15GB you mention) get a NIC all to themselves and therefore have the best network (and by extension, EBS) performance. With striping you can sustain 1500 disk iops, as oppossed to the standard 100 iops.

When used in RDS, you will see highest iops by setting your storage size to at least 300GB, which triggers back-end striping.

If you need more RAM or CPU, only the c1.xlarge and m2.4xlarge instance types have the full NIC to themselves- they would also required 300GB stoarge allocation to take advantage of it.

Reference http://stackoverflow.com/questions/6986568/which-rds-instance-type-has-better-io-extra-large-db-instance-or-high-memory-ex.

@rgoytacaz
Copy link

I would also increase innodb_read/write_io_threads to 16.

And would mention that innodb_flush_log_at_trx_commit at 0 decreases data durability in case of power loss, that should be taken very seriously.

@JanC89
Copy link

JanC89 commented Feb 22, 2014

Is the parameter query_cache_size specified in bytes or in kilobytes? A google search have reported mixed results.

If it indeed is in bytes, then the value is to low.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment