MySQL 5.5 server variables (1)

Autocommit = {0 | 1} sets whether MySQL transactions are automatically committed. 1 indicates immediate submission, and 0 indicates explicit submission. Global or session is a dynamic variable that can be used in the configuration file (but not in versions earlier than 5.5.8. Automatic_sp_privileges = {0 | 1} sets whether the MySQL server grants the EXECUTE and alter routine permissions on the created storage ROUTINE to the creation of the storage ROUTINE, the default value is 1 (grant the two permissions to the Creator ). The scope is global. Back_log = # When a MySQL main thread receives a large number of connection requests in a short time, it will take some time to detect a thread and start a new thread for the new request, the value of the back_log parameter is the number of accepted requests that arrive within a short period of time and waits for the main MySQL thread for subsequent processing. The function scope is global. You can use a configuration file that is not a dynamic variable. Basedir = PATH,-B PATH is used to specify the MySQL installation directory. All other commonly used relative paths are relative to the PATH here. It has a global scope and can be used in the configuration file but is not a dynamic variable. Bind-address = ADDR specifies the IP address of the mysqld service listener. The default value is 0.0.0.0, indicating all IP addresses configured on the local machine. It has a global scope and can be used in the configuration file but is not a dynamic variable. Binlog-format = {ROW | STATEMENT | MIXED} specifies the binary log type. The default value is STATEMENT. If the binary log format is set but the binary log is not enabled, the warning log is generated and recorded in the error log at MySQL startup. Global or session is used in configuration files and is a dynamic variable. Buld_insert_buffer_sizeMyISAM engine uses a special tree structure cache to accelerate batch INSERT operations, such as INSERT... SELECT, INSERT... VALUES (...), (...),... and load data infile commands. For each thread, the buffer size is independent, and the configured value unit is byte. The valid value range is 0 to the power of "2 ^ CPU length, the default size is 8 MB. Global or session is used as the configuration file and is a dynamic variable. Chroot = PATH,-r PATH sets the working directory of MySQL when working in chroot mode. This is a recommended mechanism in environments where security issues are particularly important. However, the operation of commands such as load data infile may be affected. Can be used in configuration files. Console is only used for Windows platform options. It is used to send error log information to standard input and error output. This function is equally effective even if the -- log-error option is configured. Concurrent_insert = {NEVER | AUTO | ALWAYS} or use {0 | 1 | 2} to set whether to allow parallel INSERT and SELECT statements on the MyISAM table. It has a global scope and can be used in configuration files and is a dynamic variable. Connect_timeout = # number of seconds that the mysqld server waits before responding to the "failed handshake operation" message to the client. The default value is 10 seconds. It has a global scope and can be used in configuration files and is a dynamic variable. Core-file: When the MySQL process goes down, the information is saved as a core file. on Linux, the core file is usually saved to the working directory of the current process and named as core. pid, whose file name suffix pid is the process Number of the current process; For MySQL, the SAVE directory is the data file directory. Datadir = PATH,-h PATH specifies the data directory of the MySQL service. It has a global scope and can be used in the configuration file but is not a dynamic variable. Default_storage_engine = {Engine_Name} sets the default storage engine of the MySQL server. Before MySQL 5.5.5, the default value is MyISAM, and later versions are InnoDB by default. It has a global scope and can be used in configuration files and is a dynamic variable. Delay-key-write = {ON | OFF | ALL} is only used for MyISAM tables, and the DELAY_KEY_WRITE option is required during table creation. When enabled, the key buffer is not cleared during each index update, but is cleared only when the table is closed. OFF indicates that DELAY_KEY_WRITE is ignored, and ON indicates that MySQL accepts any DELAY_KEY_WRITE option used when creating TABLE. ALL indicates that ALL newly opened tables follow this feature. Error-count the number of error messages caused by the previous SQL statement. This is a read-only variable. Event-schedabled = {ON | OFF | DISABLED} sets whether the MySQL server is enabled and whether to start Event schedabled. OFF indicates stop, which is the default value; ON indicates start, which is running and executes all scheduling transactions; DISABLED indicates that Event Scheduler is DISABLED, that is, it cannot be switched to start. It has a global scope and can be used in configuration files and is a dynamic variable. Expire_logs_days = {0 .. 99} sets the Expiration days of binary logs. binary log files exceeding the specified days will be automatically deleted. The default value is 0, indicating that the expired automatic deletion function is not enabled. If this function is enabled, automatic deletion usually occurs at MySQL startup or FLUSH logs. It has a global scope and can be used in configuration files and is a dynamic variable. External_user = name When performing User Authentication Based on the authentication plug-in on the MySQL server, this plug-in will initiate a connection request to the user as another user for permission check purposes, in this way, external users can act as the proxy users of the second user and have all the permissions of the second user. When MySQL's internal authentication mechanism is used or no plug-in sets a value for it, the value of this variable is NULL. The function scope is session level. It cannot be used in configuration files and is a non-dynamic variable. Flush = {ON | OFF} sets whether the MySQL server executes data synchronization for each SQL statement separately (writing data to the disk ). Under normal circumstances, MySQL executes data synchronization for each statement and submits the subsequent synchronization process to the operating system. The default value is OFF. It has a global scope and can be used in configuration files and is a dynamic variable. Flush-time = {0 ..} if the value is not 0, the MySQL server will close all opened tables at a time specified by flush_time and use them to release all resources and synchronize data to the disk. This feature must be enabled only when system resources are extremely scarce. The default value is 0. It has a global scope and can be used in configuration files and is a dynamic variable. Foreign-key-checks = {0 | 1} determines whether it is a foreign key constraint for InnoDB table check. The default value is 1, that is, check. Disable this function to avoid the 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} sets whether to enable query logs. The default value is determined by whether the -- general_log option is used when mysqld is started. If this option is enabled, the output location is defined by the -- log_output option. If the value of log_output is set to NONE, the query log is enabled, and no log information is recorded. It has a global scope and can be used in configuration files and is a dynamic variable. General_log_file = FILE_NAME: name of the log file to be queried. The default value is "hostname. log ". It has a global scope and can be used in configuration files and is a dynamic variable. Group_concat_max_len = {4 ..} sets the maximum length of the value returned by the GROUP_CONCAT () function, which is 1024 by default. Valid value range: 4 to the power of "2 ^ CPU length. It is a global or session-level configuration file that is a dynamic variable. Have-compress = {YES | NO} whether the zlib compression library can be used by the MySQL server. When the value is NO, the COMPRESS () and UNCOMPRESS () functions are unavailable. Have_crypt = {YES | NO} whether the system call of crypt () can be used by the MySQL server. When the value is NO, the ENCRYPT () function is unavailable. Have_csv = {YES | NO} When mysqld supports the CSV engine, YES is used; otherwise, NO is used. Have_dynamic_loading = {YES | NO} mysqld supports YES when dynamically loading the plug-in; otherwise, NO. Have_geometry = {YES | NO} If mysqld supports the spatial data type, the value is YES; otherwise, the value is NO. Have_innodb = {YES | NO} When mysqld supports the InnoDB Storage engine, YES; otherwise, NO. Have_openssl = {YES | NO} is the alias of the have_ssl option; have_ssl = {YES | NO} mysqld supports YES for SSL connections; otherwise, NO. DISABLED indicates that SSL support is enabled during mysqld compilation, but the correct ssl-xxx class (such as ssl_cert) option is not available when mysqld is started. Have_partitioning = {YES | NO} Does mysqld support partitioning? This option has been removed from the MySQL-5.6 and can be obtained using show engines. Have_profiling = {YES | NO} When mysqld supports statement performance analysis, the value is YES; otherwise, the value is NO. If the profiling function is supported, the -- profiling variable is used to control whether to enable this function. Have_query_cache = {YES | NO} If mysqld supports query cache, the value is YES; otherwise, the value is NO. Have_rtree_keys = {YES | NO} If mysqld supports the RTREE index, the value is YES; otherwise, the value is NO. The RTREE index is used for the spatial index of the MyISAM table. Have_symlink = {YES | NO} If mysqld supports symbolic links, the value is YES; otherwise, the value is NO. On Unix hosts, this function is useful for Data Directories and index directories. Hostname = STRINGmysqld the host name is assigned to this variable when the server is started. It is a non-dynamic variable with a global scope. The synonym of the identitylast_insert_id variable. Its main purpose is to be compatible with other database systems. Session-level variables. Init_connect = STRING indicates one or more SQL statements (separated by semicolons) executed before each client establishes a connection with mysqld. However, for users with SUPER permissions, this function is invalid. For example, the autocommit variable does not appear in MySQL before 5.5.8. to disable the autocommit function for each user by default, you can use init_connect = 'set autocommit = 0' in the mysqld configuration file. Of course, you can also use the set global init_connect = 'set autocommit = 0' command. The scope is global. It can be used in configuration files and is a dynamic variable. Init-file =/PATH/TO/SOMEFILE defines the initialization file used when mysqld is started. Each line of this file contains a separate SQL statement (no comment, does not need and cannot use statement Terminator), and will be executed one by one during mysqld startup. The value of this variable is used when the INSERT or ALTER_TABLE statement is executed for fields with AUTO_INCREMENT set for a table. It is mainly used for Binary logs. Interactive_timeout = # number of seconds before the mysqld process waits for a subsequent command of the Interactive Client that has established a connection. The default value is 28800. It is a global or session-level configuration file that is a dynamic variable. Join_buffer_size = # The minimum buffer that mysqld can use for flat index scanning (plain index scans), range index scanning, or full table scanning without indexes. Under normal circumstances, adding an index is an effective way to speed up the execution of the connection. If you cannot add an index, increasing the value of join_buffer_size can speed up the execution of the full connection. Each full connection between two tables uses a separate join buffer. Non-index-based complex full connection between multiple tables may use multiple join buffer. Setting this variable value greater than the size of each matched row does not bring too much benefit. Therefore, this value should not be set too large globally. We recommend that you use a smaller global setting to set a greater limit when a larger connection is used in a required session. The maximum value depends on the platform. For example, the maximum value of a 32-bit platform is 4 GB. Keep_files_on_create = {ON | OFF} The default value is OFF. Mysqld creates a table of the MyISAM type in the data directory. MYD file and. MYI file. If a file with the same name already exists in the data directory, it is set to overwrite by default. If this variable is set to OFF, an error message is returned. It can be used in configuration files at the global or session level and is a dynamic variable. Key_buffer_size = # the size of the index buffer space shared by all threads for the MyISAM table, also known as key cache. On a 32-bit platform, the maximum value is 4 GB. On a 64-bit platform, a larger value is allowed, however, the valid value depends on the size of available physical RAM resources and the size limit of available RAM for each process. When mysqld is started, it allocates RAM space close to the specified size to key_buffer_size, instead of the specified size. Increasing this value can accelerate the indexing processing speed during read/write operations. Therefore, you can set this value to 25% of the physical memory space in an application scenario where MyISAM is the main table type, however, a value greater than this may lead to a reduction in system performance. For example, a 50% setting between physical content may cause serious performance problems. What's more, we also need to consider the memory needs of other types of storage engines. When multiple rows of data are inserted to a table at the same time, the use of lock tables will accelerate the execution process. Of course, you can also observe the Key_read_requests, Key_reads, Key_write_requests, and Key_writes values in the output of the show status command to determine the performance of mysqld. Under normal circumstances, the ratio of Key_reads/Key_read_requests should be less than 0.01, while the ratio of Key_writes/Key_write_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 = # indexes of the MyISAM storage engine are stored in the ". MYI" file. Each ". MYI" file consists of the file header and the actual index data. In the related concepts of ". MYI", it logically involves multiple Index blocks, but not physical structures. Physically, indexes are stored on disks in the form of File blocks. The index information cached in the Key Cache is organized and stored in the Cache Block form. The Cache Block is a set of storage spaces of the same size. MYI is similar to a File Block in the physical storage of files. When a query statement uses an index to retrieve table data, it first checks whether required index information has been stored in the index cache (key_buffer_cache). If not, it reads ". "MYI" file, which reads the corresponding index data into the memory space of the Key Cache and stores the data in the Cache block format. If no idle Cache block space is available in the entire Key Cache, mysqld will clear some Cache blocks through the LRU algorithm. The key_cache_block_size parameter is used to set the cache block size. The default value is 1024. The scope is global. It can be used in configuration files and is a dynamic variable. Key_cache_division_limit = # In fact, the LRU Algorithm Used in MySQL's Key Cache is not just implemented through a unique linked list through the access frequency and the last access time, just like the traditional algorithm, it is divided into two parts. Some of them are used to store Hot Cache Lock (Hot Chain), which is called Hot Area, and others are used to store Warm Cache blocks that are not frequently used ), it is also called Warm Area. The main purpose of this operation is to protect frequent Cache blocks from being easily 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. The parameter value is the percentage of Warm Chain to the entire Chain. Set range: 1 ~ 100, the default value is 100, that is, only Warm Chain. Key_cache_age_threshold = # controls when the Cache Block in the Hot Area should be downgraded to the Warm Area. The default value is 300, and the minimum value can be 100. The smaller the value, the more likely it is to be downgraded. Large_files_support = {YES | NO} whether the compilation option of mysqld specifies support for large files. Its scope is global and non-dynamic. Large_pages = {YES | NO} special parameters on the Linux platform, used to set whether mysqld supports using large memory pages. Using large memory can increase the TLB hit rate and improve system performance. Its scope is global and can be used in the configuration file as non-dynamic variables. Large_page_size = # special parameter on Linux, used to set the size of the Large Memory Page used by mysqld, usually 4 MB. The value of this parameter is 0 on other platforms, disabled. Its scope is global and non-dynamic. Last_insert_id the value of this parameter is returned by the LAST_INSERT_ID () function. When LAST_INSERT_ID () is used in the update TABLE statement, the exact value is stored in the binary log. Lc_messages = region of the STRING error message (that is, the language region). mysqld converts this value to the language name, the error message is returned in combination with the region-related language file in the path specified by lc_messages_dir. It can be used in configuration files at the global or session level and is a dynamic variable. Lc_messages_dir =/PATH/TO/SOME_DIR the directory where the error information is stored. The error information is returned through the language area set with the lc_messages parameter. It can be used in configuration files at the global or session level and is a dynamic variable. Lc_time_names = STRING sets the language region based on the language region to display date information such as day, month, and abbreviated method. Its value is such as en_US, but it is not related to the locale of the system. This setting affects the output results of the DATE_FORMAT (), DAYNAME (), and MONTHNAME () functions. It is a dynamic variable at the global or session level. Local_infile = {YES | NO} sets whether the mysqld supports the load data infile statement. The default value is ON. It is a dynamic variable at the global level. Local_wait_timeout = # Set the timeout duration for all SQL statements to wait for the metadata lock to be obtained in seconds. The default value is 31536000 (1 year) and the valid value range is 0-31536000. The affected SQL statements include DML and DDL statements used for TABLES, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements. However, all objects it uses do not include the GRANT or REVOKE statements in the mysql database system tables and tables used to record logs, but include SELECT or UPDATE statements. In addition, the timeout period is applied to each metadata lock respectively. Therefore, a statement may hold multiple metadata locks, so the final timeout period may take longer. It can be used in configuration files at the global or session level and is a dynamic variable. Locked_in_memory = {YES | NO} Does mysqld use the -- memlock option to lock it in the memory. It is a non-dynamic variable at the global level. Log = {YES | NO} indicates whether to enable logging of all statements in general query log. The default value is OFF. MySQL 5.6 has been deprecated. Whether to enable binary log in log-bin = {YES | NO}. If the -- log-bin option is set for mysqld, the value is ON; otherwise, the value is OFF. It is only used to show whether binary logs are enabled, and does not reflect the set value of log-bin. It is a non-dynamic variable at the global level. Log_bin_trust_function_creators = {TRUE | FALSE} this parameter is only valid when binary logs are enabled, it is used to control whether to prohibit the creation of stored functions under binary log conditions if unsafe events occur during the creation of stored functions. The default value is 0, indicating that unless you have the SUPER permission in addition to the create routing or alter routine permissions, you are not allowed to CREATE or modify a storage function, you must also use the DETERMINISTIC attribute when creating a function. Otherwise, the reads SQL data or no SQL attribute is attached. If the value is set to 1, these restrictions are not enabled. The scope is global. It can be used in configuration files and is a dynamic variable. Log_error =/PATH/TO/ERROR_LOG_FILENAME defines the error log file. It can be used in configuration files at the global or session level and is a non-dynamic variable. Log_output = {TABLE | FILE | NONE} defines the storage methods of general query logs and slow query logs, which can be TABLE, FILE, NONE, it can also be a combination of tables and files (separated by commas). The default value is TABLE. If NONE appears in the combination, other settings will be invalid. At the same time, no log information is recorded whether or not the log function is enabled. The scope is global. It can be used in configuration files and is a dynamic variable. Log_query_not_using_indexes = {ON | OFF} indicates whether to record query operations that do not use indexes to slow query logs. The scope is global. It can be used in configuration files and is a dynamic variable. Log_slave_updates is used to set whether the slave server in the replication scenario records the update operations received from the master server into the binary log of the local machine. The binary log function must be enabled on the slave server. Log_slow_queries = {YES | NO} indicates whether to record slow query logs. A slow query is an event that exceeds the scheduled duration set by the long_query_time parameter. MySQL 5.6 changed this parameter to slow_query_log. The scope is global. It can be used in configuration files and is a dynamic variable. Log_warnings = # Set whether to record warning information into error logs. The default value is 1, indicating that it is enabled. You can set it to 0 to disable it; when the value is greater than 1, it indicates that the "failed connection" and "Access Denied" Errors generated when the new connection is initiated are also recorded in the error log. Long_query_time = # Set the difference between the statement execution time of a slow query and a general query. The statement execution time here is the actual execution time, rather than the execution time on the CPU. Therefore, the server with heavy load is more prone to slow queries. The minimum value is 0, and the default value is 10, in seconds. It also supports millisecond-level resolution. It can be used in configuration files at the global or session level and is a dynamic variable. Low_priority_updates = {TRUE | FALSE} indicates whether to reduce the priority of update operations. It is only valid for storage engines that only support table-level locks, such as MyISAM, MEMORY, or MERGE. If the value is 1, all INSERT, UPDATE, DELETE, or lock table write statements can be executed only when the SELECT or lock table read statements are not waiting for execution. It can be used in configuration files at the global or session level and is a dynamic variable. Lower_case_file_system = {ON | OFF} is used to describe whether the file system in which the data directory is located is case-sensitive. OFF indicates case-sensitive, and ON indicates case-insensitive. This variable is read-only. Whether it is case sensitive depends on the file system. Lower_case_table_name = {0 | 1 | 2} specifies whether to distinguish between case-sensitive characters in the table, table alias, or database name. 0 indicates case-sensitive. 1 indicates case-insensitive and all characters are stored in lower case. 2 indicates that the characters are stored in the specified case but not in the case. For file systems that do not support case-sensitive file names, the value of 0 should not be set. For systems with a value of 0, for MyISAM storage engine, accessing an index file with a name that does not match the upper or lower case may cause the index file to crash. In Windows, the default value is 1, and in Mac OS X, the default value is 2. For the InnoDB Storage engine, it should be set to 1, No matter what platform it is based on. At the same time, different settings should not be used for the master in the replication cluster. Otherwise, the replication may fail. The scope is global. It can be used in configuration files and is a non-dynamic variable. Max_allowed_packet = {YES | NO} sets the maximum length of a single message or any intermediate string (intermediate string), in bytes. The net_buffer_length parameter is used to set the message buffer, but it can be increased to the value set by the max_allowed_packet parameter as needed. The default value of this parameter is small. When BLOB columns or long strings are used, the value should be increased to the maximum BLOB Data Length. The Protocol itself limits the maximum value to 1 GB. The parameter only accepts values of 1024 integers. values other than 1024 integers are automatically rounded to the nearest 1024 integers. The length of the message that takes effect depends on the setting of the client. The default value of a client program such as mysql or mysqldump is 1 GB. The scope is global. It can be used in configuration files and is a dynamic variable. Max_connect_errors = # set the maximum number of failed attempts when the client connects to mysqld. When a client tries to connect to the current mysqld error count and reaches the value set by max_connect_errors, its subsequent connection attempts will be directly blocked. The administrator can clear the host cache by using the flush hosts statement or mysqladmin flush-hosts command to unlock access to previously blocked HOSTS. If a client successfully establishes a connection before the number of error attempts reaches the value set by this parameter, the counter of the number of error attempts will be cleared. The scope is global. It can be used in configuration files and is a dynamic variable. Max_connections = # set the maximum number of concurrent connections that the client can initiate at the same time. Adding this value will increase the number of file descriptors that mysqld processes need to access simultaneously. The scope is global. It can be used in configuration files and is a dynamic variable. Max_delayed_threads = # sets the maximum number of threads that can be started by the insert delayed statement. If the number of related threads has reached the value set by this parameter, subsequent insert delayed statements will ignore its DELAYED attribute. If the value is 0 in height, mysqld will disable the DELAYED function without creating any threads for insert delayed. The scope is global. It can be used in configuration files and is a dynamic variable. Max_error_count = # Set to the maximum number of error, warning, or note entries retained by the show errors or show warnings statements. It can be used in configuration files at the global or session level and is a dynamic variable. Max_heap_table_size = # set the maximum MEMORY space available for the MEMORY table created by each user. Modifying its value does not affect the currently created memory table unless you use the create table, alter table, or truncate table statement to recreate the TABLE. The smallest value is 16384 in bytes. The maximum value is limited by the platform font length. For example, the 32-bit platform is 4 GB. This parameter can be used together with the tmp_table_size parameter to limit the size of an internal memory table. In addition, max_heap_table_size is not copied. It can be used in configuration files at the global or session level and is a dynamic variable. Max_insert_delayed_threads = # synonym for max_delayed_threads. The scope is global and dynamic variables. Max_join_size = # set the maximum number of rows (single table) or row combinations (Multi-table queries) that can be checked when the SELECT statement is executed. This parameter can prevent the key-to-key from being used incorrectly and thus requires a long period of query operation. Therefore, this parameter is especially suitable for query scenarios WHERE users often use a WHERE clause without a WHERE clause. Valid values range from 1 to 18446744073709551615. The default value is 18446744073709551615, which can be understood as unlimited. It can be used in configuration files at the global or session level and is a dynamic variable. Max_length_for_sort_data = # Maximum length of fields that can be used by the optimized filesort algorithm. Valid value range: 4-8388608. MySQL's filesort algorithm has two versions: the original version and the modified version. If the field length is greater than that set by max_length_for_sort_data, the original version is used, if the value of this parameter is smaller than the value of this parameter, the modified version is used to complete the sorting in the sort buffer (sort buffer. If you use the version modification algorithm when the length of a field exceeds the specified length, more I/O operations may be required, resulting in slower execution of the modified algorithm rather than faster. It can be used in configuration files at the global or session level and is a dynamic variable. Max_long_data_size = # sets the maximum length of the parameter value that can be transferred by the c api function mysql_stmt_send_long_data (). If it is not set when mysqld is started, it defaults to the value of max_allowed_packet. MySQL 5.6 has discarded this variable. The scope is global. It can be used in configuration files and is a non-dynamic variable. Max_prepared_stmt_count = {0 .. 1048576} sets the total number of prepared statements executed in all connection sessions allowed by mysqld. Concurrent execution of a large number of prepared statements consumes a large amount of memory resources, which may lead to a potential "Denial of Service" risk. Therefore, you should carefully set the value of this parameter based on production needs. If the new value is lower than the total number of prepared statements currently opened, it will not affect the original statement, but will not accept new execution requests until there is a new free quota. The default value is 16382,0, indicating that the preparation statement is disabled. The scope is global. It can be used in configuration files and is a dynamic variable. Max_relay_log_size = {4096 .. 1073741824} sets the maximum size of the relay log on the server. When this limit is reached, the relay log is automatically rolled. If this parameter is set to 0, mysqld uses the max_binlog_size parameter to set the maximum size of log files for both binary logs and relay logs. The scope is global. It can be used in configuration files and is a dynamic variable. Max_seeks_for_key = {1 .. 18446744073709547520} sets the maximum number of searches allowed when a query is executed based on a key. When you search for rows matching query conditions in a table by scanning indexes, No matter what Index base is, the MySQL optimizer assumes that the number of queries does not exceed the value set by this parameter. Smaller values can force MySQL to prefer index scanning instead of table scanning. It can be used in configuration files at the global or session level and is a dynamic variable. Max_sort_length = {4 .. 8388608} sets the number of bytes used by mysqld to perform numerical sorting. The remaining bytes are ignored. The scope is global. It can be used in configuration files and is a dynamic variable. Max_sp_recoursion_depth = {0 .. 255} specifies the maximum number of times a stored procedure can be recursively called. Recursive calls increase the need for thread stack space. Therefore, to increase the value of this parameter, you may need to adjust the value of the thread_stack parameter at startup. The default value is 0, indicating that recursion is prohibited. The maximum value is 255. The scope is global. It can be used in configuration files and is a dynamic variable. Max_user_connections = {0 .. 4294967295} sets the maximum number of concurrent connection requests that a single user can initiate to mysqld at the same time. The default value is 0, indicating no upper limit. You can specify the GLOBAL parameter value for mysqld. You can also set this parameter value for a user account to read-only to set the maximum concurrency (implemented by the GRANT Statement ). This means that if the user has a non-0 value for this parameter, this limit prevails; otherwise, mysqld sets the user's value as a global value. It can be used in configuration files at the global or session level and is a dynamic variable. Max_write_lock_count = # When the number of write locks applied by mysqld reaches the value specified by this parameter, some pending read requests can be processed. The minimum value is 1, and the maximum value depends on the platform font length. It is a non-dynamic variable at the global level. 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, which is especially useful for operating systems with high costs (such as Windows XP. The default value is 1024. It is a non-dynamic variable at the global level. Min_examined_row_limit = # query operations with the number of checked rows lower than the value set by this parameter will not be recorded in the slow query log. The default value is 0. The maximum value depends on the platform font length. It can be used in configuration files at the global or session level and is a dynamic variable. Myisam_data_point_size = {2 .. 7} if the MAX_ROWS option is not set during MyISAM Table creation, the default pointer size is set through this parameter, in bytes. The default value is 6. The scope is global. It can be used in configuration files and is a dynamic variable. Myisam_max_sort_file_size = # sets the maximum volume of temporary files that MySQL can use to re-create an index for the MyISAM TABLE when using the repare table, alter table, or load data infile command. The unit is byte. If the size of the temporary file exceeds the upper limit, mysqld uses key cache to create an index. The default value is 2 GB. If the MyISAM index file is larger than this value and its file system has enough free space, increasing this value will improve MySQL performance. The scope is global. It can be used in configuration files and is a dynamic variable. Myisam_mmap_size = # set the maximum memory size that can be used to compress MyISAM files based on memory ing. In many scenarios where the compression format is MyISAM, reducing this value can help reduce the possibility of memory switching. The minimum value is 7. The default value and maximum value depend on the number of digits on the platform. The scope is global. It can be used in configuration files and is a non-dynamic variable. Myisam_recover_options is used to save the value of mysqld command line option -- myisam-revover-options. This option is used to set the recovery mode of the MyISAM storage engine. The acceptable values include OFF and DEFAULT (recovery mode, but no backup, force, or quick detection), BACKUP (if data changes during recovery, back up the original table file as a table_name-datatime.BAK), FORCE (FORCE recovery, even if data is lost), and QUICK (QUICK recovery ), you can use commas to separate multiple values for this option. You can also choose not to provide any parameter values. The DEFAULT value is DEFAULT, while "" indicates that it is OFF, that is, the recovery mode is disabled. If the recovery mode is enabled, mysqld checks whether a MyISAM table is marked as corrupt or abnormally closed each time it opens. If it is damaged, mysqld tries to fix it. If it is disabled abnormally, mysqld will detect it. Myisam_repair_threads = # Number of threads used to create an index for the MyISAM table during sorting and repair. The default value is 1. If a value greater than 1 is specified, you can start multiple threads for concurrently creating indexes (each index can only be created by one thread ). The maximum value depends on the number of digits on the platform. It can be used in configuration files at the global or session level and is a dynamic variable. Myisam_sort_buffer_size = # The buffer space used to sort indexes during the repair table process or when the create index/alter table is used to add an INDEX to the MyISAM TABLE. The minimum value is 4294967295, that is, 4 GB. A 64-bit system can use a larger space. It can be used in configuration files at the global or session level and is a dynamic variable. Myisam_stats_method = {nulls_equal | nulls_unequal | nulls_ignored} defines how to process NULL values when collecting statistics related to index distribution for the MyISAM table. Nulls_equal indicates that all NULL values are treated as the same value, nulls_unequal indicates that all NULL values are treated as different values, and nulls_ignored indicates that all NULL values are ignored. It can be used in configuration files at the global or session level and is a dynamic variable. Myisam_use_mmap = {ON | OFF} Can I use memory ing when reading and writing the MyISAM table. The default value is OFF. The scope is global. It can be used in configuration files and is a dynamic variable. Net_buffer_length = {1024 .. 1048576} each client thread has a connection buffer and a result buffer. This parameter can be used to set the buffer size, however, both of them can dynamically increase as needed to the size set by the max_allowed_packet parameter. However, after each SQL statement is completed, the result buffer will be reduced to the size defined by the net_buffer_length parameter. Generally, you do not need to modify the value of this parameter unless you want to reduce it to the expected length of the SQL statement on the client in scenarios with tight memory resources. If an SQL statement exceeds this length, the connection buffer automatically adjusts its value. The default value is 16384, in bytes. It can be used at the global or session level and is a dynamic variable in the configuration file. However, this variable is read-only for the session level. Net_read_timeout = # Set the timeout duration for mysqld to wait for receiving more data from the client. The default value is 30. It can be used in configuration files at the global and session level and is a dynamic variable. Net_write_timeout = # Set the timeout time for mysqld to wait for data transmission to the client. The default value is 60. It can be used in configuration files at the global and session level and is a dynamic variable. Net_retry_timeout = # set the number of retries before mysqld terminates the connection with the client when communication between the client and the client is interrupted. The default value is 10. The maximum value depends on the number of digits on the platform. It can be used in configuration files at the global and session level and is a dynamic variable. New = {ON | OFF} is used in MySQL 4.0 to enable some new features in MySQL 4.1, but it can still be backward compatible. In MySQL 5.5, the value is OFF. Old = {ON | OFF} is used to define variables compatible with old MySQL versions. By default, variables are disabled, but can be enabled to be compatible with old MySQL versions when mysqld is started. Reference link http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_insert_id author original, open source documentation. Reprint please be sure to keep this link, http://mageedu.blog.51cto.com.

This article is from the "Marco Education" blog. For more information, contact the author!