Shulou(Shulou.com)06/01 Report--

Connection related parameter max_connections: the maximum number of concurrent connections allowed by the client. The default value is 151.This parameter is generally set to 300-1000.

Max_connect_errors: if the number of errors the client attempts to connect exceeds the value set by this parameter, the server will no longer accept new client connections. You can remove this state of blocking new connections from the server by emptying the host's cache and using the FLUSH HOSTS or mysqladmin flush-hosts command to clear the cache. The default value for this parameter is 100, which is generally set to 100000.

The waiting time before interactive_timeout:Mysql closes the interactive connection (in seconds). The default is 8 hours. It is recommended that you do not set this parameter for more than 24 hours, that is, 86400

The waiting time before wait_timeout:Mysql closes the non-interactive connection (in seconds). The default is 8 hours. It is recommended that you do not set this parameter for more than 24 hours, that is, 86400

Skip_name_resolve: if this parameter is set to OFF, the MySQL service will resolve the hostname when checking the client connection; if this parameter is set to ON, the MySQL service will only use IP, in which case the Host field in the authorization table must be an IP address or localhost.

This parameter is off by default

The maximum number of connection requests that can be handled by the back_log:MySQL server connection request queue, and subsequent connections will not be rejected until the queue is full. This parameter takes effect when the main MySQL thread gets a large number of connection requests in a very short period of time. Next, the MySQL main thread takes a short time to check the connection and then starts a new thread. This parameter specifies the size of the TCP/IP listening queue for MySQL. If the MySQL server has a large number of connections in a short period of time, you can increase this parameter.

File related parameter sync_binlog: controls the number of binary log commit groups before binary logs are synchronized to disk. When this parameter is 0, the binary log will not be synchronized to disk; when this parameter is set to a value above 0, a binary commit group that sets this value will periodically synchronize the log to disk. When this parameter is set to 1, all transactions are synchronized to the binary log before committing, so even if the MySQL server restarts unexpectedly, any transactions that are not in the binary log will only be ready, which will cause the MySQL server to automatically recover to roll back these transactions. This ensures that the binary log does not lose transactions, which is the safest option, and this reduces performance due to the increase in disk writes. Setting this parameter to a value above 1 will improve the performance of the database, but it will also be accompanied by the risk of data loss. It is recommended that this parameter be set to 2, 4, 6, 8, 16.

Expire_logs_days: the interval at which binary logs are automatically deleted. The default value is 0, which means that the binary log will not be deleted automatically. If you want to delete the binary log manually, you can execute PURGE BINARY LOGS.

Max_binlog_size: the maximum capacity of a binary log file. When the binary log is written beyond this value, the current binary write is completed and the log is written to the new binary log. The minimum value of this parameter is 4096 bytes; the maximum and default values are 1GB. Statements in the same transaction are written to the same binary log, and when a transaction is large, the actual size of the binary log exceeds the value set by the max_binlog_size parameter. If the max_relay_log_size parameter is set to 0, the max_relay_log_size parameter uses the same size as the max_binlog_size parameter. It is recommended that this parameter be set to 512m.

Local_infile: whether to allow clients to use LOAD DATA INFILE statements. If this parameter is not turned on, the client cannot use the LOCAL parameter in the LOAD DATA statement.

Open_files_limit: the number of files the operating system allows the MySQL service to open. The actual value of this parameter is based on the value set at system startup, max_connections and table_open_cache, using the following rules: 10 + max_connections + (table_open_cache * 2) max_connections * 5MySQL specifies the value of open_files_limit at startup

Cache control parameter binlog_cache_size: the cache size used by the binary log in the transaction. If the MySQL server supports all storage engines and binary logging is enabled, each client is assigned a binary log cache. If there are many large transactions in the database, increasing this cache can achieve better performance.

The two parameters Binlog_cache_use and Binlog_cache_disk_use are useful for optimizing binlog_cache_size parameters. The binlog_cache_size parameter sets only the cache used by the transaction, and the cache used by non-transactional SQL statements is controlled by the binlog_stmt_cache_size system parameter. It is recommended that you do not set this parameter to exceed 64MB to prevent client connections from affecting the performance of the MySQL service.

Max_binlog_cache_size: if a transaction requires more memory than this parameter, the server will report an error "Multi-statement transaction required more than 'max_binlog_cache_size' bytes". The maximum recommended value for this parameter is 4GB, because MySQL does not work properly when the binary log is set to exceed 4GB. It is recommended that you set this parameter to binlog_cache_size*2.

