In the OJS production stack guide, we examined the application layer. In this second installment, we move one layer deeper: the data layer, where OJS reliability is most often won or lost.
Most journals do not break in the first few months. They degrade after one or two years, as historical volume accumulates and default database settings turn into operational debt.
The Hidden Bottleneck in Year One
Typical symptoms in active editorial environments include:
- Editorial dashboards taking 3 to 8 seconds to load
- Slow internal search and metadata-heavy views
- Latency spikes during revision peaks
- Lock waits and write contention during busy periods
Why MariaDB 10.6 LTS — and When MySQL 8 Also Works
MariaDB 10.6 LTS provides a stable operational baseline for OJS. MySQL 8 can also perform well, provided that InnoDB settings and indexing are properly tuned.
The deciding factor is not the database brand. It is workload-aware configuration, careful measurement, and disciplined operational maintenance.
OJS Tables That Shape Real Performance
1) EAV-Style Settings Tables
OJS relies heavily on metadata tables that behave like Entity-Attribute-Value (EAV) structures:
| Settings Table | Primary Role | Operational Impact |
|---|---|---|
submission_settings |
Dynamic submission metadata | Requires additional joins to reconstruct the full context of a single submission |
publication_settings |
Version-level publication metadata | Adds further joins for each published version |
user_settings |
Extended user metadata | Increases query cost across workflow, assignment, and user-related views |
These tables are one of the main reasons OJS can become slow even when submission volume remains moderate.
2) High-Pressure Transactional Tables
| Table | Dominant Workload | Typical Pressure Point |
|---|---|---|
users |
Read-heavy | Repeated joins across editorial and workflow listings |
stage_assignments |
Read/write | Concurrency spikes in active multi-editor teams |
publications |
Read-heavy | Version metadata joins combined with settings tables |
submission_files |
Read/write | Linear growth as revision rounds accumulate |
sessions |
Continuous read/write | Severe contention when Redis is not used for session storage |
If sessions remain in MariaDB under concurrent traffic, response-time degradation becomes highly predictable.
Query Analysis and Index Strategy
Sustainable performance gains come from query-level analysis, not from generic tuning advice.
Recommended Process
- Enable
slow_query_log— for example, with a 2-second threshold - Rank the worst queries by both frequency and latency
- Run
EXPLAINon critical execution paths - Add targeted single-column or composite indexes
- Re-test under production-like load
Typical Index Opportunities in OJS
| Query Family | Common Filter Path | Index Strategy |
|---|---|---|
| Editorial inbox | status, context_id, date_submitted |
Composite index aligned with workflow listing patterns |
| User lookup | username, email |
Dedicated unique and lookup indexes |
| Assignment joins | submission_id, user_id, stage_id |
Join-key indexes across assignment paths |
| Settings lookup | setting_name + entity IDs |
Composite indexes for metadata retrieval |
The Search Endpoint Is Usually the First SLA Breaker
In mature journals, search is often the slowest execution path because it combines metadata joins, filters, and pagination.
Mitigation Checklist
- Validate indexes against the actual filter columns used in production
- Limit deep result windows
- Cache recurring query patterns
- Consider dedicated search infrastructure at larger scale
InnoDB Production Baseline
Default values are designed for generic servers, not for editorial workloads.
| Parameter | Typical Default | Production Baseline | Why It Matters |
|---|---|---|---|
innodb_buffer_pool_size |
128M | 60-70% of RAM, based on workload | Keeps the active dataset and indexes in memory |
innodb_buffer_pool_instances |
1 | 4-8, measured before tuning | Reduces contention in larger buffer pools |
innodb_log_file_size |
Small default values | 256M-1G depending on write load | Prevents overly frequent checkpoints |
innodb_flush_log_at_trx_commit |
1 | 1 or 2 depending on durability policy | Balances durability and throughput |
max_connections |
151 | Based on PHP-FPM worker count plus safety margin | Prevents both under-capacity and unnecessary RAM waste |
slow_query_log |
Off | Enabled in production | Allows early diagnosis before incidents escalate |
Monitoring Thresholds for Editorial Reliability
| Signal | Alert Threshold | Recommended Action |
|---|---|---|
| Buffer pool hit rate | Sustained below 95% | Review indexes and memory allocation |
| Active connections | Above 80% of max_connections |
Review slow queries and pool configuration |
| Slow queries per hour | More than 10 above threshold | Run EXPLAIN and remediate missing or inefficient indexes |
| Disk usage | Above 80% | Expand storage and review table growth hotspots |
| Backup recency | Older than 25 hours | Treat as a critical incident and verify the backup chain immediately |
Capacity Planning by Journal Profile
| Editorial Profile | Journals | Annual Submissions | Recommended RAM | Expected DB Size | Buffer Pool Reference |
|---|---|---|---|---|---|
| Small single journal | 1 | 50-100 | 2 GB | 0.5-1 GB | ~1.2 GB |
| Active single journal | 1 | 200-400 | 4 GB | 2-4 GB | ~2.4 GB |
| Small multi-journal installation | 3-5 | 100-200 each | 8 GB | 5-10 GB | ~5 GB |
| Large multi-journal installation | 10+ | 200+ each | 16 GB | 15-30 GB | ~10 GB |
These figures are starting points only. Final sizing should always be validated against real editorial traffic patterns and peak-load behavior.
Data Integrity Controls (Non-Negotiable)
- utf8mb4 as the default character set for long-term metadata compatibility
- Verified backups combined with regular restore drills
- A staging-first policy for plugin updates and schema changes
- Scheduled change windows with clearly defined rollback criteria
If your OJS slows down when an issue closes or during review, the bottleneck is usually the database. OJS ONE's managed OJS plans run your journal on the infrastructure described in this series, with monitoring and tested backups; if you'd rather start with a diagnosis, contact us.
FAQ
How should I size innodb_buffer_pool_size correctly?
It should be sized based on the measured working set and the actual memory budget of the server, not on a generic percentage taken from the internet.
When do defaults start hurting OJS operations?
Usually after sustained growth in archival volume and concurrent editorial activity, often within 12 to 24 months in active journals.
Is one daily backup enough?
Only if restore tests are performed regularly and the retention policy is aligned with the journal’s real operational risk.
Should we optimize before monitoring?
No. Without baseline monitoring and visibility into slow queries, most optimization efforts are guesswork.
Conclusion
In OJS, database engineering is not a backend detail. It directly shapes editorial continuity, the speed of the review cycle, and metadata quality at publishing scale.
OJS ONE · managed operation
Your OJS, operated end to end
Hosting, upgrades with tested backups, editorial email that arrives, monitoring and support with defined response times. Your team edits; we run the platform.
- Upgrades and migrations without improvising
- Tested backups and monitoring
- Support with defined response times
OJS ONE · managed operation
Your OJS, operated end to end
Hosting, upgrades with tested backups, editorial email that arrives, monitoring and support with defined response times. Your team edits; we run the platform.
See plans