Re: COPY and indices?

2012/3/13 François Beausoleil <francois@teksol.info>:
>
>
> Le mardi 13 mars 2012 à 10:48, Merlin Moncure a écrit :
>
>> 2012/3/12 François Beausoleil <francois@teksol.info (mailto:francois@teksol.info)>:
>> > Currently, I can sustain 30-40 writes per second on a Rackspace VPS. I know it's not the ideal solution, but
that'swhat I'm working with. Following vmstat, the server is spending 30 to 40% of it's time in iowait. I don't have
measurementsas to what files are touched, and I'd welcome suggestions to measure the time PostgreSQL actually spends
writingindices vs data. 
>> > >
>> >
>> >
>>
>>
>> you're almost certainly blocking on fsync. A real quick'n'dirty way
>> to confirm this (although it wont be as fast as COPY) would be to wrap
>> your inserts in a transaction. VMs tend to have really horrible
>> storage latency which can hurt postgres performance. Another option
>> would be to relax your commit policy (for example by flipping
>> synchronous_commit) if that fits within your safety requirements.
>>
>
>
> I already applied the tricks you have here: I have a transaction, and synchronous_commit is off. I also have
checkpoint_segmentsset to 96, and 10 minutes. 
>
> I'll go with the COPY, since I can live with the batched requirements just fine.

30-40 'in transaction' i/o bound inserts is so slow as to not really
be believable unless each record is around 1 megabyte because being in
transaction removes storage latency from the equation.  Even on a
crappy VM.  As a point of comparison my sata workstation drive can do
in the 10s of thousands.  How many records are you inserting per
transaction?

merlin