Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the parameters of mysql". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what parameters mysql has.

Max_connections: represents the maximum number of connections to the database. If there is a frequent occurrence of 1040 too many connections, you need to increase this parameter appropriately. However, the larger the number of connections, the better. Too many connections will waste memory resources and affect the performance of the mysql server.

Back_log: indicates how many requests can be stored in the stack after the number of connections reaches max_connections. You need to increase the value of this parameter only if you expect to have many connections in a short period of time. The operating system also has a limit on this queue size, and setting the limit that back_log is higher than the operating system will not be valid.

Default_character_set: this parameter affects character_set_client (which mysql server assumes that the query sent by the client uses the character set), character_set_connection (after mysql server receives the query request issued by the client, converts it to the character set specified by character_set_connection) and character_set_results (mysql server converts the result set and error message to the character set specified by character_set_results and sends it to the client). The modification of this parameter affects the character set of the interaction between the client and the server.

Character_set_server: this parameter affects the parameters character_set_server (mysql server default character set) and character_set_database (database default character set). Changing this parameter will affect the character set of the database, so if you want to make changes to the character set of the database, you can modify character_set_server, for example: this is an APP project that involves a large number of Emoji emoticons, then you need to use the 4-byte character set of utf8mb4, where you need to change the original utf8 to utf8mb4.

Event_cheduler: this parameter affects the scheduled task. Since the restart of msyql will lead to the failure of the scheduled task, you need to change this parameter to ON so that the scheduled task will not fail.

Max_allowed_packet: the packet for query is too large error will occur if this parameter is set too small. You need to expand this parameter appropriately to an integer multiple of 1024, but it is not appropriate to expand this parameter too large, otherwise the operating system does not have enough memory, and this parameter will become invalid.

Sort_buffer_size: indicates the size of the sort cache, and the higher this value, the faster the sort.

Read_buffer_size: represents the size of the buffer allocated for each table scanned continuously by each thread. This buffer is needed when the thread reads records continuously from the table.

Read_rnd_buffer_size: represents the size of the buffer reserved for each thread, similar to read_buffer_size. However, it is mainly used to store records read in a particular order, and this value can be increased if multiple consecutive scans are performed frequently.

Innodb_flush_log_at_trx_commit: indicates when data from the redo log buffer is written to the log file. This parameter is very important for the innodb engine and has three values, 0, 1, and 2, respectively. A value of 0 means that the redo log buffer data is written to the log file every 1 second; a value of 1 means that the redo log buffer data is written to the log file every time the transaction is committed; a value of 2 means that the data is written to the redo log buffer every time the transaction is committed, and the redo log buffer data is written to the log file every 1 second. The default value of this parameter is 1, and the default value of 1 is the highest security, but each transaction commit or instructions outside the transaction need to write the log to the hard disk (flush), which is relatively time-consuming; the value of 0 is faster, but the security is relatively poor; 2's log will still play only disk per second, so even if there is a failure, generally will not lose more than 1'2 seconds of data.

Interactive_timeout: indicates the number of seconds the server waits before closing the connection.

Wait_timeout: indicates the number of seconds the server waits to close a connection.

Sort_buffer_size: represents the size of the buffer allocated by each thread that needs to be sorted. Increasing the value of this parameter increases the speed of order by or group by.

Thead_cache_size: represents the number of threads that can be reused. If there are many new threads, you can increase the value of this parameter in order to improve performance.

Innodb_buffer_pool_size: represents the maximum cache for tables and indexes of type innodb. The higher the value, the faster the query will be, but this value will affect the performance of the operating system.

Query_cache_size: indicates the size of the query buffer. This parameter needs to be used in conjunction with query_cache_type. When query_cache_type=0, all queries do not use query buffers, but query_cache_type=0 does not free the configured buffer memory. When query_cache_type=1, all queries will use query buffers unless SQL_NO_CACHE is specified in the query statement, such as select SQL_NO_CACHE * from table. When query_cache_type=2, the query buffer is used only if the SQL_CACHE keyword is used in the query statement. The use of query buffers can improve query speed, which is only suitable for situations where few modifications are performed and the same query operations are performed frequently. You can use flush query cache to flush the buffer and clean up the fragments in the query buffer.

Table_cache: indicates the number of tables opened at the same time. The higher this value, the more tables you can open at the same time. This value is not as large as possible, because too many tables open at the same time will affect the performance of the operating system.

Key_buffer_size: indicates the size of the index buffer. The index buffer is shared by all threads. Adding index buffers can get better handling of the index (for all reads and multiple overrides). Of course, this value is not as large as possible, its size depends on the size of memory, if this value is too large, causing the operating system to change pages frequently, which will degrade system performance.

At this point, I believe you have a deeper understanding of "what parameters mysql has". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.