Binlog_stmt_cache_size: this parameter determines the cache in which the binary log handles non-transactional statements. If the MySQL service supports any transactional storage engine and binary logging is turned on, each client connection is assigned a binary log transaction and statement cache. If large transactions are often run in the database, adding this cache can achieve better performance.

Table_open_cache: the number of tables that all threads can open.

The number of threads cached by the thread_cache_size:MySQL service for reuse. When the client disconnects, if the thread cache is not full, the client thread is put into the cache. If a client disconnects and connects to the MySQL service again and the thread is in the cache, the MySQL service gives priority to the threads in the cache; if the thread cache does not have these threads, the MySQL server creates a new thread. If the database has a lot of new connections, you can add this parameter to improve performance. If the MySQL server has hundreds of connections per second, you can increase the thread_cache_size parameter to make the MySQL server use cached threads. By checking the Connections and Threads_created state parameters, you can determine whether the thread cache is adequate. The default value of this parameter is determined by the following formula: 8 + (max_connections / 100)

It is recommended that this parameter be set to 300,500. The hit ratio of thread cache is calculated as (1-thread_created/connections) * 100%. You can use this formula to optimize and adjust thread_cache_size parameters.

Query_cache_size: the cache allocated for query results. This parameter is not enabled by default. The value of this parameter should be set to 1024 times the integer, and if set to another value, it will be automatically adjusted to close to 1024 times this value. This parameter requires a minimum of 40KB. It is recommended that you do not set this parameter greater than 256MB to avoid consuming too much system memory.

Query_cache_min_res_unit: the size of the smallest block allocated by the query cache. The default value is 4096 (4KB).

Query_cache_type: sets the type of query cache. When this parameter is 0 or OFF, the MySQL server does not enable query caching; when this parameter is 1 or ON, the MySQL server caches all query results (except statements with SELECT SQL_NO_CACHE); when this parameter is 2 or DEMAND, the MySQL server caches only statements with SELECT SQL_CACHE.

Sort_buffer_size: the amount of memory allocated for each session to perform a sort operation. To increase the max_sort_length parameter, you need to increase the sort_buffer_size parameter. If you see a large Sort_merge_passes state parameter per second in the SHOW GLOBAL STATUS output, consider increasing the value of sort_buffer_size to improve the processing speed of ORDER BY and GROUP BY. It is recommended to set it to 1~4MB. Increase this parameter at the session level when individual sessions need to perform large sort operations.

Read_buffer_size: the memory allocated to perform sequential reads on the MyISAm table for each thread. If the database has many sequential reads, you can increase this parameter, which defaults to 131072 bytes. The value of this parameter needs to be an integral multiple of 4KB. This parameter is also used in the following scenarios: when performing an ORDER BY operation, caching indexes to temporary files (not temporary tables); performing bulk inserts into partitioned tables; and caching the execution results of nested queries.

Read_rnd_buffer_size: this parameter is used in memory used for random reads of MyISAM tables and any storage engine tables. When reading data in key sort from the MyISAM table, the scanned rows will use this cache to avoid disk scanning. Setting this value to a higher value can significantly improve the performance of ORDER BY. This parameter is then applied to all client connections, so it should not be set to a large value at the global level; in a session running a large query, increase this parameter at the session level.

The cache size used by the join_buffer_size:MySQL server to perform normal index scans, range index scans, and full table scans without using indexes. In general, the way to get the fastest connection is to increase the index. When the index cannot be added, the way to make full join faster is to increase the join_buffer_size parameter. For two tables that perform a full join, each table is allocated a piece of join memory. For complex multi-table joins that do not use indexes, multiple blocks of join memory are required. In general, you can set this parameter to a small value globally and at the session level in a session that requires a large connection. The default value is 256KB.

Net_buffer_length: each client thread interacts with the connection cache and the result cache, and each cache is initially allocated the size of net_buffer_length and grows dynamically until it reaches the size of the max_allowed_packet parameter. When each SQL statement is executed, the result cache shrinks to the net_buffer_length size. It is not recommended to change this parameter unless your system has very little memory, you can adjust this parameter. If the memory required by the statement exceeds the size of this parameter, the connection cache automatically increases. The maximum net_buffer_length parameter can be set to 1MB. This parameter cannot be set at the session level.

