Database Tuning (Advanced Topic)

KB Home   |   Database Tuning (Advanced Topic)

Main.DatabaseTuning History

Hide minor edits - Show changes to output

September 11, 2019, at 04:31 PM by 207.173.252.92 -
Changed line 28 from:
->Number of seconds that connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled.  Zero means no enforcement and excess connections will not be removed, however it setting to zero is '''NOT''' recommended, as connections will be wasted .  60 seconds is a reasonable configuration, but could be set as low as 10 seconds to free connections quickly.  This MUST always be set to a value equal to or lower than [@database.pool.max-idle-secs@].
to:
->Number of seconds that connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled.  Zero means no enforcement and excess connections will not be removed. However, setting to zero is '''NOT''' recommended, as connections will be wasted .  60 seconds is a reasonable configuration, but could be set as low as 10 seconds to free connections quickly.  This MUST always be set to a value equal to or lower than [@database.pool.max-idle-secs@].
March 28, 2017, at 02:50 AM by matt - Add IMPORTANT note.
Changed lines 3-4 from:
This knowledge base article is targeted at experienced `DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or `MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  `DBAs are also encourage to speak with the PaperCut development team for advice.
to:
'''IMPORTANT: PaperCut's default database settings are suitable for customers of all sizes.  Tuning these settings is not typically required.    If you are experience database connection issues, please contact support before experimenting with these settings. '''

This knowledge base article is targeted at experienced `DBAs running
PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or `MySQL. 
Deleted lines 11-16:

!!!database.pool.min-pool-size
->Minimum number of database connections the pool will maintain at any given time.  Do not set this too high, as it will be very wasteful of database resources.  20 is a good default for most sites, and we would not recommend this be changed for the vast majority of sites.

-->''Default:'' [@database.pool.min-pool-size=20@]

Changed lines 13-14 from:
->Maximum number of database connections the pool will maintain at any given time.  If your DB has a hard limit (e.g. Oracle), consider setting this to slightly below the hard-limit.  e.g. if the hard-limit is 200 then set to 190.  However, we do NOT recommend reducing this as it may cause problems under high system load.  If you are seeing a high number of connections being uses consistently, please contact support.
to:
->Maximum number of database connections the pool will maintain at any given time.  If your DB has a hard limit (e.g. Oracle or MySQL), consider setting this to slightly below the hard-limit.  e.g. if the DB hard-limit is 200 then set to 190.  However, we generally to NOT recommend reducing this as it may cause problems under high system load.  If you are seeing consistently high database connection usage, please contact support.
Added lines 16-20:

!!!database.pool.min-pool-size
->Minimum number of database connections the pool will maintain at any given time.  Do not set this too high, as it will be very wasteful of database resources.  20 is a good default for most sites, and we would not recommend this be changed for the vast majority of sites.

-->''Default:'' [@database.pool.min-pool-size=20@]
March 28, 2017, at 02:44 AM by matt - Some tweaks
Changed lines 12-15 from:
->Minimum number of database connections the pool will maintain at any given time.  Do not set this too high, as it will be very wasteful of database resources.  5 is a good default for most sites, and we would not recommend more than 15 for the vast majority of sites.

-->''Default:'' [@database.pool.min-pool-size=5@]
to:
->Minimum number of database connections the pool will maintain at any given time.  Do not set this too high, as it will be very wasteful of database resources.  20 is a good default for most sites, and we would not recommend this be changed for the vast majority of sites.

-->''Default:'' [@database.pool.min-pool-size=20@]
Changed lines 17-20 from:
->Maximum number of database connections the pool will maintain at any given time.  If your DB has a hard limit (e.g. Oracle), consider setting this to slightly below the hard-limit.  e.g. if the hard-limit is 200 then set to 190.  HOWEVER, we do not recommend reducing this as it may cause problems under high system load.  We would instead recommend ensuring your database allows enough connections to meet peak load.

-->''Default:'' [@database.pool.max-pool-size=<Dynamic based on system configuration>@]
to:
->Maximum number of database connections the pool will maintain at any given time.  If your DB has a hard limit (e.g. Oracle), consider setting this to slightly below the hard-limit.  e.g. if the hard-limit is 200 then set to 190.  However, we do NOT recommend reducing this as it may cause problems under high system load.   If you are seeing a high number of connections being uses consistently, please contact support.

-->''Default:'' [@database.pool.max-pool-size=<Dynamic based on system - typically 400>@]
Changed line 27 from:
->Number of seconds that connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled.  Zero means no enforcement and excess connections will not be removed, however it setting to zero is '''NOT''' recommended, as connections will be wasted and become stale.  60 seconds is a reasonable configuration.  This MUST always be set to a value equal to or lower than [@database.pool.max-idle-secs@].
to:
->Number of seconds that connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled.  Zero means no enforcement and excess connections will not be removed, however it setting to zero is '''NOT''' recommended, as connections will be wasted .  60 seconds is a reasonable configuration, but could be set as low as 10 seconds to free connections quickly.  This MUST always be set to a value equal to or lower than [@database.pool.max-idle-secs@].
April 10, 2014, at 07:05 AM by matt - more tips on having a good configuration
Added line 13:
Changed lines 22-23 from:
->Seconds a connection can remain pooled but unused before being discarded.  This allows idle connections to be periodically renewed to clear out stale connections (e.g. if DB is restarted).
to:
->Seconds a connection can remain pooled but unused before being discarded.  This allows idle connections to be periodically renewed to clear out stale connections (e.g. if DB is restarted).  This MUST always be set to a value greater than or equal to [@database.pool.max-idle-secs-excess-connections@]
Changed lines 27-28 from:
->Number of seconds that connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled.  Zero means no enforcement and excess connections will not be removed, however it setting to zero is NOT recommended, as it will be wasteful.  60 seconds is a reasonable configuration.
to:
->Number of seconds that connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled.  Zero means no enforcement and excess connections will not be removed, however it setting to zero is '''NOT''' recommended, as connections will be wasted and become stale.  60 seconds is a reasonable configuration.  This MUST always be set to a value equal to or lower than [@database.pool.max-idle-secs@].
Changed lines 32-33 from:
->Test idle connections every number of sections.  If the connection is found to be dead it will be removed from the pool.
to:
->Test idle connections every number of seconds.  If the connection is found to be dead it will be removed from the pool.  If the connection idle timeouts are set to 60 seconds or less, it is recommended to disable the connection test by setting this value higher than idle time.
Changed line 40 from:
[-keywords: database connection limits, RDMS, performance, data base configuration -]
to:
[-keywords: database connection limits, RDMS, performance, data base configuration -]
Changed lines 16-17 from:
->Maximum number of database connections the pool will maintain at any given time.  If your DB has a hard limit (e.g. Oracle), consider setting this to slightly below the hard-limit.  e.g. if the hard-limit is 200 then set to 190.
to:
->Maximum number of database connections the pool will maintain at any given time.  If your DB has a hard limit (e.g. Oracle), consider setting this to slightly below the hard-limit.  e.g. if the hard-limit is 200 then set to 190.  HOWEVER, we do not recommend reducing this as it may cause problems under high system load.  We would instead recommend ensuring your database allows enough connections to meet peak load.
Changed lines 29-31 from:
-->''Default:'' [@database.pool.min-pool-size=120@]

to:
-->''Default:'' [@database.pool.test-period-secs=120@]

Changed lines 3-5 from:
This knowledge base article is targeted at experienced `DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or `MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  `DBAs are also encourage to speak with the PaperCut development team for advise.

Database tuning parameters are configured via alongside the database connection details in the server.properties file located at:
to:
This knowledge base article is targeted at experienced `DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or `MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  `DBAs are also encourage to speak with the PaperCut development team for advice.

Database tuning parameters are configured via the database connection details in the server.properties file located at:
Changed line 16 from:
->Maximum number of database connections the pool will maintain at any given time.
to:
->Maximum number of database connections the pool will maintain at any given time.  If your DB has a hard limit (e.g. Oracle), consider setting this to slightly below the hard-limit.  e.g. if the hard-limit is 200 then set to 190.
Changed lines 17-18 from:
-->''Default:'' [@database.pool.min-pool-size=<Dynamic based on system configuration>@]
to:
-->''Default:'' [@database.pool.max-pool-size=<Dynamic based on system configuration>@]
Changed lines 17-18 from:
-->''Default:'' Dynamically determined based on system configuration
to:
-->''Default:'' [@database.pool.min-pool-size=<Dynamic based on system configuration>@]
Changed lines 3-4 from:
This knowledge base article is targeted at experienced `DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or `MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  Some new options listed below were introduced in version 9.6.  `DBAs are also encourage to speak with the PaperCut development team for advise.
to:
This knowledge base article is targeted at experienced `DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or `MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  `DBAs are also encourage to speak with the PaperCut development team for advise.
Changed line 12 from:
->Minimum number of database connections the pool will maintain at any given time.
to:
->Minimum number of database connections the pool will maintain at any given time.  Do not set this too high, as it will be very wasteful of database resources.  5 is a good default for most sites, and we would not recommend more than 15 for the vast majority of sites.
Changed lines 17-18 from:
-->''Default:'' Approx. [@database.pool.max-pool-size=550@]
to:
-->''Default:'' Dynamically determined based on system configuration
Changed line 20 from:
->Seconds a connection can remain pooled but unused before being discarded.
to:
->Seconds a connection can remain pooled but unused before being discarded.  This allows idle connections to be periodically renewed to clear out stale connections (e.g. if DB is restarted).
Changed line 24 from:
->Number of seconds that connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled. Zero means no enforcement and excess connections will not be removed.
to:
->Number of seconds that connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled.  Zero means no enforcement and excess connections will not be removed, however it setting to zero is NOT recommended, as it will be wasteful.  60 seconds is a reasonable configuration.
Changed lines 3-4 from:
This knowledge base article is targeted at experienced `DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or `MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  Most of the options listed below were introduced in version 9.6.
to:
This knowledge base article is targeted at experienced `DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or `MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  Some new options listed below were introduced in version 9.6.  `DBAs are also encourage to speak with the PaperCut development team for advise.
Changed line 12 from:
->Minimum number of Connections a pool will maintain at any given time.
to:
->Minimum number of database connections the pool will maintain at any given time.
Changed line 16 from:
->Maximum number of Connections a pool will maintain at any given time.
to:
->Maximum number of database connections the pool will maintain at any given time.
Changed line 20 from:
->Seconds a Connection can remain pooled but unused before being discarded.
to:
->Seconds a connection can remain pooled but unused before being discarded.
Changed line 24 from:
->Number of seconds that Connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled. Zero means no enforcement, excess Connections are not idled out. 
to:
->Number of seconds that connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled. Zero means no enforcement and excess connections will not be removed.
Changed lines 3-4 from:
This knowledge base article is targeted at experienced 'DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or 'MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  Most of the options listed below were introduced in version 9.6.
to:
This knowledge base article is targeted at experienced `DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or `MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  Most of the options listed below were introduced in version 9.6.
Changed lines 3-4 from:
This knowledge base article is targeted at experienced DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  Most of the options listed below were introduced in version 9.6.
to:
This knowledge base article is targeted at experienced 'DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or 'MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  Most of the options listed below were introduced in version 9.6.
Changed line 35 from:
[-keywords: database connection limits, RDMS, performance -]
to:
[-keywords: database connection limits, RDMS, performance, data base configuration -]
Added lines 1-2:
(:title Database Tuning (Advanced Topic) :)
Changed lines 6-7 from:
-->@@[install-path]/server/server.properties@@
to:
->@@[install-path]/server/server.properties@@
Added line 10:
Added lines 31-35:

