☰

mysql 连接在 localhost 上有时很慢

tag-icon • tag-icon ubuntu mysql mariadb
mysql 连接在 localhost 上有时很慢

背景：这是一台 92G/20 CPU 的服务器，搭载 ubuntu 18.04

mysql Ver 15.1 Distrib 10.1.41-MariaDB，适用于 debian-linux-gnu (x86_64)，使用 readline 5.2

问题：

root@z:# time mysql -uroot -pXXX -e "select now()"
+---------------------+
| now()               |
+---------------------+
| 2019-09-25 14:10:24 |
+---------------------+

real    0m22.267s
user    0m0.000s
sys     0m0.006s

连接mysql需要20秒。

重要提示：情况并非总是如此，当我们有更多访问时就会发生这种情况。我们使用 php-fpm 而不是 PERSISTENT=true 用于 PDO，因为那是本地主机连接，它使用套接字（所以这不是 TCP 或网络问题）。

通常需要0.0X秒。

iostat：

    Linux 4.15.0-50-generic (fdb)   09/25/2019      _x86_64_        (20 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          48.22    0.00   17.26    0.79    3.08   30.65

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
loop0             0.00         0.00         0.00          8          0
sdb               0.09         0.27         2.20      24628     204340
sda             735.43      1740.89      5578.76  161421385  517281936

看起来还不错。

mysql 配置：

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
skip-external-locking
skip-host-cache
skip-name-resolve

bind-address            = 0.0.0.0

key_buffer_size         = 128M
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 32
wait_timeout            = 60
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options  = BACKUP
max_connections        = 30000
max_connect_errors     = 3000
#table_cache            = 64
#thread_concurrency     = 10
tmp_table_size          = 512M
max_heap_table_size     = 512M
table_open_cache        = 4096

performance_schema = on

innodb_buffer_pool_size=48G
innodb_log_file_size=6144M
innodb_buffer_pool_instances=20
innodb_purge_threads=8
innodb_flush_log_at_trx_commit=2

我们有 skip-name-resolve，所以这不是 DNS 问题

mysql 状态抱歉，它太大了所以我将其概括为：https://gist.github.com/fayland/97050f70cb258a41f1fa1bd3046df4d2

如果有人能告诉我们检查的方向就太好了。如果您需要更多信息，我们会很乐意提供。

谢谢

答案1

对 my.cnf [mysqld] 部分的建议

thread_cache_size=256  # from 32 for default with v 10.0.38
innodb_io_capacity=1900  # from 200 to enable higher IOPS to your SSD devices
read_buffer_size_256K  # from 128K to reduce handler_read_next RPS of 2,337,990
read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 1,982,624
innodb_flushing_avg_loops=5 # from 30 to reduce delays and reduce innodb_buffer_pool_pages_dirty of 23,455
thread_concurrency=18  # from 10 to use more of your 20 cores
innodb_log_buffer_size=2G  # from 8M to support about 7 log minutes in RAM

还有更多全局变量需要针对您的实例进行调整。免责声明：我是个人资料（网络个人资料）中提到的网站的内容作者，您可以在其中下载免费的实用程序脚本，并考虑其他分析服务。

相关内容