Fine Tuning MySQL DB
My_SQL Hosting
Latest versions of My SQL DB > 5 , have various options for configuring the database based on various requirements. This document explains the basic setup requirements for optimum performance of your MySQL DB.
When MySQL DB is loaded the basic setup parameters are loaded from my.cnf or my.ini file based on the operating system
In case of windows this file is located at : C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
Setup Parameters
Setup parameters can be modified by editing my.ini file. Default values setup will be very conservative and has to be modified based on the installation parameters.
Sample Setup parameters for RAM : 8GB
innodb_buffer_pool_size : 6G
innodb_buffer_pool_instances : 4
Understanding DB Parameters
Buffer Pool , Buffer Instance , Buffer Chunk:
Buffer Pool is split into multiple Buffer Instance and each buffer instance is split into buffer chunks.
Buffer Pool :
InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and row data. The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this
parameter up to 80% of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system. Note that on 32bit systems you
might be limited to 2-3.5G of user level memory per process, so do not set it too high.
Buffer Pool Instance:
The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
by reducing contention as different threads read and write to cached pages.
Reference
https://www.saotn.org/mysql-innodb-performance-improvement/
https://www.percona.com/blog/2018/06/19/chunk-change-innodb-buffer-pool-resizing/