Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20671

mysql slow connect on localhost

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.1.41
    • None
    • Server
    • 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

      Attachments

        Activity

          People

            Unassigned Unassigned
            fayland Fayland Lam
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.