Skip site navigation (1) Skip section navigation (2)

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

From: Madison Kelly <linux(at)alteeve(dot)com>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: MySQL is faster than PgSQL but a large margin in my
Date: 2005-12-22 06:58:51
Message-ID: 43AA4EAB.9080507@alteeve.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

In response to

Responses

pgsql-performance by date

Next:From: Madison KellyDate: 2005-12-22 07:02:47
Subject: Re: MySQL is faster than PgSQL but a large margin in
Previous:From: Jignesh K. ShahDate: 2005-12-22 04:07:47
Subject: Re: What's the best hardver for PostgreSQL 8.1?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group