Hi!
Important topic, how fine tune the Dédalo infrastructure to handle a huge catalogues.
One important thing. Previously, they weren't a "Dédalo thing"; they were general tips for common technologies (Apache PHP, PostgreSQL, GNU/Linux, etc.). Dédalo uses these general technologies, and the performance depends on how these base technologies are configured. For instance, if you don't modify the default memory set for PHP, you won't be able to manage complex sections that contain thousands of records and intricate relationships between them. Therefore, the infrastructure is an important topic to discuss.
The first issue you need to take into account is that your system is very BIG, you are working with a hundred of thousands of records with millions of relations. So, you need to use a server to handle this situation.
The first thing that becomes obvious is that the RAM is insufficient. Although the minimum requirement is 16 GB and you currently have 31 GB, in such situation the realistic recommended minimum would actually be 64 GB. For that reason, in your case, the current recommendations do not even appear to be sufficient.
Another clear problem is the processor clock speed. This is a key factor for the correct functioning of the system, and if the clock speed does not meet the minimum required levels, the system will never be able to perform properly. A processor speed of 2793 MHz when the minimum requirement is 3500 MHz is simply not acceptable for this environment. PHP is a single-threaded language that heavily utilizes the CPU clock. Since PHP uses a single thread for handling requests, a large installation with a clock speed of 3500MHz may not be sufficient. A 4GHz clock speed would be more appropriate.
There is also the issue of PHP memory. There is indeed a risk with large listings, exports, publication processes, and other operations that PHP handles directly in RAM. These processes consume a significant amount of memory.These processes consume a substantial amount of memory. For a large dataset with multiple users, PHP requires a significant amount of RAM. However, this is not the maximum RAM allowed to PHP; it's the "active" RAM. For instance, if a query requires 2GB of RAM for a single user, and you have 10 users working simultaneously, your system will need to handle 20GB. Therefore, set the maximum RAM for PHP to handle large datasets without exhausting it.
In addition, PostgreSQL also requires a substantial amount of memory. The problem is that it is not easy to determine exactly how much memory set to PostgreSQLThe challenge lies in accurately determining the optimal memory configuration for PostgreSQL. I highly recommend utilizing the pgtune tool and referring to the official PostgreSQL tuning article. Additionally, Sarbajit Mohanty's insightful article on Medium titled "How to Tune PostgreSQL for Maximum Performance" provides valuable guidance.
Using MVe (SSD) hard drives instead of classical HDDs is crucial for handling large datasets, as it significantly improves performance.
For a general overview of how to tune PostgreSQL, but only as a reference, these parameters could be useful.
# DB Version: 18
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 64 GB
# CPUs num: 8
# Data Storage: ssd
max_connections = 100
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 80659kB
huge_pages = try
jit = off
wal_compression = lz4
autovacuum_work_mem = 2GB
io_method = io_uring
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
Another critical issue is the HTTP/2 protocol. As I see your server is still using HTTP/1.1 instead of HTTP/2.0, which is a serious limitation. With the current setup, a single user can only establish a very small number of simultaneous connections, usually no more than four. These connections also block other processes, causing the entire system to slow down globally. As a result, the platform becomes sluggish and many operations are delayed. Therefore, the HTTP/2 connectivity issue is not a minor detail; it is an important or even critical problem that should be addressed as soon as possible.
Well, in a nutshell.
Update your server hardware to a better one. Upgrade your RAM and hard drive. Modify the PHP, PostgreSQL, and Apache settings, and try to fine-tune your server. You need to adapt your server to handle the data volume of your project. This is a crucial topic and not a "Dédalo thing." The Dédalo community can optimize Dédalo code, and we do, but we cannot solve the server infrastructure required for a large project. You can't go to the moon with a rickshaw... well, you can try, but... 😊
I hope it helps.
Best
