When should I run PaperCut on an External Database?
PaperCut uses a database to store all of its configuration and print log data. By default, PaperCut uses an internal embedded database called Apache Derby as its database. This database is used on 10,000’s of our customers PaperCut installations and has proven to be very reliable and performant. It also has the advantage of being zero-administration, so there is no need to have DBAs to administer and maintain the database.
That said, there are times when there is an advantage to running PaperCut on a dedicated external database system like Microsoft SQL Server, Oracle, Postgres or MySQL. These dedicated databases provide improved performance when running queries over large dataset (e.g. large reports), and also provide improved handling of concurrent operations to provide improved scalability and reduced database locking problems.
For sites with a large number of users, or a large printing volume it is generally recommended to run PaperCut on one of these external databases. Most organizations already have database installations so PaperCut can take advantage of the existing infrastructure (e.g. backups, maintenance, etc).
The process of upgrading to an external database is very straight-forward. This process is described in the manual:
Note: if you’re already running on an external database, and you’re wanting to migrate your database to another server, check out Migrating your database server.
The internal database will often meet a customer’s usage requirements, however there are indicators that a move should be made to an external database. These are outlined below:
If you are running a report over a large data set (e.g. 1000s or millions of print log records) and find that the reports are running slowly, then it is recommended to upgrade to an external database. These external databases are optimized to handle large datasets efficiently.
Out of Memory errors running Reports
If you receive an “Out of Memory Error” running large reports, this can indicate that the internal database doesn’t have enough memory resources to complete the operation. External databases are better at handling large data and will not encounter these issues.
You should also ensure that your system has enough RAM. We recommend at least 2GB of RAM for busy servers. For more information on memory and PaperCut see the following KB article: Increasing the Memory Available to PaperCut NG and PaperCut MF
Locking or Deadlock Errors
When databases insert/update/query data they obtain locks on the data/tables to ensure the data is consistent and valid. For most operations these locks are obtained for a very short period and do not affect the system. If there is a high level of activity, and large reports being run this can increase the amount of locking required. This can result in database locking errors occurring, like the following:
A lock could not be obtained due to a deadlock, cycle of locks and waiters is ….
A lock could not be obtained within the time requested.
In more recent versions of PaperCut when this error occurs a more friendly error message is displayed,
System under heavy load.
If you are encountering these issues, we would recommend upgrading to an external database as outlined above. These dedicated external databases are designed to be highly scalable and provide optimized locking which reduces the likelihood of these problems.
If your PaperCut print server is hosting PaperCut and all your print queues and is becoming overloaded. You can use an external database hosted on another server. This removes the disk and processing load associated with the database to a separate server, which makes more resources available for the print server.
What external database should I use?
PaperCut does not have a preference of external databases that should be utilized, and a large factor in deciding what external database you should use depends on your current knowledge of database administration. More information is available here.
Keywords: database, db, lock, slow, report, timeout, when to upsize