Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.41
-
None
-
ubuntu 18.04
Description
background: it's 92G/20 CPU server with ubuntu 18.04
mysql Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
issue:
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
|
it takes 20 seconds to connect mysql.
IMPORTANT: it's not always like that, it happens when we have more visits. we use php-fpm and not PERSISTENT=true for PDO since that's localhost connection, it uses sockets (so that's not TCP or network issue).
usually it takes 0.0X seconds.
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
it seems alright.
mysql conf:
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
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
we have skip-name-resolve so it's not DNS issue
mysql status sorry it's too large so I made it in gist: https://gist.github.com/fayland/97050f70cb258a41f1fa1bd3046df4d2
it would be great that someone can point us the direction to check. if you need any more info, we'll be glad to provide.
Thanks