Re: MySQL is faster than PgSQL but a large margin in my - Mailing list pgsql-performance

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