MySQL Advanced 16 - Memory Optimization

1. MyISAM memory optimization

The MyISAM storage engine uses key_buffer to cache the index module to speed up index reading and writing. For the data blocks of the MyISAM table, mysql has no special caching mechanism and completely relies on the IO cache of the operating system.

1. key_buffer_size setting

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

key_buffer_size=2G

2. read_buffer_size

If you need to scan MyISAM tables sequentially frequently, you can improve performance by increasing the value of read_buffer_size. But it should be noted that read_buffer_size is exclusive to each seesion. If the default value is set too large, it will cause memory waste.

3. read_rnd_buffer_size

For MyISAM table queries that need to be sorted, such as SQL with an order by clause, appropriately increasing the value of read_rnd_buffer_size can improve the performance of such SQL. But it should be noted that read_rnd_buffer_size is exclusive. If the default setting value is too large, it will cause memory waste.

2. InnoDB memory optimization

InnoDB uses a memory area as an I/O cache pool. This cache pool is not only used to cache InnoDB index blocks, but also to cache InnoDB data blocks.

1. Set Innodb_buffer_pool_size

The amount of change determines the maximum cache size of InnoDB storage engine table data and index data.

2. innodb_log_buffer_size

Determines the size of the InnoDB redo log cache, which can prevent InnoDB from performing unnecessary log writing operations to disk before the transaction is committed.

3. Adjust MySQL parameters related to concurrency parameters

1. Adjust max_connections

Increase concurrent connections

2. Adjust thread_cache_size

To speed up the connection to the database, MySQL will cache a certain number of customer service threads for reuse. The number of mysql cache client threads can be controlled through the parameter thread_cache_size.

3. innodb_lock_wait_timeout

Control the time that InnoDB transactions wait for row locks. For fast-processing SQL statements, you can increase the row lock waiting timeout to avoid large rollback operations.

Note: The above are all rewritten in the my.ini file in the MySQL directory.

The above is the content of MySQL Advanced 16 - Memory Optimization. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!