Make a little progress every day ———— optimize MySQL SERVER (1)

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)