Task #4531 » 0001-Updated-database-documentation-AUTOVACUUM-tuning.patch
| doc/sphinx/_doclib/database.rst | ||
|---|---|---|
| 129 | 129 |
.. code-block:: shell |
| 130 | 130 | |
| 131 | 131 |
# Original default value 1 |
| 132 |
effective_io_concurrency = 8
|
|
| 132 |
effective_io_concurrency = 4
|
|
| 133 | 133 | |
| 134 |
* Defines the optimal number of parallel IO requests for the disk subsystem. Default value of 1 is aimed at HDDs, SSDs perform best with values of 4 - 8, NVMe SSDs can support much higher values. Chosen value of 8 seems representative of example server's storage capabilities.
|
|
| 134 |
* Defines the optimal number of parallel IO requests for the disk subsystem. Default value of 1 is aimed at non-RAID HDDs; SATA SSDs perform best with values of 4 - 8; NVMe SSDs can support much higher values. Chosen value of 4 seems representative of example server's storage capabilities.
|
|
| 135 | 135 | |
| 136 | 136 |
.. code-block:: shell |
| 137 | 137 | |
| ... | ... | |
| 204 | 204 |
.. code-block:: sql |
| 205 | 205 | |
| 206 | 206 |
# Original default value -1 (use vacuum_cost_limit) |
| 207 |
autovacuum_vacuum_cost_limit = 10000
|
|
| 207 |
autovacuum_vacuum_cost_limit = 5000
|
|
| 208 | 208 | |
| 209 | 209 |
# Original default value 20ms |
| 210 |
autovacuum_vacuum_delay = 0ms |
|
| 210 |
autovacuum_vacuum_delay = 10ms
|
|
| 211 | 211 | |
| 212 |
* These settings limit the automatic ``VACUUM`` operation, when the cost limit is reached (same scale as other costs), the operation is postponed for the length of delay. Default values of cost 200 and delay 20 ms are aimed at very low additional load to be generated by this background task to affect normal operation. As this is mostly CPU bound and runs in single thread, we can allow for more aggressive values in our use case. For large tables such as events, this allows the autovacuum to finish in a few seconds instead of tens of minutes with default values. The risk of cancelling background ``VACUUM`` due to conflicting operation is therefore minimized and the reclamation of invalidated rows is performed much more promptly what reduces index bloat considerably.
|
|
| 212 |
* These settings limit the automatic ``VACUUM`` operation, when the cost limit is reached (same scale as other costs), the operation is postponed for the length of delay. Default values of cost 200 and delay 20 ms are aimed at very low additional load to be generated by this background task to affect normal operation. As this is mostly CPU bound and runs in single thread, we can allow for more aggressive values in our use case. For large tables such as events, this allows the autovacuum to finish in a few minutes instead of tens of minutes with default values. The risk of cancelling background ``VACUUM`` due to conflicting operation is therefore lowered and the reclamation of invalidated rows is performed much more promptly what reduces the index and table bloat considerably. The chosen value of delay still enables brief autovacuum process sleeps during the run, what is aimed at preventing disk subsystem overload as ``VACUUM`` tends to be an IO heavy operation.
|
|
| 213 | 213 | |
| 214 | 214 | |
| 215 | 215 |
Current database schema |