User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Wed, 21 Dec 2005 23:07:22 -0800
Subject: Re: MySQL is faster than PgSQL but a large margin in
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Madison Kelly" <linux@alteeve.com>, "Stephen Frost" <sfrost@snowman.net>
cc: pgsql-performance@postgresql.org
Message-ID: <BFCF90AA.194E9%llonergan@greenplum.com>
Thread-Topic: [PERFORM] MySQL is faster than PgSQL but a large margin in
 my
Thread-Index: AcYGxVa/D8+MMfkBQf2EHzkChoHvrAAAQPYs
In-Reply-To: <43AA4EAB.9080507@alteeve.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Madison,

On 12/21/05 10:58 PM, "Madison Kelly" <linux@alteeve.com> wrote:

> Ah, that makes a lot of sense (I read about the 'fsync' issue before,
> now that you mention it). I am not too familiar with MySQL but IIRC
> MyISAM is their open-source DB and InnoDB is their commercial one, ne?
> If so, then I am running MyISAM.

You can run either storage method with MySQL, I expect the default is
MyISAM.

COPY performance with or without fsync was sped up recently nearly double in
Postgresql.  The Bizgres version (www.bizgres.org, www.greenplum.com) is the
fastest, Postgres 8.1.1 is close, depending on how fast your disk I/O is (as
I/O speed increases Bizgres gets faster).

fsync isn't really an "issue" and I'd suggest you not run without it! We've
found that "fdatasync" as the wal sync method is actually a bit faster than
fsync if you want a bit better speed.

So, I'd recommend you upgrade to either bizgres or Postgres 8.1.1 to get the
maximum COPY speed.

>    Here is the MySQL table. The main difference from the PostgreSQL
> table is that the 'varchar(255)' columns are 'text' columns in PostgreSQL.

Shouldn't matter.
 
> mysql> DESCRIBE file_info_1;
> +-----------------+--------------+------+-----+---------+-------+
> | Field           | Type         | Null | Key | Default | Extra |
> +-----------------+--------------+------+-----+---------+-------+
> | file_group_name | varchar(255) | YES  |     | NULL    |       |
> | file_group_uid  | int(11)      |      |     | 0       |       |
> | file_mod_time   | bigint(20)   |      |     | 0       |       |
> | file_name       | varchar(255) |      |     |         |       |
> | file_parent_dir | varchar(255) |      | MUL |         |       |
> | file_perm       | int(11)      |      |     | 0       |       |
> | file_size       | bigint(20)   |      |     | 0       |       |
> | file_type       | char(1)      |      |     |         |       |
> | file_user_name  | varchar(255) | YES  |     | NULL    |       |
> | file_user_uid   | int(11)      |      |     | 0       |       |
> | file_backup     | char(1)      |      | MUL | i       |       |
> | file_display    | char(1)      |      |     | i       |       |
> | file_restore    | char(1)      |      |     | i       |       |
> +-----------------+--------------+------+-----+---------+-------+

What's a bigint(20)?  Are you using "numeric" in Postgresql?
 
>    I will try turning off 'fsync' on my test box to see how much of a
> performance gain I get and to see if it is close to what I am getting
> out of MySQL. If that does turn out to be the case though I will be able
> to comfortably continue recommending PostgreSQL from a stability point
> of view.

Again - fsync is a small part of the performance - you will need to run
either Postgres 8.1.1 or Bizgres to get good COPY speed.

- Luke