Max_allowed_packet: the size of a single packet when transmitted over a network. The default value is 4MB. The initial value of the package information cache is specified by net_buffer_length, but the package may grow to the value set by the max_allowed_packet parameter. If you want to use a BLOB field or a long string, you need to increase the value of this parameter. The value of this parameter needs to be set to the same size as the largest BLOB field. The protocol limit for the max_allowed_packet parameter is 1GB. This parameter should be an integer multiple of 1024.

Bulk_insert_buffer_size:MyISAM tables use a special tree cache to increase the speed of batch inserts, such as INSERT. SELECT,INSERT... VALUES (...), (...), execute LOAD DATA INFILE on the empty table. This parameter is the tree cache size of each thread. Setting this parameter to 0 turns it off. The default value for this parameter is 8MB.

Max_heap_table_size: this parameter sets the maximum capacity that the user-created MEMORY table is allowed to grow. This parameter is used for the MAX_ Rows value of the MEMORY table. Setting this parameter has no effect on existing MEMORY tables unless the table is rebuilt or ALTER TABLE or TRUNCATE TABLE statements are executed.

This parameter is also used in conjunction with the tmp_table_size parameter to determine the size of the internal in-memory table. If memory tables are used frequently, you can increase the value of this parameter.

Tmp_table_size: the maximum memory of the internal memory temporary table. This parameter does not apply to user-created MEMORY tables. If the size of the memory temporary table exceeds the value of this parameter, MySQL automatically converts the excess to a temporary table on disk. In MySQL version 5.7.5, the internal_tmp_disk_storage_engine storage engine will be the default engine for disk temporary tables. Prior to MySQL 5.7.5, the MyISAM storage engine was used. If you have a lot of GROUP BY queries and the system has plenty of memory, consider increasing this parameter.

Innodb_buffer_pool_dump_at_shutdown: specifies whether to record cache pages in the InnoDB cache pool when the MySQL service is shut down to shorten the warm-up process on the next restart. It is usually used with the innodb_buffer_pool_load_at_startup parameter. The innodb_buffer_pool_dump_pct parameter defines the percentage of recently used cache pages that are retained.

Innodb_buffer_pool_dump_now: immediately record the cache pages in the InnoDB buffer pool. It is usually used with innodb_buffer_pool_load_now.

Innodb_buffer_pool_load_at_startup: specifies that when the MySQL service starts, the InnoDB buffer pool warms up automatically by loading previous cache page data. It is usually used with the innodb_buffer_pool_dump_at_shutdown parameter.

Innodb_buffer_pool_load_now: immediately preheat the InnoDB buffer pool by loading data pages without restarting the database service. Can be used to change the cache to a known state during performance testing, or to change the database to a normal state after the database runs a report query or maintenance.

The MyISAM parameter key_buffer_size: the MyISAM table index cache common to all threads, which is used by index blocks. Increasing this parameter increases the read and write performance of the index, which can be set to 25% of the machine's total memory on systems that mainly use the MyISAM storage engine. If you set this parameter to a large value, such as more than 50% of the machine's total memory, the machine will start page and become unusually slow. You can view the status values of Key_read_requests,Key_reads,Key_write_requests and and Key_writes through the SHOW STATUS statement. Normally, the Key_reads/Key_read_requests ratio should be less than 0.01. When database updates and deletions are frequent, the Key_writes/Key_write_requests ratio should be close to 1.

The block size of the key_cache_block_size:key cache. The default value is 1024 bytes.

Myisam_sort_buffer_size: the cache size used for MyISAM index sorting in REPAIR TABLE, CREATE INDEX, or ALTER TABLE operations.

Myisam_max_sort_file_size: the maximum capacity of temporary files allowed by MySQL when rebuilding MyISAM indexes, such as REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE commands. If the MyISAM index file exceeds this value and there is plenty of space on disk, increasing this parameter can help improve performance.

Myisam_repair_threads: if the value of this parameter is greater than 1, the MyISAM table will enable parallelism when creating the index during the sorting process of the Repair operation, and the default value is 1.

The InnoDB parameter the amount of memory used by the innodb_buffer_pool_size:InnDB storage engine to cache table and index data. The default value is 128MB. In systems dominated by the InnDB storage engine, this parameter can be set to 80% of the machine's physical memory. At the same time, it should be noted whether the swapping of engine pages will lead to performance degradation when setting a larger physical memory; the InnoDB storage engine will use part of the memory for caching and control table structure information, so the actual memory spent will be more than 10% of the set value. The larger the setting of this parameter, the longer it takes to initialize the memory pool. In MySQL version 5.7.5, you can increase or decrease the size of the memory pool in terms of chunk. The size of the chunk can be set by the innodb_buffer_pool_chunk_size parameter, and the default value is 128MB. The size of the memory pool can be equal to or an integral multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

