MySQL Prism Database Optimizations

MySQL Prism Database Optimizations

The following changes to the default MySQL initialization file have been found to significantly improve replication performance.

Prerequisites

MySQL Server 5.X 64 Bit Edition

MySQL Optimizations

The MySQL initialization (my.ini) is located in the c:\programdata\MySql\MySql Server 5.x folder of your 64bit Windows operating system. It is configured by default to a low baseline configuration. The following optimizations will improve read/write performance in the database by increasing the size of the memory pools, increasing packet sizes and enlarging the redo logs. These changes should be performed prior to your initial data load. To apply the change use the following instructions:

  1. Make a backup of the MY.INI file located in the c:\programdata\MySql\MySql Server 5.x folder.
  2. Open MY.INI in notepad or another plain text editor.
  3. Search for and change each of the parameters listed below and save the file.
  4. Restart the system.

Parameters

  • innodb_buffer_pool_size to 60% of memory on host, (ie. 5g on an 8g system). This can be set up to 80% but other applications running on the same system will suffer so 60% seems optimal for a middle way approach.
  • innodb_log_buffer_size=16MB
  • innodb_log_file_size=512m
  • innodb_flush_log_at_trx_commit=1
  • innodb_buffer_pool_instances=4
  • innodb_file_per_table=1
  • innodb_thread_concurrency=0
  • innodb_open_files=1000
  • max_allowed_packet=16MB
  • thread_cache_size=40

Published on Dec 11, 2017 in Installation, Database

 

Find Another Article