Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-314

60-80k connections causing empty reply for select

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.20-19, Not 5.5.x, Not 5.6.x
    • Fix Version/s: 5.7.21-21
    • Component/s: None
    • Labels:

      Description

      Percona Server returns empty result for select query for many connections.
      In order to reproduce the issue it's important to execute some query for each connection.

      Setup:
      make sure that /proc/$(pgrep -xn mysqld)/limits shows correct limits.

      I've started mysql with: docker run -it -e MYSQL_ALLOW_EMPTY_PASSWORD=1 percona:latest
      and fixed ulimit with prlimit tool:

      root@dev:~ # prlimit --nofile=100000 -p $(pgrep -xn mysqld)
      root@dev:~ # prlimit --nofile=100000 -p 91273
      

      It's important to have RAM enough RAM and/or enable overcommit:

      ps -eo rss,vsz,pid,cmd|grep mysqld
      9668024 40908476 mysqld
      
      sysctl vm.overcommit_ratio=800 vm.overcommit_memory=1 vm.max_map_count=200000 kernel.pid_max=100000
      

      On mysql side setup is simple, no startup parameters, set max_connections in runtime:

      set global max_connections=90000;
      create database test;
      create table test.t(id int) engine=innodb;
      insert into test.t values (1),(2),(3),(4);
      

      In order to create required number of connections I've used python3:

      #!/usr/bin/python3
      # apt-get update ; apt-get install -y python3-mysql.connector
      import mysql.connector
      import time
      conns = []
      for i in range(80000):
          conn = mysql.connector.connect(user='root', database='mysql', unix_socket='/var/run/mysqld/mysqld.sock')
          conns.append(conn)
          cursor = conn.cursor()
          cursor.execute("SELECT * FROM test.t LIMIT 1")
          for id in cursor:
              print(id)
          cursor.close()
          if i % 1000 == 0:
              print("current {}".format(i))
      
      print("connected")    
      time.sleep(86400)
      

      Output:

      (1,)
      (1,)
      (1,)
      (1,)
      (1,)
      (1,)
      current 66000
      current 67000
      current 68000
      current 69000
      current 70000
      current 71000
      current 72000
      current 73000
      current 74000
      current 75000
      current 76000
      current 77000
      current 78000
      current 79000
      connected
      

      As you can see from some point select not returning 1 as expected.

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                zsolt.parragi Zsolt Parragi
                Reporter:
                nickolay.ihalainen Nickolay Ihalainen
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 hour, 38 minutes
                  1h 38m