Detailed MySQL optimization solution in 8 GB memory
For any database management system, memory allocation is definitely one of its core, so many people who want to know more about a database management system, I hope to have a look, and I am no exception.
The solution is provided here.
Try the following settings:
key_buffer = 3840Mmax_allowed_packet = 16Mtable_cache = 1024sort_buffer_size = 32Mread_buffer_size = 32Mread_rnd_buffer_size = 32Mmyisam_sort_buffer_size = 256Mthread_cache_size = 32query_cache_size = 256M# Try number of CPU's*2 for thread_concurrencythread_concurrency = 8
The maximum key_buffer_size is 4 GB.
However, in order to optimize MySql performance, debugging is required for memory allocation. We recommend that you refer to the following article for settings:
Memory usage MySQL database memory usage is divided into the following two types:
Dedicated thread memory
Global shared memory
Dedicated thread memory
In MySQL, thread exclusive memory is mainly used to store exclusive data for various operations by client connection threads, such as thread stack information, grouped sorting operations, data read/write buffering, and result set temporary storage, in addition, most parameters can be used to control the memory usage.
Thread stack information uses memory (thread_stack): it is mainly used to store the identity information of each thread, such as the thread id and basic information during thread running, we can use the thread_stack parameter to set the size of memory allocated to each thread stack.
Memory Used for sorting (sort_buffer_size): MySQL uses this memory area for sorting (filesort) to complete client sorting requests. When the cache size in the sorting area cannot meet the actual memory size required for sorting, MySQL writes data to disk files for sorting. Because the read/write performance of disks and memory is not an order of magnitude, the effect of sort_buffer_size on the Performance of sorting operations cannot be underestimated.
The Join Operation uses the memory (join_buffer_size): applications often have two or multiple tables to Join. when MySQL completes some Join requests (all/indexjoin ), to reduce the number of reads from the "driven table" involved in the Join operation to improve performance, you need to use the Join Buffer to assist in the Join operation. When the Join Buffer is too small, MySQL will not store the Buffer into a disk file. Instead, it first performs the Join operation on the result set in the Join Buffer and the table to be joined, and then clears the data in the Join Buffer, continue to write the remaining result set into this Buffer. This will inevitably cause the driver table to be read multiple times, doubling IO access and reducing efficiency.
Memory Used for sequential data buffer reading (read_buffer_size): this memory is mainly used for full table scanning and full index scanning when data needs to be read in sequence, for example, if no index is available. In this case, MySQL reads data blocks sequentially according to the data storage order. The data read is saved in read_buffer_size first. When the buffer space is full or all data is read, then, the data in the buffer is returned to the upper-layer caller to Improve the efficiency.
The random read data buffer uses the memory (read_rnd_buffer_size): corresponds to the sequential read. when MySQL reads data blocks in an unordered (random read) manner, it uses this buffer to temporarily store the read data. For example, read Table Data Based on index information, and Join the table based on the sorted result set. In general, when the reading of data blocks needs to meet certain sequence, MySQL needs to generate random reading and then use the memory buffer set by the read_rnd_buffer_size parameter.
The connection information and the result set before returning the client are temporarily stored in memory (net_buffer_size): This part is used to store the connection information of the client connection thread and return the result set of the client. When MySQL starts to generate a result set that can be returned, it will first save the result set in the buffer set through net_buffer_size before returning it to the client request thread through the network, to improve network transmission efficiency. However, the net_buffer_size parameter only sets the initialization size of the cache area. MySQL will apply for more memory as needed, but the maximum size will not exceed the value of max_allowed_packet.
Bulk_insert_buffer_size: When we use insert... Values (...), (...), (...)... MySQL first places the submitted data in a cache space. When the cache space is full or all data is submitted, mySQL writes data in the cache space to the database at a time and clears the cache. In addition, when we load data infile to Load the DATA in the text file into the database, this buffer is also used.
Temporary table memory usage (tmp_table_size): When we perform some special operations such as Order By and Group By which temporary tables are required, MySQL may need to use temporary tables. When the temporary table is small (smaller than the size set by the tmp_table_size parameter), MySQL creates the temporary table as a memory temporary table, only when the size set by tmp_table_size cannot be attached to the entire temporary table can MySQL CREATE the table as the MyISAM storage engine and store it on the disk. However, when the size of another system parameter max_heap_table_size is smaller than tmp_table_size, MySQL uses the size set by the max_heap_table_size parameter as the maximum temporary memory table size, while ignoring the value set by tmp_table_size. The tmp_table_size parameter is available only from MySQL 5.1.2, and max_heap_table_size has been used before.
The MySQL thread exclusive memory listed above is only part of the exclusive memory of all threads, not all. The selection principle may have a great impact on MySQL performance, you can also adjust the parameters.
Because the above memory is dedicated to threads, in extreme cases, the total memory usage will be a multiple of all connection threads. Therefore, you must be cautious when setting the parameter. Do not blindly increase the parameter values to improve performance, avoid Out Of Memory exceptions or severe Swap switching due to insufficient Memory to reduce overall performance.
Global shared memory
In global sharing, MySQLInstance (mysqld process) and underlying storage engines are used to store various global operations and shared temporary storage information, such as QueryCache for query Cache and Thread Cache for Cache connection threads, the Table Cache that caches the Table file handle information, the BinLogBuffer that caches binary logs, the Key Buffer that caches the index Key of the MyISAM storage engine, and the InnoDB BufferPool that stores InnoDB data and indexes. The following is a simple analysis of MySQL's main shared memory.
Query Cache: Query Cache is a unique Cache region of MySQL. It is used to Cache the Result Set information of a specific Query and share it with all clients. After a specific Hash calculation is performed on a Query statement, it is stored in the Query Cache corresponding to the result set to improve the speed of the same Query statement. After MySQL's Query Cache is enabled, MySQL receives a SELECT Query and obtains the Hash value of the Query through a fixed Hash algorithm, then, go to QueryCache to check whether there is a corresponding Query Cache. If yes, the Cache result set is directly returned to the client. If no, perform subsequent operations. After obtaining the corresponding result set, Cache the result set to the Query Cache and return it to the client. When the data of any table changes, all Query Cache related to the table will be invalid. Therefore, Query Cache is not very suitable for tables with frequent changes, however, it is very suitable for tables with fewer changes and can greatly improve the query efficiency, such as static resource tables and configuration tables. To use the Query Cache as efficiently as possible, MySQL designs multiple query_cache_type values and two QueryHint: SQL _CACHE and SQL _NO_CACHE for the Query Cache. When query_cache_type is set to 0 (or OFF), Query Cache is not used. When it is set to 1 (or ON, mySQL ignores the Query Cache only when SQL _NO_CACHE is used in the Query. When query_cache_type is set to 2 (or DEMAND, mySQL uses the Query Cache only when the SQL _CACHE prompt is used in the Query. You can use query_cache_size to set the maximum memory space that can be used.
Connection Thread Cache: to improve the efficiency of creating connection threads, MySQL keeps some idle connection threads in a Cache area for new connection requests, this can greatly improve the efficiency of creating connections, especially for applications that use transient connections. After we 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 rate of the connection thread cache through (Connections-Threads_created)/Connections * 100%. Note that the number of connection threads that can be cached is set here, rather than the memory size.
Table Cache: The Table Cache area is used to Cache file handle information of Table files. In versions earlier than MySQL5.1.3, the Table Cache parameter is set, but from MySQL5.1.3 to table_open_cache to set its size. When our client program submits a Query to MySQL, MySQL needs to obtain a Table file handle information for each Table involved in the Query. If there is no Table Cache, therefore, MySQL has to open and close files frequently, which will undoubtedly affect the system performance. Table Cache is generated to solve this problem. After TableCache is available, MySQL first queries Table file handles in idle state in Table Cache every time it needs to obtain the handle information of a Table file. If yes, it is used directly. If no, you can only open the file to obtain the file handle information. After use, MySQL puts the file handle information back in the Table Cache pool for other threads to use. Note that the number of table file handle information that can be cached is set here, rather than the size of memory space.
Table definition Cache (Table definition Cache): It is a new Cache zone introduced from MySQL5.1.3 to store Table definition information. When many tables are used in MySQL, this cache will undoubtedly improve the access efficiency of table definition information. MySQL provides the table_definition_cache parameter to set the number of tables that can be cached. In versions earlier than MySQL5.1.25, the default value is 128. From MySQL5.1.25, the default value is adjusted to 256. The maximum value is 524288. Note that the number of table definitions that can be cached is set here, rather than the size of memory space.
Binary Log Buffer: the Binary Log Buffer is mainly used to cache Binary Log information generated by various data changes. To improve system performance, MySQL does not directly write binary logs to Log files every time, but first writes information to Binlog Buffer. when certain conditions are met (such as sync_binlog parameter settings) and then write it into the Log File again. We can use binlog_cache_size to set the memory size that can be used, and use max_binlog_cache_size to limit its maximum size (MySQL will apply for more memory when a single transaction is too large ). When the required memory is greater than the max_binlog_cache_size parameter, MySQL reports the 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 the MyISAM table in the memory to improve its access performance. This cache is one of the most important factors affecting the performance of the MyISAM storage engine. You can use key_buffere_size to set the maximum memory space that can be used.
InnoDB Log Buffer: the Buffer used by transaction logs of the InnoDB Storage engine. Similar to Binlog Buffer, InnoDB writes information to Innofb Log Buffer to improve performance when writing transaction logs, when the corresponding conditions set by the innodb_flush_log_trx_commit parameter are met (or the log buffer is full), the log will be written to a file (or synchronized to the disk. You can use the innodb_log_buffer_size parameter to set the maximum memory space that can be used.
Note: The innodb_flush_log_trx_commit parameter has a critical impact on the InnoDB Log write performance. This parameter can be set to 0, 1, and 2. The explanation is as follows:
0: Data in the log buffer is written to the log file at a frequency every second, and the file system is synchronized to the disk at the same time, however, the commit of each transaction does not trigger any refresh from log buffer to log file or from the file system to disk;
1: Data in the log buffer will be written to the log file during each transaction commit, and synchronization from the file system to the disk will also be triggered;
2: transaction commit triggers refresh from log buffer to log file, but does not trigger Disk file System to disk synchronization. In addition, a file system is synchronized to the disk every second.
In addition, the MySQL documentation also mentions that the mechanism for synchronizing these settings once per second may not completely ensure that synchronization will occur every second very accurately, but also depends on the process scheduling problem. In fact, whether InnoDB can really meet the meaning of the value set by this parameter indicates that normal Recovery is still restricted by file systems and disks in different operating systems, sometimes, if the Disk Synchronization is not completed, mysqld is also notified that the disk synchronization has been completed.
InnoDB Buffer Pool: InnoDB BufferPool is used for the InnoDB Storage engine. It is similar to the impact of the Key Buffer Cache on the MyISAM storage engine, the main difference is that the InnoDB Buffer Pool not only caches index data, but also caches table data, and completely caches the data according to the fast data structure information in the data file, this is very similar to the database buffer cache in Oracle SGA. Therefore, the InnoDB Buffer Pool has a big impact on the performance of the InnoDB Storage engine. You can use (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests * 100% to calculate the InnoDB Buffer Pool hit rate.
InnoDB dictionary information cache (InnoDB Additional Memory Pool): InnoDB dictionary information cache is mainly used to store dictionary information of the InnoDB Storage engine and some internal shared data structure information. Therefore, the size of the table depends on 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 will automatically apply for more memory and record warning information in the MySQL Error Log.
The various shared memories listed here are the main shared memory that I personally think has a great impact on MySQL performance. In fact, in addition to the shared memory, MySQL also has many other shared memory information, such as the back_log queue used to store connection request information when too many connections are requested at the same time.
The above content may have some improper analysis. You are welcome to make a discussion.
