December 11, 2012 at 13:02AWS Insight: RDS Parameter Groups - What and Why
Hello! 
Today I will talk about the parameter groups AWS RDS . As you know, RDS provides us with a simple entry point to the database. Neither the disk, nor the OS itself and the database settings, we have access. We are also limited by the rights of some superuser, but this is not root anyway.
RDS Parameter Groups allow you to create the desired configuration of your database. Today I’ll show an example of how to enable the possibility of creating stored procedures in RDS under MySQL. When creating the server, the default parameter group is created. In it, you can also see a list of available parameters:
As a matter of fact, these parameters can be changed, thereby tuning and adjusting the config of your database.
Let's move on to the example of creating and configuring a group parameter:
1. Create an extended-mysql group parameter
2. Change the parameter log_bin_trust_function_creators
That's all. This group option will enable you to create functions that will be stored in RDS.
3. Add the server to the group parameter
4. Reboot the server so that the settings apply
Do you use the group option for RDS? What rake faced when working with them? It is very interesting to hear something from someone else's experience!
Today I will talk about the parameter groups AWS RDS . As you know, RDS provides us with a simple entry point to the database. Neither the disk, nor the OS itself and the database settings, we have access. We are also limited by the rights of some superuser, but this is not root anyway.
RDS Parameter Groups allow you to create the desired configuration of your database. Today I’ll show an example of how to enable the possibility of creating stored procedures in RDS under MySQL. When creating the server, the default parameter group is created. In it, you can also see a list of available parameters:
$ rds-describe-db-parameters default.mysql5.1
Team output
DBPARAMETER allow-suspicious-udfs engine-default boolean static false
DBPARAMETER auto_increment_increment engine-default integer dynamic true
DBPARAMETER auto_increment_offset engine-default integer dynamic true
DBPARAMETER automatic_sp_privileges engine-default boolean dynamic true
DBPARAMETER back_log engine-default integer static true
DBPARAMETER basedir / rdsdbbin / mysql system string static false
DBPARAMETER binlog_cache_size 32768 system integer dynamic true
DBPARAMETER binlog_format MIXED system string dynamic false
DBPARAMETER bulk_insert_buffer_size engine-default integer dynamic true
DBPARAMETER character-set-client-handshake engine-default boolean static true
DBPARAMETER character_set_client engine-default string dynamic true
DBPARAMETER character_set_connection engine-default string dynamic true
DBPARAMETER character_set_database engine-default string dynamic true
DBPARAMETER character_set_filesystem engine-default string dynamic true
DBPARAMETER character_set_results engine-default string dynamic true
DBPARAMETER character_set_server engine-default string dynamic true
DBPARAMETER collation_connection engine-default string dynamic true
DBPARAMETER collation_server engine-default string dynamic true
DBPARAMETER completion_type engine-default integer dynamic true
DBPARAMETER concurrent_insert engine-default integer dynamic true
DBPARAMETER connect_timeout engine-default integer dynamic true
DBPARAMETER datadir / rdsdbdata / db / system string static false
DBPARAMETER default_storage_engine InnoDB system string dynamic false
DBPARAMETER default_time_zone engine-default string static false
DBPARAMETER default_week_format engine-default integer dynamic true
DBPARAMETER delay_key_write engine-default string dynamic true
DBPARAMETER delayed_insert_limit engine-default integer dynamic true
DBPARAMETER delayed_insert_timeout engine-default integer dynamic true
DBPARAMETER delayed_queue_size engine-default integer dynamic true
DBPARAMETER div_precision_increment engine-default integer dynamic true
DBPARAMETER event_scheduler engine-default string dynamic true
DBPARAMETER flush engine-default boolean dynamic false
DBPARAMETER flush_time engine-default integer dynamic true
DBPARAMETER ft_boolean_syntax engine-default string dynamic false
DBPARAMETER ft_max_word_len engine-default integer static true
DBPARAMETER ft_min_word_len engine-default integer static true
DBPARAMETER ft_query_expansion_limit engine-default integer static true
DBPARAMETER ft_stopword_file engine-default string static true
DBPARAMETER general_log engine-default boolean dynamic true
DBPARAMETER group_concat_max_len engine-default integer dynamic true
DBPARAMETER ignore_builtin_innodb 1 system boolean static false 5.1.49
DBPARAMETER init_connect engine-default string dynamic true
DBPARAMETER innodb_adaptive_flushing engine-default boolean dynamic true 5.1.49
DBPARAMETER innodb_adaptive_hash_index engine-default boolean static true
DBPARAMETER innodb_additional_mem_pool_size 2097152 system integer static true
DBPARAMETER innodb_autoextend_increment engine-default integer dynamic true
DBPARAMETER innodb_autoinc_lock_mode engine-default integer static true
DBPARAMETER innodb_buffer_pool_size {DBInstanceClassMemory * 3/4} system integer static true
DBPARAMETER innodb_change_buffering engine-default string dynamic true 5.1.49
DBPARAMETER innodb_commit_concurrency engine-default integer static true
DBPARAMETER innodb_concurrency_tickets engine-default integer dynamic true
DBPARAMETER innodb_data_home_dir / rdsdbdata / db / innodb system string static false
DBPARAMETER innodb_file_format engine-default string dynamic true 5.1.49
DBPARAMETER innodb_file_per_table 1 system boolean static true
DBPARAMETER innodb_flush_log_at_trx_commit engine-default integer dynamic true
DBPARAMETER innodb_flush_method O_DIRECT system string static false
DBPARAMETER innodb_io_capacity engine-default integer dynamic true 5.1.49
DBPARAMETER innodb_lock_wait_timeout engine-default integer static true
DBPARAMETER innodb_locks_unsafe_for_binlog engine-default boolean static false
DBPARAMETER innodb_log_buffer_size 8388608 system integer static true
DBPARAMETER innodb_log_file_size 134217728 system integer static false
DBPARAMETER innodb_log_group_home_dir / rdsdbdata / log / innodb system string static false
DBPARAMETER innodb_max_dirty_pages_pct engine-default integer dynamic true
DBPARAMETER innodb_max_purge_lag engine-default integer dynamic true
DBPARAMETER innodb_old_blocks_pct engine-default integer dynamic true 5.1.49
DBPARAMETER innodb_old_blocks_time engine-default integer dynamic true 5.1.49
DBPARAMETER innodb_open_files engine-default integer static true
DBPARAMETER innodb_random_read_ahead engine-default boolean dynamic true 5.1.61
DBPARAMETER innodb_read_ahead_threshold engine-default integer dynamic true 5.1.49
DBPARAMETER innodb_read_io_threads engine-default integer static true 5.1.49
DBPARAMETER innodb_replication_delay engine-default integer dynamic true 5.1.49
DBPARAMETER innodb_rollback_on_timeout engine-default boolean static true
DBPARAMETER innodb_spin_wait_delay engine-default integer dynamic true 5.1.49
DBPARAMETER innodb_stats_on_metadata engine-default boolean static true
DBPARAMETER innodb_stats_sample_pages engine-default integer dynamic true 5.1.49
DBPARAMETER innodb_strict_mode engine-default boolean dynamic true 5.1.49
DBPARAMETER innodb_support_xa engine-default boolean dynamic true
DBPARAMETER innodb_sync_spin_loops engine-default integer dynamic true
DBPARAMETER innodb_table_locks engine-default boolean dynamic true
DBPARAMETER innodb_thread_concurrency engine-default integer dynamic true
DBPARAMETER innodb_thread_sleep_delay engine-default integer dynamic true
DBPARAMETER innodb_use_legacy_cardinality_algorithm engine-default boolean dynamic false
DBPARAMETER innodb_use_sys_malloc engine-default boolean static true 5.1.49
DBPARAMETER innodb_write_io_threads engine-default integer static true 5.1.49
DBPARAMETER interactive_timeout engine-default integer dynamic true
DBPARAMETER join_buffer_size engine-default integer dynamic true
DBPARAMETER keep_files_on_create engine-default boolean dynamic true
DBPARAMETER key_buffer_size 16777216 system integer dynamic true
DBPARAMETER key_cache_age_threshold engine-default integer dynamic true
DBPARAMETER key_cache_block_size engine-default integer dynamic true
DBPARAMETER key_cache_division_limit engine-default integer dynamic true
DBPARAMETER language / rdsdbbin / mysql / share / mysql / english / system string static false
DBPARAMETER lc_time_names engine-default string dynamic true
DBPARAMETER local_infile 1 system boolean dynamic true
DBPARAMETER log-bin / rdsdbdata / log / binlog / mysql-bin-changelog system string static false
DBPARAMETER log_bin_trust_function_creators engine-default boolean dynamic true
DBPARAMETER log_error /rdsdbdata/log/error/mysql-error.log system string static false
DBPARAMETER log_output TABLE system string dynamic false
DBPARAMETER log_queries_not_using_indexes engine-default boolean dynamic true
DBPARAMETER log_slave_updates engine-default boolean static true
DBPARAMETER log_warnings engine-default integer dynamic true
DBPARAMETER long_query_time engine-default integer dynamic true
DBPARAMETER low_priority_updates engine-default boolean dynamic true
DBPARAMETER lower_case_table_names engine-default integer static true
DBPARAMETER max_allowed_packet engine-default integer dynamic true
DBPARAMETER max_binlog_cache_size engine-default integer dynamic true
DBPARAMETER max_binlog_size 134217728 system integer dynamic false
DBPARAMETER max_connect_errors engine-default integer dynamic true
DBPARAMETER max_connections {DBInstanceClassMemory / 12582880} system integer dynamic true
DBPARAMETER max_delayed_threads engine-default integer dynamic true
DBPARAMETER max_error_count engine-default integer dynamic true
DBPARAMETER max_heap_table_size engine-default integer dynamic true
DBPARAMETER max_insert_delayed_threads engine-default integer dynamic true
DBPARAMETER max_join_size engine-default integer dynamic true
DBPARAMETER max_length_for_sort_data engine-default integer dynamic true
DBPARAMETER max_prepared_stmt_count engine-default integer dynamic true
DBPARAMETER max_seeks_for_key engine-default integer dynamic true
DBPARAMETER max_sort_length engine-default integer dynamic true
DBPARAMETER max_sp_recursion_depth engine-default integer dynamic true
DBPARAMETER max_tmp_tables engine-default integer dynamic true
DBPARAMETER max_user_connections engine-default integer dynamic true
DBPARAMETER max_write_lock_count engine-default integer dynamic true
DBPARAMETER min_examined_row_limit engine-default integer dynamic true
DBPARAMETER myisam_data_pointer_size engine-default integer dynamic true
DBPARAMETER myisam_max_sort_file_size engine-default integer dynamic true
DBPARAMETER myisam_sort_buffer_size engine-default integer dynamic true
DBPARAMETER myisam_stats_method engine-default string dynamic true
DBPARAMETER myisam_use_mmap engine-default boolean dynamic true
DBPARAMETER net_buffer_length engine-default integer dynamic true
DBPARAMETER net_read_timeout engine-default integer dynamic true
DBPARAMETER net_retry_count engine-default integer dynamic true
DBPARAMETER net_write_timeout engine-default integer dynamic true
DBPARAMETER old-style-user-limits engine-default boolean static true
DBPARAMETER old_passwords engine-default boolean dynamic true
DBPARAMETER optimizer_prune_level engine-default boolean dynamic true
DBPARAMETER optimizer_search_depth engine-default integer dynamic true
DBPARAMETER optimizer_switch engine-default string dynamic true
DBPARAMETER pid_file /rdsdbdata/log/mysql-{EndPointPortasket.pid system string static false
DBPARAMETER plugin-load innodb = ha_innodb_plugin.so; innodb_trx = ha_innodb_plugin.so; innodb_locks = ha_innodb_plugin.so; innodb_lock_waits = ha_innodb_plugin.so; innodb_cmp = ha_innodb_plugin.so; innodb_cmp_reset = ha_innodb_plugin.so; innodb_cmpmem = ha_innodb_plugin.so; innodb_cmpmem_reset = ha_innodb_plugin. so system string static false 5.1.49
DBPARAMETER plugin_dir / rdsdbbin / mysql / lib / mysql / plugin system string static false
DBPARAMETER port {EndPointPort} system integer static false
DBPARAMETER preload_buffer_size engine-default integer dynamic true
DBPARAMETER profiling_history_size engine-default integer dynamic true
DBPARAMETER query_alloc_block_size engine-default integer dynamic true
DBPARAMETER query_cache_limit engine-default integer dynamic true
DBPARAMETER query_cache_min_res_unit engine-default integer dynamic true
DBPARAMETER query_cache_size engine-default integer dynamic true
DBPARAMETER query_cache_type engine-default integer static true
DBPARAMETER query_cache_wlock_invalidate engine-default boolean dynamic true
DBPARAMETER query_prealloc_size engine-default integer dynamic true
DBPARAMETER range_alloc_block_size engine-default integer dynamic true
DBPARAMETER read_buffer_size 262144 system integer dynamic true
DBPARAMETER read_only {TrueIfReplica} engine-default boolean dynamic true 5.1.50
DBPARAMETER read_rnd_buffer_size 524288 system integer dynamic true
DBPARAMETER relay-log / rdsdbdata / log / relaylog / relaylog system string static false 5.1.50
DBPARAMETER safe-user-create engine-default boolean static true
DBPARAMETER secure_auth engine-default boolean dynamic true
DBPARAMETER secure_file_priv / tmp system string static false
DBPARAMETER server_id {ServerId} system integer dynamic false 5.1.50
DBPARAMETER skip-character-set-client-handshake engine-default boolean static true
DBPARAMETER skip_external_locking engine-default boolean static false
DBPARAMETER skip_name_resolve engine-default boolean static true 5.1.57
DBPARAMETER skip_show_database engine-default boolean static false
DBPARAMETER slow_launch_time engine-default integer dynamic true
DBPARAMETER slow_query_log engine-default boolean dynamic true
DBPARAMETER socket /tmp/mysql.sock system string static false
DBPARAMETER sort_buffer_size engine-default integer dynamic true
DBPARAMETER sql_mode engine-default string dynamic true
DBPARAMETER sql_select_limit engine-default integer dynamic true
DBPARAMETER sync_binlog engine-default integer dynamic true
DBPARAMETER sync_frm engine-default boolean dynamic true
DBPARAMETER sysdate-is-now engine-default boolean static true
DBPARAMETER table_definition_cache engine-default integer dynamic true
DBPARAMETER table_open_cache engine-default integer dynamic true
DBPARAMETER temp-pool engine-default boolean static true
DBPARAMETER thread_cache_size engine-default integer dynamic true
DBPARAMETER thread_stack 196608 engine-default integer static true
DBPARAMETER timed_mutexes engine-default boolean dynamic true
DBPARAMETER tmp_table_size engine-default integer dynamic true
DBPARAMETER tmpdir / rdsdbdata / tmp system string static false
DBPARAMETER transaction_alloc_block_size engine-default integer dynamic true
DBPARAMETER transaction_prealloc_size engine-default integer dynamic true
DBPARAMETER tx_isolation engine-default string dynamic true
DBPARAMETER updatable_views_with_limit engine-default boolean dynamic true
DBPARAMETER wait_timeout engine-default integer dynamic true As a matter of fact, these parameters can be changed, thereby tuning and adjusting the config of your database.
Let's move on to the example of creating and configuring a group parameter:
1. Create an extended-mysql group parameter
$ rds-create-db-parameter-group extended-mysql -f mysql5.1 -d "allows to store functions"
2. Change the parameter log_bin_trust_function_creators
$ rds-modify-db-parameter-group extended-mysql --parameters="name=log_bin_trust_function_creators, value=on, method=immediate”
That's all. This group option will enable you to create functions that will be stored in RDS.
3. Add the server to the group parameter
$ rds-modify-db-instance myrdsserver --db-parameter-group-name=extended-mysql
4. Reboot the server so that the settings apply
$ rds-reboot-db-instance myrdsserver
Do you use the group option for RDS? What rake faced when working with them? It is very interesting to hear something from someone else's experience!