MySQL tuning

[tars]

I need to do some tuning on my MySQL server for Zabbix. I've been looking at the following document. Two of the main things recommended to change are:

innodb_buffer_pool_size
innodb_log_file_size

I'm going to set innodb_buffer_pool_size to 60% of the memory on the server.

Currently mine is 48Megs and the article recommends at least 512Megs. I see that the article is from 2021. Is innodb_log_file_size still a recommend parameter to change?

Thanks

[tim.mooney]

The log file setting would be a secondary thing to look at, if setting the buffer pool doesn't do enough.

Tuning the buffer pool is definitely the first thing to look at.

If your database storage is on flash storage, rather than than traditional spinning disk, there may be some beneficial I/O tuning that could be done too, but focus on the buffer pool first.

[tars]

Hi Tim

OK this is great thanks, you. That was my gut feeling. I will give that a go and see how I get on.

The disks are SSD so I think we should be fine there (3000 IOPS)

[tim.mooney]

The disks are SSD so I think we should be fine there (3000 IOPS)

That's good.

You may want to review the documentation for 'innodb_io_capacity' and 'innodb_flush_method' and make some changes based upon what you find in the documentation. The MySQL documentation for "Configuring the InnoDB I/O Capacity" does a good job of explaining the tradeoffs. Just be certain you're reading the documentation for your version of MySQL The defaults have changed dramatically in very recent versions of MySQL, so knowing what your version defaults to is important.

[tars]

That's good.

You may want to review the documentation for 'innodb_io_capacity' and 'innodb_flush_method' and make some changes based upon what you find in the documentation. The MySQL documentation for "Configuring the InnoDB I/O Capacity" does a good job of explaining the tradeoffs. Just be certain you're reading the documentation for your version of MySQL The defaults have changed dramatically in very recent versions of MySQL, so knowing what your version defaults to is important.

Thanks, will look into it.