InnoDB Purging
---------------------
- The purge operations (a type of garbage collection) that InnoDB performs automatically is now done in one or more separate threads, rather than as part of the master thread. This change improves scalability, because the main database operations run independently from maintenance work happening in the background.
- To control this feature, increase the value of the configuration option innodb_purge_threads. If DML action is concentrated on a single table or a few tables, keep the setting low so that the threads do not contend with each other for access to the busy tables. If DML operations are spread across many tables, increase the setting. Its maximum is 32.
- There is another related configuration option, innodb_purge_batch_size with a default value of 300 and maximum value of 5000. This option is mainly intended for experimentation and tuning of purge operations, and should not be interesting to typical users.
innodb_purge_threads :: The number of background threads devoted to the InnoDB purge operation. A minimum value of 1 signifies that the purge operation is always performed by background threads, never as part of the master thread. Running the purge operation in one or more background threads helps reduce internal contention within InnoDB, improving scalability. Increasing the value to greater than 1 creates that many separate purge threads, which can improve efficiency on systems where DML operations are performed on multiple tables. The maximum is 32.
innodb_purge_batch_size :: Defines the number of undo log pages that purge parses and processes in one batch from the history list. In a multi-threaded purge configuration, the coordinator purge thread divides innodb_purge_batch_size by innodb_purge_threads and assigns that number of pages to each purge thread. The innodb_purge_batch_size option also defines the number of undo log pages that purge frees after every 128 iterations through the undo logs.
The innodb_purge_batch_size option is intended for advanced performance tuning in combination with the innodb_purge_threads setting. Most MySQL users need not change innodb_purge_batch_size from its default value.
References:
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_purge_threads
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_purge_threads
---------------------
- The purge operations (a type of garbage collection) that InnoDB performs automatically is now done in one or more separate threads, rather than as part of the master thread. This change improves scalability, because the main database operations run independently from maintenance work happening in the background.
- To control this feature, increase the value of the configuration option innodb_purge_threads. If DML action is concentrated on a single table or a few tables, keep the setting low so that the threads do not contend with each other for access to the busy tables. If DML operations are spread across many tables, increase the setting. Its maximum is 32.
- There is another related configuration option, innodb_purge_batch_size with a default value of 300 and maximum value of 5000. This option is mainly intended for experimentation and tuning of purge operations, and should not be interesting to typical users.
innodb_purge_threads :: The number of background threads devoted to the InnoDB purge operation. A minimum value of 1 signifies that the purge operation is always performed by background threads, never as part of the master thread. Running the purge operation in one or more background threads helps reduce internal contention within InnoDB, improving scalability. Increasing the value to greater than 1 creates that many separate purge threads, which can improve efficiency on systems where DML operations are performed on multiple tables. The maximum is 32.
innodb_purge_batch_size :: Defines the number of undo log pages that purge parses and processes in one batch from the history list. In a multi-threaded purge configuration, the coordinator purge thread divides innodb_purge_batch_size by innodb_purge_threads and assigns that number of pages to each purge thread. The innodb_purge_batch_size option also defines the number of undo log pages that purge frees after every 128 iterations through the undo logs.
The innodb_purge_batch_size option is intended for advanced performance tuning in combination with the innodb_purge_threads setting. Most MySQL users need not change innodb_purge_batch_size from its default value.
References:
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_purge_threads
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_purge_threads
No comments:
Post a Comment