Conversion to InnoDB

[blackholemac]

When using the Jamf-Pro binary, to make a conversion of the database to InnoDB, I am encountering new settings for MySQL that I am unfamiliar with.

InnoDB buffer pool size says to choose around 70% of the total system memory and will automatically do that. Any wisdom as to why I should accept, increase or decrease this value?

InnoDB key buffer size: The only nugget of info I have found is the 16M value that is present when the question is asked. Any wisdom on how I would calculate a value?

InnoDB flush log at transaction commit: Presuming yes, but knowing more about what is being done is good.

InnoDB file per table: my choices are 0, 1 or 2. Logic dictates to choose 1 but again, I'm open to understanding what this setting is about.

For those wanting to know about the scale of our environment, I'm definitely willing to provide details. I'm just for now wanting to understand what these settings are and how one might determine what values to use.

Kind regards,
blackholemac

[tak10]

I'm running into the same situation.
I'm not sure the proper parameters for InnoDB buffer pool size and InnoDB key buffer size.

I set the InnoDB flush log at transaction commit to "1". https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2

InnoDB file per table will accept True or False boolean values. I set it to True. https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html

I'm going to try the default values for buffer pool size and key buffer size. We will see if this causes performance problems.

[jleomcdo]

Did you guys figure out some of these values? I'm working on converting to InnoDB database storage engine.

[rbrinckmann]

Push!

[Hugonaut]

Here are some resources that helped a lot

*NOTE the #G at the end of "innodb_buffer_pool_size=#G" it is equal to "75% of total Ram followed by G" (or M if you so choose to write it out in megabytes verse gigabytes. Below is my explicit example, we have 16GB of ram on our MySQL server.

*innodb_buffer_pool_size is the setting that controls the size of the memory buffer that InnoDB uses to cache indexes and data.

*Innodb_file_per_table=1 - https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table

Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash. The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit. With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash. With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash. For settings 0 and 2, once-per-second flushing is not 100% guaranteed. Flushing may occur more frequently due to DDL changes and other internal InnoDB activities that cause logs to be flushed independently of the innodb_flush_log_at_trx_commit setting, and sometimes less frequently due to scheduling issues. If logs are flushed once per second, up to one second of transactions can be lost in a crash. If logs are flushed more or less frequently than once per second, the amount of transactions that can be lost varies accordingly.

*key_buffer_Size - 16M - https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

*innodb_flush_log_at_trx_commit = 0 - https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

#Added Values to /etc/my.cnf on RHEL Sql Server 

innodb_buffer_pool_size=12G 
innodb_file_per_table=1 
key_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0