COPY / extend ExclusiveLock

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: COPY / extend ExclusiveLock
Date: 2012-03-22 19:13:52
Message-ID: 20120322191352.GD3938@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Thoughts?

Thanks,

Stephen

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2012-03-22 19:36:50 Re: checkpoint patches
Previous Message Peter Geoghegan 2012-03-22 19:11:48 Re: Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)