Skip to content

Instantly share code, notes, and snippets.

@jonathanvx
Created October 12, 2017 19:21
Show Gist options
  • Save jonathanvx/5ca16fbb493369e1e483eca1ab9d5fcd to your computer and use it in GitHub Desktop.
Save jonathanvx/5ca16fbb493369e1e483eca1ab9d5fcd to your computer and use it in GitHub Desktop.
RDS generic performance tuning
#!/bin/bash
cmd='aws rds modify-db-parameter-group --db-parameter-group-name mysql57-performance'
$cmd --parameters="ParameterName=tmp_table_size, ParameterValue=67108864, ApplyMethod=pending-reboot"
$cmd --parameters="ParameterName=max_heap_table_size, ParameterValue=67108864, ApplyMethod=pending-reboot"
#Max size a tmp table can be in memory
$cmd --parameters="ParameterName=join_buffer_size, ParameterValue=2097152, ApplyMethod=pending-reboot"
#Helps when a join doesn’t have an index or an index wont be more helpful than a full table scan
$cmd --parameters="ParameterName=sort_buffer_size, ParameterValue=2097152, ApplyMethod=pending-reboot"
#Helps with ORDER BY queries.
$cmd --parameters="ParameterName=read_buffer_size, ParameterValue=1048576, ApplyMethod=pending-reboot"
#Helps with reading temp tables faster, bulk inserts and nested queries
$cmd --parameters="ParameterName=read_rnd_buffer_size , ParameterValue=2097152, ApplyMethod=pending-reboot"
#Helps with multi range queries and ORDER Bys.
$cmd --parameters="ParameterName=key_buffer_size, ParameterValue=16777216, ApplyMethod=pending-reboot"
#This variable helps with MyISAM temp tables. Current default on Prod.
#$cmd --parameters="ParameterName=innodb_purge_threads, ParameterValue=2, ApplyMethod=pending-reboot"
#May reduce locks for when updates/deletes are run often
$cmd --parameters="ParameterName=innodb_page_cleaners, ParameterValue=4, ApplyMethod=pending-reboot"
#Will help with start up and shutdown
$cmd --parameters="ParameterName=log_output, ParameterValue=FILE, ApplyMethod=pending-reboot"
#Changes the logs to write to files instead of a table in MySQL
$cmd --parameters="ParameterName=long_query_time, ParameterValue=0.15, ApplyMethod=pending-reboot"
#Slow queries over 2 seconds will be written to the slow log
$cmd --parameters="ParameterName=min_examined_row_limit, ParameterValue=1, ApplyMethod=pending-reboot"
#Only queries that read at least 1 row will be written to the slow logs. Helps with too many writes to the slow log.
$cmd --parameters="ParameterName=innodb_log_file_size, ParameterValue=1073741824, ApplyMethod=pending-reboot"
$cmd --parameters="ParameterName=innodb_log_buffer_size, ParameterValue=134217728, ApplyMethod=pending-reboot"
$cmd --parameters="ParameterName=binlog_cache_size, ParameterValue=131072, ApplyMethod=pending-reboot"
$cmd --parameters="ParameterName=binlog_stmt_cache_size, ParameterValue=1048576, ApplyMethod=pending-reboot"
#May speed up transactions and long writes to disk
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment