Re: Performance - Mailing list pgsql-novice

From Ron Chmara
Subject Re: Performance
Date
Msg-id 39377622.C4584051@opus1.com
Whole thread Raw
In response to Performance - fsync-off  (Thomas Weholt <Thomas@cintra.no>)
List pgsql-novice
Thomas Weholt wrote:
>
> Hi,
> I just got my Postgres-database up and running, putting in data using a
> Python-script, using the latest version of the PyGres-module by
> Darcy@druid.net. It works great!

Python is pretty. But it can suffer from poor scripting, from excessive
overhead (quite common in OO).

> But insertion seems slow, even though I
> just got a few indexes.

How slow is it?

> How can I turn off the fsync-variable? There must be
> a way to turn it off when starting the database, like postmaster -x where x
> is some command-line option or a setting in a config-file etc.

-F
http://www.postgresql.org/mhonarc/pgsql-general/1999-11/msg00127.html

> I haven`t
> found anything in my search so far. What will this do in terms of
> performance ( 1-5 times ??? )

See:
http://www.postgresql.org/mhonarc/pgsql-general/1999-12/msg00476.html

> and is it considered safe? ( I`m running Linux)

as long as you don't crash or lose power. :-)

> How does PostgreSQL compare in terms of speed compared to other databases,
> commercial products included??

It's slower than MySQL on simple tables, but that's because you can do
much more complex tables.

> I need to insert about 2000-10000 records into a database. I`m using Python
> to do this. Is there a way to use some form of bulk copy that will do this
> faster than plain-old insert for each and every record?

On an extremely fast system, with Fsync off, this should take less than
30 seconds. On slower systems, or with fsync on, your performance will
degrade greatly. I've seen 468 machines running at 2 inserts a second...

Aside from that backend speed, you might try converting the text data
you have into SQL _first_, and then feeding that in as a file. If you
are reading a line, parsing, inserting, reading the next line, parsing,
inserting... well, you get the picture. That loop will cost you.

-Ronabop

--
--2D426F70|759328624|00101101010000100110111101110000
Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
which is currently in LinuxPPC land.  Your bopping may vary. Do not eat
contents. Cape does not enable wearer to fly. Use only as directed.

pgsql-novice by date:

Previous
From: Thomas Weholt
Date:
Subject: Bulk-copy?
Next
From: Alfred Perlstein
Date:
Subject: Re: Performance - fsync-off