Hi,
I am currently using ERPNext v11.1.67 . My current database size is around 17.8 GB.
There is a lot of transactions going on in my ERPNext and most of them are imported from another source.
So, the actual transactions are stored on a core system using Postgresql. Then, a connector exists to get all the transactions data from core system and stored inside a queue. From the queue, the datas are exported to ERPNext. So, I use RPC call to create transactions on ERPNext. These days, it takes longer to create 1 transaction. 1 transaction might take more than 40 seconds, sometimes longer.
Sometimes, users complain it takes long to browse ERPNext because they need it for accounting purposes. It gets worse when they open some reports like Stock Balance, General Ledger. And much worse when they upload csv file to create Journal Entry transactions. The import status mostly is stuck on In Progress status, never change to Success, so I have to help uploading the csv via command prompt. To have a rough idea, currently there are more than 570.000 Journal Entries, and more than 3.3 million records of GL Entry already. It will keep adding up. I almost give up on how to make the performance better.
I wonder whether it is the server configuration or Mariadb config that has to be tuned. I have used several ways but I don’t think it helps.
At the server, I run wget -qO- bench.sh | bash and this is what I get :
CPU Model : AMD EPYC 7R32
CPU Cores : 8
CPU Frequency : 3267.024 MHz
CPU Cache : 512 KB
Total Disk : 194.4 GB (46.4 GB Used)
Total Mem : 15827 MB (6297 MB Used)
Total Swap : 4095 MB (17 MB Used)
System uptime : 8 days, 7 hour 19 min
Load average : 2.63, 2.11, 2.06
OS : Ubuntu 18.04.1 LTS
Arch : x86_64 (64 Bit)
Kernel : 5.4.0-1045-aws
TCP CC : cubic
Virtualization : KVM
Organization : AS16509 Amazon.com, Inc.
Location : Singapore / SG
Region : Singapore
I/O Speed(1st run) : 229 MB/s
I/O Speed(2nd run) : 211 MB/s
I/O Speed(3rd run) : 209 MB/s
Average I/O speed : 216.3 MB/s
Then, at frappe-bench/sites/common_site_config.json, I have changed the background_workers to 3.
Then, at frappe-bench/config/supervisor.conf, I changed this line :
command=/home/frappe/frappe-bench/env/bin/gunicorn -b 127.0.0.1:8000 -w 9 -t 1200 --threads 10 frappe.app:application --preload
About the setting at my.cnf at /etc/mysql, I am not sure whether the change made by my infrastructrue team is correct already.
max_connections = 600
connect_timeout = 50
wait_timeout = 6000
max_allowed_packet = 256M
thread_cache_size = 128
sort_buffer_size = 64M
bulk_insert_buffer_size = 128M
tmp_table_size = 256M
max_heap_table_size = 256M
net_buffer_length = 100K
myisam_recover_options = BACKUP
key_buffer_size = 512M
table_open_cache = 400
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 64M
read_rnd_buffer_size = 32M
query_cache_limit = 32K
query_cache_size = 256M
default_storage_engine = InnoDB
innodb_buffer_pool_size = 2560M
innodb_log_buffer_size = 8M
innodb_log_file_size = 1000M
innodb_buffer_pool_instance = 2
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
If there is any insight, please help me. Thank you very much.