Details of the rds mysql's database parameters Maximum number of instrumented tables.
innodb_buffer_pool_load_at_startup=ON
Specifies that, on MySQL server startup, the InnoDB buffer pool is automatically warmed up by loading the same pages it held at an earlier time. Typically used in combination with innodb_buffer_pool_dump_at_shutdown.
loose_performance_schema_max_index_stat=10000
Maximum number of index statistics for instrumented tables.
bulk_insert_buffer_size=4194304
MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.
show_old_temporals=OFF
Whether SHOW CREATE TABLE output includes comments to flag temporal columns found to be in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). This variable is disabled by default. If enabled
ft_query_expansion_limit=20
Number of best matches to use for query expansion
innodb_old_blocks_time=1000
Move blocks to the 'new' end of the buffer pool if the first access was at least this many milliseconds ago. The timeout is disabled if 0.
innodb_stats_sample_pages=8
Deprecated, use innodb_stats_transient_sample_pages instead
thread_stack=262144
The stack size for each thread. The default of 192KB (256KB for 64-bit systems) is large enough for normal operation. If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions.
lc_time_names=en_US
This variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME() and MONTHNAME() functions. Locale names are POSIX-style values such as 'ja_JP' or 'pt_BR'. The default value is 'en_US' regardless of your system's locale setting.
innodb_thread_concurrency=0
Helps in performance tuning in heavily concurrent environments. Sets the maximum number of threads allowed inside InnoDB. Value 0 will disable the thread throttling.
sync_master_info=10000
The effects of this variable on a replication slave depend on whether the slaves master_info_repository is set to FILE or TABLE, as explained in the following paragraphs.
default_time_zone=+8:00
The default time zone for the database
old_passwords=0
This variable controls the password hashing method used by the PASSWORD() function. It also influences password hashing performed by CREATE USER and GRANT statements that specify a password using an IDENTIFIED BY clause
loose_recycle_bin=OFF
Switch of recycle bin.
optimizer_search_depth=62
The maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query. Values smaller than the number of relations in a query return an execution plan quicker, but the resulting plan may be far from being optimal. If set to 0, the system automatically picks a reasonable value.
loose_innodb_data_file_purge_interval=100
Time interval each of data file purge operation (milliseconds)
innodb_compression_level=6
Specifies the level of zlib compression to use for InnoDB compressed tables and indexes. A higher value lets you fit more data onto a storage device, at the expense of more CPU overhead during compression. A lower value lets you reduce CPU overhead when storage space is not critical, or you expect the data is not especially compressible.
loose_innodb_log_optimize_ddl=OFF
loose_performance_schema_max_prepared_statements_instances=1000
Maximum number of instrumented prepared statements.
max_sort_length=1024
The number of bytes to use when sorting data values. The server uses only the first max_sort_length bytes of each value and ignores the rest. Consequently, values that differ only after the first max_sort_length bytes compare as equal for GROUP BY, ORDER BY, and DISTINCT operations.
max_binlog_cache_size=18446744073709547520
max_binlog_cache_size sets the size for the transaction cache only.The visibility to sessions of max_binlog_cache_size matches that of the binlog_cache_size system variable.
innodb_online_alter_log_max_size=134217728
Maximum modification log file size for online index creation
key_cache_block_size=1024
The default size of key cache blocks
mysql_native_password_proxy_users=OFF
This variable controls whether the mysql_native_password built-in authentication plugin supports proxy users. It has no effect unless the check_proxy_users system variable is enabled.
innodb_adaptive_max_sleep_delay=150000
Permits InnoDB to automatically adjust the value of innodb_thread_sleep_delay up or down according to the current workload. Any nonzero value enables automated, dynamic adjustment of the innodb_thread_sleep_delay value, up to the maximum value specified in the innodb_adaptive_max_sleep_delay option. The value represents the number of microseconds. This option can be useful in busy systems, with greater than 16 InnoDB threads. (In practice, it is most valuable for MySQL systems with hundreds or thousands of simultaneous connections.)
innodb_purge_rseg_truncate_frequency=128
Defines the frequency with which the purge system frees rollback segments in terms of the number of times that purge is invoked. An undo tablespace cannot be truncated until its rollback segments are freed. Normally, the purge system frees rollback segments once every 128 times that purge is invoked. The default value is 128. Reducing this value increases the frequency with which the purge thread frees rollback segments.
query_alloc_block_size=8192
Allocation block size for query parsing and execution
loose_performance_schema_max_socket_instances=1000
Maximum number of opened instrumented sockets.
innodb_lock_wait_timeout=50
Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. Values above 100000000 disable the timeout.
innodb_purge_threads=1
Purge threads can be from 1 to 32. Default is 4.
loose_performance_point_iostat_interval=2
IO statistics collection interval, in milliseconds
innodb_compression_failure_threshold_pct=5
Defines the compression failure rate threshold for a table, as a percentage, at which point MySQL begins adding padding within compressed pages to avoid expensive compression failures. When this threshold is passed, MySQL begins to leave additional free space within each new compressed page, dynamically adjusting the amount of free space up to the percentage of page size specified by innodb_compression_pad_pct_max. A value of zero disables the mechanism that monitors compression efficiency and dynamically adjusts the padding amount.
innodb_compression_pad_pct_max=50
Specifies the maximum percentage that can be reserved as free space within each compressed page, allowing room to reorganize the data and modification log within the page when a compressed table or index is updated and the data might be recompressed. Only applies when innodb_compression_failure_threshold_pct is set to a nonzero value, and the rate of compression failures passes the cutoff point.
loose_innodb_data_file_purge=ON
Data file purge little by little
loose_internal_tmp_disk_storage_engine=INNODB
On-disk internal temporary table engine.
binlog_rows_query_log_events=OFF
The binlog_rows_query_log_events system variable affects row-based logging only. When enabled, it causes the MySQL Server to write informational log events such as row query log events into its binary log. This information can be used for debugging and related purposes.such as obtaining the original query issued on the master when it cannot be reconstructed from the row updates.
loose_validate_password_check_user_name=ON
innodb_stats_persistent_sample_pages=20
The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by ANALYZE TABLE. Increasing the value improves the accuracy of index statistics, which can improve the query execution plan, at the expense of increased I/O during the execution of ANALYZE TABLE for an InnoDB table.
innodb_ft_total_cache_size=640000000
The total memory allocated, in bytes, for the InnoDB full-text search index cache for all tables. Creating numerous tables, each with a FULLTEXT search index, could consume a significant portion of available memory. innodb_ft_total_cache_size defines a global memory limit for all full-text search indexes to help avoid excessive memory consumption. If the global limit is reached by an index operation, a forced sync is triggered.
event_scheduler=OFF
Enable the event scheduler. Possible values are ON, OFF, and DISABLED (keep the event scheduler completely deactivated, it cannot be activated run-time)
innodb_flush_method=O_DIRECT
Defines the method used to flush data to InnoDB data files and log files, which can affect I/O throughput.
loose_performance_schema_accounts_size=10000
Maximum number of instrumented user@host accounts.
eq_range_index_dive_limit=100
The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used.
loose_sql_safe_updates=OFF
If this variable is enabled, UPDATE and DELETE that do not use a key in the WHERE clause or a LIMIT clause produce an error.
loose_max_execution_time=0
statement be interrupted if the executing time exceeds this value
loose_optimizer_trace_features=greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
Those features can be excluded from tracing using the optimizer_trace_features system variable
rds_reserved_connections=512
The reserved connection for maintain user.
connect_timeout=10
The number of seconds the mysqld server is waiting for a connect packet before responding with 'Bad handshake'
innodb_purge_batch_size=300
Number of UNDO log pages to purge in one batch from the history list.
div_precision_increment=4
Precision of the result of '/' operator will be increased on that value
avoid_temporal_upgrade=OFF
This variable controls whether ALTER TABLE implicitly upgrades temporal columns found to be in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). Upgrading such columns requires a table rebuild, which prevents any use of fast alterations that might otherwise apply to the operation to be performed.
innodb_sync_array_size=1
Defines the size of the mutex/lock wait array. Increasing the value splits the internal data structure used to coordinate threads, for higher concurrency in workloads with large numbers of waiting threads. This setting must be configured when the MySQL instance is starting up, and cannot be changed afterward. Increasing the value is recommended for workloads that frequently produce a large number of waiting threads, typically greater than 768.
innodb_stats_method=nulls_equal
Specifies how InnoDB index statistics collection code should treat NULLs. Possible values are NULLS_EQUAL (default), NULLS_UNEQUAL and NULLS_IGNORED
lock_wait_timeout=31536000
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.
net_read_timeout=30
Number of seconds to wait for more data from a connection before aborting the read
innodb_deadlock_detect=ON
This option is used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.
innodb_write_io_threads=4
Number of background write I/O threads in InnoDB.
end_markers_in_json=OFF
Whether optimizer JSON output should add end markers.
loose_performance_schema_hosts_size=10000
Maximum number of instrumented hosts.
loose_ccl_queue_bucket_count=4
How many groups when use ccl queue
ngram_token_size=2
Defines the n-gram token size for the n-gram full-text parser. The ngram_token_size option is read-only and can only be modified at startup. The default value is 2 (bigram). The maximum value is 10.
loose_innodb_numa_interleave=ON
loose_performance_schema_max_cond_instances=10000
Maximum number of instrumented condition objects.
max_binlog_stmt_cache_size=18446744073709547520
If nontransactional statements within a transaction require more than this many bytes of memory, the server generates an error. The minimum value is 4096. The maximum and default values are 4GB on 32-bit platforms and 16EB (exabytes) on 64-bit platforms.
loose_performance_schema_max_table_lock_stat=10000
Maximum number of lock statistics for instrumented tables.
loose_performance_schema_max_table_handles=10000
Maximum number of opened instrumented tables.
innodb_checksum_algorithm=crc32
Specifies how to generate and verify the checksum stored in the disk blocks of InnoDB tablespaces.
loose_innodb_data_file_purge_max_size=512
Max size each of data file purge operation (MB)
query_cache_type=0
OFF = Don't cache or retrieve results. ON = Cache all results except SELECT SQL_NO_CACHE ... queries. DEMAND = Cache only SELECT SQL_CACHE ... queries
innodb_ft_enable_diag_print=OFF
Whether to enable additional full-text search (FTS) diagnostic output. This option is primarily intended for advanced FTS debugging and will not be of interest to most users. Output is printed to the error log and includes information
innodb_ft_enable_stopword=ON
Specifies that a set of stopwords is associated with an InnoDB FULLTEXT index at the time the index is created. If the innodb_ft_user_stopword_table option is set, the stopwords are taken from that table. Else, if the innodb_ft_server_stopword_table option is set, the stopwords are taken from that table. Otherwise, a built-in set of default stopwords is used.
innodb_io_capacity=20000
The innodb_io_capacity parameter sets an upper limit on the number of I/O operations performed per second by InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.
slow_launch_time=2
If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented
innodb_table_locks=ON
Enable InnoDB locking in LOCK TABLES
innodb_stats_persistent=ON
Specifies whether InnoDB index statistics are persisted to disk. Otherwise, statistics may be recalculated frequently which can lead to variations in query execution plans. This setting is stored with each table when the table is created. You can set innodb_stats_persistent at the global level before creating a table, or use the STATS_PERSISTENT clause of the CREATE TABLE and ALTER TABLE statements to override the system-wide setting and configure persistent statistics for individual tables.
tmp_table_size=2097152
If an internal in-memory temporary table exceeds this size, MySQL will automatically convert it to an on-disk MyISAM table
loose_performance_point_lock_rwlock_enabled=ON
disconnect_on_expired_password=ON
This variable controls how the server handles clients with expired passwords
default_storage_engine=InnoDB
The default storage engine for new tables
net_retry_count=10
If a read on a communication port is interrupted, retry this many times before giving up
innodb_ft_cache_size=8000000
The memory allocated, in bytes, for the InnoDB FULLTEXT search index cache, which holds a parsed document in memory while creating an InnoDB FULLTEXT index. Index inserts and updates are only committed to disk when the innodb_ft_cache_size size limit is reached. innodb_ft_cache_size defines the cache size on a per table basis.
binlog_cache_size=128KB
The size of the transactional cache for updates to transactional engines for the binary log. If you often use transactions containing many statements, you can increase this to get more performance
innodb_max_dirty_pages_pct=75
Percentage of dirty pages allowed in bufferpool.
query_cache_limit=1048576
Don't cache results that are bigger than this
innodb_disable_sort_file_cache=ON
if ON, disable page cache when innodb using sort file
innodb_lru_scan_depth=1024
A parameter that influences the algorithms and heuristics for the flush operation for the InnoDB buffer pool. Primarily of interest to performance experts tuning I/O-intensive workloads. It specifies, per buffer pool instance, how far down the buffer pool LRU page list the page cleaner thread scans looking for dirty pages to flush. This is a background operation performed once per second.
loose_performance_schema_max_mutex_instances=10000
Maximum number of instrumented MUTEX objects.
innodb_ft_result_cache_limit=2000000000
The InnoDB full-text search query result cache limit (defined in bytes) per full-text search query or per thread. Intermediate and final InnoDB full-text search query results are handled in memory. Use innodb_ft_result_cache_limit to place a size limit on the full-text search query result cache to avoid excessive memory consumption in case of very large InnoDB full-text search query results (millions or hundreds of millions of rows, for example). Memory is allocated as required when a full-text search query is processed. If the result cache size limit is reached, an error is returned indicating that the query exceeds the maximum allowed memory.
long_query_time=1
Log all queries that have taken more than long_query_time seconds to execute to file. The argument will be treated as a decimal value with microsecond precision
interactive_timeout=7200
The number of seconds the server waits for activity on an interactive connection before closing it
innodb_read_io_threads=4
Number of background read I/O threads in InnoDB.
transaction_prealloc_size=4096
There is a per-transaction memory pool from which various transaction-related allocations take memory. The initial size of the pool in bytes is transaction_prealloc_size. For every allocation that cannot be satisfied from the pool because it has insufficient memory available, the pool is increased by transaction_alloc_block_size bytes. When the transaction ends, the pool is truncated to transaction_prealloc_size bytes.
open_files_limit=65535
If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit()
loose_performance_schema_max_metadata_locks=10000
Maximum number of metadata locks.
innodb_open_files=3000
How many files at the maximum InnoDB keeps open at the same time.
max_heap_table_size=67108864
This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.
loose_performance_schema_digests_size=10000
Size of the statement digest.
automatic_sp_privileges=ON
When this variable has a value of 1 (the default), the server automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (The ALTER ROUTINE privilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. If automatic_sp_privileges is 0, the server does not automatically add or drop these privileges.
explicit_defaults_for_timestamp=OFF
This system variable determines whether the server enables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns.
ft_max_word_len=84
The maximum length of the word to be included in a MyISAM FULLTEXT index.
innodb_autoextend_increment=64
The increment size (in megabytes) for extending the size of an auto-extending InnoDB system tablespace file when it becomes full. The default value is 64.
sql_mode=
Syntax: sql-mode=mode[,mode[,mode...]]. See the manual for the complete list of valid sql modes
innodb_stats_transient_sample_pages=8
The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by ANALYZE TABLE. The default value is 8. Increasing the value improves the accuracy of index statistics, which can improve the query execution plan, at the expense of increased I/O when opening an InnoDB table or recalculating statistics.
innodb_random_read_ahead=OFF
Enables the random read-ahead technique for optimizing InnoDB I/O.
table_open_cache={LEAST(DBInstanceClassMemory/1073741824*256, 2048)}
The number of cached open tables (total for all table cache instances)
range_optimizer_max_mem_size=8388608
The limit on memory consumption for the range optimizer. A value of 0 means “no limit.” If an execution plan considered by the optimizer uses the range access method but the optimizer estimates that the amount of memory needed for this method would exceed the limit, it abandons the plan and considers other plans. For more information, see Limiting Memory Use for Range Optimization.
loose_innodb_rds_faster_ddl=ON
Feature switch of faster ddl.
innodb_status_output=OFF
Enables or disables periodic output for the standard InnoDB Monitor. Also used in combination with innodb_status_output_locks to enable or disable periodic output for the InnoDB Lock Monitor.
loose_validate_password_number_count=1
innodb_log_compressed_pages=OFF
Specifies whether images of re-compressed pages are written to the redo log. Re-compression may occur when changes are made to compressed data.
slave_net_timeout=60
The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect
delay_key_write=ON
Type of DELAY_KEY_WRITE
query_cache_wlock_invalidate=OFF
Normally, when one client acquires a WRITE lock on a MyISAM table, other clients are not blocked from issuing statements that read from the table if the query results are present in the query cache. Setting this variable to 1 causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to the table. This forces other clients that attempt to access the table to wait while the lock is in effect.
max_points_in_geometry=65536
The maximum value of the points_per_circle argument to the ST_Buffer_Strategy() function.
general_log=OFF
Whether the general query log is enabled. The destination for log output is controlled by the log_output system variable.
max_prepared_stmt_count=16382
Maximum number of prepared statements in the server
wait_timeout=86400
The number of seconds the server waits for activity on a connection before closing it
log_bin_use_v1_row_events=1
If equal to 1 then version 1 row events are written to a row based binary log. If equal to 0, then the latest version of events are written. This option is useful during some upgrades.
loose_performance_schema_setup_actors_size=10000
Maximum number of rows in SETUP_ACTORS.
query_cache_min_res_unit=1024
The minimum size (in bytes) for blocks allocated by the query cache. The default value is 4096 (4KB).
innodb_print_all_deadlocks=OFF
Print all deadlocks to MySQL error log (off by default)
loose_thread_pool_size=1
thread groups in the thread pool
binlog_stmt_cache_size=32768
The size of the statement cache for updates to non-transactional engines for the binary log. If you often use statements updating a great number of rows, you can increase this to get more performance
transaction_isolation=READ-COMMITTED
Default transaction isolation level
innodb_buffer_pool_dump_at_shutdown=ON
Specifies whether to record the pages cached in the InnoDB buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart. Typically used in combination with innodb_buffer_pool_load_at_startup. The innodb_buffer_pool_dump_pct option defines the percentage of most recently used buffer pool pages to dump.
query_prealloc_size=8192
Persistent buffer for query parsing and execution
key_cache_age_threshold=300
This characterizes the number of hits a hot block has to be untouched until it is considered aged enough to be downgraded to a warm block. This specifies the percentage ratio of that number of hits to the total number of blocks in key cache
loose_performance_schema_setup_objects_size=10000
Maximum number of rows in SETUP_OBJECTS.
loose_rds_kill_connections=20
The extra connection for user in rds_user_with_kill_option.
transaction_alloc_block_size=8192
The amount in bytes by which to increase a per-transaction memory pool which needs memory. See the description of transaction_prealloc_size.
optimizer_trace_limit=1
The maximum number of optimizer traces to display.
metadata_locks_cache_size=1024
The size of the metadata locks cache. The server uses this cache to avoid creation and destruction of synchronization objects. This is particularly helpful on systems where such operations are expensive, such as Windows XP.
optimizer_prune_level=1
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. A value of 0 disables heuristics so that the optimizer performs an exhaustive search. A value of 1 causes the optimizer to prune plans based on the number of rows retrieved by intermediate plans.
loose_performance_schema_max_file_instances=1000
Maximum number of instrumented files.
innodb_max_purge_lag=0
Defines the maximum length of the purge queue. The default value of 0 indicates no limit (no delays).
innodb_buffer_pool_dump_pct=25
Specifies the percentage of the most recently used pages for each buffer pool to read out and dump. The range is 1 to 100. The default value is 25. For example, if there are 4 buffer pools with 100 pages each, and innodb_buffer_pool_dump_pct is set to 25, the 25 most recently used pages from each buffer pool are dumped.
innodb_max_dirty_pages_pct_lwm=0
Defines a low water mark representing the percentage of dirty pages at which preflushing is enabled to control the dirty page ratio. The default of 0 disables the pre-flushing behavior entirely
max_sp_recursion_depth=0
The number of times that any given stored procedure may be called recursively. The default value for this option is 0, which completely disables recursion in stored procedures. The maximum value is 255.
innodb_status_output_locks=OFF
Enables or disables the InnoDB Lock Monitor. When enabled, the InnoDB Lock Monitor prints additional information about locks in SHOW ENGINE INNODB STATUS output and in periodic output printed to the MySQL error log. Periodic output for the InnoDB Lock Monitor is printed as part of the standard InnoDB Monitor output. The standard InnoDB Monitor must therefore be enabled for the InnoDB Lock Monitor to print data to the MySQL error log periodically.
updatable_views_with_limit=YES
This variable controls whether updates to a view can be made when the view does not contain all columns of the primary key defined in the underlying table, if the update statement contains a LIMIT clause. (Such updates often are generated by GUI tools.) An update is an UPDATE or DELETE statement. Primary key here means a PRIMARY KEY, or a UNIQUE index in which no column can contain NULL.
binlog_row_image=full
Controls whether rows should be logged in 'FULL', 'NOBLOB' or 'MINIMAL' formats. 'FULL', means that all columns in the before and after image are logged. 'NOBLOB', means that mysqld avoids logging blob columns whenever possible (eg, blob column was not changed or is not part of primary key). 'MINIMAL', means that a PK equivalent (PK columns or full row if there is no PK in the table) is logged in the before image, and only changed columns are logged in the after image. (Default: FULL).
innodb_change_buffer_max_size=25
Maximum size for the InnoDB change buffer, as a percentage of the total size of the buffer pool. You might increase this value for a MySQL server with heavy insert, update, and delete activity, or decrease it for a MySQL server with unchanging data used for reporting.
innodb_optimize_fulltext_only=OFF
Changes the way OPTIMIZE TABLE operates on InnoDB tables. Intended to be enabled temporarily, during maintenance operations for InnoDB tables with FULLTEXT indexes.
local_infile=ON
This variable controls server-side LOCAL capability for LOAD DATA statements.
loose_opt_rds_last_error_gtid=ON
if ON, show Last_SQL_Error_Gtid in show slave status
innodb_ft_max_token_size=84
InnoDB Fulltext search maximum token size in characters
innodb_undo_log_truncate=OFF
When enabled, undo tablespaces that exceed the threshold value defined by innodb_max_undo_log_size are marked for truncation.
loose_performance_schema_users_size=10000
Maximum number of instrumented users.
innodb_max_undo_log_size=1073741824
Defines a threshold size for undo tablespaces. If an undo tablespace exceeds the threshold, it can be marked for truncation when innodb_undo_log_truncate is enabled. The default value is 1073741824 bytes (1024 MiB).
slave_parallel_type=LOGICAL_CLOCK
This variable specifies the policy used to decide which transactions are allowed to execute in parallel on the slave. The variable has no effect on slaves for which multithreading is not enabled.When slave_preserve_commit_order=1 is set, you can only use LOGICAL_CLOCK.
loose_rds_check_core_file_enabled=ON
enable check core file
loose_thread_pool_enabled=ON
enable or disable thread pool
innodb_adaptive_hash_index=OFF
Enable InnoDB adaptive hash index (enabled by default). Disable with --skip-innodb-adaptive-hash-index.
innodb_sync_spin_loops=30
The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended.
net_write_timeout=60
Number of seconds to wait for a block to be written to a connection before aborting the write
flush_time=0
If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk. This option is best used only on systems with minimal resources.
lower_case_table_names=1
If set to 1 table names are stored in lowercase on disk and table names will be case-insensitive. Should be set to 2 if you are using a case insensitive file system
sha256_password_proxy_users=OFF
This variable controls whether the sha256_password built-in authentication plugin supports proxy users. It has no effect unless the check_proxy_users system variable is enabled.
character_set_filesystem=binary
The file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multibyte file names are permitted, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to 'utf8'.
innodb_flush_sync=ON
The innodb_flush_sync parameter, which is enabled by default, causes the innodb_io_capacity setting to be ignored for bursts of I/O activity that occur at checkpoints. To adhere to the limit on InnoDB background I/O activity defined by the innodb_io_capacity setting, disable innodb_flush_sync.
tls_version=TLSv1,TLSv1.1,TLSv1.2
The protocols permitted by the server for encrypted connections. The value is a comma-separated list containing one or more protocol names. The protocols that can be named for this variable depend on the SSL library used to compile MySQL.
key_cache_division_limit=100
The minimum percentage of warm blocks in key cache
loose_performance_schema_max_rwlock_instances=10000
Maximum number of instrumented RWLOCK objects.
delayed_insert_timeout=300
How long a INSERT DELAYED thread should wait for INSERT statements before terminating. This variable is deprecated along with INSERT DELAYED.
loose_rds_audit_max_sql_size=8192
max sql size in rds audit log
preload_buffer_size=32768
The size of the buffer that is allocated when preloading indexes.
innodb_read_ahead_threshold=56
Number of pages that must be accessed sequentially for InnoDB to trigger a readahead.
loose_optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
The optimizer_switch system variable enables control over optimizer behavior.
concurrent_insert=1
Use concurrent insert with MyISAM. Possible values are NEVER, AUTO, ALWAYS
block_encryption_mode="aes-128-ecb"
["aes-128-ecb"|"aes-192-ecb"|"aes-256-ecb"|"aes-128-cbc"|"aes-192-cbc"|"aes-256-cbc"]
slow_query_log=ON
Specify the initial slow query log state. With no argument or an argument of 1, the --slow-query-log option enables the log. If omitted or given with an argument of 0, the option disables the log.
net_buffer_length=16384
Each client thread is associated with a connection buffer and result buffer. Both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement.
query_cache_size=0
The memory allocated to store results from old queries RDS内存管理说明
innodb_buffer_pool_size={DBInstanceClassMemory*3/4}
The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. RDS内存管理说明
delayed_insert_limit=100
After inserting delayed_insert_limit rows, the INSERT DELAYED handler will check if there are any SELECT statements pending. If so, it allows these to execute before continuing. This variable is deprecated along with INSERT DELAYED.
innodb_large_prefix=OFF
Support large index prefix length of REC_VERSION_56_MAX_INDEX_COL_LEN (3072) bytes.
innodb_monitor_disable
Disables InnoDB metrics counters. Counter data may be queried using the INFORMATION_SCHEMA.INNODB_METRICS table.
loose_performance_schema_max_program_instances=10000
Maximum number of instrumented programs.
innodb_adaptive_flushing_lwm=10
Defines the low water mark representing percentage of redo log capacity at which adaptive flushing is enabled.
innodb_log_checksums=ON
Enables or disables checksums for redo log pages. innodb_log_checksums replaces innodb_log_checksum_algorithm.
delayed_queue_size=1000
What size queue (in rows) should be allocated for handling INSERT DELAYED. If the queue becomes full, any client that does INSERT DELAYED will wait until there is room in the queue again. This variable is deprecated along with INSERT DELAYED.
session_track_gtids=OFF
Controls a tracker for capturing GTIDs and returning them in the OK packet. Depending on the value of this option, at the end of executing a transaction, the GTIDs specified are captured by the tracker and appended to the OK packet.
innodb_thread_sleep_delay=10000
Time of innodb thread sleeping before joining InnoDB queue (usec). Value 0 disable a sleep
loose_rds_set_connection_id_enabled=ON
Patch about Connection ID for proxy
innodb_old_blocks_pct=37
Percentage of the buffer pool to reserve for 'old' blocks.
innodb_ft_sort_pll_degree=2
Number of threads used in parallel to index and tokenize text in an InnoDB FULLTEXT index when building a search index.
log_slow_admin_statements=OFF
Include slow administrative statements in the statements written to the slow query log
innodb_stats_on_metadata=OFF
Enable statistics gathering for metadata commands such as SHOW TABLE STATUS for tables that use transient statistics (off by default)
stored_program_cache=256
Sets a soft upper limit for the number of cached stored routines per connection. The value of this variable is specified in terms of the number of stored routines held in each of the two caches maintained by the MySQL Server for, respectively, stored procedures and stored functions.
group_concat_max_len=1024
The maximum length of the result of function GROUP_CONCAT()
innodb_sort_buffer_size=1048576
Specifies the size of sort buffers used to sort data during creation of an InnoDB index. The specified size defines the amount of data that is read into memory for internal sorting and then written out to disk. This process is referred to as a “run”. During the merge phase, pairs of buffers of the specified size are read in and merged. The larger the setting, the fewer runs and merges there are.
innodb_page_cleaners={LEAST(DBInstanceClassMemory/1073741824, 8)}
The number of page cleaner threads that flush dirty pages from buffer pool instances. Page cleaner threads perform flush list and LRU flushing. A single page cleaner thread was introduced in MySQL 5.6 to offload buffer pool flushing work from the InnoDB master thread. In MySQL 5.7, InnoDB provides support for multiple page cleaner threads. A value of 1 maintains the pre-MySQL 5.7 configuration in which there is a single page cleaner thread. When there are multiple page cleaner threads, buffer pool flushing tasks for each buffer pool instance are dispatched to idle page cleaner threads. The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7. If the number of page cleaner threads exceeds the number of buffer pool instances
innodb_spin_wait_delay=6
The maximum delay between polls for a spin lock. The low-level implementation of this mechanism varies depending on the combination of hardware and operating system, so the delay does not correspond to a fixed time interval. For more information, see Section 14.6.10, “Configuring Spin Lock Polling”.
myisam_sort_buffer_size=262144
The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE
innodb_rollback_segments=128
Defines the number of rollback segments used by InnoDB.
innodb_commit_concurrency=0
The number of threads that can commit at the same time. A value of 0 (the default) permits any number of transactions to commit simultaneously.
innodb_concurrency_tickets=5000
Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket
table_definition_cache={LEAST(DBInstanceClassMemory/1073741824*512, 2048)}
The number of cached table definitions
auto_increment_increment=1
Auto-increment columns are incremented by this
binlog_checksum=CRC32
Type of BINLOG_CHECKSUM_ALG. Include checksum for log events in the binary log. Possible values are NONE and CRC32; default is CRC32.
max_seeks_for_key=18446744073709500000
Limit the assumed maximum number of seeks when looking up rows based on a key. The MySQL optimizer assumes that no more than this number of key seeks are required when searching for matching rows in a table by scanning an index, regardless of the actual cardinality of the index. By setting this to a low value (say, 100), you can force MySQL to prefer indexes instead of table scans.
autocommit=ON
The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1, MySQL performs an automatic COMMIT of any open transaction.
loose_ccl_queue_bucket_size=64
The max concurrency allowed when use ccl queue.
max_length_for_sort_data=1024
Max number of bytes in sorted records
back_log=3000
The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time
max_error_count=64
The maximum number of error, warning, and information messages to be stored for display by the SHOW ERRORS and SHOW WARNINGS statements. This is the same as the number of condition areas in the diagnostics area, and thus the number of conditions that can be inspected by GET DIAGNOSTICS.
loose_recycle_bin_retention=604800
How long to save the recycle bin(seconds).
innodb_io_capacity_max=40000
If flushing activity falls behind, InnoDB can flush more aggressively than the limit imposed by innodb_io_capacity. innodb_io_capacity_max defines an upper limit the number of I/O operations performed per second by InnoDB background tasks in such situations.
innodb_strict_mode=OFF
Use strict mode when evaluating create options.
binlog_order_commits=ON
When this variable is enabled on a master (the default), transactions are externalized in the same order as they are written to the binary log. If disabled, transactions may be committed in parallel. In some cases, disabling this variable might produce a performance increment.
min_examined_row_limit=0
Queries that examine fewer than this number of rows are not logged to the slow query log.
performance_schema={LEAST(DBInstanceClassMemory/8589934592, 1)}
Enable the performance schema.
innodb_ft_min_token_size=3
InnoDB Fulltext search minimum token size in characters
loose_validate_password_policy=1
sync_relay_log_info=10000
Setting this variable takes effect for all replication channels immediately, including running channels.
innodb_stats_auto_recalc=ON
Causes InnoDB to automatically recalculate persistent statistics after the data in a table is changed substantially. The threshold value is 10% of the rows in the table. This setting applies to tables created when the innodb_stats_persistent option is enabled. Automatic statistics recalculation may also be configured by specifying STATS_PERSISTENT=1 in a CREATE TABLE or ALTER TABLE statement. The amount of data sampled to produce the statistics is controlled by the innodb_stats_persistent_sample_pages configuration option.
max_connect_errors=100
If there is more than this number of interrupted connections from a host this host will be blocked from further connections
join_buffer_size=352KB
The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.
innodb_change_buffering=all
Whether InnoDB performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. Permitted values are described in the following table.
optimizer_trace_max_mem_size=16384
The maximum cumulative size of stored optimizer traces.
innodb_autoinc_lock_mode=2
innodb_rollback_on_timeout=OFF
Roll back the complete transaction on lock wait timeout, for 4.x compatibility (disabled by default)
loose_opt_rds_enable_show_slave_lag=ON
Roll back the complete transaction on lock wait timeout, for 4.x compatibility (disabled by default)
loose_performance_schema_max_thread_instances=10000
Maximum number of instrumented threads.
max_write_lock_count=102400
After this many write locks, allow some read locks to run in between
master_verify_checksum=OFF
Enabling this variable causes the master to examine checksums when reading from the binary log. master_verify_checksum is disabled by default.in this case, the master uses the event length from the binary log to verify events, so that only complete events are read from the binary log.
innodb_ft_num_word_optimize=2000
Number of words to process during each OPTIMIZE TABLE operation on an InnoDB FULLTEXT index. Because a bulk insert or update operation to a table containing a full-text search index could require substantial index maintenance to incorporate all changes, you might do a series of OPTIMIZE TABLE statements, each picking up where the last left off.
max_join_size=18446744073709551615
Do not permit statements that probably need to examine more than max_join_size rows (for single-table statements) or row combinations (for multiple-table statements) or that are likely to do more than max_join_size disk seeks. By setting this value, you can catch statements where keys are not used properly and that would probably take a long time. Set it if your users tend to perform joins that lack a WHERE clause, that take a long time, or that return millions of rows.
loose_validate_password_mixed_case_count=1
log_error_verbosity=2
The log_error_verbosity system variable specifies the verbosity for handling events intended for the error log.
loose_validate_password_length=8
log_throttle_queries_not_using_indexes=0
If log_queries_not_using_indexes is enabled, the log_throttle_queries_not_using_indexes variable limits the number of such queries per minute that can be written to the slow query log.
innodb_max_purge_lag_delay=0
Specifies the maximum delay in microseconds for the delay imposed by theinnodb_max_purge_lag configuration option. A nonzero value represents an upper limit on the delay period computed from the formula based on the value of innodb_max_purge_lag. The default of zero means that there is no upper limit imposed on the delay interval.
loose_optimizer_trace=enabled=off,one_line=off
The optimizer_trace system variable has these on/off switches,enabled,one_line
default_week_format=0
The default week format used by WEEK() functions
innodb_cmp_per_index_enabled=OFF
Enables per-index compression-related statistics in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table. Because these statistics can be expensive to gather, only enable this option on development, test, or slave instances during performance tuning related to InnoDB compressed tables.
show_compatibility_56=OFF
The INFORMATION_SCHEMA has tables that contain system and status variable information The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.
host_cache_size=644
The size of the internal host cache (see Section 8.12.5.2, “DNS Lookup Optimization and the Host Cache”). Setting the size to 0 disables the host cache. Changing the cache size at runtime implicitly causes a FLUSH HOSTS operation to clear the host cache and truncate the host_cache table.
low_priority_updates=0
INSERT/DELETE/UPDATE has lower priority than selects
auto_increment_offset=1
Offset added to Auto-increment columns. Used when auto-increment-increment != 1
range_alloc_block_size=4096
The size of blocks that are allocated when doing range optimization.
ft_min_word_len=4
The minimum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable
loose_performance_point_iostat_volume_size=10000
IO statistics collection maximum capacity, the unit is the number of slots
sort_buffer_size=704KB
Each session that must perform a sort allocates a buffer of this size.
slave_type_conversions=
Controls the type conversion mode in effect on the slave when using row-based replication.
max_allowed_packet=1024M
The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.
read_buffer_size=704KB
Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. The value of this variable should be a multiple of 4KB.
thread_cache_size=256
optimizer_trace_offset=-1
The offset of optimizer traces to display.
loose_thread_pool_oversubscribe=32
How many additional active worker threads in a group are allowed.
loose_validate_password_special_char_count=1
character_set_server=utf8
The default character set
innodb_adaptive_flushing=ON
Specifies whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer pool based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity.
log_queries_not_using_indexes=OFF
Log queries that are executed without benefit of any index to the slow log if it is open
innodb_monitor_enable=
innodb_monitor_enable
table_open_cache_instances={LEAST(DBInstanceClassMemory/1073741824, 16)}
The number of open tables cache instances. To improve scalability by reducing contention among sessions, the open tables cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances . A session needs to lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that use the cache when there are many sessions accessing tables. (DDL statements still require a lock on the entire cache, but such statements are much less frequent than DML statements.)
innodb_flush_neighbors=0
Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent.
innodb_buffer_pool_instances={LEAST(DBInstanceClassMemory/1073741824, 8)}
The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.
