Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
5.7.20-19, Not 5.5.x, Not 5.6.x
-
None
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:
[email protected]:~ # prlimit --nofile=100000 -p $(pgrep -xn mysqld) [email protected]:~ # 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.
