Setup high-availability ERPNext Server

Setting up ERPNext on Ubuntu Server for high availability, high load, and heavy usage with 500 users involves several key components: ERPNext Application Servers: Two

 · 6 min read

Setting up ERPNext on Ubuntu Server for high availability, high load, and heavy usage with 500 users involves several key components:

  1. ERPNext Application Servers: Two servers for redundancy.
  2. ProxySQL: To manage the load and distribute queries to the MySQL database.
  3. MySQL Database Cluster: One master for read and write operations, and two read-only replicas.

Prerequisites

  1. Ubuntu Server: Ensure all servers are running Ubuntu Server 20.04 or later.
  2. Networking: Ensure all servers can communicate with each other.
  3. DNS/Load Balancer: For distributing traffic to your application servers.

Steps

1. Setting Up ERPNext Application Servers

On both ERPNext application servers:

  1. Update and Upgrade:

    sudo apt update && sudo apt upgrade -y

  2. Install Dependencies:

    sudo apt install python3-dev python3-setuptools python3-pip python3-distutils redis-server build-essential ntp vim curl -y

  3. Install Node.js and npm:

    curl -sL https://deb.nodesource.com/setup_14.x | sudo -E bash -
sudo apt install -y nodejs

  4. Install Yarn:

    sudo npm install -g yarn

  5. Install MariaDB Server:

    sudo apt install mariadb-server mariadb-client -y

  6. Setup ERPNext User:

    sudo adduser erpnext
sudo usermod -aG sudo erpnext
su - erpnext

  7. Install Bench:

    sudo pip3 install frappe-bench

  8. Initialize Bench and Create ERPNext Site:

    bench init erpnext --frappe-branch version-13
cd erpnext
bench new-site your-site-name
bench get-app erpnext --branch version-13
bench --site your-site-name install-app erpnext

  9. Configure Supervisor and Nginx:

    bench setup supervisor
bench setup nginx
sudo supervisorctl reload
sudo systemctl restart nginx

2. Setting Up ProxySQL

  1. Install ProxySQL:

    sudo apt install proxysql -y

  2. Configure ProxySQL:

    Edit /etc/proxysql.cnf:

    datadir="/var/lib/proxysql"
admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
    threads=4
    max_connections=4096
    default_query_delay=0
    default_query_timeout=36000000
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.7.26"
    connect_timeout_server=3000
    monitor_username="monitor"
    monitor_password="monitor"
}
mysql_servers =
(
    { address="db-master", port=3306, hostgroup=0, max_connections=1000 }
    { address="db-replica1", port=3306, hostgroup=1, max_connections=1000 }
    { address="db-replica2", port=3306, hostgroup=1, max_connections=1000 }
)
mysql_users =
(
    { username = "erpnext", password = "your_password", default_hostgroup = 0 }
)
mysql_query_rules =
(
    { rule_id=1, active=1, match_pattern="^SELECT.*FOR UPDATE", destination_hostgroup=0, apply=1 }
    { rule_id=2, active=1, match_pattern="^SELECT", destination_hostgroup=1, apply=1 }
)

  3. Restart ProxySQL:

    sudo systemctl restart proxysql

3. Setting Up MySQL Cluster

On the master server:

  1. Install MySQL Server:

    sudo apt install mysql-server -y

  2. Configure MySQL:

    Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

    [mysqld]
server-id=1
log_bin=mysql-bin
binlog_do_db=erpnext

  3. Restart MySQL:

    sudo systemctl restart mysql

  4. Create Replication User:

    CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

  5. Dump the Database:

    mysqldump -u root -p --all-databases --master-data > dbdump.db

On each replica server:

  1. Install MySQL Server:

    sudo apt install mysql-server -y

  2. Configure MySQL:

    Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

    [mysqld]
server-id=2 # use 3 for the third server
relay-log=relay-log

  3. Restart MySQL:

    sudo systemctl restart mysql

  4. Import the Database Dump:

    mysql -u root -p < dbdump.db

  5. Start Replication:

    CHANGE MASTER TO MASTER_HOST='master-ip', MASTER_USER='replica', MASTER_PASSWORD='replica_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 154;
START SLAVE;

4. Load Balancer/Reverse Proxy

Set up an Nginx or HAProxy instance to load balance traffic between your ERPNext application servers.

