Very slow insert speed
Hi all,
I understand that the problem is by me, but do not have any idea how to
search it.
My configuration:
Dual Athlon MP 1800 MHz,
1 GB RAM,
Mandrake 8.2 with standart postgres install.
I try to insert 5000 records in a flat table with 10 column.
I am working with jdbc for my test.
The speed that I get is max. 20 rows per second inserted !!!
I think it is very low and I make the next step by inserting anoder 5000
rows. The time was 6 rows/sec.
Processor usage was 100% from the first processor and it is the maximum
.
By the next atempt I got one record per second.
How can I detect my problem and what is the normal speed for inserts?
Many thanks in advance.
Do you have an index on the table? If so that could be slowing things
down as the db has to update the index constantly. If you are going to
be doubling the number of entries in the table you might want to drop
the index before hand and then recreate it when your done. You might
also want to wrap the inserts into several large transactions (say every
500 or 1000 inserts) rather than incurring the transactional overhead on
every insert.
Robert Treat
Show quoted text
On Tue, 2002-08-13 at 07:19, pginfo wrote:
Hi all,
I understand that the problem is by me, but do not have any idea how to
search it.My configuration:
Dual Athlon MP 1800 MHz,
1 GB RAM,Mandrake 8.2 with standart postgres install.
I try to insert 5000 records in a flat table with 10 column.
I am working with jdbc for my test.
The speed that I get is max. 20 rows per second inserted !!!I think it is very low and I make the next step by inserting anoder 5000
rows. The time was 6 rows/sec.
Processor usage was 100% from the first processor and it is the maximum
.By the next atempt I got one record per second.
How can I detect my problem and what is the normal speed for inserts?
Many thanks in advance.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Hello,
First, if you are doing multiple inserts per session, make sure you
batch the inserts via a transaction (say 10 at a time).
Second make sure that if you are fsync=True/On then use open_sync as the
sync type.
J
pginfo wrote:
Show quoted text
Hi all,
I understand that the problem is by me, but do not have any idea how to
search it.My configuration:
Dual Athlon MP 1800 MHz,
1 GB RAM,Mandrake 8.2 with standart postgres install.
I try to insert 5000 records in a flat table with 10 column.
I am working with jdbc for my test.
The speed that I get is max. 20 rows per second inserted !!!I think it is very low and I make the next step by inserting anoder 5000
rows. The time was 6 rows/sec.
Processor usage was 100% from the first processor and it is the maximum
.By the next atempt I got one record per second.
How can I detect my problem and what is the normal speed for inserts?
Many thanks in advance.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster