Table of Contents

  1. Introduction

  2. Understanding Dolibarr’s Database Structure

  3. Why Database Optimization Matters

  4. Key Performance Bottlenecks in Dolibarr Installations

  5. Server-Level Optimization

  6. MySQL/MariaDB Configuration Tuning

  7. Dolibarr-Specific Table Optimizations

  8. Using Indexes Wisely

  9. Archiving and Cleaning Data

  10. Query Optimization in Custom Modules

  11. Monitoring and Profiling Database Performance

  12. Cron Jobs and Background Process Optimization

  13. Best Practices for Long-Term Database Health

  14. Common Mistakes and How to Avoid Them

  15. Conclusion

  16. Tools and Resources

1. Introduction

Dolibarr ERP & CRM is a widely used open-source software platform designed to manage the business processes of small to medium-sized enterprises. It is modular, web-based, and built on a LAMP or LEMP stack, primarily using PHP and MySQL or MariaDB.

While Dolibarr is praised for its ease of use and flexibility, its performance can degrade over time—especially as the volume of data grows. Database optimization is one of the most effective ways to maintain or improve the responsiveness and reliability of Dolibarr installations.

In this guide, we’ll explore how to optimize the Dolibarr database for better performance, covering everything from server-level tuning to SQL indexing strategies. Whether you're a system administrator, developer, or business owner managing your own Dolibarr instance, these strategies will help you ensure your ERP system remains fast and efficient.

2. Understanding Dolibarr’s Database Structure

Dolibarr uses a relational database model powered by MySQL or MariaDB. All business logic—such as customers, invoices, products, users, orders, and stock—is stored in relational tables using the llx_ prefix by default (this can be changed during installation).

Key features of Dolibarr’s database structure:

  • Flat schema per module: Each module (e.g., invoice, order, product) has its own dedicated tables.

  • Single-entity model (unless using MultiCompany): All data belongs to one organization.

  • ORM-like object abstraction: Dolibarr uses CommonObject classes to handle database interactions with PHP.

  • No third-party ORM: Database access is built-in and directly uses SQL with helper functions.

Because of its modular structure, Dolibarr’s database can grow large and complex over time. This is why regular maintenance and smart optimization strategies are vital.

3. Why Database Optimization Matters

Poor database performance can manifest in many ways:

  • Slow loading of list views and reports

  • Timeouts during invoice generation

  • Excessive memory and CPU usage

  • Latency when importing/exporting large data sets

  • Application crashes or lockups under heavy use

Optimizing the database is critical because:

  • Dolibarr is data-heavy: Invoices, orders, products, stock, users, and logs all accumulate data.

  • Real-time interaction: Every user interaction (e.g., clicking on a customer or saving an invoice) triggers database queries.

  • I/O-bound operations: Reports, exports, and analytics read large amounts of data from disk and RAM.

  • Shared hosting limitations: Many Dolibarr users deploy on low-resource environments where tuning is crucial.

Optimization improves speed, scalability, stability, and long-term maintainability of your ERP system.

4. Key Performance Bottlenecks in Dolibarr Installations

Here are common sources of performance issues related to the database layer:

  • Poorly configured MySQL or MariaDB server settings

  • Lack of indexes on high-use columns

  • Huge unarchived tables (e.g., logs, invoices, events)

  • Inefficient SQL queries in custom modules

  • Overuse of joins or subqueries

  • Misuse of cron jobs writing to DB every minute

  • No caching for frequently accessed data

Addressing these issues systematically will lead to measurable improvements in performance.

5. Server-Level Optimization

Before optimizing Dolibarr itself, ensure your server infrastructure is appropriate for your workload.

5.1 Use SSD Storage

Database read/write speed depends heavily on disk I/O. Move from HDD to SSD for significant performance gains.

5.2 Allocate Enough RAM

  • Minimum: 2 GB

  • Recommended: 4–8 GB for production with multiple users

