Make a little progress every day ---- optimize MySQL SERVER (1) and make a little progress every day

Make a little progress every day ---- optimize MySQL SERVER (1) and make a little progress every day
1. Optimize MySQL Server 7 background processes

Masterthread: refreshes dirty cache pages to data files, performs purge operations, triggers checkpoints, and merges and inserts buffers.

Insertbuffer thread: Used to merge the inserted buffer.

Readthread: reads data from the database. Multiple Threads can be configured.

Writethread: write operations on the database. Multiple Threads can be configured.

Logthread: Used to refresh the redo log to logfile.

Purgethread: After MySQL5.5, it is used for executing the purge operation on a separate purge thread.

Lockthread: used for Lock control and Deadlock Detection.

Error monitoring thread: mainly responsible for error monitoring and error handling.

Memory Management and Optimization of memory management principles

Pay attention to the following points when adjusting the MySQL memory allocation.

Allocate as much memory as possible to MySQL for caching, but reserve enough memory for the operation of the operating system and other programs. Otherwise, SWAP page switching will seriously affect the system performance.

MyISAM Data File Reading depends on the operating system's own IO cache. Therefore, if there is a MyISAM table, more memory should be reserved for the operating system for IO cache.

Cache in the sorting area and connection area is allocated to each database session (seesion). The default value should be allocated based on the maximum number of connections. If it is too large, it will not only waste memory resources, in addition, the physical memory consumption will be exhausted when concurrent connections are high.

MyISAM Memory Optimization

The MyISAM storage engine uses key buffer to cache index blocks to speed up reading and writing of MyISAM indexes. MySQL has no iron caching mechanism for database blocks changed from MyISAM to operating system I/O caching.

Key_buffer_size settings

Key_buffer_size determines the size of the cache area of the MyISAM index block, which directly affects the access efficiency of the MyISAM table. You can set the value of key_buffer_size In the MySQL parameter file. For general MyISAM databases, we recommend that you allocate at least 1/4 of available memory to key_buffer_size.

We can evaluate the index cache efficiency by checking MySQL states such as key_read_requests, key_reads, key_write_requests, and key_writes. Generally, the physical read ratio of the index block key_reads/key_read_requests should be less than 0.01. The index block write ratio key_wirte/key_write_requests should be as small as possible. However, this is related to application features. For applications with many update and delete operations, key_writes/key_wirte_requests may be close to 1. for applications that update many Row Records, key_wirtes/key_write_requests will be relatively small.

In addition to measuring the efficiency of key_buffer by the physical read/write ratio of the index block, we can also determine whether the index cache settings are reasonable by evaluating the usage of the key buffer. The formula for calculating the key buffer usage is as follows:

1-(key_blocks_unused * key_cache_block_size)/key_buffer_size)

Generally, the usage rate is around 80% notes. If the usage rate is greater than 80%, the performance may degrade due to insufficient index size. If the usage rate is less than 80%, the memory will be wasted.

Set key_buffer_size

Mysql> set global key_buffer_size = 8088608;

Query OK, 0 rows affected, 1 warning (0.00sec)

Permanent settings

[Root @ localhostbinlog] # 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)

Use multiple index caches

MySQL improves MyISAM index access performance through session-shared key buffer, but ice cannot eliminate competition for key buffer between sessions. For example, if a session scans a large index, other index data may be stored in the base index cache. These index blocks may be the hot data used by other sessions.

Create 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 of the new index.

Delete key buffer

Mysql> set global hot_cache.key_buffer_size = 0;

Query OK, 0 rows affected (0.01 sec)

However, we cannot 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 indicates table T1

 

Automatically create and load the index cache in the configuration file

[Root @ localhostbinlog] # 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

[Root @ localhostbinlog] # 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 Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.