table_open_cache
================
Variable Scope: Global
Dynamic Variable: Yes
Type: Integer
Default: 2000
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.
#################################################################
table_open_cache_instances
===========================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default: 1 or 16 (depedns upon MySQL version)
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.)
A value of 8 or 16 is recommended on systems that routinely use 16 or more cores.
#################################################################
sort_buffer_size
=================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 262144 (256K)
Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.At minimum the sort_buffer_size value must be large enough to accommodate fifteen tuples in the sort buffer
If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort.
#################################################################
read_buffer_size
================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 131072 (128k)
Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072.
This option is also used in the following context for all storage engines:
- For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
- For bulk insert into partitions.
- For caching results of nested queries.
#################################################################
read_rnd_buffer_size
=====================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 262144 (256k)
This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization.
When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.
#################################################################
query_cache_size
================
Variable Scope: Global
Dynamic Variable: Yes
Type: Integer
Default: 1048576 (1M)
The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. To reduce overhead significantly, you should also start the server with query_cache_type=0 if you will not be using the query cache. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.
#################################################################
query_cache_type
=================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Enumeration
Default: 0
Set the query cache type. Setting the GLOBAL value sets the type for all clients that connect thereafter. Individual clients can set the SESSION value to affect their own use of the query cache. This variable defaults to OFF.
If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.
Options:
- 0 or OFF :: Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0.
- 1 or ON :: Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
- 2 or DEMAND :: Cache results only for cacheable queries that begin with SELECT SQL_CACHE.
#################################################################
tmp_table_size
==============
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 16777216 (16M)
The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.
The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table.
Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.
You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
#################################################################
join_buffer_size
================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 262144 (256K)
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. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
#################################################################
sync_binlog
===========
Variable Scope: Global
Dynamic Variable: Yes
Type: Integer
Default: 0 or 1
Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. When sync_binlog=0, the binary log is never synchronized to disk, and when sync_binlog is set to a value greater than 0 this number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed. Therefore, even in the event of an unexpected restart, any transactions that are missing from the binary log are only in prepared state. This causes the server's automatic recovery routine to rollback those transactions. This guarantees that no transaction is lost from the binary log, and is the safest option. However this can have a negative impact on performance because of an increased number of disk writes. Using a higher value improves performance, but with the increased risk of data loss.
When sync_binlog=0 or sync_binlog is greater than 1, transactions are committed without having been synchronized to disk. Therefore, in the event of a power failure or operating system crash, it is possible that the server has committed some transactions that have not been synchronized to the binary log. Therefore it is impossible for the recovery routine to recover these transactions and they will be lost from the binary log.
Prior to MySQL 5.7.7, the default value of sync_binlog was 0, which configures no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. MySQL 5.7.7 and later use a default value of 1, which is the safest choice, but as noted above can impact performance.
#################################################################
References:
http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
================
Variable Scope: Global
Dynamic Variable: Yes
Type: Integer
Default: 2000
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.
#################################################################
table_open_cache_instances
===========================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default: 1 or 16 (depedns upon MySQL version)
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.)
A value of 8 or 16 is recommended on systems that routinely use 16 or more cores.
#################################################################
sort_buffer_size
=================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 262144 (256K)
Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.At minimum the sort_buffer_size value must be large enough to accommodate fifteen tuples in the sort buffer
If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort.
#################################################################
read_buffer_size
================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 131072 (128k)
Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072.
This option is also used in the following context for all storage engines:
- For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
- For bulk insert into partitions.
- For caching results of nested queries.
#################################################################
read_rnd_buffer_size
=====================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 262144 (256k)
This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization.
When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.
#################################################################
query_cache_size
================
Variable Scope: Global
Dynamic Variable: Yes
Type: Integer
Default: 1048576 (1M)
The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. To reduce overhead significantly, you should also start the server with query_cache_type=0 if you will not be using the query cache. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.
#################################################################
query_cache_type
=================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Enumeration
Default: 0
Set the query cache type. Setting the GLOBAL value sets the type for all clients that connect thereafter. Individual clients can set the SESSION value to affect their own use of the query cache. This variable defaults to OFF.
If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.
Options:
- 0 or OFF :: Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0.
- 1 or ON :: Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
- 2 or DEMAND :: Cache results only for cacheable queries that begin with SELECT SQL_CACHE.
#################################################################
tmp_table_size
==============
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 16777216 (16M)
The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.
The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table.
Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.
You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
#################################################################
join_buffer_size
================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 262144 (256K)
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. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
#################################################################
sync_binlog
===========
Variable Scope: Global
Dynamic Variable: Yes
Type: Integer
Default: 0 or 1
Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. When sync_binlog=0, the binary log is never synchronized to disk, and when sync_binlog is set to a value greater than 0 this number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed. Therefore, even in the event of an unexpected restart, any transactions that are missing from the binary log are only in prepared state. This causes the server's automatic recovery routine to rollback those transactions. This guarantees that no transaction is lost from the binary log, and is the safest option. However this can have a negative impact on performance because of an increased number of disk writes. Using a higher value improves performance, but with the increased risk of data loss.
When sync_binlog=0 or sync_binlog is greater than 1, transactions are committed without having been synchronized to disk. Therefore, in the event of a power failure or operating system crash, it is possible that the server has committed some transactions that have not been synchronized to the binary log. Therefore it is impossible for the recovery routine to recover these transactions and they will be lost from the binary log.
Prior to MySQL 5.7.7, the default value of sync_binlog was 0, which configures no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. MySQL 5.7.7 and later use a default value of 1, which is the safest choice, but as noted above can impact performance.
#################################################################
References:
http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
No comments:
Post a Comment