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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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?


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster