psql is slow and it does not take much resources

Javier de la Torre wrote:

Hi all,

I've been searching around for an answer to this, but I coulnd't find
anything. So here we go.

I am running PostgreSQL 8.1.3 on Red Hat on an Intel server with 2GB
of RAM and lot of free HD space.

I have a very large dump file, more then 4GB, to recreate a database.
When I run:

psql -Uuser -q -dmydatabase <mydump.sql &

It works, but is slow, it inserts about 1.000 record in 10 seconds.
But the curious thing is that if I check the resources of the server
it seems that psql is not using them much. "top" says that postgres is
using around 2% of the processor and psql around 1% so that server is
only 3% used. "top" also says that more or less 48% idle and
50%waiting...

In the other hand the memory seems to be very high...

What could be the problem? Is that the server is just slow reading
from the file? It has a 300GB SCSI drive.

Is there any way to make this work faster? I would like to recreate
the db at least once a month, but this taked too much time...

Thanks in advance for any advice.

Javier.

How is the MyDump created? is it inserts? Or copy's?

--
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry.Rosenman@pervasive.com
Web: www.pervasive.com

It is inserts.

I create the inserts myself with a Python programmed I hace created to
migrate MySQL databases to PostgreSQL (by th way if someone wants
it...)

Thanks.

Javier.

Javier de la Torre wrote:

It is inserts.

I create the inserts myself with a Python programmed I hace created to
migrate MySQL databases to PostgreSQL (by th way if someone wants
it...)

Ok, that makes *EACH* insert a transaction, with all the overhead.

You need to batch the inserts between BEGIN;/COMMIT; pairs, or, better
yet
set it up as a COPY.

--
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry.Rosenman@pervasive.com
Web: www.pervasive.com

On Wednesday 03 May 2006 16:12, Larry Rosenman wrote:

Javier de la Torre wrote:

It is inserts.

I create the inserts myself with a Python programmed I hace created
to migrate MySQL databases to PostgreSQL (by th way if someone wants
it...)

Ok, that makes *EACH* insert a transaction, with all the overhead.

You need to batch the inserts between BEGIN;/COMMIT; pairs, or, better
yet set it up as a COPY.

I'm using essentially the same approach for my custom backup/restore
procedure. I also found it a very slow process. But when I wrapped up
each table script (ie. 20-30k of INSERTs) the time it took to populate
the entire database went down from about half an hour to 50 seconds.
Very impressive ;-)

However, I'm wondering if there's a practical limit to how many rows you
can insert within one transaction?
--
Leif Biberg Kristensen :: Registered Linux User #338009
http://solumslekt.org/ :: Cruising with Gentoo/KDE

Yes,

Thanks. I am doing this now...

Is definetly faster, but I will also discover now if there is a limit
in a transaction side... I am going to try to insert into one single
transaction 60 million records in a table.

In any case I still don't understand how why PostgreSQL was not taking
resources before without the transaction. If it has to create a
transaction per insert I understand it will have to do more things,
but why is not taking all resources from the machine? I mean, why is
it only taking 3% of them.

Javier.

Javier de la Torre wrote:

Yes,

Thanks. I am doing this now...

Is definetly faster, but I will also discover now if there is a limit
in a transaction side... I am going to try to insert into one single
transaction 60 million records in a table.

In any case I still don't understand how why PostgreSQL was not taking
resources before without the transaction. If it has to create a
transaction per insert I understand it will have to do more things,
but why is not taking all resources from the machine? I mean, why is
it only taking 3% of them.

I'll bet your WAL disk is mostly WAIT-I/O, waiting for the WAL log
flushes at end of transaction.

LER

--
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry.Rosenman@pervasive.com
Web: www.pervasive.com

On Wed, May 03, 2006 at 04:28:10PM +0200, Leif B. Kristensen wrote:

However, I'm wondering if there's a practical limit to how many rows you
can insert within one transaction?

