Re: COPY / extend ExclusiveLock

On 3/22/12 2:13 PM, Stephen Frost wrote:
> Greetings,
>
> I've recently become a bit annoyed and frustrated looking at this in
> top:
>
> 23296 postgres 20 0 3341m 304m 299m S 12 0.9 1:50.02 postgres: sfrost gis [local] COPY waiting
> 24362 postgres 20 0 3353m 298m 285m D 12 0.9 1:24.99 postgres: sfrost gis [local] COPY
> 24429 postgres 20 0 3340m 251m 247m S 11 0.8 1:13.79 postgres: sfrost gis [local] COPY waiting
> 24138 postgres 20 0 3341m 249m 244m S 10 0.8 1:28.09 postgres: sfrost gis [local] COPY waiting
> 24153 postgres 20 0 3340m 246m 241m S 10 0.8 1:24.44 postgres: sfrost gis [local] COPY waiting
> 24166 postgres 20 0 3341m 318m 313m S 10 1.0 1:40.52 postgres: sfrost gis [local] COPY waiting
> 24271 postgres 20 0 3340m 288m 283m S 10 0.9 1:34.12 postgres: sfrost gis [local] COPY waiting
> 24528 postgres 20 0 3341m 290m 285m S 10 0.9 1:21.23 postgres: sfrost gis [local] COPY waiting
> 24540 postgres 20 0 3340m 241m 236m S 10 0.7 1:15.91 postgres: sfrost gis [local] COPY waiting
>
> Has anyone been working on or considering how to improve the logic
> around doing extends on relations to perhaps make larger extensions
> for larger tables? Or make larger extensions when tables are growing
> very quickly?
>
> I haven't looked at the code, but I'm guessing we extend relations
> when they're full (that part makes sense..), but we extend them an
> itty-bitty bit at a time, which very quickly ends up being not fast
> enough for the processes that want to get data into the table.
>
> My gut feeling is that we could very easily and quickly improve this
> situation by having a way to make larger extensions, and then using
> that method when we detect that a table is growing very quickly.

I know that there's been discussion around this. Way back in the day we extended relations one page at a time. I don't remember if that was changed or not.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net