InnoDB parameters which needs tuning/attention at 1st glance:
innodb_file_per_table
=================
Variable Scope: Global
Dynamic Variable: Yes
Type: Boolean
Default: On
Definition: When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table in a separate .ibd file, rather than in the system tablespace. The storage for these InnoDB tables is reclaimed when the tables are dropped or truncated.
Note: Be aware that enabling innodb_file_per_table also means that an ALTER TABLE operation will move InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY). An exception to this rule is for tables that were placed in the system tablespace using the TABLESPACE=innodb_system option with CREATE TABLE or ALTER TABLE. These tables are unaffected by the innodb_file_per_table setting and can only be moved to file-per-table tablespaces using ALTER TABLE ... TABLESPACE=innodb_file_per_table.
By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called "multiple tablespaces" because each table that is created when this setting is in effect has its own tablespace.
.frm - table format file
.ibd - contains index and data
When innodb_file_per_table is disabled, InnoDB stores the data for all tables and indexes in the ibdata files that make up the system tablespace. This setting reduces the performance overhead of filesystem operations for operations such as DROP TABLE or TRUNCATE TABLE.
Optimal Value: ON
######################################################################
######################################################################
innodb_buffer_pool_size
===================
Variable Scope: Global
Dynamic Variable(>= 5.7.5): Yes
Dynamic Variable(<= 5.7.4): No
Type: Integer
Default: 128M
The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The maximum value depends on the CPU architecture (2^64-1 on 64 bit architecture).
The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur:
- Competition for physical memory might cause paging in the operating system.
- InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.
Note:
- When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.
- As of MySQL 5.7.5, when you increase or decrease buffer pool size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128 MB.
Optimal Value: 70% of RAM dedicated to server
######################################################################
######################################################################
innodb_log_file_size
================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default: 48M
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. A pair of 255 GB log files, for example, would allow you to approach the limit but not exceed it. The default value is 48MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration.
Optimal Value: 128M to 256M
######################################################################
######################################################################
innodb_log_buffer_size
==================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default(>= 5.7.6): 16M
Default(<= 5.7.5): 8M
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value changed from 8MB to 16MB in 5.7.6 with the introduction of 32k and 64k innodb_page_size values. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O.
Optimal Value: 32M to 64M
######################################################################
######################################################################
innodb_flush_log_at_trx_commit
=========================
Variable Scope: Global
Dynamic Variable: Yes
Type: enumeration
Default: 1
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 up to a second of transactions in a crash.
- The default value of 1 is required for full ACID compliance. 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.
- With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash.
- With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.
Note:
- DDL changes and other internal InnoDB activities flush the InnoDB log independent of the innodb_flush_log_at_trx_commit setting.
- InnoDB's crash recovery works regardless of the innodb_flush_log_at_trx_commit setting. Transactions are either applied entirely or erased entirely.
Optimal Value:
At Primary (Master) Server: innodb_flush_log_at_trx_commit=1
At Secondary (Slave) Server: innodb_flush_log_at_trx_commit=2
######################################################################
######################################################################
innodb_flush_method
=================
Variable Scope: Global
Dynamic Variable: No
Type: string
Default: NULL
Defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput. If innodb_flush_method=NULL on a Unix-like system, the fsync option is used by default.
The innodb_flush_method options for Unix-like systems include:
- fsync: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting.
- O_DSYNC: InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.
- O_DIRECT: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.
- O_DIRECT_NO_FSYNC: InnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call afterward. This setting is suitable for some types of file systems but not others.
How each settings affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine the Innodb_data_fsyncs status variable to see the overall number of fsync() calls for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system's file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with hardware and workload that reflect your production environment.
Optimal Value:
innodb_flush_method=O_DIRECT
######################################################################
######################################################################
innodb_flush_neighbors
===================
Variable Scope: Global
Dynamic Variable: Yes
Type: enumeration
Default: 1
Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent.
- The default value of 1 flushes contiguous dirty pages in the same extent from the buffer pool.
- A setting of 0 turns innodb_flush_neighbors off and no other dirty pages are flushed from the buffer pool.
- A setting of 2 flushes dirty pages in the same extent from the buffer pool.
When the table data is stored on a traditional HDD storage device, flushing such neighbour pages in one operation reduces I/O overhead (primarily for disk seek operations) compared to flushing individual pages at different times. For table data stored on SSD, seek time is not a significant factor and you can turn this setting off to spread out the write operations.
Optimal:
innodb_flush_neighbors=0
######################################################################
######################################################################
innodb_read_io_threads
===================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default: 4
The number of I/O threads for read operations in InnoDB.
innodb_write_io_threads
===================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default: 4
The number of I/O threads for write operations in InnoDB.
Note:
- InnoDB uses background threads to service various types of I/O requests. You can configure the number of background threads that service read and write I/O on data pages, using the configuration parameters innodb_read_io_threads and innodb_write_io_threads. These parameters signify the number of background threads used for read and write requests respectively. They are effective on all supported platforms. You can set the value of these parameters in the MySQL option file (my.cnf or my.ini); you cannot change them dynamically. The default value for these parameters is 4 and the permissible values range from 1-64.
- The purpose of this change is to make InnoDB more scalable on high end systems. Each background thread can handle up to 256 pending I/O requests. A major source of background I/O is the read-ahead requests. InnoDB tries to balance the load of incoming requests in such way that most of the background threads share work equally. InnoDB also attempts to allocate read requests from the same extent to the same thread to increase the chances of coalescing the requests together.
Optimal Value:
innodb_write_io_threads=8
innodb_read_io_threads=8
######################################################################
######################################################################
References:
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-performance-multiple_io_threads.html
innodb_file_per_table
=================
Variable Scope: Global
Dynamic Variable: Yes
Type: Boolean
Default: On
Definition: When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table in a separate .ibd file, rather than in the system tablespace. The storage for these InnoDB tables is reclaimed when the tables are dropped or truncated.
Note: Be aware that enabling innodb_file_per_table also means that an ALTER TABLE operation will move InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY). An exception to this rule is for tables that were placed in the system tablespace using the TABLESPACE=innodb_system option with CREATE TABLE or ALTER TABLE. These tables are unaffected by the innodb_file_per_table setting and can only be moved to file-per-table tablespaces using ALTER TABLE ... TABLESPACE=innodb_file_per_table.
By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called "multiple tablespaces" because each table that is created when this setting is in effect has its own tablespace.
.frm - table format file
.ibd - contains index and data
When innodb_file_per_table is disabled, InnoDB stores the data for all tables and indexes in the ibdata files that make up the system tablespace. This setting reduces the performance overhead of filesystem operations for operations such as DROP TABLE or TRUNCATE TABLE.
Optimal Value: ON
######################################################################
######################################################################
innodb_buffer_pool_size
===================
Variable Scope: Global
Dynamic Variable(>= 5.7.5): Yes
Dynamic Variable(<= 5.7.4): No
Type: Integer
Default: 128M
The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The maximum value depends on the CPU architecture (2^64-1 on 64 bit architecture).
The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur:
- Competition for physical memory might cause paging in the operating system.
- InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.
Note:
- When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.
- As of MySQL 5.7.5, when you increase or decrease buffer pool size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128 MB.
Optimal Value: 70% of RAM dedicated to server
######################################################################
######################################################################
innodb_log_file_size
================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default: 48M
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. A pair of 255 GB log files, for example, would allow you to approach the limit but not exceed it. The default value is 48MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration.
Optimal Value: 128M to 256M
######################################################################
######################################################################
innodb_log_buffer_size
==================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default(>= 5.7.6): 16M
Default(<= 5.7.5): 8M
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value changed from 8MB to 16MB in 5.7.6 with the introduction of 32k and 64k innodb_page_size values. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O.
Optimal Value: 32M to 64M
######################################################################
######################################################################
innodb_flush_log_at_trx_commit
=========================
Variable Scope: Global
Dynamic Variable: Yes
Type: enumeration
Default: 1
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 up to a second of transactions in a crash.
- The default value of 1 is required for full ACID compliance. 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.
- With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash.
- With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.
Note:
- DDL changes and other internal InnoDB activities flush the InnoDB log independent of the innodb_flush_log_at_trx_commit setting.
- InnoDB's crash recovery works regardless of the innodb_flush_log_at_trx_commit setting. Transactions are either applied entirely or erased entirely.
Optimal Value:
At Primary (Master) Server: innodb_flush_log_at_trx_commit=1
At Secondary (Slave) Server: innodb_flush_log_at_trx_commit=2
######################################################################
######################################################################
innodb_flush_method
=================
Variable Scope: Global
Dynamic Variable: No
Type: string
Default: NULL
Defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput. If innodb_flush_method=NULL on a Unix-like system, the fsync option is used by default.
The innodb_flush_method options for Unix-like systems include:
- fsync: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting.
- O_DSYNC: InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.
- O_DIRECT: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.
- O_DIRECT_NO_FSYNC: InnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call afterward. This setting is suitable for some types of file systems but not others.
How each settings affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine the Innodb_data_fsyncs status variable to see the overall number of fsync() calls for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system's file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with hardware and workload that reflect your production environment.
Optimal Value:
innodb_flush_method=O_DIRECT
######################################################################
######################################################################
innodb_flush_neighbors
===================
Variable Scope: Global
Dynamic Variable: Yes
Type: enumeration
Default: 1
Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent.
- The default value of 1 flushes contiguous dirty pages in the same extent from the buffer pool.
- A setting of 0 turns innodb_flush_neighbors off and no other dirty pages are flushed from the buffer pool.
- A setting of 2 flushes dirty pages in the same extent from the buffer pool.
When the table data is stored on a traditional HDD storage device, flushing such neighbour pages in one operation reduces I/O overhead (primarily for disk seek operations) compared to flushing individual pages at different times. For table data stored on SSD, seek time is not a significant factor and you can turn this setting off to spread out the write operations.
Optimal:
innodb_flush_neighbors=0
######################################################################
######################################################################
innodb_read_io_threads
===================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default: 4
The number of I/O threads for read operations in InnoDB.
innodb_write_io_threads
===================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default: 4
The number of I/O threads for write operations in InnoDB.
Note:
- InnoDB uses background threads to service various types of I/O requests. You can configure the number of background threads that service read and write I/O on data pages, using the configuration parameters innodb_read_io_threads and innodb_write_io_threads. These parameters signify the number of background threads used for read and write requests respectively. They are effective on all supported platforms. You can set the value of these parameters in the MySQL option file (my.cnf or my.ini); you cannot change them dynamically. The default value for these parameters is 4 and the permissible values range from 1-64.
- The purpose of this change is to make InnoDB more scalable on high end systems. Each background thread can handle up to 256 pending I/O requests. A major source of background I/O is the read-ahead requests. InnoDB tries to balance the load of incoming requests in such way that most of the background threads share work equally. InnoDB also attempts to allocate read requests from the same extent to the same thread to increase the chances of coalescing the requests together.
Optimal Value:
innodb_write_io_threads=8
innodb_read_io_threads=8
######################################################################
######################################################################
References:
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-performance-multiple_io_threads.html
No comments:
Post a Comment