GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL performance test results using employee database

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL performance test results using employee database

    I've used the http://dev.mysql.com/doc/employee/en/index.html database schema and data.

    I've tried these tests with mysqlslap.
    Code:
    mysqlslap --user="root" --concurrency=100 --iterations={{#iterations}} --delimiter=";" --create-schema="employees" --query="{{a-query-here-}}"
    **UPDATE**
    OS: Slackware 14.0 x64 with 32bit alien multilib support
    MySQL: Ver 14.14 Distrib 5.5.27, for Linux (x86_64) using readline 5.1

    RAM: 6GB
    CPU: Intel(R) Core(TM) i5-2410M CPU @ 2.30GHz


    Structure( I've added only the ones I've used ):
    Code:
    CREATE TABLE `employees` (
      `emp_no` int(11) NOT NULL,
      `birth_date` date NOT NULL,
      `first_name` varchar(14) NOT NULL,
      `last_name` varchar(16) NOT NULL,
      `gender` enum('M','F') NOT NULL,
      `hire_date` date NOT NULL,
      PRIMARY KEY (`emp_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
    CREATE TABLE `dept_emp` (
      `emp_no` int(11) NOT NULL,
      `dept_no` char(4) NOT NULL,
      `from_date` date NOT NULL,
      `to_date` date NOT NULL,
      PRIMARY KEY (`emp_no`,`dept_no`),
      KEY `emp_no` (`emp_no`),
      KEY `dept_no` (`dept_no`),
      CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
      CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
    CREATE TABLE `salaries` (
    `emp_no` int(11) NOT NULL,
    `salary` int(11) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    `name` text NOT NULL,
    PRIMARY KEY (`emp_no`,`from_date`),
    KEY `emp_no` (`emp_no`),
    CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
    CREATE TABLE `titles` (
      `emp_no` int(11) NOT NULL,
      `title` varchar(50) NOT NULL,
      `from_date` date NOT NULL,
      `to_date` date DEFAULT NULL,
      PRIMARY KEY (`emp_no`,`title`,`from_date`),
      KEY `emp_no` (`emp_no`),
      CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    Number of rows in tables:
    departments 9
    dept_emp 332,289
    dept_manager 24
    employees 300,584
    salaries 2,844,092
    titles 443,803



    First test query:
    Code:
    SELECT employees.* FROM  employees
    LEFT JOIN dept_emp ON ( dept_emp.emp_no =  employees.emp_no )
    LEFT JOIN salaries ON ( salaries.emp_no =  salaries.emp_no )
    WHERE employees.first_name
    LIKE '%Jo%'
    AND salaries.from_date > '1993-01-21'
    AND salaries.to_date < '1998-01-01'
    LIMIT 0, 100
    20 iterations
    Average number of seconds to run all queries: 10.109 seconds
    Minimum number of seconds to run all queries: 9.946 seconds
    Maximum number of seconds to run all queries: 10.542 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1


    100 iterations
    Average number of seconds to run all queries: 10.037 seconds
    Minimum number of seconds to run all queries: 9.963 seconds
    Maximum number of seconds to run all queries: 10.239 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1

    Then I've altered the table salaries and added a name TEXT column and populated it with its employee related first_name column value with this sql query:
    Code:
    UPDATE salaries 
    LEFT JOIN ` ON ( `salaries`.`emp_no` =  `employees`. 
    SET `salaries`.`name` = `employees`.`first_name`
    Then I"ve repeated the previous 2 tests in which I've modified the LIKE part. In the first test it was referencing the employee.first_name. I've replaced it with the newly created and populated salaries.name, like this:
    Code:
    SELECT `employees`.* FROM  `employees`
    LEFT JOIN `dept_emp` ON ( `dept_emp`.`emp_no` =  `employees`.`emp_no` )
    LEFT JOIN `salaries` ON ( `salaries`.`emp_no` =  `salaries`.`emp_no` )
    WHERE `salaries`.`name` LIKE "%Jo%"
    AND `salaries`.`from_date` > '1993-01-21'
    AND `salaries`.`to_date` < '1998-01-01'
    LIMIT 0, 100
    And here are the results:
    20 Iterations

    Average number of seconds to run all queries: 1.505 seconds
    Minimum number of seconds to run all queries: 1.452 seconds
    Maximum number of seconds to run all queries: 1.557 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1


    100 Iterations
    Average number of seconds to run all queries: 1.546 seconds
    Minimum number of seconds to run all queries: 1.444 seconds
    Maximum number of seconds to run all queries: 1.729 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1

    The number of iterations obviously doesn't have a significant impact. But after moving the first_name column to to salaries tables, the overall number of seconds is drastically reduced.

    Why is the execution time difference so large between the two queries?

    Last edited by keeperhood; 04-16-2013, 10:57 AM.

  • #2
    The queries are different, you are now running an inner join (not a left join) on the salaries table. That means that the execution order may be different and that an index on a dates field in the salaries table may now be used.

    btw. SHOW CREATE TABLE is more readable for showing the table structure (especially for indices).

    Comment


    • #3
      Originally posted by gmouse View Post
      The queries are different, you are now running an inner join (not a left join) on the salaries table. That means that the execution order may be different and that an index on a dates field in the salaries table may now be used
      .
      How am i now running an inner join when the only thing that's changed is the LIKE parameter?

      btw. SHOW CREATE TABLE is more readable for showing the table structure (especially for indices).
      Thanks I've updated the structure.

      Comment


      • #4
        It becomes an inner join since `salaries`.`name` LIKE "%Jo%" does not match NULL values (so, the LEFT JOIN does not make sense).

        Comment


        • #5
          I guess I'll have to read more about mysql to fully understand that. The thing that bothers me is those 10.037 seconds Would that be a reasonable calculation result average with 100 users concurrently quering searches on 1 server?

          Comment


          • #6
            You definitely want queries to run faster, since users want instant results if they browse your website.

            Comment

            Working...
            X