Getting started with MySQL Tutorial: Getting started with mysql optimization

MySQL is a commonly used RDBMS (RelationalDatabaseManagementSystem/relational database management system), and other relational database management systems such as PostgreSQL, Oracle, and DB2. The importance of database performance does not need to be emphasized. Here, we will briefly talk about the optimization issues after MySQL is installed.

Computing MySQL memory usage

First, check whether the MySQL process occupies the memory.

MySQL Memory Usage = global cache + (thread cache x maximum number of connections)
The memory occupied by the global cache is calculated as follows.

The max_heap_table_size parameter does not necessarily allocate memory. Therefore, the global cache is also calculated for security purposes.

Global Cache = key_buffer_size
+ Innodb_buffer_pool_size
+ Innodb_additional_mem_pool_size
+ Innodb_log_buffer_size
+ Max_heap_table_size
+ Query_cache_size
The memory occupied by the thread cache is calculated as follows.

In normal queries, myisam_sort_buffer_size is unlikely to be used, so it is not computed in the thread cache. In addition, max_allowed_packet is calculated in the thread cache. If the amount of data transmitted is small, net_buffer_length can be used for calculation.

Thread cache = sort_buffer_size
+ Read_rnd_buffer_size
+ Join_buffer_size
+ Read_buffer_size
+ Max_allowed_packet
+ Thread_stack
MySQL cache

How to use the cache is crucial to MySQL Performance. MySQL has the following two types of cache.

Global Cache)
Thread Cache)
Global Cache

Innodb_buffer_pool_size

Cache InnoDB indexes and data
Key parameters when using InnoDB
Innodb_additional_mem_pool_size

Data directory information and internal data structure stored in InnoDB
Warning (Warning) is output to the MySQL error log file when the problem persists)
Use the default value. If the MySQL error log file is insufficient, add
Innodb_log_buffer_size

Buffer used by InnoDB transaction logs
The transaction ends or logs in the cache area are written to a file (synchronized to the disk) at a certain interval)
Try to configure more memory for other parameters
Key_buffer_size

Cache MyISAM indexes
Query_cache_size

Cache query (SELECT) results
It has a direct impact on MySQL Performance.
The query_cache_type parameter can change the MySQL cache behavior.
Thread cache

Sort_buffer_size

Use Area for order by and GROUP
Configure according to program usage
Read_rnd_buffer_size

Used to read sorted data
Improve order by performance
Join_buffer_size

This region is used if no index is used for table combination.
Indexes are recommended for tables, so this parameter does not need to be configured too large.
Read_buffer_size

Regions used to read the entire table
If you do not use an index for a query, you cannot use this parameter. Therefore, this parameter does not need to be configured too large.
Myisam_sort_buffer_size

The region used to sort the index of the MyISAM DDL (DataDefinitionLanguage).
This region is usually not used for queries, so it can be queried by default.
Max_allowed_packet

The data packet sending buffer is the memory area that stores the transmitted data packets.
It is initially converted by the net_buffer_length parameter and expanded to the size specified by max_allowed_packe as needed
Parameters other than memory

Max_connections

Maximum number of connections to the MySQL database
The default value is 151.
Innodb_lof_file_size

Save InnoDB update log to disk
When innodb_log_file is full, innodb_buffer_pool updates logs are written to the disk.
When innodb_buffer_pool_size is adjusted, innodb_log_file_size also needs to be adjusted.
The larger the adjustment, the longer the time for Crash Recovery.
Table_open_cache

Save the file pointer for using a Table
At least "concurrent connections x Table count" is required 」
MyISAM is a Table that requires two file pointers.
Note OS restrictions ※cat/proc/sys/fs/file-max
Thread_cache_size

Reduces the connection load by caching threads
Configuration based on actual load