Madison, On 12/21/05 10:58 PM, "Madison Kelly" <linux@xxxxxxxxxxx> wrote: > 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. You can run either storage method with MySQL, I expect the default is MyISAM. COPY performance with or without fsync was sped up recently nearly double in Postgresql. The Bizgres version (www.bizgres.org, www.greenplum.com) is the fastest, Postgres 8.1.1 is close, depending on how fast your disk I/O is (as I/O speed increases Bizgres gets faster). fsync isn't really an "issue" and I'd suggest you not run without it! We've found that "fdatasync" as the wal sync method is actually a bit faster than fsync if you want a bit better speed. So, I'd recommend you upgrade to either bizgres or Postgres 8.1.1 to get the maximum COPY speed. > Here is the MySQL table. The main difference from the PostgreSQL > table is that the 'varchar(255)' columns are 'text' columns in PostgreSQL. Shouldn't matter. > 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 | | > +-----------------+--------------+------+-----+---------+-------+ What's a bigint(20)? Are you using "numeric" in Postgresql? > 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. Again - fsync is a small part of the performance - you will need to run either Postgres 8.1.1 or Bizgres to get good COPY speed. - Luke
