PgSqlLoader.Buffersize and Remote/Local performance

Tiago_G · Post by **Tiago_G** » Wed 14 Apr 2010 09:59

Hello,

I am developing an application where i need to insert lots of records (several thousands) as fast as possible. To do this i am using PgSqlLoader.
By trying several values for PgSqlLoader.Buffersize i see that i get the best performance when it's about 256. Being the default 256K (and in the version before 32MB), i am quite puzzled. Shouldn't bigger values be faster? What exactly does this BufferSize do?

I also see ~2x performance gain if i execute my application on the server. The connection i have is Gigabit Ethernet, so i don't think 2x gain is to be expected. The bandwidth is not the bottleneck. What should i be looking into? Round trip-times? What other factors might create this difference between local/remote usage?

Thanks

Regards,
Tiago

Shalex · Post by **Shalex** » Fri 16 Apr 2010 15:15

1. The best value for the BufferSize property can be defined experimentally in your environment. We will inverstigate the reason why greater value of BufferSize not always leads to increasing the PgSqlLoader performance.

2. I have compared the performance of PgSqlLoader locally (on my machine) and remotely (via local network). I've got 20% performance gain only. The round trip for local requests is faster than for remote ones. Probably, the reason of the big difference you recieved is not only ping time, but internal implementation of PostgreSQL server for network request processing or some network latency (please contact you system administrator).