There's a limit of (I think) 2-4 billion commands per transaction. Each
command can insert any number of tuples.

So if you're doing one tuple per command that limits you to a few
billion inserts per transaction. Ofcourse, COPY is always faster
still...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Great! Then there will be no problems.

I would use COPY but I think I can not. While moving from MySQL to
PostgreSQL I am also transforming a pair of fields, latitude,
longitude, into a geometry field, POINT, that is understood for
Potgis. I though I will not be able to use COPY when inserting data
with functions.

Thanks again all.

Javier.

Martijn van Oosterhout <kleptog@svana.org> writes:

However, I'm wondering if there's a practical limit to how many rows you
can insert within one transaction?

There's a limit of (I think) 2-4 billion commands per transaction. Each
command can insert any number of tuples.

So if you're doing one tuple per command that limits you to a few
billion inserts per transaction. Ofcourse, COPY is always faster
still...

If you have any deferred triggers (including foreign keys) on the table
then the practical limit is likely to be a lot less, say in the few
millions. However, in a bulk data load situation you probably don't
need to have such triggers; it's better to establish the constraint
after you load the data.

BTW, has the OP read
http://developer.postgresql.org/docs/postgres/populate.html
? Much of this thread seems to be rehashing that page ...

regards, tom lane

Javier de la Torre wrote:

Great! Then there will be no problems.

I would use COPY but I think I can not. While moving from MySQL to
PostgreSQL I am also transforming a pair of fields, latitude,
longitude, into a geometry field, POINT, that is understood for
Potgis. I though I will not be able to use COPY when inserting data
with functions.

I definitely recommend using copy if you are inserting a large amount of
data into postgis.
we use something like the following python code to read from a csv file
and insert into pgsql.
I can't remember the rate it works at but it was much quicker than
anything else we tried.

def insertData( header, delimiter, filename, table, SRID, dbname, user,
host ):

f = open(filename, 'r')

# Open a new process to enter data (~10x faster than psycopg)
process = os.popen('psql %s %s -c "COPY %s (geom, elevation) from
stdin;" -h %s' % (dbname, user, table, host), "w")

for a in f:
unpackline = a[:-1].split(delimiter)
easting, northing, elevation = unpackline
process.write("SRID=%s;POINT(%s %s)\t%s\n" %( SRID, easting,
northing, elevation))

f.close()
process.close()

Hope that helps,

Joe

Javier de la Torre wrote:

Great! Then there will be no problems.

I would use COPY but I think I can not. While moving from MySQL to
PostgreSQL I am also transforming a pair of fields, latitude,
longitude, into a geometry field, POINT, that is understood for
Potgis. I though I will not be able to use COPY when inserting data
with functions.

I think you'd get better results COPYing into a (temporary) table (say
it's called 'fish') and then inserting your data into your table using
INSERT (...) SELECT ... FROM fish; with the necessary conversions.

I think it'll be still faster than a couple million seperate INSERTs,
and it gives you the opportunity to fix mistakes if you issue the above
command inside a transaction.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

On Wed, May 03, 2006 at 04:28:10PM +0200, Leif B. Kristensen wrote:

However, I'm wondering if there's a practical limit to how many rows you
can insert within one transaction?

I believe transactions are limited to 4B commands, so the answer would
be 4B rows.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Wed, May 03, 2006 at 04:43:15PM +0200, Javier de la Torre wrote:

Yes,

Thanks. I am doing this now...

Is definetly faster, but I will also discover now if there is a limit
in a transaction side... I am going to try to insert into one single
transaction 60 million records in a table.

In any case I still don't understand how why PostgreSQL was not taking
resources before without the transaction. If it has to create a
transaction per insert I understand it will have to do more things,
but why is not taking all resources from the machine? I mean, why is
it only taking 3% of them.

Because a server has more than just CPU as a resource. In this case you
were undoubtedly limited by the drives that pg_xlog is on.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Wed, May 03, 2006 at 04:11:36PM +0200, Javier de la Torre wrote:

It is inserts.

I create the inserts myself with a Python programmed I hace created to
migrate MySQL databases to PostgreSQL (by th way if someone wants
it...)

Have you looked at http://pgfoundry.org/projects/my2postgres/ ?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Thursday 04 May 2006 22:30, Jim C. Nasby wrote:

On Wed, May 03, 2006 at 04:28:10PM +0200, Leif B. Kristensen wrote:

However, I'm wondering if there's a practical limit to how many rows
you can insert within one transaction?

I believe transactions are limited to 4B commands, so the answer would
be 4B rows.

That is definitely not the case. I routinely do around 36000 inserts
wrapped up in one transaction.

I know that there is one hard-wired limit due to the OID wrap-around
problem, at 2^31 commands in one transaction. But the practical limit
due to hardware resources is probably much lower.
--
Leif Biberg Kristensen :: Registered Linux User #338009
http://solumslekt.org/ :: Cruising with Gentoo/KDE

On Thu, May 04, 2006 at 10:58:24PM +0200, Leif B. Kristensen wrote:

On Thursday 04 May 2006 22:30, Jim C. Nasby wrote:

On Wed, May 03, 2006 at 04:28:10PM +0200, Leif B. Kristensen wrote:

However, I'm wondering if there's a practical limit to how many rows
you can insert within one transaction?

I believe transactions are limited to 4B commands, so the answer would
be 4B rows.

That is definitely not the case. I routinely do around 36000 inserts
wrapped up in one transaction.

Check your eyes or cleen your monitor. ;) I said 4B as in 4 *billion*.
And as Tom mentioned, if you have foreign keys or triggers each insert
will burn through multiple CIDs.