Nginx Configuration Example:

upstream erpnext_app {
    server erpnext-app1:8000;
    server erpnext-app2:8000;
}

server {
    listen 80;
    server_name your_domain.com;

    location / {
        proxy_pass http://erpnext_app;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

Monitoring and Maintenance

  1. Monitor Servers: Set up monitoring using tools like Prometheus, Grafana, or Zabbix.
  2. Regular Backups: Implement a backup strategy for your databases and application data.
  3. Security: Ensure proper firewall settings, SSH security, and keep your system updated.

This setup provides a high-availability and scalable ERPNext environment suitable for handling high loads and ensuring continuous service for your 500 users.

The Frappe framework, which ERPNext is built on, offers several features and techniques to make your application more efficient and faster. Here are some nuances and best practices to consider:

1. Caching

Frappe provides various caching mechanisms to speed up your application.

  • Redis Cache: Frappe uses Redis for caching. You can cache data that doesn't change frequently and is expensive to compute or fetch.

    from frappe.utils import cint
from frappe import cache

def get_cached_data():
    cache_key = "your_cache_key"
    data = cache().get_value(cache_key)
    if not data:
        # Compute or fetch the data
        data = compute_expensive_data()
        # Cache the data for a certain period
        cache().set_value(cache_key, data, expires_in_sec=600)  # Cache for 10 minutes
    return data

  • Local Cache: Frappe also provides a local cache mechanism for caching within a request.

    from frappe import local

def get_local_cached_data():
    cache_key = "your_local_cache_key"
    if not hasattr(local, cache_key):
        local.your_local_cache_key = compute_expensive_data()
    return local.your_local_cache_key

2. Optimizing Database Queries

Efficient database access is crucial for performance.

  • Use .select() and .pluck(): When fetching data, use .select() to limit the columns retrieved and .pluck() to get a specific column's values.

    names = frappe.get_all('DocType', fields=['name'], filters={'some_field': 'value'})

  • Batch Queries: Instead of running multiple queries in a loop, fetch all necessary data in a single query and process it in memory.

    docs = frappe.get_all('DocType', filters={'some_field': 'value'}, limit_page_length=1000)
for doc in docs:
    process(doc)

  • Avoid N+1 Queries: Prefetch related data to avoid multiple queries.

    docs = frappe.get_all('DocType', filters={'some_field': 'value'}, fields=['name', 'related_field'])
related_ids = [d.related_field for d in docs]
related_docs = frappe.get_all('RelatedDocType', filters={'name': ['in', related_ids]})

3. Background Jobs

Offload long-running or resource-intensive tasks to background jobs using the frappe.async module.

  • Background Job Example: 
    import frappe

@frappe.whitelist()
def long_running_task(param):
    frappe.enqueue('path.to.your.method', param=param, queue='long')

def your_method(param):
    # Long-running task here

4. Indexes

Ensure that your database tables have appropriate indexes to speed up queries.

  • Adding Indexes: 
    # In your DocType definition
columns = [
    {
        'fieldname': 'your_field',
        'fieldtype': 'Data',
        'in_list_view': 1,
        'index': 1
    }
]

5. Document Caching

Use document caching to avoid fetching the same document multiple times in a single request.

from frappe.model.document import get_doc

def get_cached_doc(doctype, name):
    doc = frappe.get_doc(doctype, name)
    return doc

6. Lazy Loading

Load data only when it's needed.

class LazyLoadedObject:
    def __init__(self):
        self._data = None

    @property
    def data(self):
        if self._data is None:
            self._data = expensive_data_loading_function()
        return self._data

7. Optimize Form Loads

Use the add_fetch method to preload related field values.

frappe.ui.form.on('Sales Order', {
    refresh: function(frm) {
        frappe.model.add_fetch('customer', 'customer_name', 'customer_name');
    }
});

8. Optimize API Responses

Limit the amount of data sent in API responses.

@frappe.whitelist()
def get_custom_data(param):
    data = frappe.get_all('DocType', filters={'some_field': param}, fields=['field1', 'field2'])
    return data

9. Use Correct Field Types

Ensure fields are of the correct type for their usage to optimize storage and retrieval.

  • Example: 
    columns = [
    {
        'fieldname': 'is_active',
        'fieldtype': 'Check'
    }
]

10. Batch Processing

Process records in batches to avoid timeouts and memory overflows.

def process_in_batches():
    total_records = frappe.db.count('DocType')
    batch_size = 100
    for start in range(0, total_records, batch_size):
        records = frappe.get_all('DocType', limit_start=start, limit_page_length=batch_size)
        for record in records:
            process_record(record)

By leveraging these Frappe framework features and best practices, you can significantly enhance the performance and efficiency of your ERPNext application.

To handle high availability, high load, and heavy usage for around 500 users with the described ERPNext setup, it's essential to ensure that your server specifications can manage the expected load efficiently. Here are the recommended specifications for each component of your infrastructure:

ERPNext Application Servers (2 Servers)

  1. CPU: 4-8 cores
  2. RAM: 16-32 GB
  3. Storage: 500 GB SSD (or NVMe) for fast I/O
  4. Network: 1 Gbps NIC
  5. Operating System: Ubuntu Server 20.04 or later
  6. Additional Software: Redis for caching, Node.js, Python, Yarn

ProxySQL Server

  1. CPU: 4-8 cores
  2. RAM: 8-16 GB
  3. Storage: 100 GB SSD
  4. Network: 1 Gbps NIC
  5. Operating System: Ubuntu Server 20.04 or later
  6. Additional Software: ProxySQL

MySQL Master Database Server

  1. CPU: 8-16 cores
  2. RAM: 32-64 GB
  3. Storage: 1 TB SSD (or NVMe) for fast I/O
  4. Network: 1 Gbps NIC
  5. Operating System: Ubuntu Server 20.04 or later
  6. Additional Software: MySQL Server

MySQL Read-Only Replica Servers (2 Servers)

  1. CPU: 8-16 cores
  2. RAM: 32-64 GB
  3. Storage: 1 TB SSD (or NVMe) for fast I/O
  4. Network: 1 Gbps NIC
  5. Operating System: Ubuntu Server 20.04 or later
  6. Additional Software: MySQL Server

Load Balancer/Reverse Proxy (Nginx or HAProxy)

  1. CPU: 4-8 cores
  2. RAM: 8-16 GB
  3. Storage: 100 GB SSD
  4. Network: 1 Gbps NIC
  5. Operating System: Ubuntu Server 20.04 or later
  6. Additional Software: Nginx or HAProxy

Additional Considerations

  1. Backup Storage: Ensure you have a separate server or cloud storage for regular backups of your databases and application data.
  2. Monitoring and Management: Allocate resources for monitoring tools like Prometheus, Grafana, or Zabbix to keep an eye on system performance and health.
  3. Redundancy and Failover: Consider additional servers or a cloud-based solution for failover and disaster recovery scenarios.

Example Cloud Instance Types

If you are using a cloud provider like AWS, Google Cloud, or Azure, you can use the following instance types as a reference:

  1. ERPNext Application Servers:

    • AWS: m5.xlarge or m5.2xlarge
    • Google Cloud: n1-standard-4 or n1-standard-8
    • Azure: D4s v3 or D8s v3

  2. ProxySQL Server:

    • AWS: m5.large or m5.xlarge
    • Google Cloud: n1-standard-4 or n1-standard-8
    • Azure: D4s v3 or D8s v3

  3. MySQL Master Database Server:

    • AWS: r5.2xlarge or r5.4xlarge
    • Google Cloud: n1-highmem-4 or n1-highmem-8
    • Azure: E4s v3 or E8s v3

  4. MySQL Read-Only Replica Servers:

    • AWS: r5.2xlarge or r5.4xlarge
    • Google Cloud: n1-highmem-4 or n1-highmem-8
    • Azure: E4s v3 or E8s v3

  5. Load Balancer/Reverse Proxy:

    • AWS: m5.large or m5.xlarge
    • Google Cloud: n1-standard-4 or n1-standard-8
    • Azure: D4s v3 or D8s v3

By using these specifications, you can ensure that your ERPNext setup will be capable of handling the high load and heavy usage expected from 500 users.

Book a free 30min tailored consultation

Free first 30min of enterprise consultation to manage your business better using digital processes.

Published on
Apoorv Soral

No comments yet.

Add a comment
Ctrl+Enter to add comment