Configuring InnoDB Settings

Technical Articles
Solution
Application
Content Type
Technical Documentation
Utilities & Services
ft:locale
en-US

Before converting the database engine from MyISAM to InnoDB, configure two InnoDB-specific MySQL settings. The two settings are innodb_buffer_pool_size and innodb_file_per_table.

innodb_buffer_pool_size

This setting controls how much RAM is available for the InnoDB engine to cache table and index data and must be configured before attempting the conversion. The recommended InnoDB buffer pool size is around 50 to 70% of the total system memory for dedicated database servers. However, if the Jamf Pro database server is also hosting Tomcat, calculate this value to be 50 to 70% of the remaining system memory, after accounting for how much is already allocated to the Tomcat service, the operating system, and any other running services on the server.

For example, a server running both Tomcat and MySQL with 16 GB of RAM total has 8 GB allocated to the Tomcat service and 4 GB has been set aside for the system and other processes. 4 GB remains from the original total, so the calculation would be to allocate anywhere from 2 to 3 GB to the InnoDB buffer pool size setting.

To set the innodb_buffer_pool_size, execute the following command:
jamf-pro database config set --innodb-buffer-pool-size <value>
This command will accept values in megabytes (MB) or gigabytes (GB). For example:
jamf-pro database config set --innodb-buffer-pool-size 2048MB
jamf-pro database config set --innodb-buffer-pool-size 2GB

innodb_file_per_table

This setting allows for data and indexes to be stored in separate .ibd files, allowing for compression and performance enhancement. This setting must be set to true (boolean, 1) before attempting the conversion.
  1. To set the innodb_file_per_table to true, execute the following command:
    jamf-pro database config set --innodb-file-per-table true
  2. Execute the following command to restart the MySQL service: 
    jamf-pro database restart