Thread: COPY / extend ExclusiveLock
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
largerextensions for larger tables? Or make larger extensions when tables are growing very quickly? I haven't
lookedat the code, but I'm guessing we extend relations when they're full (that part makes sense..), but we extend them
anitty-bitty bit at a time, which very quickly ends up being not fast enough for the processes that want to get data
intothe 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.
Thoughts?
Thanks,
Stephen
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
Jim Nasby <jim@nasby.net> writes:
> On 3/22/12 2:13 PM, Stephen Frost wrote:
>> 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 know that there's been discussion around this. Way back in the day we extended relations one page at a time. I
don'tremember if that was changed or not.
No, it's still on the to-do list. One issue is that you probably don't
want individual insertion queries taking big response-time hits to do
large extensions themselves --- so somehow the bulk of the work needs to
be pushed to some background process.
regards, tom lane