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

This article shows you how to achieve thread sharing memory parameters in MySQL, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

MySQL thread shared memory parameters

The global shared memory is mainly MySQL Instance (d process) and the underlying storage engine is used to store all kinds of global operations and shareable temporary information, such as Query Cache to store query cache, Thread Cache to cache connection threads, Table Cache to cache table file handle information, BinLog Buffer to cache binary logs, Key Buffer to cache MyISAM storage engine index keys, InnoDB Buffer Pool to store InnoDB data and indexes, and so on. The following is a simple analysis of the main shared memory of MySQL. Www.2cto.com

Query cache (Query Cache): query cache is a unique cache area of MySQL, which is used to cache the result set (Result Set) information of a particular Query and share it with all clients. Through the specific Hash calculation of the Query statement, it is stored in the Query Cache corresponding to the result set to improve the corresponding speed of the identical Query statement. When we open the Query Cache of MySQL, after receiving each Query of SELECT type, MySQL will first get the Hash value of the Query through the fixed Hash algorithm, and then go to the Query Cache to find out whether there is a corresponding Query Cache. If so, the result set of Cache is returned directly to the client. If not, follow up, get the corresponding result set, cache the result set in Query Cache, and then return it to the client.

When there is any change in the data of any table, all Query Cache related to the table will be invalidated, so Query Cache is not very suitable for tables that change frequently, but it is very suitable for tables with few changes, which can greatly improve the query efficiency, such as static resource tables, configuration tables and so on. In order to utilize Query Cache,MySQL as efficiently as possible, multiple query_cache_type values and two Query Hint:SQL_CACHE and SQL_NO_CACHE are designed for Query Cache. When query_cache_type is set to 0 (or OFF), Query Cache is not used, when set to 1 (or ON), MySQL ignores Query Cache if and only if SQL_NO_CACHE is used in Query, and when query_cache_type is set to 2 (or DEMAND), MySQL uses Query Cache for that Query only if and only if the SQL_CACHE prompt is used in Query. You can set the maximum memory space that can be used through query_cache_size.

Connection thread cache (Thread Cache): connection threads are used by MySQL to improve the efficiency of creating connection threads and keep some idle connection threads in a buffer area for new connection requests, which can greatly improve the efficiency of creating connections, especially for those applications that use short connections. After we have set the size of the connection thread that can be cached by the connection thread cache pool through thread_cache_size, we can calculate the hit ratio of the connection thread cache by (Connections-Threads_created) / Connections * 100%. Note that what is set here is the number of connection threads that can be cached, not the size of the memory space. Thread_cache_size, default is 20 www.2cto.com

Table cache (Table Cache): the table cache is mainly used to cache the file handle information of table files. Before MySQL5.1.3, it was set by the table_cache parameter, but changed to table_open_cache from MySQL5.1.3 to set its size. When our client program submits Query to MySQL, MySQL needs to get a table file handle information for every table involved in Query. If there is no Table Cache, MySQL will have to open and close files frequently, which will undoubtedly have a certain impact on system performance. Table Cache is produced to solve this problem. With Table Cache, every time MySQL needs to get handle information about a table file, it will first look in Table Cache to see if there is a table file handle in an idle state. If so, take it out and use it directly, and if not, you can only open the file to get the file handle information. After using it, MySQL puts the file handle information back into the Table Cache pool for use by other threads. Note that what is set here is the number of table file handle information that can be cached, not the size of the memory space.

Table definition information cache (Table definition Cache): the table definition information cache is a new cache area introduced from the MySQL5.1.3 version to store table definition information. When more tables are used in our MySQL, this cache will undoubtedly improve access to table definition information. MySQL provides the table_definition_cache parameter to set the number of tables that can be cached. In previous versions of MySQL5.1.25, the default value was 128. starting with the MySQL5.1.25 version, the default value was adjusted to 256, with a maximum setting of 524288. Note that what is set here is the number of table definition information that can be cached, not the amount of memory space.

Binary log buffer (Binlog Buffer): the binary log buffer is mainly used to cache Binary Log information generated by various data changes. In order to improve the performance of the system, MySQL does not write the binary log directly to Log File every time, but first writes the information to Binlog Buffer, and then writes it to Log File again when some specific conditions (such as sync_binlog parameter settings) are met. We can set the amount of memory it can use through binlog_cache_size, while limiting its maximum size through max_binlog_cache_size (MySQL requests more memory when a single transaction is too large). When the memory required is greater than the max_binlog_cache_size parameter setting, MySQL will report an error: "Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage".

MyISAM index cache (Key Buffer): the MyISAM index cache caches the index information of MyISAM tables in memory to improve its access performance. This cache is arguably one of the most important factors affecting the performance of the MyISAM storage engine, setting the maximum memory space that can be used through key_buffere_size. Www.2cto.com

InnoDB log buffer (InnoDB Log Buffer): this is the buffer used by the transaction logs of the InnoDB storage engine. Similar to when Binlog Buffer,InnoDB writes transaction logs, in order to improve performance, the information is first written to Innofb Log Buffer, and the log will not be written to a file (or synchronized to disk) until the corresponding conditions set by the innodb_flush_log_trx_commit parameter are met (or the log buffer is full). You can set the maximum memory space it can use through the innodb_log_buffer_size parameter.

Note: the innodb_flush_log_trx_commit parameter has a critical impact on the write performance of InnoDB Log. This parameter can be set to 0Query 1 # 2, which is explained as follows:

Data in 0:log buffer is written to log file at a rate of once per second, and file system-to-disk synchronization occurs at the same time, but the commit of each transaction does not trigger any log buffer-to-log file refresh or file system-to-disk refresh.

1: data from log buffer is written to log file each time the transaction is committed, and synchronization of the file system to disk is triggered.

2: transaction commit triggers log buffer-to-log file refresh, but does not trigger disk file system-to-disk synchronization. In addition, there is a file system-to-disk synchronization operation every second.

In addition, as mentioned in the MySQL documentation, the mechanism of synchronizing once per second in these settings may not fully guarantee that synchronization will occur per second with very accurate accuracy, depending on process scheduling issues. In fact, whether the InnoDB can really meet the meaning represented by the value set by this parameter is still limited by the file system under different OS and the disk itself, and may sometimes tell mysqld that the disk synchronization has been completed even if the disk synchronization is not really completed.

InnoDB data and Index caching (InnoDB Buffer Pool): the effect of InnoDB Buffer Pool on the InnoDB storage engine is similar to that of Key Buffer Cache on the MyISAM storage engine, except that InnoDB Buffer Pool not only caches index data, but also caches table data, and caches data exactly according to the data fast structure information in the data file, which is very similar to database buffer cache in SGA. Therefore, it is conceivable that InnoDB Buffer Pool has a great impact on the performance of the InnoDB storage engine. The hit rate of InnoDB Buffer Pool can be calculated by (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%. Www.2cto.com

InnoDB dictionary information cache (InnoDB Additional Memory Pool): InnoDB dictionary information cache is mainly used to store dictionary information of InnoDB storage engine and some shared data structure information of internal. So its size also has a lot to do with the number of InnoDB storage engine tables used in the system. However, if the memory size we set through the innodb_additional_mem_pool_size parameter is not enough, InnoDB automatically requests more memory and logs a warning message in the Error Log of MySQL.

The above is how to implement thread shared memory parameters in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.