Message-ID: <43AA4EAB.9080507@alteeve.com>
Date: Thu, 22 Dec 2005 01:58:51 -0500
From: Madison Kelly <linux@alteeve.com>
User-Agent: Debian Thunderbird 1.0.2 (X11/20051002)
MIME-Version: 1.0
To: Stephen Frost <sfrost@snowman.net>
CC: pgsql-performance@postgresql.org
Subject: Re: MySQL is faster than PgSQL but a large margin in my
References: <43AA0966.6090401@alteeve.com>
	<20051222021418.GB6026@ns.snowman.net>
In-Reply-To: <20051222021418.GB6026@ns.snowman.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-