Very slow loading site

[nutudan1990]

SYSTEM INFORMATION
OS type and version	Ubuntu Linux 22.04.3
Webmin version	[2.202]
Virtualmin version	7.30.4
Webserver version	[Apache Webserver
Related packages	SUGGESTED

PHP-FPM 7.4.33 Server

Database: MariaDB version 10.6.18
procesor Intel(R) Xeon(R) CPU X5650 @ 2.67GHz, 24 miezuri
Virtual memory: 1.35 GiB utilizat / 1.99 GiB total
Real memory: 5.09 GiB folosit / 17.85 GiB în cache / 23.49 GiB total
Only i have HDD: 239.38 GiB utilizat / 33.55 GiB liber / 272.93 GiB total

I installed an opencart online store with over 190,000 products and it loads very slowly, more precisely extrastore.ro, but I also have a lenjeriialecasei with 2,000 products, it loads very quickly, what would be the problem with the extrastore.ro site with over 190,000 products.

have /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
bind-address = 127.0.0.1
expire_logs_days = 10
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
innodb_file_per_table = 1
max_allowed_packet = 64M
ssl_ca = /etc/mysql/mysql-ssl.ca
ssl_key = /etc/mysql/mysql-ssl.key
ssl_cert = /etc/mysql/mysql-ssl.cert
myisam_sort_buffer_size = 64M

CACHE & BUFFER OPTIMIZAT

query_cache_size = 64M # Mai mic, deoarece query_cache poate încetini HDD-ul
query_cache_limit = 4M
tmp_table_size = 128M # Reducem pentru a evita scrierea excesivă pe disc
max_heap_table_size = 128M

OPTIMIZARE CONEXIUNI

max_connections = 150 # Limităm conexiunile pentru a preveni consum excesiv de RAM
net_buffer_length = 256K # Buffer optimizat pentru HDD

OPTIMIZARE BUFFER & INDEX

key_buffer_size = 256M # Scădem pentru a reduce solicitările pe HDD
sort_buffer_size = 4M
read_rnd_buffer_size = 4M
join_buffer_size = 4M

OPTIMIZARE INNODB PENTRU HDD

innodb_buffer_pool_size = 8G # Reducem la 8GB pentru a lăsa loc altor servicii
innodb_buffer_pool_instances = 4 # Menținem 4 instanțe pentru echilibrare
innodb_log_file_size = 512M # Reducem pentru a evita latențele la restart
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1 # Mai sigur pentru HDD (evită coruperea datelor)
innodb_flush_method = fsync # Mai potrivit pentru HDD decât O_DIRECT
innodb_read_io_threads = 4 # Limităm citirile pentru HDD
innodb_write_io_threads = 2 # Limităm scrierile pentru HDD
innodb_io_capacity = 200 # Reducem pentru HDD (mai sigur și mai stabil)
innodb_io_capacity_max = 400
innodb_thread_concurrency = 8 # Limităm paralelizarea pentru a evita suprasolicitarea HDD-ului
innodb_autoinc_lock_mode = 2 # Optimizare pentru INSERT rapid

ACTIVARE LOG INTEROGĂRI LENTE

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # Loghează interogările mai lente de 1 secunde
log_queries_not_using_indexes = 1

have in /etc/php/7.4/fpm/pool.d/173980717823374.conf
[173980717823374]
user = extrastore
group = extrastore
listen.owner = extrastore
listen.group = extrastore
listen.mode = 0660
listen = /run/php/173980717823374.sock
pm = dynamic
pm.max_children = 30
pm.start_servers = 10
pm.min_spare_servers = 5
pm.max_spare_servers = 15
php_value[upload_tmp_dir] = /home/extrastore/tmp
php_value[session.save_path] = /home/extrastore/tmp
php_value[memory_limit] = 2G
php_value[max_execution_time] = 3600
php_value[file_uploads] = On
php_value[upload_max_filesize] = 512M
php_value[post_max_size] = 512M
php_admin_value[max_input_time] = 300
php_admin_value[max_input_vars] = 5000

[calport]

Yes, I can confirm that the site is loading slowly. So slowly that a couple of tools that I use to analyse websites are timing out.

I was unable to peruse database the config that you have posted. Perhaps this recent discussion might help you optimize database configuration:

Low Performance in Database Server - #7 by Joe

[ID10T]

There is plenty on optimizing Open Cart if you search it. Open Cart has a paid module of course. Several options for cash. The ones I saw in a quick search was pretty content free and just generalizations. I did see something for “lazy loading” that sounds pretty much what you are looking for though.

[stefan1959]

I use Pagespeed to check website speed issue and it not even resolving your site.

So I checked your name servers and you need to fix, maybe slowness can be part domain resolution in the browser, so fix that first off.

If you running mail you should fix dmarc as well

[stefan1959]

I used just your IP and its still very slow, not sure if these error are causing it.

Initial load is very slow

Do you have any pages that are not handled by the database, like a plain html page, or other Virtual Server not using mysql, it is looking like the database though

[asadister]

curl -o /dev/null -s -w "DNS: %{time_namelookup} | TCP: %{time_connect} | SSL: %{time_appconnect} | TTFB: %{time_starttransfer}\n" https://www.extrastore.ro/
DNS: 0.000001 | TCP: 0.140000 | SSL: 0.328000 | TTFB: 99.109000

Hello, according to my research, slow loading of the site is related to database processing. Regular hard disks (HDD) are not suitable for large databases with heavy transactions and cause input/output delays. For optimization and fast access, you can cache the database in RAM. Using virtual memory when your memory is HDD will not be beneficial in loading speed. Also, enabling Redis is very useful.
To check and optimize the database in detail, you can use the MySQLTuner script to find the most optimal settings in the database. For large data, it is recommended to use the INNODB engine.

[Tactikast]

@stefan1959 Highlighted a good point but still @asadister is probably right. You didn’t say the size of the DB. But we can already see 18GO in cache.

Please tell us the size of your DB ? It’s probably this, If the DB is bigger than your DDR you will never do anything good. And with an HDD it will be worst

Still if you need a enormous DB you can increase the DDR or try to work with Micro Table inside the DB (The second suggestion is, from far, the best for performance but it may implies to reworks the architecture, what you probably don’t want. So you can still increase the DDR)