Обсуждение: 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 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: >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 > >