Database Tuning (Advanced Topic)

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. Note: if you’re using MS SQL Server 2016 or later, our recommendation is to ensure you’re using an updated MS SQL JDBC Driver before attempting database tuning. This is known to have significant database performance improvements. See Upgrading the Microsoft SQL JDBC Driver (DB driver) article for more information. This knowledge base article is targeted at experienced DBAs running PaperCut on external databases such as Oracle, Postgresql, Microsoft SQL Server or MySQL. Database tuning parameters are configured via 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: database.pool.max-pool-size 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 do 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. Default: database.pool.max-pool-size=<Dynamic based on system - typically 400> 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 database.pool.max-idle-secs 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 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 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. Default: database.pool.max-idle-secs-excess-connections=60 database.pool.test-period-secs 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. Default: database.pool.test-period-secs=120