MySQL Performance Degrades during Concurrent Stored Proc Execution

Question

Machine Specs : macOS Sierra, Version 10.12.5, Memory 8 GB

MySQL Version: MySQL Enterprise Server - Advanced Edition (Commercial)3 innodb_version : 5.7.18

Problem : 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.

Query

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

Explain Extended Plan is below ( Indexes are perfectly used)

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

Stored Proc:

CREATE PROCEDURE `fetchEmployeeDetails`()
BEGIN

DECLARE l_StartDate DATE DEFAULT CURRENT_DATE()-INTERVAL 7 DAY;
DECLARE l_EndDate DATE DEFAULT CURRENT_DATE();

DECLARE I_Range INT DEFAULT 0;

WHILE I_Range < 30 DO   

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

    SET I_Range = I_Range + 1;

 END WHILE;

END;

The same SP when called CONCURRENTLY from a different terminal/session, both the SPs take nearly 50 seconds.

The sample is run on both MySQL Community Version and also on Enterprise edition ( with thread pool ON) on the same Mac Machine, but the issue persists. Please let me know if you have any ways to resolve this performance issue.

CREATE TABLE `employee` (
  `COMPANY_ID` int(11) NOT NULL,
  `COMPANY_NAME` varchar(255) CHARACTER SET utf8 NOT NULL,
  `EMPLOYEE_ID` int(11) NOT NULL,
  `BEGIN_DATE` timestamp NULL DEFAULT NULL,
  `END_DATE` timestamp NULL DEFAULT NULL,
  KEY `Emp_Name_Dt_idx` (`EMPLOYEE_ID`,`BEGIN_DATE`,`END_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `department` (
  `Employee_ID` int(11) NOT NULL,
  `Department_ID` int(11) NOT NULL,
  KEY `deptmnt_dept_idx` (`Department_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

_Please_ provide `SHOW CREATE TABLE` and/or qualify all columns so we know which table `END_date` is in ! Without such, we can't give you an answer! — Rick James, Jul 06 '17 at 23:18
Why are you running the loop 30 times? It seems to give the same result each time! — Rick James, Jul 06 '17 at 23:21
Thanks Rick for looking into this. I am running 30 times so as to get me a window period (i.e. 3s) to get another concurrent execution run. Also added the create table DDLs to the question as asked. — DeeJay14, Jul 07 '17 at 10:07
Also note that concurrent execution of some of the Stored procedures does hang, even though they work fine ( < 1s response) when executed individually. The above scenarios just replicates the issue. — DeeJay14, Jul 07 '17 at 10:18
Did you try a performance comparison in other Operating Systems (other than mac)? — SoorajSethumadhavan, Jul 11 '17 at 14:21
Thanks Sooraj. The performance is good in Windows and Unix. It has issues only with Mac — DeeJay14, Jul 17 '17 at 14:47

score 0 · Answer 1 · answered Jul 07 '17 at 15:52

Please re-think the schema.

A table named employee should talk about one person. What you have seems to have multiple rows if the person was employed multiple times. Perhaps it should be called tenure, and be separate from employee.

department should be a department. What you have is a many:many mapping between employees and departments. But, can an employee belong to multiple departments? If not, then it is a 1:many mapping, and the table is not needed. Instead have department_id in the employee table.

And, you need a PRIMARY KEY on each table:

PRIMARY KEY(employee_id) -- on employee
PRIMARY KEY(employee_id, start_date, end_date) -- on tenure
PRIMARY KEY(department_id) -- on department
PRIMARY KEY(department_id, employee_id) -- on many:many mapping
INDEX(department_id) -- on employee, if 1:many
INDEX(employee_id, department_id) -- if many:many

More discussion on many:many .

After all of that, we can continue the discussion of your timing anomaly, if it still exists.

Hi Rick, As I mentioned, the employee and department tables were constructed so as to replicate and share the problematic scenario. We can change the name of the table from 'employee' to 'salary', if that makes sense. The employee(salary)-department relation may not be a normalised one, but what we have is a problem which occurs only during concurrent execution of stored procedures. This happens not only for this scenario, but also for others. — DeeJay14, Jul 10 '17 at 08:31

MySQL Performance Degrades during Concurrent Stored Proc Execution

1 Answers1