Magento 2的MYSQL优化

Question

我运行我的magento 2项目在5核心10 my内存虚拟机。我做了一些基准测试，我发现我的DB查询需要相当长的时间来执行。这个网站的流量几乎为零。Mysql正在5.7.31-34 Percona服务器上运行。数据库大小1.7gb。有人能发现一些明显的错误并给我一些建议吗？

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

此刻my.cnf是..。

[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 

编辑：

服务器正在使用SSD驱动器。

显示全局状态https://pastebin.com/raw/KqGjHwjA

显示全局变量https://pastebin.com/raw/97TnapvB

显示完整的进程列表https://pastebin.com/raw/E3HQ6HUE

显示引擎INNODB状态https://pastebin.com/raw/JWKYQe3w

超极限-a https://pastebin.com/raw/ew4XpCud

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

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

Answer 1

您的ulimit -a报告指出，“打开文件”的限制为1024。

从OS命令提示符中，ulimit -n 96000将动态地支持更高的打开文件限制。

要获得在OS停止/启动过程中持久化的更高限制，请参考这个URL -- https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/ --作为示例。请使用96000作为您的值，而不是该示例的500000。由于操作系统的需求，您的需求可能会有所不同。

每秒速率= RPS

关于my.cnf 米舍尔德部分应考虑的建议

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

观察到，您的统计数据显示每秒有16个select_scan操作。分析缓慢的查询日志将为需要更快完成索引的表提供线索。

其他建议，查看配置文件，网络配置文件的联系信息和免费下载实用程序脚本，以协助性能调优。

有更多的机会可以提高您的实例的性能。