RAM is especially important for MySQL’s innodb_buffer_pool.

5.3 Optimize CPU Resources

While Dolibarr isn’t extremely CPU-bound, having multiple cores can help especially with concurrent users.

5.4 Run Dolibarr on a VPS or Cloud Instance

Avoid shared hosting if you handle large volumes of data or require custom MySQL tuning.

Recommended providers: DigitalOcean, Hetzner, Linode, AWS Lightsail.

6. MySQL/MariaDB Configuration Tuning

Optimizing your MySQL configuration can have a massive impact.

Edit your MySQL config file (e.g., /etc/mysql/my.cnf) and tune the following:

6.1 Key Parameters

ini

innodb_buffer_pool_size = 1G # ~60-70% of available RAM query_cache_size = 0 # Deprecated, disable tmp_table_size = 64M max_allowed_packet = 64M innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 2 max_connections = 100

Use tools like MySQLTuner to analyze and optimize settings dynamically.

6.2 Enable Slow Query Logging

Enable logging to identify queries that are too slow:

ini

slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2

This helps you identify bottlenecks for further query optimization.

7. Dolibarr-Specific Table Optimizations

7.1 Focus on Large Tables

Common large tables include:

  • llx_facture – Invoices

  • llx_commande – Orders

  • llx_adherent – Members

  • llx_actioncomm – Events

  • llx_user and llx_userlog – Login data

  • llx_product_stock – Inventory

Use ANALYZE TABLE and SHOW TABLE STATUS to review size and index usage.

7.2 Use OPTIMIZE TABLE

Run this command periodically on high-write tables:

sql

OPTIMIZE TABLE llx_facture, llx_actioncomm;

This reclaims space and improves access speed.

8. Using Indexes Wisely

Indexes speed up reads but slow down writes. Use them strategically.

8.1 Add Indexes to Frequently Queried Fields

Examples:

sql

ALTER TABLE llx_facture ADD INDEX idx_ref (ref); ALTER TABLE llx_commande ADD INDEX idx_fk_soc (fk_soc);

8.2 Avoid Redundant Indexes

Check for overlapping or duplicate indexes using:

sql

SHOW INDEXES FROM llx_facture;

Remove unused indexes that don’t help performance.

 

9. Archiving and Cleaning Data

Over time, your Dolibarr database can become bloated with historical records such as old invoices, proposals, emails, logs, and stock movements. This buildup degrades query performance and increases backup times.

9.1 Why Archiving Matters

  • Improves SELECT performance on operational tables

  • Reduces the size of indexes

  • Speeds up backups and data exports

  • Helps ensure compliance with data retention policies

9.2 What Can Be Archived?

  • Old invoices (llx_facture)

  • Inactive third parties (llx_societe)

  • Events older than 12–24 months (llx_actioncomm)

  • User logs and audit trails (llx_userlog)

  • Email message history (llx_mail)

9.3 Archiving Strategies

  • Move older data to archive tables (e.g., llx_facture_archive)

  • Use scripts to export old records to CSV and delete from DB

  • Set up a cron job to run monthly or quarterly

  • Use modules or custom pages to trigger selective archiving

Example SQL to archive invoices older than 2 years:

sql

INSERT INTO llx_facture_archive SELECT * FROM llx_facture WHERE datef < DATE_SUB(NOW(), INTERVAL 2 YEAR); DELETE FROM llx_facture WHERE datef < DATE_SUB(NOW(), INTERVAL 2 YEAR);

⚠️ Always test on a staging database first and ensure backups exist before bulk deletions.

10. Query Optimization in Custom Modules

Many performance issues stem from poorly written SQL in custom modules or reports.

10.1 Use Prepared Statements

Avoid building SQL queries with raw user input. Use Dolibarr's $db->query() and $db->escape() functions.

Bad:

php

$sql = "SELECT * FROM llx_product WHERE ref = '".$_POST['ref']."'";

Better:

php