The number of regions into which the innodb_buffer_pool_instances:InnoDB cache pool is divided. For large InnoDB caches above 1GB, dividing the cache into multiple parts can improve the concurrency of MySQL services and reduce the read-write competition of different threads reading cache pages. Each cache pool has its own free list, refresh list, LRU list, and other data structures connected to the memory pool, which are protected by mutex locks. This parameter takes effect only if the innodb_buffer_pool_size parameter is set to 1GB or above. It is recommended that each divided memory area be set to the size of 1GB.

Innodb_max_dirty_pages_pct: when the percentage of dirty pages in the Innodb cache pool reaches the value of this parameter, InnoDB writes data from the cache to disk. The default value is 75.

The maximum number of threads that the innodb_thread_concurrency:InnoDB storage engine can use concurrently. When the thread used by InnoDB exceeds the value of this parameter, the subsequent thread will enter the waiting state and process it with a first-in-first-out algorithm. The thread waiting for the lock does not count towards the value of this parameter. The range of this parameter is 0mm 1000. The default value is 0. When this parameter is 0, there is no limit to the number of concurrency that represents InnoDB threads, which causes MySQL to create as many threads as it needs. Setting this parameter can refer to the following rule: if the number of concurrency of user threads is less than 64, you can set this parameter to 0; if the system concurrency is serious, you can set this parameter to 128 first. Then gradually reduce the parameter to 96, 80, 64 or other values until a better value is found.

Innodb_flush_method: specifies how to refresh data to InnoDB data files and log files. The refresh method will have an impact on Imax O. If the value of this parameter is empty, on Unix-like systems, the default value of this parameter is fsync; on Windows systems, and the default value of this parameter is async_unbuffered. On Unix-like systems, this parameter can be set as follows: fsync:InnoDB uses the fsync () system function to refresh data and log files, and fsync is the default parameter. O_DSYNC:InnoDB uses the O_SYNC function to open and refresh the log file, and the fsync () function to refresh the data file littlesync: this option is used for internal performance testing, which is not supported by MySQL at present, and it is risky to use this parameter: this option is used for internal performance testing, but MySQL currently does not support it. With this parameter, there is a certain risk that O_DIRECT:InnoDB uses the O_DIRECT (or directio () in Solaris) function to open the data file, and uses fsync () to refresh the data file and log file O_DIRECT_NO_FSYNC: InnoDB uses the O_DIRECT method when refreshing Imax O.

In systems with raid cards and write caching, O_DIRECT helps avoid double caching between the InnoDB cache pool and the operating system cache. In systems where InnoDB data and log files are placed on top of SAN storage, the default value or O_DSYNC method accelerates read-based databases.

The physical path to the data file used by the innodb_data_home_dir:InnoDB system tablespace, and the default path is the MySQL data file path. If the value of this parameter is empty, you can use the absolute path in the innodb_data_file_path parameter

Path and size of the innodb_data_file_path:InnoDB data file.

Innodb_file_per_table: when this parameter is enabled, InnoDB stores the data and indexes of the newly created table in an .ibd format file separately, rather than in the system tablespace. When this table is deleted or TRUNCATE, the storage occupied by the InnoDB table is freed. This setting enables some other features of InnoDB, such as table compression. When this parameter is turned off, InnoDB will store the table and index data in the ibdata file of the system tablespace, which will be a problem because the system tablespace will not shrink, and this setting will cause the space not to be played back.

The physical path to the tablespace where the innodb_undo_directory:InnoDB undo logs are located. Work with innodb_undo_logs and innodb_undo_tablespaces parameters to set the path of the undo log. The default path is the path of the data file.

Innodb_undo_logs: specifies the number of undo logs used by InnoDB. In MySQL version 5.7.2, 32 undo logs are reserved for use by temporary tables and are stored in a temporary table space (ibtmp1). If the undo logs are stored only in the system tablespace, and you want to allocate additional undo logs for data modification transactions, the innodb_undo_logs parameter must be set to an integer above 32. If you configure a separate undo tablespace, set the innodb_undo_logs parameter above 33 to allocate additional undo logs for data modification transactions. Each undo log can support up to 1024 transactions. If this parameter is not set, it is set to the default value of 128.

