MySQL Bugs: #87108: MySQL concurrent execution of queries are extremely slow

Bug #87108	MySQL concurrent execution of queries are extremely slow
Submitted:	19 Jul 2017 6:51	Modified:	27 Jul 2017 15:33
Reporter:	Dukes Pitt	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	MacOS (macOS Sierra, Version 10.12.5, Memory 8 GB)
Assigned to:		CPU Architecture:	Any

Description:
When same stored procedure is called concurrently by different sessions, the response time gets delayed a lot.

The below SQL is the one which takes 0.12s per execution. For simulating the issue, I am running the below SQL in a while loop which iterates for 30 times thereby taking an average of 3 seconds per Stored procedure execution.

However, when I run the same stored proc CONCURRENTLY from a different terminal/session, both the stored procs take nearly 50 seconds.

The tables are created in innodb and the buffer size and read/write io's are set with normal values.

How to repeat:
Create EMPLOYEE and SALARY table

Employee table count : 142289
Salary table count : 947

 SELECT MIN(BEGIN_date) , MAX(END_date) 
     FROM employee e, SALARY d 
  WHERE e.employee_ID = d.employee_ID 
       AND d.Department_ID = 72641 ;

Composite Index is there on Employee table (Employee_ID, Begin_Date, End_Date)
Simple index on  Salary Table ( Department_iD)

----+-------------+-------+------------+------+------------------+----------    --------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys    | key               | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------------------+------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | ref  | salary_dept_idx | salary_dept_idx | 4       | const                         |  808 |   100.00 | NULL        |
|  1 | SIMPLE      | e     | NULL       | ref  | Emp_Name_Dt _idx | Emp_Name_Dt _idx | 4       | employee_db.d.Employee_ID |  156 |   100.00 | Using index |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------------------+------+----------+-------------+

Hi!

Stored routines do add some additional locking, so , please, can you try running two queries in parallel, with different constant values and let us know whether concurrency speed is any better ???

Next, I do hope that you are using InnoDB SE for both tables. If not, please let us know what storage engine are you using ??? 

If you do not get any concurrency speed execution improvement with plain queries and if you are using InnoDB SE, then please upload a dump of both tables to this bug report by using "Files" tab.

Thanks in advance.

Thanks for looking into  the issue. But I don't think it is a problem with the stored proc locking. 
Reason : I created a separate duplicate schema (database) with its own tables and ran the stored procedure from that schema as well as the old schema. Even then it got stuck.  Both the databases were there in the same server.

For Eg : 
Server MySQL_XYZ,    Database: DB_A,    Tables: EmpA, SalaryA,     SP :    StoredProcA
Server MySQL_XYZ,,   Database: DB_B,    Tables: EmpB, SalaryB,     SP :    StoredProcB

If I execute StoredProcA and StoredProcB together, then performance deteriorates.
Kindly check

Hi !

I was not able to repeat your results ...... This is the time report what I got for a single query that you ran:

real	0m4.328s
user	0m0.007s
sys	0m0.004s

When I ran the same query in two parallel threads, I have got these results:

real	0m4.006s
user	0m0.007s
sys	0m0.004s

and

real	0m4.599s
user	0m0.007s
sys	0m0.004s

Hence ...... everything is OK .....

WOW.Thanks for the quick reply Have you run it in Mac Sierra?

Hi!

iMac with macOS Sierra is the only machine that I have. If I need to test on some other OS, I have to use company resources.

I tested on both 10.12.5 and 10.12.6 and got very similar results. I have run the queries from mysql CLI in bash shell.

MySQLSlap executions for the same query is as shown. This shows clear deterioration during concurrent calls.  Is there any reason to say why average time jumped from 0.097s to 2.028s when another session is run concurrently?

CONCURRENCY = 1
APPLEMAC:~ root# sudo mysqlslap  --user=root --password --host=localhost  --concurrency=1 --iterations=30 --create-schema=employee_db --query=" SELECT MIN(BEGIN_date) , MAX(END_date) FROM employee e, SALARY d WHERE e.employee_ID = d.employee_ID AND d.Department_ID = 72641 ;"

Benchmark
	Average number of seconds to run all queries: 0.097 seconds
	Minimum number of seconds to run all queries: 0.096 seconds
	Maximum number of seconds to run all queries: 0.099 seconds
	Number of clients running queries: 1
	Average number of queries per client: 1

CONCURRENCY = 2
APPLEMAC:~ root# sudo mysqlslap  --user=root --password --host=localhost  --concurrency=2 --iterations=30 --create-schema=employee_db --query=" SELECT MIN(BEGIN_date) , MAX(END_date) FROM employee e, SALARY d WHERE e.employee_ID = d.employee_ID AND d.Department_ID = 72641 ;"

Benchmark
Average number of seconds to run all queries: 2.028 seconds
	Minimum number of seconds to run all queries: 1.976 seconds
	Maximum number of seconds to run all queries: 2.082 seconds
	Number of clients running queries: 2
	Average number of queries per client: 1

CONCURRENCY = 3
APPLEMAC:~ root# sudo mysqlslap  --user=root --password --host=localhost  --concurrency=3 --iterations=30 --create-schema=employee_db --query=" SELECT MIN(BEGIN_date) , MAX(END_date) FROM employee e, SALARY d WHERE e.employee_ID = d.employee_ID AND d.Department_ID = 72641 ;"

Benchmark
Average number of seconds to run all queries: 6.100 seconds
	Minimum number of seconds to run all queries: 6.012 seconds
	Maximum number of seconds to run all queries: 6.245 seconds
	Number of clients running queries: 3
	Average number of queries per client: 1

Hi!

I would not recommend using mysqlslap for concurrency checking .....

For low concurrency use background tasks and for high, sysbench ......