From: 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 my
Date: Thu, 22 Dec 2005 01:58:51 -0500
Message-ID: <43AA4EAB.9080507@alteeve.com> (raw)
In-Reply-To: <20051222021418.GB6026@ns.snowman.net>
References: <43AA0966.6090401@alteeve.com>
<20051222021418.GB6026@ns.snowman.net>
Stephen Frost wrote:
> * Madison Kelly (linux@alteeve.com) wrote:
>
>> If the performace difference comes from the 'COPY...' command being
>>slower because of the automatic quoting can I somehow tell PostgreSQL
>>that the data is pre-quoted? Could the performance difference be
>>something else?
>
>
> I doubt the issue is with the COPY command being slower than INSERTs
> (I'd expect the opposite generally, actually...). What's the table type
> of the MySQL tables? Is it MyISAM or InnoDB (I think those are the main
> alternatives)? IIRC, MyISAM doesn't do ACID and isn't transaction safe,
> and has problems with data reliability (aiui, equivilant to doing 'fsync
> = false' for Postgres). InnoDB, again iirc, is transaction safe and
> whatnot, and more akin to the default PostgreSQL setup.
>
> I expect some others will comment along these lines too, if my response
> isn't entirely clear. :)
>
> Stephen
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.
Here is the MySQL table. The main difference from the PostgreSQL
table is that the 'varchar(255)' columns are 'text' columns in PostgreSQL.
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 | |
+-----------------+--------------+------+-----+---------+-------+
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.
Thanks!!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum: http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
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: linux@alteeve.com, sfrost@snowman.net
Subject: Re: MySQL is faster than PgSQL but a large margin in my
In-Reply-To: <43AA4EAB.9080507@alteeve.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