Visitor : 134

Read Comments

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/

 

 



Add Comments