How to Learn and think about MySQL

The purpose of this article is to read and understand your own books and use this picture to explain. If there are any mistakes, I hope you can correct them and share them with us for the purpose of mutual discussion.

First, let's introduce the source of the Architecture diagram: I recently read some of my experiences on mysql and converted the text into images for ease of understanding.

I will mainly elaborate on three aspects: read, write, and underlying Disk:

1. Read operations:

We know that when reading data, we need to read the data from the disk to the memory and then perform corresponding operations. when optimizing the read operation, we mainly want to optimize the buffer and cache:

Key_buffer_size
This is a very important parameter for the MyIsam table. Generally, you can set it to 30%-40% of the memory. Of course, this depends on the actual situation, myISAM tables use the operating system cache to cache data. Therefore, you need to leave some memory for them. In many cases, the data is much larger than the index.
 
Innodb_buffer_pool_size
This is an important parameter for InnoDB, while InnoDB is more sensitive to buffering. MyISAM can run under the default key_buffer_size setting, however, the default innodb_buffer_pool_size setting of Innodb is similar to snail bait. Because Innodb caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need Innodb, you can set it to up to 70-80% of available memory. Some rules apply to key_buffer: If your data volume is small and does not increase rapidly, you do not need to set innodb_buffer_pool_size too large.
 
Table_cache
Table cache, which occupies system resources and memory. Because each temporary table needs to be opened in Chengdu, this value can be increased when the number of connections is large.
 
Thread-cache
The Thread cache, Thread creation and destruction overhead may be very large, so the connection and disconnection needs for each Thread. If the number of active concurrent connections in the program and the value of Thread-Created is large, you can set the value to be larger. [Help house http://www.bkjia.com]
 
Query-cache
If the application has a large number of reads, you can set a large value, but not too large, because maintaining it also requires a lot of overhead. Generally, you can set 32M-512M.
 
Sort_buffer_size
This is a connection-level parameter. When each connection needs to use this buffer for the first time, the set memory is allocated at one time. The larger the value is, the better. If the value is too large, in the case of high concurrency, the performance will be reduced. When sort_buffer_size exceeds 2 kb, mmap () instead of malloc () will be used for memory allocation, resulting in lower efficiency.
 
When a mysql temporary table is working on a very large table, you may occasionally need to run many queries to obtain a small subset of a large amount of data, instead of running these queries on the entire table, instead, MySQL can find a few required records each time, and it may be faster to select a temporary table, and then run the query on these tables. The mysql server will automatically create an internal temporary table: This temporary table can be a temporary memory table that only exists in the memory, or a temporary myisam table that is stored in the hard disk; in addition, the initial memory temporary table may be changed to a myisam temporary table because of the increase of the table. The conversion critical point is determined by the small values of the max_heap_table_size and tmp_table_size system variables! Note: max_heap_table_size system variables are applied to all memory engine tables, whether they are temporary tables, normal tables, or internal temporary tables. Of course, the program can also create a temporary table: create temporary table XX; of course, this is the program control. After the creation and use are completed, the deletion will be controlled by the program.

The above are some introduction to read operations, followed by write operations.

1 2 Next Page