Why is my OpenBoxes slow? Five causes on self-hosted servers

When a self-hosted OpenBoxes feels slow, the cause is usually one of a handful of things. This article walks through five of them, in the order worth checking, with how to recognise each one and what to do about it. It is written for self-hosted installations; the last section says how this works when OpenBoxes Lift runs the server.

First, is it the server at all?#

Two quick checks save a lot of tuning:

• Is everyone slow, or one person? If one user on one machine is slow, look at their browser, extensions and network before the server.
• Is every page slow, or one? A single slow page is usually a large report or list, covered in cause 5. Everything slow at once points at the server.

1. Java does not have enough memory#

OpenBoxes is a Java application running in Tomcat, and it needs more memory than a typical small web app. On our own deployments the process uses about 1.4 GB of memory while starting (the database migrations are the peak) and about 1.2 GB once running. A server or Java heap sized close to those figures leaves no headroom, and the symptoms creep in: pages that get slower over hours or days, pauses of several seconds, and eventually OutOfMemoryError in the Tomcat log (catalina.out).

What to do: set the heap explicitly with -Xmx in Tomcat's CATALINA_OPTS (usually in bin/setenv.sh), with room above those figures, and restart. If the database runs on the same server, leave it enough memory too: giving Java all of the server's RAM just moves the problem to the database.

2. The database has too little memory#

MySQL and MariaDB keep frequently used data in memory in the InnoDB buffer pool, and its default size is only 128 MB. An OpenBoxes database with a few years of transactions is far larger than that, so a default configuration reads from disk for queries that should be served from memory.

What to do: check the current value with SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; and raise it in the database configuration. On a server that runs only the database, a common starting point is half or more of its memory. On a server shared with Tomcat, split the memory deliberately between the two.

3. The background jobs#

OpenBoxes runs scheduled jobs that rebuild its reporting tables. With the default configuration they run overnight, in the server's timezone:

• Midnight: the transaction reporting table refresh, and OpenBoxes' own stock alert email.
• 01:00: the stock-out data refresh and the demand data refresh.
• 02:00: the order summary refresh.

The demand refresh rebuilds a table from your whole request history, and on a large history it can run for a long time and load the database heavily while it does. If people work at night, or your "overnight" is someone else's working day, they feel it.

Two details are easy to miss:

• The same refreshes also run every time OpenBoxes starts. The setting openboxes.refreshAnalyticsDataOnStartup.enabled is on by default, so each restart triggers the stock-out, demand, product availability and order summary refreshes.
• The product availability refresh is not scheduled. The configuration file carries a two-hourly cron line for it, but nothing reads it: OpenBoxes recalculates availability when stock changes.

What to do: each job has a switch and, for most, a schedule under openboxes.jobs in the OpenBoxes configuration file (for example openboxes.jobs.refreshDemandDataJob.enabled and openboxes.jobs.refreshDemandDataJob.cronExpression). Move heavy jobs to a time nobody works, or turn off the ones whose reports you never use. Set them in the configuration file, not as environment variables, and remember that dashboards and reports reading those tables go stale when a job is off.

4. It has just been restarted or upgraded#

A restart is not instant for OpenBoxes. Each start runs the database migrations, which drop every database view and rebuild them at the end, and then the reporting refreshes from cause 3. The application does not serve pages until the views are rebuilt; after that, the reporting refreshes keep the database busy and dashboard figures can be stale. If more than one OpenBoxes server shares the database, restarting one drops the views under the others.

What to do: after a restart, give it time before judging. Watch the Tomcat log for All migrations ran successfully!, and avoid restarting again because it seems slow, which starts the whole sequence over. OpenBoxes upgrade checklist: before, during and after covers the migration step in more detail.

5. One long query everybody waits behind#

Sometimes the server is not short of anything: one heavy query is holding things up. A large report or export over a long date range can do it, and so can a job rebuilding a reporting table while other queries need that table.

What to do: look at what the database is running right now:

SHOW FULL PROCESSLIST;

Look for one query with a very large Time, and for others whose state is Waiting for table metadata lock. The long-running one is your cause.

Be careful about killing a refresh job's query. The demand refresh, for example, builds a new copy of its table, drops the old one and then fills it from the copy. Interrupt it at the wrong moment and that table can be missing until the next successful run. If a user's report is the culprit, ask them to narrow the date range or filter it by location or category instead.

A checking order#

When someone says "it's slow", this order finds the cause fastest:

1. One user or everyone? One page or all?
2. Was it restarted or upgraded in the last hour? If so, wait for the migrations and refreshes.
3. SHOW FULL PROCESSLIST: is one query holding everyone up?
4. Is it the same time every night? Look at the job schedule.
5. Is it getting worse over days? Look at the Java heap and the Tomcat log.
6. Slow on every page, all day, since installation? Look at the buffer pool size.

If OpenBoxes Lift runs the server#

On OpenBoxes Lift, the Java memory, the database configuration and the job schedules are ours to set, and they are not exposed in your instance's settings on any plan. On Shared, OpenBoxes' overnight reporting jobs do not run per instance, and some of the heaviest are switched off, because Shared instances sit on common infrastructure. On Dedicated, your instance has its own database and its own application, and the jobs run on the standard OpenBoxes schedule.

If something is slow on Lift, start with your Instance page in the portal. It shows the latest Response time measured by the platform's health check. Its View access log link lists requests to your instance with their status and how long each took, so you can see whether requests really were slow or failing at the time someone noticed, and View platform log shows activity such as backups, restores, imports and health changes, which can explain a slow spell. status.openboxes.cloud shows any platform incident. If it is still slow, tell us in the support chat what was slow, when it started and whether it affects everyone; Common issues has the browser-side checks.

If you would rather not tune a server at all, Get started compares the plans.

Summary#

• Rule out the browser and the single slow page first.
• Give Java enough heap, and the database a buffer pool far above the 128 MB default.
• Know when the background jobs run, and that the refreshes also run on every start.
• After a restart or upgrade, wait for the migrations to finish before judging.
• SHOW FULL PROCESSLIST finds the one query everyone is waiting behind.