MySQL 5.5 Configuration Item detailed one

Source: Internet
Author: User
Tags bulk insert create index deprecated locale ssl connection

Add by Zhj: In the MySQL5.6 of some parameters have been increased, see http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html Original:/http mageedu.blog.51cto.com/4265610/1062628 Marco Education Linux Operations training  autocommit={0|1} Set whether the MySQL transaction is automatically committed, 1 means immediate commit, and 0 indicates that an explicit commit is required. The scope is global or session, which can be used in a configuration file (but not in a configuration file prior to 5.5.8) and is a dynamic variable.  AUTOMATIC_SP_PRIVILEGES={0|1} Sets whether the MySQL server creates the Execute and alter routine permissions on the stored routine that it created for the storage routines, and defaults to 1 (giving the two permissions to its creator). The scope is global.  back_log= #当MySQL的主线程在短时间内收到大量连接请求时, it will take some time to detect the thread and start new threads for new requests Back_ The value of the log parameter is the number of requests that arrive in a short period of time that can be accepted and wait for the main MySQL thread to be processed later. The scope is global, can be used with configuration files, non-dynamic variables.  basedir=path,-B path is used to specify the installation directory for MySQL, and all other common relative paths are relative to the path here. The scope is global and can be used in the configuration file, but it is non-dynamic variable.  BIND-ADDRESS=ADDR Specifies the IP address that the MYSQLD service listens on, which defaults to 0.0.0.0, which represents all IP addresses that have been configured for this machine. The scope is global and can be used in the configuration file, but it is non-dynamic variable.  binlog-format={row| Statement| MIXED} Specifies the type of binary log, which defaults to statement. If the binary log format is set and the binary log is not enabled, MySQL starts with warning log information and is logged in the error log. The scope is global or session, can be used for configuration files, and is a dynamic variable. The  buld_insert_buffer_sizemyisam engine uses a special tree-like cache to speed up bulk insert operations, such as insert ... SELECT, INSERT ... VALUES (...), (...),... And the load DATA infile command completes the insert operation. For each thread, this buffThe size of the ER is independent, the value of which is configured in bytes, the valid range is 0 to "2^cpu word length", the default size is 8MB. The scope is global or session and can be used for configuration files, which belong to dynamic variables.  chroot=path,-R PATH sets the working directory in which MySQL works based on chroot mode, which is the recommended mechanism in environments where security issues are particularly important. At this point, however, the work of commands such as LOAD DATA infile may be affected. Available for configuration files. The  console is only available for Windows platform options to enable error log information to be sent to standard inputs and error outputs, even if the--LOG-ERROR option is configured.  concurrent_insert={never| auto| Always} or use {0|1|2} respectively to set whether insert and SELECT statements are allowed to be executed in parallel on the MyISAM table. The scope is global and can be used for configuration files, which belong to dynamic variables.  connect_timeout= #mysqld服务器端在响应 the number of seconds to wait before the failed handshake action message to the client, which defaults to 10 seconds. The scope is global and can be used for configuration files, which belong to dynamic variables.  core-file when the MySQL process is down, save the information as a core file, and on the Linux platform, the core file is usually saved to the working directory of the current process. and named Core.pid, whose filename suffix pid is the process number of the current process; for MySQL, save the directory as a data file directory.  datadir=path,-H PATH Specifies the data directory for the MySQL service. The scope is global and can be used in the configuration file, but it is non-dynamic variable.  default_storage_engine={engine_name} sets the default storage engine for the MySQL server. MySQL 5.5. Before version 5 defaults to MyISAM, and the later version defaults to InnoDB. The scope is global and can be used for configuration files, which belong to dynamic variables.  delay-key-write={on| Off| All} is used only for MyISAM tables and requires the Delay_key_write option to be used when creating tables. When enabled, key buffer does not empty every time the index is updated, but instead performs a key buffer flush operation when the table is closed. Off means that ignoring delay_key_write,on means any delay_key_write option used when MySQL accepts the CREATE TABLE, the all tableAll newly opened tables follow this attribute.  error-count the number of error messages caused by the previous SQL statement, which is a read-only variable.  event-scheduler={on| Off| DISABLED} Sets whether the MySQL server is enabled and no event Scheduler is started. Off means stop, which is the default value; On means start, which is running and performs all scheduled transactions; Disabled disables the event Scheduler, which means that it cannot switch to the startup state. The scope is global and can be used for configuration files, which belong to dynamic variables.  expire_logs_days={0..99} Sets the number of days that the binary log expires, and binary log files that exceed this number are automatically deleted. The default is 0, which means that the expired auto-delete feature is not enabled. If this feature is enabled, automatic removal of work usually occurs at MySQL startup or flush logs. The scope is global and can be used for configuration files, which belong to dynamic variables.  external_user=name on the MySQL server based on authentication plug-in user authentication, this plug-in will initiate the connection request user as another user to achieve the purpose of permission check, so that the external user as a second user of the proxy users, and have all permissions for the second user. The value of this variable is null when using the internal authentication mechanism of MySQL or when no plug-in has set a value for it. The scope is session level, not used for configuration file, is non-dynamic variable.  flush={on| OFF} Sets whether the MySQL server performs data synchronization (writing data to disk) separately for each SQL statement. Normally, MySQL performs data synchronization for each statement and takes the subsequent synchronization process to the operating system. The default is off. The scope is global and can be used for configuration files, which belong to dynamic variables.  flush-time={0..} When the value is not 0, the MySQL server shuts down all open tables for a specified length of time per flush_time, using them to release all resources and synchronize the data to disk. This feature needs to be enabled only if system resources are extremely scarce. The default value is 0, which is to disable this feature. The scope is global and can be used for configuration files, which belong to dynamic variables.  FOREIGN-KEY-CHECKS={0|1} Sets whether the foreign key constraint is checked for the InnoDB table, which defaults to 1. Disabling this feature avoids side effects of foreign key constraints when you do not ensure that all InnoDB tables are reloaded in the original order.  general_log={on| OFF} to set whether the query log is enabled, the default value depends on whether the--general_log option is used when starting mysqld.If enabled, the output location is defined by the--log_output option, and if the value of Log_output is set to none, the query log is enabled and no log information is logged. The scope is global and can be used for configuration files, which belong to dynamic variables.  general_log_file=file_name the log file name of the query log, which defaults to "Hostname.log". The scope is global and can be used for configuration files, which belong to dynamic variables.  group_concat_max_len={4..} Sets the maximum length of the return value of the Group_concat () function, which defaults to 1024. Valid values range from 4 to "2^cpu word length". The scope is global or session level and is used for configuration files, which belong to dynamic variables.  have-compress={yes|no}zlib compression library can be used for MySQL server. The COMPRESS () and uncompress () functions are not available when their value is no.  have_crypt={yes|no}crypt () system calls can be used by the MySQL server. The ENCRYPT () function is not available when its value is no.  have_csv={yes|no}mysqld is YES when supporting the CSV engine, otherwise NO.  HAVE_DYNAMIC_LOADING={YES|NO}MYSQLD supports dynamically loading plug-ins for YES, otherwise NO. YES when  HAVE_GEOMETRY={YES|NO}MYSQLD supports spatial data type, otherwise NO.  have_innodb={yes|no}mysqld is YES when supporting InnoDB storage engine, otherwise NO.  have_openssl={yes|no} This is an alias for the HAVE_SSL option, YES if  HAVE_SSL={YES|NO}MYSQLD supports SSL connection, otherwise NO. Disabled indicates that support for SSL is enabled at Mysqld compile time, but the option to use the correct ssl-xxx class (such as Ssl_cert) is not available when starting mysqld.  HAVE_PARTITIONING={YES|NO}MYSQLD supports partitioning, this option has been largely deprecated and has been removed in MySQL-5.6, using show engines to obtain this information.  HAVE_PROFILING={YES|NO}MYSQLD Support Statement Performance analysis is YES, otherwiseNO. If the profiling feature is supported, the--profiling variable is used to control whether this feature is started.  HAVE_QUERY_CACHE={YES|NO}MYSQLD supports query caching YES, otherwise NO.  HAVE_RTREE_KEYS={YES|NO}MYSQLD supports Rtree index is YES, otherwise NO. The Rtree index is used for the spatial index of the MyISAM table.  have_symlink={yes|no}mysqld support for symbolic links is YES, otherwise NO. On UNIX hosts, this functionality is useful for data directories and index catalogs. The host name is assigned to this variable when the  HOSTNAME=STRINGMYSQLD server starts. The scope is global and is not a dynamic variable. The main purpose of the  IDENTITYLAST_INSERT_ID variable is to be compatible with other database systems. Session-level variables.  init_connect=string sets one or more (separated by semicolons) SQL statements that are executed in advance of each client's connection to mysqld, but this feature is not valid for users with super privileges. For example, the autocommit variable is not present in MySQL prior to 5.5.8, so you can use init_connect= ' SET autocommit=0 ' in Mysqld's configuration file to disable Autocommit functionality by default for each user , of course, you can also use set GLOBAL init_connect= ' Set autocommit=0 '; command complete. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  init-file=/path/to/somefile defines the initialization file that is used at mysqld startup, which contains a separate SQL statement per line (cannot have annotations, does not need and cannot use a statement terminator), and is executed one at a time at mysqld startup.  insert_id the value of this variable is used when an INSERT or alter_table statement is executed for a field that has auto_increment set in a table. Used primarily for binary logs.  interactive_timeout= #mysqld进程等待一个已经建立连接的交互式客户端的后续命令之前所经过的秒数, default is 28800. The scope is global or session level and is used for configuration files, which belong to dynamic variables.  join_buffer_size= #mysqld用于平面索引扫描 (Plain index scans), the minimum buffer that can be used when a range index is scanned or a full table scan without an index is used. In normal cases, adding an index is an effective means of speeding up the execution of a connection, and when an index cannot be added, increasing the value of join_buffer_size can speed up the execution of a full connection. Each full connection between the two tables uses a separate join buffer, and it is possible to use multiple join buffer for non-index-based, complex full connections between multiple tables. Setting this variable value larger than the size of each matching row does not bring too much benefit, so you should not set this value too large at the global scope. It is recommended that you use a smaller global setting to set a larger value only for larger connections when you need to use a session. The maximum value depends on the platform, such as the maximum value of 4G on the 32bit platform.  keep_files_on_create={on| OFF} The default value for this entry is off. When you create a table of type MyISAM, Mysqld creates one for it in the data catalog. MyD file and a. myi file, if a file with the same name already exists in the data directory, the default setting is overwrite, and when this variable is set to OFF, an error message is returned. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  key_buffer_size= #所有线程共享的, the size of the index buffer used for MyISAM tables, is also commonly referred to as key cache. A larger value is allowed on a 32-bit platform with a maximum value of 4g,64 bit platform, but the more effective value depends on the size of the available physical RAM resources and the limit on the amount of RAM available per process. When Mysqld starts, it allocates as much RAM space as is close to the specified size to key_buffer_size, not necessarily the specified size. Increasing this value speeds up the processing of indexes on read and write operations, so in a scenario where MyISAM is the primary table type, this value can be set to 25% of the physical memory space, however, a value larger than this may cause system performance to degrade, such as setting to 50% between physical content Can lead to serious performance problems. What's more, the memory needs of other types of storage engines need to be considered. When you insert multiple rows of data into a table, using lock tables speeds up its execution. Of course, it is also possible to observe the key_read_requests, Key_reads, key_write_requests and key_writes values in the show status command output to determine the performance of mysqld. Under normal circumstances, the ratio of key_reads/key_read_requests should be less than 0.01, while Key_writes/key_wriThe ratio of te_requests is usually close to 1, but in scenarios where the Delay_key_write option is enabled, this ratio may be smaller.  key_cache_block_size= #MyISAM存储引擎的索引存放于 ". MYI "file, each". MYI "files are composed of file headers and actual index data. In the. MYI ", it is logically represented by multiple index blocks, but not by physical structures. Physically, the index is stored as a block of files (file block) in the form of a disk. The index information cached in key cache is organized in the form of cache blocks, which are a set of storage spaces of the same size, and ". MYI is similar to the block (file block) where the files are physically stored. When a query statement retrieves table data by index, it first checks that the index cache (key_buffer_cache) has stored the required index information and, if not, reads ". MYI "file that reads the corresponding index data into the memory space in the key cache and stores it as a cache block format. At this point, if no free cache block space is available in the entire key cache, MYSQLD will purge some cache blocks through the LRU algorithm. The key_cache_block_size parameter is used to set the cache block size, which defaults to 1024. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  key_cache_division_limit= #实际上, the LRU algorithm used in MySQL key cache is not the same as the traditional algorithm just through the frequency of access and the last access time through a unique list of the implementation, Instead, it is divided into two parts. Some of these are used to store hot Chain, which are used more frequently, and are called hot-area, while the other part is used to store Warm cache Block (Warm Chain), which is less frequent, and is also known as Warm area. This is done primarily to protect the more frequently used cache block from being swapped out. The Key_cache_division_limit parameter is used to tell MySQL how to divide the entire cache chain into hot chain and warm chain, with a parameter value of warm chain as the percentage value of the entire chain. Set the range 1~100, the system defaults to 100, that is, only warm Chain.  key_cache_Age_threshold= when the cache block in the #控制Hot area should be downgraded to warm area. The system default value is 300 and the minimum can be set to 100. The smaller the value, the greater the likelihood of being demoted.  large_files_support={yes|no}mysqld the support for large files is specified in compile-time compilation options. Its scope is global and is not a dynamic variable. A dedicated parameter on the  large_pages={yes|no}linux platform to set whether the MYSQLD supports the use of large memory pages. With large memory, you can increase the hit ratio of the TLB to improve system performance. Its scope is global and can be used in configuration files as non-dynamic variables.  large_page_size= #Linux平台上专用的参数, used to set the size of large memory pages used by mysqld, typically 4MB, on other platforms the value of this parameter is 0, which is disabled. Its scope is global and is not a dynamic variable.  last_insert_id the value of this parameter is returned by the last_insert_id () function, and its corresponding exact value is stored in the binary log when using last_insert_id () in the statement that updates the table.  lc_messages=string the locale of the error message (that is, the locale), MYSQLD converts this value to a language name and returns an error message in conjunction with the locale-dependent language file in the path specified by the Lc_messages_dir parameter. The scope is global or session level and can be used for configuration files, which belong to dynamic variables. The  LC_MESSAGES_DIR=/PATH/TO/SOME_DIR error message is stored in the directory, and the error message is returned through the language area set with the Lc_messages parameter. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  lc_time_names=string sets the locale of the language, based on the locale, to display date information such as days, months, and abbreviations, with values such as en_US, but not related to the system locale. This setting affects the output of the Date_format (), Dayname (), and MonthName () functions. The scope is global or session level, which belongs to the dynamic variable.  local_infile={yes|no} Sets whether mysqld supports the use of the load DATA infile statement. The default is on. The scope is the global level, which belongs to the dynamic variable.  local_wait_timeout= #以秒为单位设定所有SQL语句等待获取元数据锁 (Metadata lock), the default is 31536000 (1 years), the valid value range is 0-31536000. The SQL statements that affect include DML and DDL statements for tables, views, stored procedures, and stored functions, as well as lock TABLES, FLUSH TABLES with READ Lock, and handler statements. However, all objects that do not include the system tables in the MySQL database and the grant or REVOKE statements on the table used to log the logs, but include select or UPDATE statements. In addition, this timeout length is applied to each metadata lock respectively, so that a statement may hold multiple metadata locks, then its last effective timeout length is likely to be the setting value. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  LOCKED_IN_MEMORY={YES|NO}MYSQLD is locked in memory using the--memlock option. The scope is the global level, which is a non-dynamic variable.  log={yes|no} Whether logging information for all statements is enabled in the General query log, which is usually off by default. MySQL 5.6 has deprecated this option.  log-bin={yes|no} whether the binary log is enabled, if the--log-bin option is set for MYSQLD, the value is on, otherwise it is off. It is used only to show if the binary log is enabled and does not reflect the set value of the Log-bin. The scope is the global level, which is a non-dynamic variable.  log_bin_trust_function_creators={true| FALSE} This parameter is only valid when binary logging is enabled, and is used to control whether the creation of a storage function is prohibited when creating a stored function if it causes an unsafe event to log the binary log condition. The default value is 0, which means that unless the user has super privileges in addition to the create routing or alter routine permission, it is forbidden to create or modify the storage function, and also requires that the deterministic property be used when creating the function. Or else it comes with reads SQL data or no SQL properties. When you set its value to 1, these restrictions are not enabled. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  log_error=/path/to/error_log_filename defines the error log file. The scope is global or session level and can be used for configuration files, which are non-dynamic variables.  log_output={table| file| NONE} defines how the general query log and the slow query log are saved, which can be TABLE, file, NONE, can also be a combination of table and file (separated by commas), the default is table. If none is present in the combination, the other settings are invalidated, and no relevant log information is logged, regardless of whether logging is enabled or not. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  log_query_not_using_indexes={on| OFF} Sets whether query operations that do not use indexes are logged to the slow query log. The scope is the global level, which can be used for configuration files, which belong to dynamic variables. The  log_slave_updates is used to set whether the from server in the replication scenario logs updates received from the primary server into the native binary log. The effective setting of this parameter requires the binary logging feature to be enabled from the server.  log_slow_queries={yes|no} whether the slow query log is logged. A slow query is an event where the execution time of a query exceeds the length set by the Long_query_time parameter. MySQL 5.6 Modifies this parameter in order to Slow_query_log. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  log_warnings= #设定是否将警告信息记录进错误日志. The default setting is 1, which means it is enabled, it can be set to 0 to disable, and a value greater than 1 indicates that error messages for the "Failed connections" and "denied access" classes that are generated when a new connection is initiated are also logged in the error log.  long_query_time= #设定区别慢查询与一般查询的语句执行时间长度. The execution time of the statement here is actual execution times, not the CPU time, so it is more prone to slow queries on servers with heavier loads. Its minimum value is 0, the default value is 10, and the unit is seconds. It also supports the resolution of the millisecond level. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  low_priority_updates={true| FALSE} to reduce the priority of the update operation, only valid for storage engines that support only table-level locks, such as MyISAM, memory, or merge. A value of 1 indicates that all INSERT, UPDATE, delete, or lock table write statements can only be executed if there are no select or lock table read statements waiting to be executed. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  lower_case_file_system={on| Off} is used to describe whether the file system where the data directory resides distinguishes between file name character case, off is case sensitive, On means case insensitive. This variable is read-only and is case-sensitive depending on the file system.  LOWER_CASE_TABLE_NAME={0|1|2} Sets whether to differentiate the case of characters in a table, table alias, or database name. 0 is case-sensitive, 1 is case-insensitive and is stored as lowercase characters, and 2 is stored in a given case but is not case-sensitive. For a file system that does not natively support file name capitalization, it should not be set to a value of 0, whereas on a system set to a value of 0, access to a MyISAM storage engine with a name that does not correspond to a case may cause the index file to crash. On Windows systems, its default value is 1,mac OS x on its default value of 2. For the InnoDB storage engine, it should be set to 1 regardless of the platform on which it is based. At the same time, you should not use different settings for the master from the hosts in the replication cluster, or it may cause replication to fail. The scope is the global level, which can be used for configuration files, which are non-dynamic variables.  max_allowed_packet={yes|no} sets the maximum length of a single message or any intermediate string (intermediate string), in bytes. Message buffering is set by the Net_buffer_length parameter, but it can eventually grow as needed to the size of the Max_allowed_packet parameter setting. The default value for this parameter is small, and in a scene that uses a BLOB column or a long string, you should increase its value to the length of the maximum BLOB data that can be accommodated. The protocol itself limits this value to a maximum of 1G, the parameter only accepts a value of 1024 integers, and a non-1024 integer multiple will be automatically rounded to a value that is more than the nearest 1024 integer. The length of the message that finally comes into effect depends on the client setting. The default setting for client programs, such as MySQL or mysqldump, is 1G. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  max_connect_errors= #设定客户端连接至mysqld时的最大错误尝试次数. When a client attempts to connect to the current mysqld number of errors the connection reaches the value set by Max_connect_errors, its subsequent connection attempts are directly blocked. The administrator can clear the host cache with the FLUSH hosts statement or the mysqladmin flush-hosts command to unlock access to previously blocked hosts. If a client successfully establishes a connection before the number of its error attempts has reached the value set by this parameter, the counter of its number of error attempts will be emptied. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  max_connections= #设定mySqld the maximum number of concurrent connections that the client is allowed to initiate concurrently. Increasing this value increases the number of file descriptors that the MYSQLD process needs to access at the same time. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  max_delayed_threads= #设定为INSERT The maximum number of threads that the delayed statement can start. If the current number of related threads has reached the value set by this parameter, subsequent insert delayed statements will disregard their delayed properties. If you set its value to 0,mysqld, no thread will be created for insert delayed, which disables the delayed feature. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  max_error_count= #设定为SHOW The maximum number of error, warning, or attention information entries reserved by the errors or show warnings statement. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  max_heap_table_size= #设定每个用户创建的MEMORY表所能够使用的最大内存空间. Modifying its value has no effect on the memory table that is currently created unless the table has been rebuilt with the CREATE TABLE, ALTER table, or TRUNCATE table. Its minimum value is 16384, and the unit is byte. The maximum value is limited to the platform word length, such as 32-bit platform 4G. This parameter is used in conjunction with the Tmp_table_size parameter to limit the size of the internal memory table. In addition, Max_heap_table_size will not be copied. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  max_insert_delayed_threads= #max_delayed_threads的同义词. The scope is global level, dynamic variable.  max_join_size= the maximum value of a #设定SELECT语句执行时所能够检查的行数 (single table) or row combination (multi-table query). This parameter prevents the use of a key from being used for a long time, so it is especially useful in a query scenario where the user often uses the without a WHERE clause. The valid value range is 1-18446744073709551615, and the default is 18446744073709551615, which can be understood as unrestricted. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  max_length_for_sort_data= #filesort算法改进版所能够使用的字段最大长度值. The valid value range is 4-8388608.MySQL's filesort algorithm has two versions, that is, the original version and the modified version, the field length is greater than the Max_length_for_sort_data setting will use the original version, less than this parameter value of the use of the modified version in the sorting buffer (sort buffers) to complete the sorting. Using the modified version algorithm when using out-of-bounds fields exceeds a specified length will cause the modified version of the algorithm to execute more slowly, rather than faster, because more I/O operations may be required. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  max_long_data_size= #设定可以由mysql_stmt_send_long_data () The maximum length of the parameter value transmitted by this C API function, if not set at mysqld startup, it defaults to Max_ The value of the Allowed_packet variable. MySQL 5.6 has deprecated this variable. The scope is the global level, which can be used for configuration files, which are non-dynamic variables.  max_prepared_stmt_count={0..1048576} Sets the total number of prepared statements that are executed in all connection sessions allowed by MYSQLD. A large number of prepared statements can consume a large amount of memory resources, which poses a potential "denial of service" risk, so the value of this parameter should be carefully set according to production needs. If the newly set value is lower than the total number of prepared statements that are currently open, it does not affect the original statement, but no longer accepts new execution requests until a new amount of free is available. The default value is 16382,0, which indicates that the prepare statement is disabled. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  max_relay_log_size={4096..1073741824} sets the maximum size of the trunk log from the server, which automatically scrolls the trunk log when it reaches this limit. With this parameter value of 0 o'clock, Mysqld will use the Max_binlog_size parameter to set the log file volume limit for both binary and trunk logs. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  max_seeks_for_key={1:18446744073709547520} Sets the maximum number of lookups allowed when a query is executed based on a key. When a table is searched for a row that matches the query criteria by scanning the index, the MySQL optimizer assumes that the number of lookups does not need to exceed the value set by this parameter, regardless of the cardinality of the index. A smaller value can force MySQL to prefer an index scan rather than a table scan. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  max_sort_length={4:8388608} set MYsqld the number of bytes used when performing numeric sorting, the remaining will be ignored. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  max_sp_recoursion_depth={0:255} Sets the maximum number of times a stored procedure can be called recursively. Recursive calls increase the need for line stacks space, so increasing the value of this parameter may also require you to adjust the value of the Thread_stack parameter at startup. The default value is 0, which means no recursion, and a maximum value of 255. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  max_user_connections={0:4294967295} Sets the maximum number of concurrent connection requests that an individual user is allowed to initiate simultaneously to mysqld. The default value is 0, which means no upper limit. You can specify a global parameter value for MYSQLD for this parameter, or you can set this parameter value for a user account to read-only to set a valid concurrency limit (implemented through the grant statement). This means that if the user has a non-0 value for this parameter, the qualification will prevail; otherwise, mysqld sets the user's parameter value to a global value. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  max_write_lock_count= #mysqld已施加的写锁个数达到此参数值指定的个数时, some pending read requests will be allowed to be processed. The minimum value is 1, and the maximum value depends on the platform word length. The scope is the global level, which is a non-dynamic variable.  metadata_locks_cache_size={1:1048576} Sets the upper limit of the MYSQLD metadata lock cache. This cache can be used to avoid creating or destroying synchronization objects (synchronization object), which is especially useful for operating systems such as Windows XP, which are expensive to operate. The default value is 1024. The scope is the global level, which is a non-dynamic variable.  min_examined_row_limit= #所检查的行数低于此参数设定的数值的查询操作将不被记入慢查询日志. The default value is 0, and the maximum value depends on the platform word length. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  myisam_data_point_size={2.7} If the Max_Rows option is not set when the MyISAM table is created, this parameter sets the size of its default pointer, in bytes. The default value is 6. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  myisam_max_sort_file_size= #设定在MySQL在使用REPARE tablE, ALTER table or load DATA infile command when re-creating an index for the MyISAM table is the maximum volume of temporary files that can be used, in bytes. If the size of the temporary file is larger than this upper value, MYSQLD will create the index using the key cache. The default value is 2G, and increasing this value increases MySQL performance if the MyISAM index file itself is larger than this value and the file system on which it resides has enough free space. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  myisam_mmap_size= #设定基于内存映射压缩MyISAM文件时可以使用的内存上限. In scenes with many compressed formats MyISAM tables, lowering this value can help reduce the likelihood of memory swapping. Its minimum value is 7, and the default and maximum values depend on the number of platform bits. The scope is the global level, which can be used for configuration files, which are non-dynamic variables.  myisam_recover_options the value that is used to save the mysqld command-line option--myisam-revover-options. This option is used to set the recovery mode of the MyISAM storage engine with an acceptable value of off, default (recovery mode, but no backup, forced or fast detection), backup (if the data changes during recovery, the original table file is backed up to Table_ Name-datatime. BAK), force (forced recovery, even if the data is lost) and quick (quick fix), you can specify multiple values for this option at the same time as comma delimited. You can also not provide any parameter values for it, defaults to default, and "" is off, which disables recovery mode. If recovery mode is enabled, MYSQLD detects if a MyISAM table is marked as corrupt or shuts down gracefully each time it is opened. If it is damaged, mysqld will try to fix it, and if it is not properly closed, MYSQLD will detect it.  myisam_repair_threads= #在通过排序修复过程中为MyISAM表创建索引的线程的个数, default is 1, and if given a value greater than 1, you can start multiple threads that create indexes in parallel (each index can be created by only one thread). The maximum value depends on the number of platform bits. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  myisam_sort_buffer_size= The amount of buffer space that can be used in order to sort an index #在REPAIR table procedure, or when an index is added to MyISAM tables by the Create Index/alter table. The minimum value is 4, and the maximum value that can be used on a 32-bit system is 4294967295, which is the 4g;64 bitLarger space can be used on the system. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  myisam_stats_method={nulls_equal|nulls_unequal|nulls_ignored} defines how null values are handled when statistics related to the index distribution are collected for the MyISAM table. Nulls_equal means that all null values are treated as the same value, nulls_unequal means that all null values are treated as different values, and nulls_ignored means that all null values are ignored. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.  myisam_use_mmap={on| OFF} can use memory mappings when reading and writing to MyISAM tables. The default value is off. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.  net_buffer_length={1024.1048576} Each client thread has a connection buffer (connection buffer) and a result buffer, which can be set to the size of both buffers. However, they can dynamically grow to the size set by the Max_allowed_packet parameter as needed. However, after each SQL statement is complete, the resulting buffer shrinks to the size defined by the Net_buffer_length parameter. In general, you do not need to modify the value of this parameter unless it is in a tight-memory scenario that can be adjusted to the length of the SQL statement expected by the client. If the SQL statement exceeds this length, the connection buffer automatically adjusts its value. Its default value is 16384, and the unit is bytes. The scope is global or session level, can be used for configuration files, is a dynamic variable, but for session level, this variable is read-only.  net_read_timeout= #设定mysqld等待从客户端接收更多数据的超时时长, the default value is 30. The scope is global and session level and can be used for configuration files, which belong to dynamic variables.  net_write_timeout= #设定mysqld等待向客户端传输数据的超时时长, the default value is 60. The scope is global and session level and can be used for configuration files, which belong to dynamic variables.  net_retry_timeout= #设定mysqld与客户端的通讯中断时 The number of retries that need to be retried before terminating the connection to the client. The default value is 10, and its maximum value depends on the number of platform bits. The scope is global and session level and can be used for configuration files, which belong to dynamic variables.  new={on| OFF} for MySQL 4.0 to enable support for MySQL version 4.1Some of the new features on the ability, but still can remain backwards compatible. The MySQL 5.5 does not need to be set, so its value is off.  old={on| Off} is used to define variables compatible with older versions of MySQL, which are disabled by default, but can be enabled at mysqld startup to be compatible with older versions of MySQL.   Reference Links  http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_insert_id

MySQL 5.5 Configuration item one (GO)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.