Innodb Settings:
innodb_buffer_pool_size:
The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data.
innodb_buffer_pool_instances:
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. Increase up to 16 if you have large number of CPU cores.
innodb_sort_buffer_size:
Specifies the size of sort buffers in BYTES, used to sort data during creation of an InnoDB index. Default is 1048576.
innodb_flush_method:
O_DIRECT - uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. Bypass OS cache. Good choice for most IO subsystems, default - NULL
innodb_flush_log_at_trx_commit:
With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk. Default 1, 1 – durable, 0 – not durable, 2 – in between
log-bin[=base_name]:
Permitted value - File name. Enables binary logging. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. The binary log is a sequence of files with a base name and numeric extension. If you supply a value for the --log-bin option, the value is used as the base name for the log sequence. The server creates binary log files in sequence by adding a numeric
suffix to the base name.
log-bin-index[=file_name]:
The index file for binary log file names. In absence of this parameter, MySQL uses host_name-bin.index as the file name.
sync_binlog:
Is set to a value greater than 0. This number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed. For value 1, binlog durability but high performance cost, default 0.
innodb_io_capacity:
Sets an upper limit on the number of I/O operations performed / second by InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer. Tell Innodb how much IO storage system can handle, default 200.
innodb_io_capacity_max:
Setting is a total limit for all buffer pool instances.
innodb_old_blocks_pct:
Approximate percentage of the InnoDB buffer pool used for the old block sublist. Range of values is 5 to 95. Default is 37. Recommended 70.
innodb_old_blocks_time:
Specifies how long in milliseconds a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist.
Non-zero values protect against the buffer pool being filled by data that is referenced only for a brief period, such as during a full table scan. Increasing this value offers more protection against full table scans interfering with data cached in the buffer pool.
Default is 1000. Recommended 5000.
innodb_lock_wait_timeout:
Before issuing an ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction, the length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds.
innodb_log_buffer_size:
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. Recommended =32M.
innodb_log_file_size:
The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. Recommended size is 1G.
innodb_max_purge_lag:
The unit value is microseconds. The default 0 (no delays). Controls how to delay INSERT, UPDATE, and DELETE operations when purge operations are lagging. Recommended 10000000.
innodb_max_purge_lag_delay:
Specifies the maximum delay in microseconds for the delay imposed by the innodb_max_purge_lag configuration option. The default 0, means that there is no upper limit imposed on the delay interval. Recommended 10000.
innodb_change_buffer_max_size:
Percentage of the total size of the buffer pool. You can increase it for heavy DML activity, or decrease it for with unchanging data used for reporting. Default is 25. Recommended 5.
innodb_adaptive_hash_index:
Default is ON. Whether the InnoDB adaptive hash index is enabled or disabled. The adaptive hash index (AHI) lets InnoDB perform more like an in-memory database on systems with appropriate combinations of workload and ample memory for the buffer pool, without sacrificing any transactional features or reliability.
innodb_buffer_pool_dump_pct:
Specifies the percentage of the most recently used pages for each buffer pool to read out and dump. Recommended 80.
innodb_doublewrite:
When enabled InnoDB stores all data twice, first to the doublewrite buffer, then to the actual data files. Recommended disable it. Default on.
innodb_flush_neighbors:
Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent. Default is 1. Valid value 0,1,2. Recommended 0. 0 disable Innodb Flush Neighbors if running on SSD.
• Innodb Buffer Pool Scan Resistance
innodb_open_files:
It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum is 10. The default is 300 if innodb_file_per_table is not enabled, and the higher of 300 and table_open_cache otherwise.
innodb_page_size:
Specifies the page size for all InnoDB tablespaces in a MySQL instance. Page size can be 64k, 32k, 16k (the default), 8k, or 4k. Alternatively, you can specify page size in bytes (65536, 32768, 16384, 8192, 4096). innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. Smaller Page sizes better for some workloads 4096.
sort_buffer_size:
allocate filesort buffer size, not specific to any storage engine, must accommodate fifteen tuples (row (database) In the context of a relational database, a row—also called a tuple—represents a single), implicitly structured data item in a table.). Think to increase when sort_merge_passes per second in SHOW GLOBAL STATUS is many. Performance of ORDER BY or GROUP BY operations will be improved, which you
can't improved after query optimization and indexing. Default - 262144 Bytes (256 KB), Maximum - 4294967295 (4 GB)
read_buffer_size:
when sorting rows for ORDER BY, it CACHES Indexes in a temporary file and not in a temporary table, for bulk insert into partitions, caching results of nested queries, for all storage engines. Max - 2147479552 (2 GB), Default - 131072 (128 MB),
Max - 2147479552 (2 GB)
read_rnd_buffer_size:
Large value can improve ORDER BY performance by a lot. Default 262144 (256 KB), Max 2147483647 (2 GB). Rahter than setting large value for system, change the session variable only from within those clients that need to run large queries.
join_buffer_size:
Used for plain index scans, range index scans, and joins that don't use indexes and thus perform full table scans. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary. Default is 256 KB, Max is 4 GB.
max_heap_table_size:
Sets maximum size to which user-created MEMORY tables are permitted to grow. Also used in conjunction with tmp_table_size. Default - 16777216 (16 MB), Max is 4GB.
tmp_table_size:
maximum size of internal in-memory temporary tables. Default - 16777216 (16 MB). Max size differ for 32 bit an 64 bit.
table_open_cache:
number of open tables for all threads. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.
Default - 4000 No., Max - 524288 No.
table_open_cache_instances:
Partition open table cache into small cache to reduce contention. Default 16 No. Max 64 No.
table_definition_cache:
You can keep large to speed up opening of tables. Default is -1 (Auto sizing). Min 400 No. Max - 524288
max_allowed_packet:
The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. Default is 64MB, Max is 1 GB.
max_connections:
The maximum permitted number of simultaneous client connections. Default 151, and max 1000000.
thread_cache_size:
When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Default -1 (signifies autosizing; do not assign this literal value). Max - 16384
Operating System Settings:
• Number of Open Files for MySQL Process
• If using large number of connections or large number of tables
• Filesystem
• XFS or EXT4
• Mount –o noatime
• Disk Scheduler
• Deadline or Noop
• cat /sys/block/sda/queue/scheduler
• NVMe Storage Does not have Scheduler
• NUMA
• Only on NUMA (Multi-Socket Hardware)
• MySQL 5.7 Option innodb_numa_interleave=1
• Percona Server 5.5+ numa_interleave=1
• CPU Governor
• Saving Power can cost you performance
• cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor
• cpufreq-set -r -g performance
Swapping:
If using swap
• Set vm.swappiness=1
• echo 'vm.swappiness = 1' >> /etc/sysctl.conf
Settings to Think About:
• How Many Connections do you need ?
• max_connections=N
• How Many Tables do you have ?
• table_open_cache=X
• table_definition_cache=Y
What Character Set do you use ?
• character-set-server=utf8
• collation-server=utf8_general_ci
How Long to Keep Binary Log
• log_bin
• expire_log_days=N
Ref.:
https://www.saotn.org/mysql-innodb-performance-improvement/
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
https://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/
innodb_buffer_pool_size:
The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data.
innodb_buffer_pool_instances:
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. Increase up to 16 if you have large number of CPU cores.
innodb_sort_buffer_size:
Specifies the size of sort buffers in BYTES, used to sort data during creation of an InnoDB index. Default is 1048576.
innodb_flush_method:
O_DIRECT - uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. Bypass OS cache. Good choice for most IO subsystems, default - NULL
innodb_flush_log_at_trx_commit:
With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk. Default 1, 1 – durable, 0 – not durable, 2 – in between
log-bin[=base_name]:
Permitted value - File name. Enables binary logging. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. The binary log is a sequence of files with a base name and numeric extension. If you supply a value for the --log-bin option, the value is used as the base name for the log sequence. The server creates binary log files in sequence by adding a numeric
suffix to the base name.
log-bin-index[=file_name]:
The index file for binary log file names. In absence of this parameter, MySQL uses host_name-bin.index as the file name.
sync_binlog:
Is set to a value greater than 0. This number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed. For value 1, binlog durability but high performance cost, default 0.
innodb_io_capacity:
Sets an upper limit on the number of I/O operations performed / second by InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer. Tell Innodb how much IO storage system can handle, default 200.
innodb_io_capacity_max:
Setting is a total limit for all buffer pool instances.
innodb_old_blocks_pct:
Approximate percentage of the InnoDB buffer pool used for the old block sublist. Range of values is 5 to 95. Default is 37. Recommended 70.
innodb_old_blocks_time:
Specifies how long in milliseconds a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist.
Non-zero values protect against the buffer pool being filled by data that is referenced only for a brief period, such as during a full table scan. Increasing this value offers more protection against full table scans interfering with data cached in the buffer pool.
Default is 1000. Recommended 5000.
innodb_lock_wait_timeout:
Before issuing an ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction, the length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds.
innodb_log_buffer_size:
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. Recommended =32M.
innodb_log_file_size:
The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. Recommended size is 1G.
innodb_max_purge_lag:
The unit value is microseconds. The default 0 (no delays). Controls how to delay INSERT, UPDATE, and DELETE operations when purge operations are lagging. Recommended 10000000.
innodb_max_purge_lag_delay:
Specifies the maximum delay in microseconds for the delay imposed by the innodb_max_purge_lag configuration option. The default 0, means that there is no upper limit imposed on the delay interval. Recommended 10000.
innodb_change_buffer_max_size:
Percentage of the total size of the buffer pool. You can increase it for heavy DML activity, or decrease it for with unchanging data used for reporting. Default is 25. Recommended 5.
innodb_adaptive_hash_index:
Default is ON. Whether the InnoDB adaptive hash index is enabled or disabled. The adaptive hash index (AHI) lets InnoDB perform more like an in-memory database on systems with appropriate combinations of workload and ample memory for the buffer pool, without sacrificing any transactional features or reliability.
innodb_buffer_pool_dump_pct:
Specifies the percentage of the most recently used pages for each buffer pool to read out and dump. Recommended 80.
innodb_doublewrite:
When enabled InnoDB stores all data twice, first to the doublewrite buffer, then to the actual data files. Recommended disable it. Default on.
innodb_flush_neighbors:
Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent. Default is 1. Valid value 0,1,2. Recommended 0. 0 disable Innodb Flush Neighbors if running on SSD.
• Innodb Buffer Pool Scan Resistance
innodb_open_files:
It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum is 10. The default is 300 if innodb_file_per_table is not enabled, and the higher of 300 and table_open_cache otherwise.
innodb_page_size:
Specifies the page size for all InnoDB tablespaces in a MySQL instance. Page size can be 64k, 32k, 16k (the default), 8k, or 4k. Alternatively, you can specify page size in bytes (65536, 32768, 16384, 8192, 4096). innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. Smaller Page sizes better for some workloads 4096.
sort_buffer_size:
allocate filesort buffer size, not specific to any storage engine, must accommodate fifteen tuples (row (database) In the context of a relational database, a row—also called a tuple—represents a single), implicitly structured data item in a table.). Think to increase when sort_merge_passes per second in SHOW GLOBAL STATUS is many. Performance of ORDER BY or GROUP BY operations will be improved, which you
can't improved after query optimization and indexing. Default - 262144 Bytes (256 KB), Maximum - 4294967295 (4 GB)
read_buffer_size:
when sorting rows for ORDER BY, it CACHES Indexes in a temporary file and not in a temporary table, for bulk insert into partitions, caching results of nested queries, for all storage engines. Max - 2147479552 (2 GB), Default - 131072 (128 MB),
Max - 2147479552 (2 GB)
read_rnd_buffer_size:
Large value can improve ORDER BY performance by a lot. Default 262144 (256 KB), Max 2147483647 (2 GB). Rahter than setting large value for system, change the session variable only from within those clients that need to run large queries.
join_buffer_size:
Used for plain index scans, range index scans, and joins that don't use indexes and thus perform full table scans. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary. Default is 256 KB, Max is 4 GB.
max_heap_table_size:
Sets maximum size to which user-created MEMORY tables are permitted to grow. Also used in conjunction with tmp_table_size. Default - 16777216 (16 MB), Max is 4GB.
tmp_table_size:
maximum size of internal in-memory temporary tables. Default - 16777216 (16 MB). Max size differ for 32 bit an 64 bit.
table_open_cache:
number of open tables for all threads. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.
Default - 4000 No., Max - 524288 No.
table_open_cache_instances:
Partition open table cache into small cache to reduce contention. Default 16 No. Max 64 No.
table_definition_cache:
You can keep large to speed up opening of tables. Default is -1 (Auto sizing). Min 400 No. Max - 524288
max_allowed_packet:
The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. Default is 64MB, Max is 1 GB.
max_connections:
The maximum permitted number of simultaneous client connections. Default 151, and max 1000000.
thread_cache_size:
When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Default -1 (signifies autosizing; do not assign this literal value). Max - 16384
Operating System Settings:
• Number of Open Files for MySQL Process
• If using large number of connections or large number of tables
• Filesystem
• XFS or EXT4
• Mount –o noatime
• Disk Scheduler
• Deadline or Noop
• cat /sys/block/sda/queue/scheduler
• NVMe Storage Does not have Scheduler
• NUMA
• Only on NUMA (Multi-Socket Hardware)
• MySQL 5.7 Option innodb_numa_interleave=1
• Percona Server 5.5+ numa_interleave=1
• CPU Governor
• Saving Power can cost you performance
• cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor
• cpufreq-set -r -g performance
Swapping:
If using swap
• Set vm.swappiness=1
• echo 'vm.swappiness = 1' >> /etc/sysctl.conf
Settings to Think About:
• How Many Connections do you need ?
• max_connections=N
• How Many Tables do you have ?
• table_open_cache=X
• table_definition_cache=Y
What Character Set do you use ?
• character-set-server=utf8
• collation-server=utf8_general_ci
How Long to Keep Binary Log
• log_bin
• expire_log_days=N
Ref.:
https://www.saotn.org/mysql-innodb-performance-improvement/
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
https://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/
Comments
Post a Comment