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
rememberif that was changed or not.
 
-- 
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net