MYSQL optimization for Magento 2

Question

Im running my magento 2 project on 5 core 10gb ram VM. I did some benchmarks and i see that my DB queries take quite some time to execute. Traffic on this site is almost zero. Mysql is running on 5.7.31-34 Percona Server. Database size 1.7gb. Can anyone spot some obvious mistakes and give me some suggestions ?

2947 queries in 1,057.20ms (average time: 0.36ms) - 2788 queries/second
2912 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION


2618 queries in 1,132.66ms (average time: 0.43ms) - 2311 queries/second
2593 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION

2915 queries in 1,213.47ms (average time: 0.42ms) - 2402 queries/second
2878 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION


620 queries in 235.52ms (average time: 0.38ms) - 2633 queries/second
610 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION

3214 queries in 1,930.16ms (average time: 0.60ms) - 1665 queries/second
3179 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION

At moment my.cnf is ...

[mysqld]
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp


### MyISAM #
key_buffer_size = 16M
myisam-recover-options = FORCE,BACKUP

### SAFETY #
innodb = force
max_allowed_packet = 250M
max_connect_errors = 100000
bind-address = 127.0.0.1
skip-name-resolve

### LANGUAGE #
#init_connect='SET collation_connection = utf8_unicode_ci'
#init_connect='SET NAMES utf8'
#character-set-server=utf8
#collation-server=utf8_unicode_ci
#skip-character-set-client-handshake

### CACHES AND LIMITS #
back_log = 20
interactive_timeout = 7200
wait_timeout = 7200
net_read_timeout = 120
net_write_timeout = 300
sort_buffer_size = 8M
read_buffer_size = 8M 
read_rnd_buffer_size = 2M
join_buffer_size = 16M
tmp_table_size = 512M 
max_heap_table_size = 512M
query_cache_type = 1
query_cache_size = 128M
max_connections = 60
thread_cache_size = 32
thread_pool_size = 16
open_files_limit = 65535
table_definition_cache = 8000
table_open_cache = 10000

### INNODB_ #
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 7200
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_file_per_table = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_buffer_pool_instances = 2
innodb_buffer_pool_size = 2000M 

EDIT:

Server is using SSD drives.

SHOW GLOBAL STATUS https://pastebin.com/raw/KqGjHwjA

SHOW GLOBAL VARIABLES https://pastebin.com/raw/97TnapvB

SHOW FULL PROCESS LIST https://pastebin.com/raw/E3HQ6HUE

SHOW ENGINE INNODB STATUS https://pastebin.com/raw/JWKYQe3w

ulimit -a https://pastebin.com/raw/ew4XpCud

iostat -xm 5 3 https://pastebin.com/raw/KJxSfnq7

htop https://i.imgur.com/5JzHBQo.png

Answer 1

Your ulimit -a report indicates Open Files is limited to 1024.

From OS command prompt, ulimit -n 96000 would enable higher Open Files limit dynamically.

For the higher limit to persist across OS stop/start, refer to this URL - https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/ - as an example. Please use 96000 for your values rather than the 500000 of the example. Your requirements may be different due to OS requirements.

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

read_rnd_buffer_size=128K  # from 2M to reduce handler_read_rnd_next RPS of 16,996
innodb_io_capacity=1000  # from 200 to enable higher IOPS to your SSD data device
innodb_buffer_pool_size=3G  # from 2G to reduce innodb_buffer_pool_reads RPHr of 4203
innodb_log_buffer_size=128M  # from 32M to support ~ 30 minutes RAM log before write

Observation, your statistics indicate 16 select_scan operations per second. Analyzing your Slow Query Log will give you clues for the tables needing indexes to complete faster.

For additional suggestions, view profile, Network profile for contact info and free downloadable Utility Scripts to assist with performance tuning.

There are many more opportunities available to improve performance for your instance.