pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMySQL is faster than PgSQL but a large margin in my program... any ideas why?
11+ messages / 8 participants
[nested] [flat]
* MySQL is faster than PgSQL but a large margin in my program... any ideas why?
@ 2005-12-22 02:03 Madison Kelly <linux@alteeve.com>
0 siblings, 1 reply; 11+ messages in thread
From: Madison Kelly @ 2005-12-22 02:03 UTC (permalink / raw)
To: pgsql-performance
Hi all,
On a user's request, I recently added MySQL support to my backup
program which had been written for PostgreSQL exclusively until now.
What surprises me is that MySQL is about 20%(ish) faster than PostgreSQL.
Now, I love PostgreSQL and I want to continue recommending it as the
database engine of choice but it is hard to ignore a performance
difference like that.
My program is a perl backup app that scans the content of a given
mounted partition, 'stat's each file and then stores that data in the
database. To maintain certain data (the backup, restore and display
values for each file) I first read in all the data from a given table
(one table per partition) into a hash, drop and re-create the table,
then start (in PostgreSQL) a bulk 'COPY..' call through the 'psql' shell
app.
In MySQL there is no 'COPY...' equivalent so instead I generate a
large 'INSERT INTO file_info_X (col1, col2, ... coln) VALUES (...),
(blah) ... (blah);'. This doesn't support automatic quoting, obviously,
so I manually quote my values before adding the value to the INSERT
statement. I suspect this might be part of the performance difference?
I take the total time needed to update a partition (load old data
into hash + scan all files and prepare COPY/INSERT + commit new data)
and devide by the number of seconds needed to get a score I call a
'U.Rate). On average on my Pentium3 1GHz laptop I get U.Rate of ~4/500.
On MySQL though I usually get a U.Rate of ~7/800.
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?
If it would help I can provide code samples. I haven't done so yet
because it's a little convoluded. ^_^;
Thanks as always!
Madison
Where the big performance concern is when
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum: http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in my program... any ideas why?
@ 2005-12-22 02:14 Stephen Frost <sfrost@snowman.net>
parent: Madison Kelly <linux@alteeve.com>
0 siblings, 2 replies; 11+ messages in thread
From: Stephen Frost @ 2005-12-22 02:14 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
* 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
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in my program... any ideas why?
@ 2005-12-22 02:44 Kevin Brown <blargity@gmail.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 11+ messages in thread
From: Kevin Brown @ 2005-12-22 02:44 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; pgsql-performance
On Wednesday 21 December 2005 20:14, 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. :)
Is fsync() on in your postgres config? If so, that's why you're slower. The
default is to have it on for stability (writes are forced to disk). It is
quite a bit slower than just allowing the write caches to do their job, but
more stable. MySQL does not force writes to disk.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in
@ 2005-12-22 03:33 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 1 reply; 11+ messages in thread
From: Luke Lonergan @ 2005-12-22 03:33 UTC (permalink / raw)
To: linux@alteeve.com; pgsql-performance
What version of postgres?
Copy has been substantially improved in bizgres and also in 8.1.
- Luke
--------------------------
Sent from my BlackBerry Wireless Device
-----Original Message-----
From: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Sent: Wed Dec 21 21:03:18 2005
Subject: [PERFORM] MySQL is faster than PgSQL but a large margin in my program... any ideas why?
Hi all,
On a user's request, I recently added MySQL support to my backup
program which had been written for PostgreSQL exclusively until now.
What surprises me is that MySQL is about 20%(ish) faster than PostgreSQL.
Now, I love PostgreSQL and I want to continue recommending it as the
database engine of choice but it is hard to ignore a performance
difference like that.
My program is a perl backup app that scans the content of a given
mounted partition, 'stat's each file and then stores that data in the
database. To maintain certain data (the backup, restore and display
values for each file) I first read in all the data from a given table
(one table per partition) into a hash, drop and re-create the table,
then start (in PostgreSQL) a bulk 'COPY..' call through the 'psql' shell
app.
In MySQL there is no 'COPY...' equivalent so instead I generate a
large 'INSERT INTO file_info_X (col1, col2, ... coln) VALUES (...),
(blah) ... (blah);'. This doesn't support automatic quoting, obviously,
so I manually quote my values before adding the value to the INSERT
statement. I suspect this might be part of the performance difference?
I take the total time needed to update a partition (load old data
into hash + scan all files and prepare COPY/INSERT + commit new data)
and devide by the number of seconds needed to get a score I call a
'U.Rate). On average on my Pentium3 1GHz laptop I get U.Rate of ~4/500.
On MySQL though I usually get a U.Rate of ~7/800.
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?
If it would help I can provide code samples. I haven't done so yet
because it's a little convoluded. ^_^;
Thanks as always!
Madison
Where the big performance concern is when
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum: http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in my
@ 2005-12-22 06:58 Madison Kelly <linux@alteeve.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 11+ messages in thread
From: Madison Kelly @ 2005-12-22 06:58 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: 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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in
@ 2005-12-22 07:02 Madison Kelly <linux@alteeve.com>
parent: Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 1 reply; 11+ messages in thread
From: Madison Kelly @ 2005-12-22 07:02 UTC (permalink / raw)
To: Luke Lonergan <LLonergan@greenplum.com>; +Cc: pgsql-performance
Luke Lonergan wrote:
> What version of postgres?
>
> Copy has been substantially improved in bizgres and also in 8.1.
> - Luke
Currently 7.4 (what comes with Debian Sarge). I have run my program on
8.0 but not since I have added MySQL support. I should run the tests on
the newer versions of both DBs (using v4.1 for MySQL which is also
mature at this point).
As others mentioned though, so far the most likely explanation is the
'fsync' being enabled on PostgreSQL.
Thanks for the reply!
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in
@ 2005-12-22 07:07 Luke Lonergan <llonergan@greenplum.com>
parent: Madison Kelly <linux@alteeve.com>
0 siblings, 0 replies; 11+ messages in thread
From: Luke Lonergan @ 2005-12-22 07:07 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-performance
Madison,
On 12/21/05 10:58 PM, "Madison Kelly" <linux@alteeve.com> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in
@ 2005-12-22 07:10 Luke Lonergan <llonergan@greenplum.com>
parent: Madison Kelly <linux@alteeve.com>
0 siblings, 1 reply; 11+ messages in thread
From: Luke Lonergan @ 2005-12-22 07:10 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
Madison,
On 12/21/05 11:02 PM, "Madison Kelly" <linux@alteeve.com> wrote:
> Currently 7.4 (what comes with Debian Sarge). I have run my program on
> 8.0 but not since I have added MySQL support. I should run the tests on
> the newer versions of both DBs (using v4.1 for MySQL which is also
> mature at this point).
Yes, this is *definitely* your problem. Upgrade to Postgres 8.1.1 or
Bizgres 0_8_1 and your COPY speed could double without even changing fsync
(depending on your disk speed). We typically get 12-14MB/s from Bizgres on
Opteron CPUs and disk subsystems that can write at least 60MB/s. This means
you can load 100GB in 2 hours.
Note that indexes will also slow down loading.
- Luke
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in
@ 2005-12-22 13:34 Markus Schaber <schabi@logix-tt.com>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 11+ messages in thread
From: Markus Schaber @ 2005-12-22 13:34 UTC (permalink / raw)
To: pgsql-performance
Hi, Madison,
Hi, Luke,
Luke Lonergan wrote:
> Note that indexes will also slow down loading.
For large loading bunches, it often makes sense to temporarily drop the
indices before the load, and recreate them afterwards, at least, if you
don't have normal users accessing the database concurrently.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in
@ 2005-12-23 02:44 Juan Casero <caseroj@comcast.net>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Juan Casero @ 2005-12-23 02:44 UTC (permalink / raw)
To: pgsql-performance
Agreed. I have a 13 million row table that gets a 100,000 new records every
week. There are six indexes on this table. Right about the time when it
reached the 10 million row mark updating the table with new records started
to take many hours if I left the indexes in place during the update. Indeed
there was even some suspicion that the indexes were starting to get corrupted
during the load. So I decided to fist drop the indexes when I needed to
update the table. Now inserting 100,000 records into the table is nearly
instantaneous although it does take me a couple of hours to build the indexes
anew. This is still big improvement since at one time it was taking almost
12 hours to update the table with the indexes in place.
Juan
On Thursday 22 December 2005 08:34, Markus Schaber wrote:
> Hi, Madison,
> Hi, Luke,
>
> Luke Lonergan wrote:
> > Note that indexes will also slow down loading.
>
> For large loading bunches, it often makes sense to temporarily drop the
> indices before the load, and recreate them afterwards, at least, if you
> don't have normal users accessing the database concurrently.
>
> Markus
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MySQL is faster than PgSQL but a large margin in
@ 2005-12-23 16:16 Vivek Khera <vivek@khera.org>
parent: Juan Casero <caseroj@comcast.net>
0 siblings, 0 replies; 11+ messages in thread
From: Vivek Khera @ 2005-12-23 16:16 UTC (permalink / raw)
To: pgsql-performance
On Dec 22, 2005, at 9:44 PM, Juan Casero wrote:
> Agreed. I have a 13 million row table that gets a 100,000 new
> records every
> week. There are six indexes on this table. Right about the time
> when it
i have some rather large tables that grow much faster than this (~1
million per day on a table with > 200m rows) and a few indexes.
there is no such slowness I see.
do you really need all those indexes?
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-12-23 16:16 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-22 02:03 MySQL is faster than PgSQL but a large margin in my program... any ideas why? Madison Kelly <linux@alteeve.com>
2005-12-22 02:14 ` Stephen Frost <sfrost@snowman.net>
2005-12-22 02:44 ` Kevin Brown <blargity@gmail.com>
2005-12-22 06:58 ` Re: MySQL is faster than PgSQL but a large margin in my Madison Kelly <linux@alteeve.com>
2005-12-22 07:07 ` Re: MySQL is faster than PgSQL but a large margin in Luke Lonergan <llonergan@greenplum.com>
2005-12-22 03:33 Re: MySQL is faster than PgSQL but a large margin in Luke Lonergan <LLonergan@greenplum.com>
2005-12-22 07:02 ` Re: MySQL is faster than PgSQL but a large margin in Madison Kelly <linux@alteeve.com>
2005-12-22 07:10 ` Re: MySQL is faster than PgSQL but a large margin in Luke Lonergan <llonergan@greenplum.com>
2005-12-22 13:34 ` Re: MySQL is faster than PgSQL but a large margin in Markus Schaber <schabi@logix-tt.com>
2005-12-23 02:44 ` Re: MySQL is faster than PgSQL but a large margin in Juan Casero <caseroj@comcast.net>
2005-12-23 16:16 ` Re: MySQL is faster than PgSQL but a large margin in Vivek Khera <vivek@khera.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox