Bug #88315 5.7 INSERTS is 20% slower than 5.6

• Submitted: 1 Nov 2017 11:18
• Modified: 9 Nov 2017 14:24
• Reporter: Yiftach Kaplan
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.7.19
• OS: CentOS
• CPU Architecture: Any

[1 Nov 2017 11:18] Yiftach Kaplan

Description:
MySQL 5.7 is a lot slower than 5.6 when we are just trying to insert a lot of rows to a simple table.

Maybe its a duplicate of https://bugs.mysql.com/bug.php?id=75981

How to repeat:
With auto commit ON, open the client and run:

DROP TABLE IF EXISTS table_one;
    CREATE TABLE table_one
    (
      id BIGINT PRIMARY KEY AUTO_INCREMENT,
      value BIGINT
    )
      CHARACTER SET "UTF8"
      ENGINE = InnoDb;

    DROP PROCEDURE IF EXISTS go;
    DELIMITER DTR
    CREATE PROCEDURE go()
      SQL SECURITY INVOKER
      BEGIN
        DECLARE count  INT DEFAULT 0;
        DECLARE size INT DEFAULT 500000;

        SET count = 0;
        SET @start_one = UNIX_TIMESTAMP(CURTIME(6));
        WHILE count < size DO
             INSERT INTO table_one VALUES(0, count);
             SET count = count + 1;
        END WHILE;
        SET @end_one = UNIX_TIMESTAMP(CURTIME(6));

        SELECT @end_one - @start_one AS 'Total time';
      END
    DTR
    DELIMITER ;

    CALL go();

On 5.6.37. and 5.7.19 on the same machine (or two machines with similar hardware).

[5 Nov 2017 7:12] MySQL Verification Team

Hello Yiftach Kaplan,

Thank you for the report and test case.
This is most likely duplicate of Bug #75981.

Thanks,
Umesh

[9 Nov 2017 14:24] Yiftach Kaplan

Thanks Umesh,
Is there a workaround? 75981 had been open for a while and 30% performance hit is not trivial.