$ref = $db->escape(GETPOST('ref')); $sql = "SELECT * FROM llx_product WHERE ref = '".$ref."'";

10.2 Use Limits and Pagination

Never return thousands of rows in one query.

sql

SELECT * FROM llx_product ORDER BY ref LIMIT 0, 50;

Use LIMIT and OFFSET for pagination.

10.3 Avoid N+1 Queries

Instead of calling one query per item in a loop, use JOIN or IN() conditions to batch fetch data.

Bad:

php

foreach ($products as $p) { $sql = "SELECT * FROM llx_stock WHERE fk_product = ".$p->id; }

Better:

php

$sql = "SELECT * FROM llx_stock WHERE fk_product IN (".implode(',', $product_ids).")";

11. Monitoring and Profiling Database Performance

11.1 Use MySQL's Built-in Tools

  • SHOW FULL PROCESSLIST: View current active queries

  • EXPLAIN SELECT ...: Understand query plan

  • SHOW INDEXES FROM tablename: Evaluate index use

  • INFORMATION_SCHEMA: Inspect tables, indexes, and table sizes

11.2 Use Monitoring Tools

✅ MySQLTuner

Command-line script to evaluate MySQL performance:

bash

perl mysqltuner.pl

Gives suggestions for buffer sizes, index usage, slow queries, and memory usage.

✅ Percona Toolkit

Advanced suite for analyzing performance and replication.

✅ Adminer / phpMyAdmin

Use to inspect table sizes, query plans, and performance metrics with UI.

✅ Netdata / Zabbix / Prometheus

For full-stack performance monitoring, including database stats.

12. Cron Jobs and Background Process Optimization

Dolibarr uses cron tasks for background jobs like:

  • Email reminders

  • Auto-generated documents

  • Data synchronizations

  • Scheduled imports/exports

These jobs often interact with the database.

12.1 Optimize Cron Frequency

Don't run all jobs every minute. Schedule jobs based on necessity.

bash

*/15 * * * * php htdocs/scripts/emailings/cron_emailing_send.php 0 3 * * * php htdocs/scripts/invoices/cron_invoice_reminder.php

12.2 Profile Heavy Cron Jobs

If a cron job triggers a 5-second query every 60 seconds, it can block performance.

  • Log query durations inside scripts

  • Use slow query log to track long-running background queries

  • Reduce scope or batch size in heavy processing scripts

13. Best Practices for Long-Term Database Health

✅ Use Backups and Test Restores

Always keep automated, off-site backups. Test them monthly.

✅ Run Maintenance Scripts Regularly

  • OPTIMIZE TABLE for fragmented tables

  • Rebuild indexes

  • Prune logs and historical records

✅ Use a Staging Environment

Test queries and schema changes in a sandbox before production.

✅ Audit Custom Code

Every 6 months, audit custom modules for bad queries, missing indexes, and unoptimized joins.

✅ Train Users

User behavior affects performance. Avoid large exports or searches without filters.

14. Common Mistakes and How to Avoid Them

Mistake Fix
Not using indexes on large tables Add indexes on columns used in WHERE and JOIN
Storing everything in default llx_* tables Use custom tables in custom modules
Running all cron jobs too frequently Spread cron execution time and audit task load
Ignoring slow query log Enable it and review monthly
Using shared hosting for growing ERP Move to VPS or cloud instance
Not cleaning old data Archive and purge periodically
Relying on GUI-only tools Learn to profile queries via command-line tools

15. Conclusion

The Dolibarr database is the backbone of your ERP system—and like any critical system component, it requires care, attention, and periodic tuning. As your business grows and your data increases, optimization becomes not just a nice-to-have, but an essential part of system administration.

By combining server-level optimization, MySQL tuning, indexing strategies, and data hygiene, you can ensure that your Dolibarr instance continues to perform at its best. With a structured approach and the right tools, even large installations can remain responsive, stable, and scalable for years to come.

16. Tools and Resources