From: Luke Lonergan <llonergan@greenplum.com>
To: Madison Kelly <linux@alteeve.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: MySQL is faster than PgSQL but a large margin in
Date: Wed, 21 Dec 2005 23:07:22 -0800
Message-ID: <BFCF90AA.194E9%llonergan@greenplum.com> (raw)
In-Reply-To: <43AA4EAB.9080507@alteeve.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: llonergan@greenplum.com, linux@alteeve.com, sfrost@snowman.net
Subject: Re: MySQL is faster than PgSQL but a large margin in
In-Reply-To: <BFCF90AA.194E9%llonergan@greenplum.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox