"mysql Optimization Theme" Detailed engine (INNODB,MYISAM) memory optimization strategy? (9)

Note: The following are rewritten in the My.ini file in the MySQL directory (technical text).

First, InnoDB memory optimization

InnoDB uses a chunk of memory to do the I/O cache pool, which is used not only to cache InnoDB index blocks, but also to cache InnoDB blocks of data.

1, Innodb_log_buffer_size

Determines the size of the InnoDB redo log cache, which prevents InnoDB from performing unnecessary log writes to disk operations before the transaction commits.

2. Set Innodb_buffer_pool_size

The amount of change determines the maximum buffer size for INNODB storage engine table data and index data.

Second, MyISAM memory optimization

The MyISAM storage engine uses the Key_buffer Cache index module to accelerate the read and write speed of the index. For data blocks of the MyISAM table, MySQL does not have a special caching mechanism and relies entirely on the OS's IO cache.

1, Read_rnd_buffer_size

For MyISAM table queries that need to be sorted, such as SQL with an ORDER BY clause, an appropriate increase in the value of read_rnd_buffer_size can improve the SQL performance of this class. However, it is important to note that Read_rnd_buffer_size exclusive, if the default setting value is too large, it will cause memory waste.

2. Key_buffer_size setting

Key_buffer_size determines the size of the MyISAM index block cache partition. Directly affects the access efficiency of the MyISAM table. For a general MyISAM database, it is recommended that 1/4 available memory be allocated to Key_buffer_size:

key_buffer_size=2g

3, Read_buffer_size

If you need to scan the MyISAM table frequently sequentially, you can improve performance by increasing the value of read_buffer_size. However, it is important to note that Read_buffer_size is exclusive to each seesion, and if the default value is set too large, it can cause memory waste.

Third, adjust the parameters of MySQL parameters concurrency related

1, adjust the max_connections

Increase concurrent connections

2, adjust the thread_cache_size

To speed up the connection to the database, MySQL caches a certain number of customer service threads for reuse, with parameter thread_cache_size to control the number of MySQL cache client threads.

3, Innodb_lock_wait_timeout

Controls the time that InnoDB transactions wait for row locks, and for fast-processing SQL statements, you can increase the row lock wait time-out to avoid a large rollback operation. (technical article)

"mysql Optimization Theme" Detailed engine (INNODB,MYISAM) memory optimization strategy? (9)