1. Optimize MySQL SERVER7 group background process
Masterthread: It is mainly responsible for flushing dirty cache pages to data files, performing purge operations, triggering checkpoints, merging insert buffers, and so on.
Insertbuffer Thread: A merge operation that is primarily responsible for inserting buffers.
Readthread: Responsible for database read operation, can configure multiple threads
Writethread: Responsible for database write operations, can configure multiple threads.
Logthread: Used to flush redo logs into logfile.
purgethread:mysql5.5 is then used for a separate purge thread to perform purge operations.
Lockthread: Responsible for lock control and deadlock detection.
Error Monitoring thread: primarily responsible for error monitoring and error handling.
Memory management and optimized memory management principles
Note the following points when adjusting the MySQL memory allocation.
Allocate as much memory as possible to MySQL for caching, but make enough memory available for the operating system and other programs to run, otherwise the Swap page Exchange will severely affect system performance.
MyISAM data file reads depend on the operating system's own IO cache, so if you have a MyISAM table, you need to reserve more memory for the OS to do IO caching.
The cache for the sort area, connection area, and so on, is dedicated to each database session (Seesion), and its default setting is allocated according to the maximum number of connections, and if set too large, not only wastes memory resources, but also causes physical memory consumption when the concurrent connection is high.
MyISAM Memory optimization
The MyISAM storage engine uses key buffer to cache index blocks to speed up the read and write speed of the MyISAM index. For MyISAM database blocks, MySQL does not have an iron cache mechanism and relies entirely on the OS's IO cache.
Key_buffer_size settings
Key_buffer_size determines the size of the MyISAM index block buffer, which directly affects the access efficiency of the MyISAM table. You can set the value of Key_buffer_size in the MySQL parameter file, and it is recommended that at least 1/4 available memory be allocated to key_buffer_size for a general MyISAM database.
We can evaluate the efficiency of the index cache by examining MySQL states such as Key_read_requests, Key_reads, key_write_requests, and Key_writes. In general, the index block physical read ratio key_reads/key_read_requests should be less than 0.01. The index block write ratio key_wirte/key_write_requests should also be as small as possible. However, this is related to the application characteristics, key_writes/key_wirte_requests may be close to 1 for applications with very many updates and deletions, and for applications that update many rows of records, Key_wirtes/key_write_ Requests will be smaller.
In addition to measuring the efficiency of key_buffer by the physical read-write ratio of the index block, we can determine whether the index cache setting is justified by evaluating the usage of key buffer. The key buffer usage calculation formula is as follows:
((key_blocks_unused*key_cache_block_size)/key_buffer_size)
In general, the usage rate at around 80% notes Oh ah appropriate, greater than 80% may be due to the index is not enough to cause performance degradation, less than 80% will cause memory waste
Set Key_buffer_size
Mysql>set Global key_buffer_size=8088608;
Query OK, 0 rows affected, 1 warning (0.00sec)
Permanent settings
[[Email protected]]# vim/etc/my.sql
[MySQL]
key_buffer_size=8088608
View Key_buffer_size
Mysql> Show variables like ' key_buffer_size ';
+-----------------+---------+
| variable_name | Value |
+-----------------+---------+
| Key_buffer_size | 8388608 |
+-----------------+---------+
1 row in Set (0.00 sec)
Mysql>show Global status like ' key_% ';
+------------------------+-------+
| variable_name | Value |
+------------------------+-------+
| key_blocks_not_flushed | 0 |
| key_blocks_unused | 6452 |
| key_blocks_used | 0 |
| key_read_requests | 0 |
| Key_reads | 0 |
| key_write_requests | 0 |
| Key_writes | 0 |
+------------------------+-------+
7 Rows in Set (0.00 sec)
Using multiple index Caches
MySQL improves the performance of MyISAM index access through the session-shared key buffer, but the ice does not eliminate the contention between the session and key buffer. Like a session. If a large index is scanned, it is possible to have other index data underlying the index buffer, which may be the hot data to be used by the other session.
New Key buffer
Mysql>set Global hot_cache.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.01 sec)
Hot_cache is the cache name for the new index.
Delete key buffer
Mysql>set Global hot_cache.key_buffer_size=0;
Query OK, 0 rows affected (0.01 sec)
But we can't delete the default Keybuffer
Add the index of a table to the new key buffer
Mysql>cache index T1 in Hot_cache;
+----------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+--------------------+----------+----------+
| Test2.t1 | Assign_to_keycache |status | OK |
+----------+--------------------+----------+----------+
1 row in Set (0.00 sec)
T1 represents T1 Table
Automatically create and load the index cache in a configuration file
[[Email protected]stbinlog]# vim/etc/my.sql
[MySQL]
key_buffer_size=8088608
hot_cache.key_buffer_size=128*1024
cold_cache.key_buffer_size=1g
Init_file=/usr/local/mysql/config/mysqld_init.sql
[[Email protected]]# vim/usr/local/mysql/config/mysqld_init.sql
Cache index T1 Hot_cache
Cache index T2 Cold_cache;
Load index into cache t1,t2;
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Make a little progress every day ———— optimize MySQL SERVER (1)