The number of tablespace files for the innodb_undo_tablespaces:undo log. By default, all undo logs are part of the system tablespace. Because the undo log grows when you run a large transaction, setting the undo log in multiple table spaces reduces the size of one table space. The undo tablespace file is created under the path specified by the innodb_undo_directory parameter, named in undoN format, where N is a series of integers starting with 0. The default size of the undo tablespace is 10m. You need to set the innodb_undo_tablespaces parameter before initializing InnoDB. In MySQL version 5.7.2, of the 128 undo logs, 32 undo logs are reserved for temporary tables and 95 undo logs are used for undo tablespaces.

The number of logs contained in the innodb_log_files_in_group:InnoDB log group. InnoDB writes to the log in a circular manner. The default value and recommended value for this parameter are both 2. The path to the log is set by the innodb_log_group_home_dir parameter.

The physical path to the innodb_log_group_home_dir:InnoDB redo log file, and the number of redo logs is specified by the innodb_log_files_in_group parameter. If you do not specify any InnoDB log parameters, MySQL creates two redo log files named ib_logfile0 and ib_logfile1 under the MySQL data file path by default, and their size is set by the innodb_log_file_size parameter.

Innodb_log_file_size: the byte size of each log file in the log group. The size of all log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed 512GB.

The cache byte size used by innodb_log_buffer_size:InnoDB to write to the disk log file. The default value is 8MB if the innodb_page_size parameter is 32K and 16MB if the innodb_page_size parameter is 64K. If the cache setting for the log is large, MySQL does not need to write the log file to disk before committing the transaction when processing a large transaction. It is recommended that you set this parameter to 4~8MB.

Innodb_flush_log_at_trx_commit: this parameter controls the balance between ACID consistency and high performance of the commit operation when the submission-related Iamp O operations are rearranged in batches. You can change the default value of this parameter to improve database performance, but a small number of transactions will be lost when the database goes down. The default value of this parameter is 1, which means that the database follows the full ACID model, and whenever a transaction commits, the contents of the InnoDB log cache are flushed to the log file and written to disk. When this parameter is 0, the InnDB log cache flushes the log files to disk approximately once a second. When a transaction commits, the log cache does not write to the log file immediately, and this mechanism does not guarantee that the log will be refreshed to the log file every second, and transaction data with a duration of about 1 second may be lost when the mysqld process is down. When this parameter is 2, when the transaction commits, the contents of the InnoDB log cache are written to the log file and the log file is flushed to disk at a rate of about once per second. In MySQL version 5.6.6, the refresh rate of InnoDB logs is determined by the innodb_flush_log_at_timeout parameter. A parameter is usually set to 1.

Innodb_flush_log_at_timeout: the interval at which the log is written or refreshed. This parameter was introduced in MySQL version 5.6.6. Prior to MySQL version 5.6.6, the refresh rate was once per second. The default value of the innodb_flush_log_at_timeout parameter is also refreshed once a second.

The length of time that the innodb_lock_wait_timeout:InnDB transaction waits for the row lock. The default value is 50 seconds. When one transaction locks a row and another transaction wants to access and modify the row, when the wait time reaches the value set by the innodb_lock_wait_timeout parameter, MySQL will error "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction" and roll back the statement (not the entire transaction). If you want to roll back the entire transaction, you need to start MySQL with the-- innodb_rollback_on_timeout parameter. On highly interactive applications or OLTP systems, you can reduce this parameter to quickly display user feedback or queue updates for later processing. In a data warehouse, you can increase this parameter in order to better handle long-running operations. This parameter applies only to InnoDB row locks and is not valid for table-level locks. This parameter does not apply to deadlocks because when a deadlock occurs, InnoDB immediately detects the deadlock and rolls back a transaction in which the deadlock occurred.

Innodb_fast_shutdown:InnoDB shutdown mode. If this parameter is 0 century InnoDB, a slow shutdown will be performed, and a complete refresh operation will be performed before shutdown. This level of database shutdown will last for several minutes, even for several hours when the amount of data in the cache is large. If the database is to be upgraded or degraded, you need to perform this level of database shutdown to ensure that all data changes are written to the data file. If the value of this parameter is 1 (the default), to save time for shutting down the library, InnoDB skips the new operation and performs a refresh operation through crash recovery the next time it is powered on. If the value of this parameter is 2jue InnoDB, the log will be refreshed and the library will be shut down in a cold manner, just like when MySQL is down, uncommitted transactions will be lost, and crash recovery will take longer to open the database next time. In case of emergency or error, it will be used to shut down the database immediately.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.