Why postgresql's single insert statement is faster than mysql?

Question

I wonder why postgresql's single "insert" statement is completely faster than MySQL's when autocommit is turned on? The following is the same code that I did on them.

Version:

MySQL: 5.6.10 
PostgreSQL:  PostgreSQL 9.3.2 on x86_64

Table definition:

MySQL:

CREATE TABLE `user` (
  `username` char(36) NOT NULL,
  `password` char(32) NOT NULL,
  `register_time` datetime NOT NULL,
  `mobile_phone` char(11) NOT NULL,
  `is_admin` enum('yes','no') NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

PostgreSQL:

CREATE TYPE ytt_enum AS ENUM ('yes','no');
CREATE TABLE ytt."user" (
  "username" char(36) NOT NULL,
  "password" char(32) NOT NULL,
  "register_time" timestamp  NOT NULL,
  "mobile_phone" char(11) NOT NULL,
  "is_admin" ytt_enum NOT NULL,
  PRIMARY KEY ("username")
) ;

Store functions:

MySQL:

DELIMITER $$

USE `t_girl`$$

DROP PROCEDURE IF EXISTS `sp_insert_user_simple`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_user_simple`(
IN f_input      INT
)
BEGIN
    DECLARE i      INT DEFAULT 0;
    WHILE i <= f_input
    DO
        INSERT INTO t_girl.user (`username`, `password`, register_time,mobile_phone,is_admin) 
        VALUES (UUID(),MD5(REPLACE(UUID(),'-','')),DATE_SUB(NOW(),INTERVAL CEIL(RAND()*40)  DAY),CEIL(RAND()*10000)+13800000000,IF(TRUNCATE(RAND()*2,0)=1,'yes','no'));
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

PostgreSQL:

CREATE  or replace function sp_insert_user_simple(
IN f_input      INT
) returns void as
$ytt$
    declare i int := 0;
    v_username char(36);
    v_password char(32);
    v_register_time timestamp;
    v_mobile_phone char(11);
    v_is_admin ytt_enum;
BEGIN

    WHILE i < f_input
    loop
        v_username := uuid_generate_v1();
        v_password :=MD5(REPLACE(uuid_generate_v1()::text,'-',''));
        v_register_time := to_timestamp((now() - '1 day'::interval*ceil(random()*40))::text,'yyyy-mm-dd HH24:MI:SS');
        v_mobile_phone :=CEIL(RANDOM()*10000)+13800000000;
        v_is_admin := (case TRUNC(RANDOM()*2) when 1  then 'yes' else'no' end)::ytt_enum;
        INSERT INTO ytt.user (username, password, register_time,mobile_phone,is_admin) 
        VALUES (v_username,v_password,v_register_time,v_mobile_phone,v_is_admin);
        i := i + 1;
    END loop;
END;
$ytt$language plpgsql;

Parameters:

MySQL:
innodb_buffer_pool_size=32M
bulk_insert_buffer_size=20M
autocommit=on
PostgreSQL:
shared_memory=32M
effective_cache_size=20M
autocommit=on

Test result: MySQL:

mysql> call sp_insert_user_simple(10000);
Query OK, 1 row affected (1 min 9.93 sec)

PostgreSQL:

ytt=# select sp_insert_user_simple(10000); 
 sp_insert_user_simple 
-----------------------

(1 row)

Time: 1177.043 ms

The above test shows that MySQL's running time is 69.93 second but PostgreSQL's is only 1.17 second.
Any answer is appreciated. Thanks.

Unrelated, but: (a) You can turn on ANSI mode in MySQL then use sensible quoting; and (b) Don't use the char type, use varchar. char is awful. Also, both these procedures are unnecessary, you should be able to write them as a single insert statement with no need for a procedure. Anyway: I'm wondering if MySQL's autocommit commits each insert individually within the procedure (I haven't used MySQL's procedures). PostgreSQL doesn't, it does a single commit for the entire function at the end. That would easily explain the difference. — Craig Ringer
– Craig Ringer, Commented Mar 15, 2014 at 2:57
Thanks, I got it. I'll try to rewrite a test code using application language. — Moon_of_father
– Moon_of_father, Commented Mar 15, 2014 at 3:15

Craig Ringer · Accepted Answer · 2014-03-15 03:58:58Z

2

I think what's happening here is that MySQL's procedures may be doing a commit for each individual INSERT. In PostgreSQL the whole procedure commits at the end; procedures cannot run individual transactions. (I'm not totally sure if that's how MySQL's procedures behave with autocommit=off, but it seems to be from a quick look at the docs).

You should really be doing this INSERT as a single statement anyway, using INSERT ... SELECT:

CREATE  or replace function sp_insert_user_simple(
    IN f_input integer
) returns void AS $$
    INSERT INTO ytt.user (username, password, register_time,mobile_phone,is_admin) 
    SELECT
        uuid_generate_v1(),
        MD5(REPLACE(uuid_generate_v1()::text,'-','')),
        to_timestamp((now() - '1 day'::interval*ceil(random()*40))::text,'yyyy-mm-dd HH24:MI:SS'),
        CEIL(RANDOM()*10000)+13800000000,
        case TRUNC(RANDOM()*2) when 1  then 'yes' else'no' end
    FROM generate_series(1,$1);
$$ LANGUAGE sql;

(I assume this is dummy user-data generation?).

Also, use char, not varchar. char is an awful data type and should be avoided. Also, consider using boolean for the is_admin column.

answered Mar 15, 2014 at 3:58

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ypercubeᵀᴹ Over a year ago

char is not that bad - in MySQL.

Moon_of_father Over a year ago

Thanks for your reply. I now understand postgresql's special autocommit feature.

Nick · Accepted Answer · 2014-03-15 04:10:38Z

0

Try testing simple insert queries:

INSERT INTO ytt.user (username, password) VALUES ('a', 'b');

and loop it in a procedure, thus making time measurement more accurate. Avoid using other built-in functions (like rng and timestamp), since their perfomance can differ singnificantly on a large sample, unless, of course, you tested those first.

answered Mar 15, 2014 at 4:10

Nick

1238 bronze badges

Collectives™ on Stack Overflow

Why postgresql's single insert statement is faster than mysql?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related