From: | Havasvölgyi Ottó <h(dot)otto(at)freemail(dot)hu> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | feeding big script to psql |
Date: | 2005-08-02 01:11:22 |
Message-ID: | 001101c596ff$18d227e0$8000a8c0@OTTO |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
A generated a big SQL script (about 20 Mb), and fed it to psql. I was very
surprised that within a minute psql became quite slow. There were areas,
where less than 10 row were inserted in a second.
This is on a WinXP machine with local server 8.0.3, and only I use it.
Looking at the log files of PG the commands are executed fast.
I have monitored psql with te TaskManager, and it was at 45-50% CPU (this is
Hyperthreaded, so 50% is the max for one thread), and the memory
allocation/deallocation was very active, even about +-2Mb/sec.
The command were simple create table and insert commands chunked into
several lines like this:
CREATE TABLE aeloleg(
vevo CHAR(6),
szallito INTEGER,
datum DATE,
hatarido DATE,
vevo_nev CHAR(30),
ir_szam INTEGER,
helyseg CHAR(20),
cim CHAR(20),
befizetes INTEGER,
terheles INTEGER,
hitel INTEGER,
rendeles INTEGER,
jel CHAR(1),
trans INTEGER,
szoveg TEXT,
storno BOOLEAN) WITHOUT OIDS;
The insert commands for one table were surrounded by BEGIN and COMMIT like
this:
CREATE
BEGIN
INSERT
INSERT
...
INSERT
COMMIT
I know it would be faster with COPY, but this is extremly slow, and the
bottleneck is psql.
What is the problem?
Regards,
Otto
From | Date | Subject | |
---|---|---|---|
Next Message | hisatomo | 2005-08-02 01:28:19 | could not bind IPv6 socket |
Previous Message | Tom Lane | 2005-08-02 00:32:17 | Re: Check postgres compile-time options |