I know that there is one hard-wired limit due to the OID wrap-around
problem, at 2^31 commands in one transaction. But the practical limit
due to hardware resources is probably much lower.

This has nothing to do with OIDs, and in fact I don't believe there's
any intrinsic reason why you couldn't insert more than 2B records in a
table with OIDs so long as you don't have a unique index defined on it.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Thu, 2006-05-04 at 16:06, Jim C. Nasby wrote:

On Thu, May 04, 2006 at 10:58:24PM +0200, Leif B. Kristensen wrote:

I know that there is one hard-wired limit due to the OID wrap-around
problem, at 2^31 commands in one transaction. But the practical limit
due to hardware resources is probably much lower.

This has nothing to do with OIDs, and in fact I don't believe there's
any intrinsic reason why you couldn't insert more than 2B records in a
table with OIDs so long as you don't have a unique index defined on it.

But there is a truth there. Most people are far more likely to be
limited by their machine's hardware than by the database at this point.

Of course, SOMEONE will find a way to hit the limit.

On Thursday 04 May 2006 23:06, Jim C. Nasby wrote:

On Thu, May 04, 2006 at 10:58:24PM +0200, Leif B. Kristensen wrote:

On Thursday 04 May 2006 22:30, Jim C. Nasby wrote:

I believe transactions are limited to 4B commands, so the answer
would be 4B rows.

That is definitely not the case. I routinely do around 36000 inserts
wrapped up in one transaction.

Check your eyes or cleen your monitor. ;) I said 4B as in 4 *billion*.

I've wiped my glasses now :-)

I'm not used to reading B for Billion, and I thought I saw an 8. (Here
in Scandinavia, we adhere to the French convention of 1000 millions = 1
milliard. 1000 milliards = 1 billion.) But it looks like we do agree on
the order of magnitude after all.

Another interpretation leads to reading 4B as 4 bytes, and given that a
byte equals 8 bits, 4B would be the width of a 32-bit integer.
--
Leif Biberg Kristensen :: Registered Linux User #338009
http://solumslekt.org/ :: Cruising with Gentoo/KDE