Stephen Frost wrote:
* Madison Kelly (linux@xxxxxxxxxxx) 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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