----
''Categories:'' [[!Databases]], [[!Architecture]]
----
[-keywords: database connection limits, RDMS, performance -]
Changed line 7 from:
@@database.pool.min-pool-size@@
to:
!!!database.pool.min-pool-size
Changed lines 9-15 from:
->''Default:'' [@database.pool.min-pool-size=5@]

@@database.pool.max-pool-size@@
Maximum number of Connections a pool will maintain at any given time.
->''Default:'' Approx. [@database.pool.max-pool-size=550@]

@@database.pool.max-idle-secs@@
to:
-->''Default:'' [@database.pool.min-pool-size=5@]

!!!database.pool.max-pool-size
->Maximum number of Connections a pool will maintain at any given time.
-->''Default:'' Approx. [@database.pool.max-pool-size=550@]

!!!database.pool.max-idle-secs
Changed lines 17-19 from:
->''Default:'' [@database.pool.max-idle-secs=600@]

@@database.pool.max-idle-secs-excess-connections@@
to:
-->''Default:'' [@database.pool.max-idle-secs=600@]

!!!database.pool.max-idle-secs-excess-connections
Changed lines 21-23 from:
->''Default:'' [@database.pool.max-idle-secs-excess-connections=60@]

@@database.pool.test-period-secs@@
to:
-->''Default:'' [@database.pool.max-idle-secs-excess-connections=60@]

!!!database.pool.test-period-secs
Changed lines 25-26 from:
->''Default:'' [@database.pool.min-pool-size=120@]
to:
-->''Default:'' [@database.pool.min-pool-size=120@]
Added lines 7-26:
@@database.pool.min-pool-size@@
->Minimum number of Connections a pool will maintain at any given time.
->''Default:'' [@database.pool.min-pool-size=5@]

@@database.pool.max-pool-size@@
Maximum number of Connections a pool will maintain at any given time.
->''Default:'' Approx. [@database.pool.max-pool-size=550@]

@@database.pool.max-idle-secs@@
->Seconds a Connection can remain pooled but unused before being discarded.
->''Default:'' [@database.pool.max-idle-secs=600@]

@@database.pool.max-idle-secs-excess-connections@@
->Number of seconds that Connections in excess of [@min-pool-size@] should be permitted to remain idle in the pool before being culled. Zero means no enforcement, excess Connections are not idled out.
->''Default:'' [@database.pool.max-idle-secs-excess-connections=60@]

@@database.pool.test-period-secs@@
->Test idle connections every number of sections.  If the connection is found to be dead it will be removed from the pool.
->''Default:'' [@database.pool.min-pool-size=120@]

Added lines 1-6:
This knowledge base article is targeted at experienced DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or MySQL.  Sites experimenting with database tuning parameters are advised to upgrade to the latest version of PaperCut.  Most of the options listed below were introduced in version 9.6.

Database tuning parameters are configured via alongside the database connection details in the server.properties file located at:
-->@@[install-path]/server/server.properties@@
This file may be edited with a standard text editor.  The following tuning parameters may be appending to this file:

Comments

Share your findings and experience with other PaperCut users. Feel free to add comments and suggestions about this Knowledge Base article. Please don't use this for support requests.

Article last modified on September 11, 2019, at 04:31 PM
Printable View   |   Article History   |   Edit Article