Mariadb problem processing larger databases

Community Support Database
1

I changed my VPS provider. I installed a clean HestiaCP on Unbuntu 22.04. For some reason, the processing of larger databases is slow… I have also tried different ubuntu and debian versions but nothing…

The previous VPS was 2x e5 + 2GB RAM + 100GB SSD, where the score of the same database was around 8-9.

Currently, the new VPS machine is faster and has more resources:

Basic System Information:
---------------------------------
Uptime: 0 days, 20 hours, 19 minutes
Processor: AMD EPYC 7313 16-Core Processor
CPU cores : 4 @ 2999.998 MHz
AES-NI : ✔ Enabled
VM-x/AMD-V : ✔ Enabled
RAM: 7.7 GiB
Swap : 0.0 KiB
Disk : 155.0 GiB
Distro: Ubuntu 22.04.3 LTS
Kernel : 5.15.0-89-generic
VM Type : KVM
IPv4/IPv6 : ✔ Online / ✔ Online

fio Disk Speed Tests (Mixed R/W 50/50):
---------------------------------
Block Size | 4k (IOPS) | 64k (IOPS)
   ------ | --- ---- | ---- ----
Rows | 202.49 MB/s (50.6k) | 1.42 GB/s (22.3k)
Write | 203.02 MB/s (50.7k) | 1.43 GB/s (22.4k)
Total | 405.52 MB/s (101.3k) | 2.86 GB/s (44.7k)
            | |
Block Size | 512k (IOPS) | 1m (IOPS)
   ------ | --- ---- | ---- ----
Rows | 1.58 GB/s (3.0k) | 1.60 GB/s (1.5k)
Write | 1.66 GB/s (3.2k) | 1.71 GB/s (1.6k)
Total | 3.25 GB/s (6.3k) | 3.32 GB/s (3.2k)

Last benchmark score:

Screenshot from 2023-11-23 01-16-39
Screenshot from 2023-11-23 01-16-39400×503 39.6 KB

What could be the reason and how to solve it?

Thank you!

2

Hi @martin1994,

First of all, I’m not a mysql expert so keep that in mind :wink:

When showing benchmarks, it is good to say what’s the software you are using so others can test it themselves.

I suppose the first benchmark has been done with YABS.

And the second one with Wordpress plugin Wordpress Hosting Benchmark tool

Here my result with default mysql conf:

imagen
imagen492×644 46 KB

Now, I edited /etc/mysql/my.cnf conf file and added this to [mysqld] section:

innodb_buffer_pool_size = 1G
innodb_log_buffer_size = 256M
innodb_log_file_size = 512M
innodb_write_io_threads = 12
innodb_flush_log_at_trx_commit = 0

Restarted mysql

systemctl restart mysqld

And here the result:

imagen
imagen492×645 46.3 KB

Here you can find some tips to raise mysql’s performance:

3

Curious to know, what is the your server spec?

4 
Processor  : AMD EPYC Processor
CPU cores  : 3 @ 2445.406 MHz
RAM        : 3.7 GiB
Swap       : 0.0 KiB
Disk       : 75.0 GiB
Distro     : Debian GNU/Linux 12 (bookworm)
Kernel     : 6.1.0-13-amd64
VM Type    : KVM
1 Like