Boost MySQL Performance: 3 Simple InnoDB Tweaks You Must Apply

Preface

Adjusting the following three parameters can provide good optimization effects; simple and practical.

1. Use InnoDB storage engine

If you are still using MyISAM, it is strongly recommended to switch to InnoDB, which has many advantages. For example:

MyISAM: stores only indexes in memory

InnoDB: stores both indexes and data in memory

How to modify:

ALTER TABLE table_name ENGINE=InnoDB;

2. Let InnoDB use all memory

The innodb_buffer_pool_size parameter specifies the total memory InnoDB can use.

It is recommended to set it to 80% of physical memory, leaving space for the operating system.

If your memory is 32GB, you can set it to about 25GB: innodb_buffer_pool_size = 25600M Note:

(1) If the value is less than 1GB, you really should upgrade the server.

(2) If memory is especially large, e.g., 200GB, you don’t need to reserve 20% for the OS because the OS cannot use more than 40GB.

3. Enable InnoDB multitasking

When the value of innodb_buffer_pool_size is greater than 1G, innodb_buffer_pool_instances will split the buffer pool into multiple instances.

Benefits of multiple buffer pools:

When multiple threads access the buffer pool simultaneously, contention may become a bottleneck; multiple buffer pools can minimize this conflict.

Official recommendation for the number of buffers:

Each buffer pool instance should be at least 1G.

For example, with memory 32GB and innodb_buffer_pool_size set to 25GB, a suitable configuration is:

innodb_buffer_pool_instances = 24

Conclusion

Hope these three small suggestions can be helpful.