In order to make the most out of MySQL, it can be beneficial to optimize certain settings. Therefore, putting the below items into practice is advised. However, please note that these settings are recommendations and should be considered on a case by case basis. Mainly due to each customer environment differing in terms of resources/setup.

Changing the below values can be done within either Workbench (from the Options File section) or the my.ini file on disk.

INNODB_BUFFER_POOL_SIZE

Possibly the most important setting that will require changes is the InnoDB Buffer Pool. This is the memory space that can hold in-memory data structures for things such as buffers, caches, indexes and sometimes row.data. This setting is therefore the parameter that specifies the amount mof memory allocated to the Buffer Pool size for the MySQL instance. 

A common rule of thumb for this parameter, is to set it to around 60-70% of the system RAM. However, this is mainly beneficial for database-only servers that simply process database requests and operating system activities. For Curator environments, you need to understand what services are installed, so that this parameter change does not negatively affect these services. 

Once you've decided what value to configure, simply save the change and restart the MySQL service.

For existing customer environments that are having this value changed, one method of getting a rough indication of the value required for this is by running the below query:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
 SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
 FROM
 (
 SELECT SUM(data_length+index_length)*1.1*growth RIBPS
 FROM information_schema.tables AAA,
 (SELECT 1.25 growth) BBB
 WHERE ENGINE='InnoDB'
 ) AA
) A;;

The above takes information from your MySQL instance and uses this to provide a recommendation for how much RAM is required (This is based off of current InnoDB Data and indexes, with an additional 50%).

INNODB LOG FILE SIZE

Depending on the amount of work the system is carrying out on a daily basis, especially at peak times, the InnoDB log file size can impact performance. However, there is a trade off between better performance (i.e., bigger log files) and time required to restore a system should issues arise. This is because the more space available for the log file, the better InnoDB can optimize write IO. However, increasing the value too much also means longer recovery times when the system loses power or crashes. 

When the log file size is quite low, MySQL will continually have to ensure it is logging less than the limit. This is until it has more data than the log limit, forcing InnoDB to move the information out into the data files on disk, thus utilizing MySQL performance that could otherwise be used elsewhere. Therefore, setting a Log File Size limit here to a decent size will help reduce the amount of time MySQL uses to continually flush information out of the log, allowing it to more smoothly flush information out at its own convenience

As a rule of thumb, setting a log file size between 2-5GB can be beneficial to system performance, depending on how active the system is on a daily basis. There are some MySQL queries, like the below, that can help estimate the log file size required. However, these are not always accurate and can recommend higher than average recommendations. So take the below with a pinch of salt:

SELECT
 innodb_os_log_written_per_minute*60
 AS estimated_innodb_os_log_written_per_hour,
 CONCAT(ROUND(innodb_os_log_written_per_minute*60/1024/1024, 1), 'MB')
 AS estimated_innodb_os_log_written_per_hour_mb
FROM
 (SELECT SUM(value) AS innodb_os_log_written_per_minute FROM (
 SELECT -VARIABLE_VALUE AS value
 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
 WHERE VARIABLE_NAME = 'innodb_os_log_written'
 UNION ALL
 SELECT SLEEP(60)
 FROM DUAL
 UNION ALL
 SELECT VARIABLE_VALUE
 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
 WHERE VARIABLE_NAME = 'innodb_os_log_written'
 ) s1
) s2
;

Networking

max_allowed_packet = 1073741824

net_buffer_length = 1048576

Notes:

As we evolve with MySQL, the above settings will need to evolve too. Therefore, this article needs to be a living document updated and improved to keep the settings viable for how Curator grows. Should any additional settings/recommendations become known to Tech Ops, please ensure this document gets updated as well.