Re: MySQL is faster than PgSQL but a large margin in my

Stephen Frost wrote:
> * Madison Kelly (linux(at)alteeve(dot)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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-